Database Topic Archive
Lock waits, deadlocks, blocking chains, idle transactions, and transaction hygiene.
A ported importer hit one duplicate key at row 312 of a 2-million-row batch and failed the entire run — because code that coasts on MySQL's statement-level rollback meets PostgreSQL's aborted-transaction state and dies. Savepoints look identical in the manual;…
A nightly job ran twice at 02:00 because GET_LOCK was held on the old MySQL primary and the failover moved the lock namespace with nothing in it. Advisory locks are application-level mutexes with no row attached, and each engine scopes,…
We flipped password_encryption and pg_hba.conf in the same change window and locked out 41 of 46 roles on an inherited cluster. The correct order is the reverse — rotate passwords while pg_hba still says md5, then flip it last.
Concurrent FOR SHARE locks and foreign key checks consume MultiXact IDs, and multixact has its own wraparound emergency separate from XID wraparound. How to monitor mxid_age(datminmxid), SLRU pressure, and vacuum's freezing before it stops your database.
At peak our p99 commit latency jumped from 4 ms to 2.8 seconds while CPU pinned at 100% and the disks sat idle. pg_stat_activity showed 1,400 sessions queued on wait_event='WALInsertLock' — this is how we found it and what actually…
One runaway query or one forgotten BEGIN can take a production PostgreSQL database down. Three timeout settings are the difference between an incident and a non-event.
MySQL made big-table DDL survivable with online DDL, gh-ost, and pt-osc. PostgreSQL DDL is often instant but brutally lock-sensitive. Here is how to translate your schema-change habits.
Long-running transactions hurt both engines, but differently: InnoDB accumulates undo history and purge lag, PostgreSQL accumulates dead tuples and marches toward xid wraparound. Know what to watch.
InnoDB and PostgreSQL both detect and break deadlocks, but they detect them differently, report them differently, and reward different diagnosis habits. A field guide for migrating teams.
Both MySQL 8.0 and PostgreSQL support SELECT ... FOR UPDATE SKIP LOCKED, which makes database-backed job queues practical. The claim patterns, locking details, and operational costs differ.