Blogmark

Nikolay points out some of the things that are uniquely hard and common challenges for many PostgreSQL databases. This means it is also a good list of things to demonstrate expertise in because companies will likely need consulting help with each of these.

Five hard problems in #PostgreSQL:

1. LWLock contention of various kinds when various buffers overflow / thresholds exceeded
2. no simple path to sharding for OLTP
3. single-threaded processes (eg replication bottlenecks)
4. partitioning requires a lot of effort
5. upgrades too

I've done a number of PostgreSQL major version upgrades for companies. They are usually pretty uneventful because of how careful Postgres is with backward-compatibility. That said, they need to be carefully planned and executed to avoid or minimize downtime.

I explored partitioning with one client who had a massive events table. It is awkward and challenging to set up a partition after the fact. Ideally, you've designed the schema with partitioning from the start. In lieu of partitioning, we decided to do a swap-and-drop migration to remove hundreds of millions of old, unneeded rows.