5 ms·

It usually comes down to setting up a pgbouncer in front of the DB, from there you can mostly ignore the pooling layer in your application. It sure is one more

It usually comes down to setting up a pgbouncer in front of the DB, from there you can mostly ignore the pooling layer in your application. It sure is one more step that for MySQL, but we're talking production level DB, and in my experience it's a small task compared to the rest of what you will be doing to have your architecture properly running.

dotancohen 5y ago
But pgbouncer comes with it's own set of bugs and behaviours to be familiar with. Just look at the changelog for the bugs fixed (and yes, there are unfixed bugs as well): https://www.pgbouncer.org/ https://www.pgbouncer.org/ And the FAQs for the unusual behaviours to be aware of and find workarounds for: https://www.pgbouncer.org/faq.html https://www.pgbouncer.org/faq.html I would love nothing more than to be convinced. I find MySQL to be adequate, but far from great. Postgres is described as great by everyone who uses it, however I prefer adequate but reliable over great but leads to downtime.
- makeitdouble 5y ago
  On your main point, yes, you need to understand how pgbouncer works, it has its quirks and limitations. In particular a dev needs to be aware of how sessions and transactions work if they have some critical transactions. When introducing pgbouncer on one of our rails project we had to fine tune ActiveRecord to play nice, but once it's done there wasn't much to adjust from there. I'm with on how much it would help if it had a magic mode without these quirks, but in my experience you're still ahead of MySQL and its per session configuration quirks for instance. On a day to day basis, you'll rarely be scratching your head about how to deal with the pgbouncer pooling behavior you chose, an you'll still have the option to directly hit the DB on anything you don't trust the pooler to deal with appropriately. For the bugs, those are mostly on the security and configuration side. For the few years I've used it, I never hit a behavioral bug TBH.
  - dotancohen 5y ago
    Terrific, thank you

dotancohen 5y ago

But pgbouncer comes with it's own set of bugs and behaviours to be familiar with. Just look at the changelog for the bugs fixed (and yes, there are unfixed bugs as well): https://www.pgbouncer.org/ https://www.pgbouncer.org/ And the FAQs for the unusual behaviours to be aware of and find workarounds for: https://www.pgbouncer.org/faq.html https://www.pgbouncer.org/faq.html I would love nothing more than to be convinced. I find MySQL to be adequate, but far from great. Postgres is described as great by everyone who uses it, however I prefer adequate but reliable over great but leads to downtime.

makeitdouble 5y ago
On your main point, yes, you need to understand how pgbouncer works, it has its quirks and limitations. In particular a dev needs to be aware of how sessions and transactions work if they have some critical transactions. When introducing pgbouncer on one of our rails project we had to fine tune ActiveRecord to play nice, but once it's done there wasn't much to adjust from there. I'm with on how much it would help if it had a magic mode without these quirks, but in my experience you're still ahead of MySQL and its per session configuration quirks for instance. On a day to day basis, you'll rarely be scratching your head about how to deal with the pgbouncer pooling behavior you chose, an you'll still have the option to directly hit the DB on anything you don't trust the pooler to deal with appropriately. For the bugs, those are mostly on the security and configuration side. For the few years I've used it, I never hit a behavioral bug TBH.
- dotancohen 5y ago
  Terrific, thank you