Postgres tips for Rails developers | Hacker News Reader

Postgres tips for Rails developers

citusdata.com

192 pointscraigkerstiens21 comments

Surprised the list doesn't mention PGHero (https://github.com/ankane/pghero). It's been really useful at our company to find slow queries and identify missing indices.

Whoa. This seems very nice. I'm on my phone now and can fully check it, this works directly with PostgreSQL, right? It doesn't matter if the application layer is Rails or Django or whatever, correct?

Correct. Works directly off pg stats. It's one of the first items on the deployment checklist for every product or service in the company.

> It doesn't matter if the application layer is Rails or Django or whatever, correct?

>> Correct. Works directly off pg stats

Huh - PgHero is a rails app isn't it?

There are two applications in scope: The application whose database is being monitored, and the application displaying the dashboard.

> Huh - PgHero is a rails app isn't it?

pghero is a rails app but it just tracks a postgres db, you don't have to embed it into a rails application (though that's an option) you can run it independently.

That's awesome! So many great tools coming out in the past few years.

Do you (or anyone else) have any recommendations/tips for other tools? I really love Postgres.

I really like that you can just embed it into a Rails project.

I wonder why some of these tips, like a statement timeout, aren't the default behavior of Rails. Some things can't be, like pg-bouncer, which makes sense. But why not have pg_stat_statements running on PG? Or have log_lock_waits in each statement?

The appropriate setting for a statement timeout could really depend on the application. 5 seconds is probably sane for 90% of applications, but the other 5% may have a legitimate need for longer running statements.

But isn't that the idea behind Rails? Opinionated defaults with a config to change the setting somewhere?

Yes, but at some point you can't just go around installing all the possible extensions/gems/whatever that you think are "good". The default gemfile would be 300 lines long with "developer happiness" gems such as `oink`, `bullet`, etc etc etc.

Most of the things that Rails is "opinionated" about are far more arbitrary than this, like whether the database primary key should be “id”, “postId”, “posts_id”, or “pid”.

> Most of the things that Rails is "opinionated" about are far more arbitrary than this

To my eye, at least, it isn't a matter of being arbitrary, but rather not having an answer.

PK names don't matter to the machine, so long as everything agrees and modulo illegal values. Humans get massively opinionated, but the machine doesn't care.

Compare with: How long should a query take to run? No, I'm not going to tell you what kind of query, or the size of the dataset, or the hardware at the bottom of the stack, or what the app is actually doing.

It is like asking what specific clothes an arbitrary person should wear.

The key is, I think, the "opinionated" part. Something like "how long should the DB statement timeout be" isn't really something people would have a general opinion on.

Also this stuff is all heavily Postgres specific; I think @nateberkopec's estimate of 300 gems is probably too low, even if you limit it to only say the top five most popular datastores.

Would you like to help introduce this change to Rails? I'd love to help if it's feasible.

A feature somewhat similar to `marginalia` is probably going to merged into Rails very soon: https://github.com/rails/rails/pull/26815

Yes, very excited for this! It should replace much of the need for marginalia, really nice to see more of these key things needed to get visibility come into Rails.

If that may interest someone and to do some self promotion too :-)

I have built a product (rails app monitoring service) that shows the file:line for each SQL request: https://rorvswild.com

I still can't believe it took until rails 5 to land.

Well, that's because no one opened a PR until now.

Exactly.

Reply on news.ycombinator.com