21–30 of 93 posts
I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)
It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.
https://pganalyze.com/blog/postgres-14-performance-monitorin...
Earlier quoted context omitted.
It's not a limitation that affects most of users, but lack of official multi master support makes it less suitable for "always on" databases. But to be honest multi master is something hard to get right in ANY database. PostgreSQL is great for many use cases and good enough for many more!
Why would you need multi master to be "always on"?
Postgres has no built in support yet, but tools like STOLON exist.
Earlier quoted context omitted.
I don't remember specifics, but that Uber article made it clear that they didn't understand how Postgres worked and made some very basic mistakes. The whole thing made them seem surprisingly incompetent, although the general crappiness of Uber apps maybe should have tipped me off sooner.
In what way did they make basic mistakes?
In general it seems like they could have solved all of their problems within Postgres, but someone at Uber seemed to just know MySQL better and want to switch. They also made other major changes (like removing schemas from certain data stores) that likely gave them more of an advantage than switching could have.
I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)
I don't remember specifics, but that Uber article made it clear that they didn't understand how Postgres worked and made some very basic mistakes. The whole thing made them seem surprisingly incompetent, although the general crappiness of Uber apps maybe should have tipped me off sooner.
I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)
It's not a limitation that affects most of users, but lack of official multi master support makes it less suitable for "always on" databases. But to be honest multi master is something hard to get right in ANY database. PostgreSQL is great for many use cases and good enough for many more!
I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)
A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second.
The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially, the planner would sometimes wildly mis-estimate costs due to under-sampling, and would choose a bad plan. We fixed it in two different ways: 1) lower the auto-ANALYZE threshold; 2) increase the number of rows sampled when collecting statistics for the relevant column.
Again, this was “user error”. That said, it will probably happen again on the same or another query, because it’s hard to know if/when a query plan is about to change, and pg_hint_plan and similar are very heavy-handed solutions.
Earlier quoted context omitted.
I don't remember specifics, but that Uber article made it clear that they didn't understand how Postgres worked and made some very basic mistakes. The whole thing made them seem surprisingly incompetent, although the general crappiness of Uber apps maybe should have tipped me off sooner.
Speaking about mistakes, do you have any good resources on Postgres to ensure that you don't make the same kind of mistakes?
You should start by knowing how other people design the type of DB you're building. A reporting DB is going to work very differently from a transactional DB. If you're using PG's excellent JSONB support as an alternative to Mongo, that's also something you need to consider at the design stage.
pgbouncer is very commonly used in PG setups, to the point where I don't understand why it isn't included in the default build.
Sensible config generators:
- https://pgtune.leopard.in.ua/
- https://postgresqlco.nf/ (this one is more educational and works as a reference as well)
Security intro:
It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.
Pooling can be managed in client server processes instead of using pg_bouncer
Earlier quoted context omitted.
Why would you need multi master to be "always on"?
If for always on or High Availability setups you want two DBs running that are in constant communication, with every write being committed on both machines synchronously - so if one of them goes down the other can take its place immediately with no downtime. Postgres has no built in support yet, but tools like STOLON exist.