5 ms·

Postgres tips for Rails developers

192 points by craigkerstiens 10y ago

misterbowfinger 10y ago
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?
- olivierlacan 10y ago
  Would you like to help introduce this change to Rails? I'd love to help if it's feasible.
- nateberkopec 10y ago
  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.
  - jhardcastle 10y ago
    But isn't that the idea behind Rails? Opinionated defaults with a config to change the setting somewhere?
    - nateberkopec 10y ago
      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”.
      - _jal 10y ago
        > 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.
    - mnarayan01 10y ago
      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.
nateberkopec 10y ago
A feature somewhat similar to `marginalia` is probably going to merged into Rails very soon: https://github.com/rails/rails/pull/26815 https://github.com/rails/rails/pull/26815
- craigkerstiens 10y ago
  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.
  - a12b 10y ago
    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 https://rorvswild.com
- throwaway91111 10y ago
  I still can't believe it took until rails 5 to land.
  - nateberkopec 10y ago
    Well, that's because no one opened a PR until now.
    - throwaway91111 10y ago
      Exactly.
ChairmanPao 10y ago
Here's the best tip: switch to Ecto / Elixir
theodorton 10y ago
Surprised the list doesn't mention PGHero (https://github.com/ankane/pghero https://github.com/ankane/pghero). It's been really useful at our company to find slow queries and identify missing indices.
- lm2s 10y ago
  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?
  - sudhirj 10y ago
    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.
    - duncanawoods 10y ago
      > 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?
      - MichaelBurge 10y ago
        There are two applications in scope: The application whose database is being monitored, and the application displaying the dashboard.
      - masklinn 10y ago
        > 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.
- thejosh 10y ago
  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.

192 points by craigkerstiens 10y ago

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?

olivierlacan 10y ago
Would you like to help introduce this change to Rails? I'd love to help if it's feasible.
nateberkopec 10y ago
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.
- jhardcastle 10y ago
  But isn't that the idea behind Rails? Opinionated defaults with a config to change the setting somewhere?
  - nateberkopec 10y ago
    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”.
    - _jal 10y ago
      > 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.
  - mnarayan01 10y ago
    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.

nateberkopec 10y ago

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

craigkerstiens 10y ago
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.
- a12b 10y ago
  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 https://rorvswild.com
throwaway91111 10y ago
I still can't believe it took until rails 5 to land.
- nateberkopec 10y ago
  Well, that's because no one opened a PR until now.
  - throwaway91111 10y ago
    Exactly.

ChairmanPao 10y ago

Here's the best tip: switch to Ecto / Elixir

theodorton 10y ago

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

lm2s 10y ago
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?
- sudhirj 10y ago
  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.
  - duncanawoods 10y ago
    > 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?
    - MichaelBurge 10y ago
      There are two applications in scope: The application whose database is being monitored, and the application displaying the dashboard.
    - masklinn 10y ago
      > 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.
thejosh 10y ago
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.