The author analyzed three PostgreSQL incidents involving performance degradation and locking issues. In the first case, a massive update caused table bloat, increasing disk reads. The team resolved this by using pg_repack, implementing batch updates, setting fillfactor to 80, and tuning autovacuum settings for specific tables.
The second incident involved a production outage caused by a long-running idle transaction that blocked an ALTER TABLE command. This created a queue of dependent queries. The team introduced a 3-second lock_timeout, set an idle_in_transaction_session_timeout of 5 minutes, and moved external HTTP calls outside of database transactions.
The third case featured frequent deadlocks in an enterprise platform due to concurrent updates on the same table in different orders. The team implemented a fixed update order using primary keys, added SKIP LOCKED to queue processing queries, and reduced transaction sizes to improve concurrency.
To prevent future issues, the team established monitoring for long-running transactions, idle connections, lock wait events, and table bloat. These metrics allow for proactive identification of bottlenecks before they impact end-user performance.
