Nikolay and Michael discuss ten dangerous Postgres related issues — ones that might be painful enough to get onto the CTO and even CEOs desk, and then what you can do proactively.
The ten issues discussed are:
- Heavy lock contention
- Bloat control and index maintenance
- Lightweight lock contention
- Transaction ID wraparound
- 4-byte integer PKs hitting the limit
- Replication limits
- Hard limits
- Data loss
- Poor HA choice (split brain)
- Corruption of various kinds
Some previous episodes they mentioned that cover the issues in more detail:
- PgDog https://postgres.fm/episodes/pgdog
- Performance cliffs https://postgres.fm/episodes/performance-cliffs
- Zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations
- Queues in Postgres https://postgres.fm/episodes/queues-in-postgres
- Bloat https://postgres.fm/episodes/bloat
- Index maintenance https://postgres.fm/episodes/index-maintenance
- Subtransactions https://postgres.fm/episodes/subtransactions
- Four million TPS https://postgres.fm/episodes/four-million-tps
- Transaction ID wraparound https://postgres.fm/episodes/transaction-id-wraparound
- pg_squeeze https://postgres.fm/episodes/pg_squeeze
- synchronous_commit https://postgres.fm/episodes/synchronous_commit
- Managed service support https://postgres.fm/episodes/managed-service-support
And finally, some other things they mentioned:
- A great recent SQL Server-related podcast episode on tuning techniques https://kendralittle.com/2024/05/20/erik-darling-and-kendra-little-rate-sql-server-performance-tuning-techniques/
- Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/
- Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg
- pg_stat_wal https://pgpedia.info/p/pg_stat_wal.html
- The benefit of lz4 and zstd for Postgres WAL compression (Small Datum blog, Mark Callaghan) https://smalldatum.blogspot.com/2022/05/the-benefit-of-lz4-and-zstd-for.html
- Split-brain in case of network partition (CloudNativePG issue/discussion) https://github.com/cloudnative-pg/cloudnative-pg/discussions/7462
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
Postgres FM is produced by:
- Michael Christofides, founder of pgMustard
- Nikolay Samokhvalov, founder of Postgres.ai
With credit to:
- Jessie Draws for the elephant artwork