Why am I seeing deadlocks in PostgreSQL on a busy app?

Deadlocks in PostgreSQL usually mean two or more transactions are waiting on each other in a cycle:

• Transaction A holds a lock that Transaction B needs
• Transaction B holds a lock that Transaction A needs
• PostgreSQL detects the cycle and aborts one transaction to break it

On a busy app, this often happens because multiple requests are touching the same rows/tables but in different orders.

Common causes

1. Updating the same rows in different order

Example:

• Txn 1 updates row users id=1, then orders id=10
• Txn 2 updates row orders id=10, then users id=1

That can deadlock if both run at the same time.

2. Long transactions

If transactions stay open too long, they hold locks longer, increasing the chance of conflicts.

Common reasons:

• Doing application logic inside the transaction
• Calling external APIs inside the transaction
• Slow queries
• Large batch updates

3. Missing indexes on foreign keys / update filters

If PostgreSQL must scan many rows to find what to lock, it can hold locks longer and lock more rows than expected.

4. SELECT ... FOR UPDATE / row locking patterns

These can deadlock if different code paths lock rows in different orders.

5. Triggers, cascades, or constraint checks

Foreign key checks and triggers can lock additional rows/tables behind the scenes.

6. Concurrent bulk jobs

Background workers, cron jobs, and user traffic can conflict if they touch the same tables.

How to confirm the cause

Check PostgreSQL logs for the deadlock report. It usually shows:

• The SQL statements involved
• The lock types
• The waiting relationship

Also useful:

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_locks;

If logging is available, enable:

• log_lock_waits = on
• deadlock_timeout set reasonably low for diagnosis

How to reduce deadlocks

Keep transactions short

Do only database work inside the transaction.

Lock rows in a consistent order

If multiple code paths touch the same resources, always lock/update them in the same sequence.

Add the right indexes

Especially on:

• foreign key columns
• columns used in WHERE for updates/deletes

Retry deadlocked transactions

Deadlocks are usually transient. The standard fix is to catch PostgreSQL error 40P01 and retry the transaction safely.

Reduce contention

• Break large updates into smaller batches
• Move non-essential work out of the transaction
• Use SKIP LOCKED for worker queues if appropriate

Important note

Deadlocks are not usually a PostgreSQL bug — they’re a sign of concurrent access patterns that need tuning.

If you want, I can help you:

1. read a deadlock log,
2. diagnose a specific query pattern, or
3. suggest a retry strategy for your app stack.