What rules should I follow to prevent database deadlocks?
Acquire locks in one global order such as ascending PK, keep transactions short, target FOR UPDATE at the fewest rows, and retry the rest with backoff.
Tag
The PostgreSQL database: index strategies, the query planner, vacuum and performance. Questions under this tag, answered directly. (Page 2/2)
14 answered questions carry this tag.
Acquire locks in one global order such as ascending PK, keep transactions short, target FOR UPDATE at the fewest rows, and retry the rest with backoff.
Don't hand-roll failover: put promotion behind a Patroni + etcd quorum, and let a primary cut off from the majority demote itself by fencing.
Pick Transaction mode for a web fleet; the price is protocol-level prepared statements, so disable them in the app or turn on PgBouncer 1.21+ support.
Build one (user_id, status, created_at DESC) index with equality columns first and the sort column last, then drop the now-redundant user_id index.