Top ten dangerous issues

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:

1. Heavy lock contention
2. Bloat control and index maintenance  
3. Lightweight lock contention
4. Transaction ID wraparound
5. 4-byte integer PKs hitting the limit
6. Replication limits
7. Hard limits
8. Data loss
9. Poor HA choice (split brain)
10. 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