Concurrency Best Practices

Concurrency hygiene prevents lock storms, bloat, and replication lag.

How to Use This List

Review pg_stat_activity weekly for idle in transaction.
Set timeouts in role or pool defaults.

A - Transaction Shape

Keep transactions short. No user pacing inside TX.
One business action per TX when possible. Clear failure domains.
Retry deadlocks and serialization failures. Exponential backoff with jitter.

B - Locks and Isolation

Default read committed unless stronger needed. Serializable for invariant-heavy writes.
FOR UPDATE SKIP LOCKED for work queues. Avoid advisory lock soup.
Index rows you lock via predicates. Prevent seq scan + lock all rows.

C - Housekeeping

Monitor long xact and replication slots. Slots retain WAL.
Autovacuum tuned for update-heavy tables. Dead tuples block visibility.
Set idle_in_transaction_session_timeout. Kill abandoned sessions.

FAQs

Is repeatable read enough?

Often for reports; serializable for strict invariants across concurrent writers.

Transactions Basics - boundaries
Long Transactions - WAL retention

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Concurrency Best Practices

Concurrency hygiene prevents lock storms, bloat, and replication lag.

How to Use This List

Review pg_stat_activity weekly for idle in transaction.
Set timeouts in role or pool defaults.

A - Transaction Shape

Keep transactions short. No user pacing inside TX.
One business action per TX when possible. Clear failure domains.
Retry deadlocks and serialization failures. Exponential backoff with jitter.

B - Locks and Isolation

Default read committed unless stronger needed. Serializable for invariant-heavy writes.
FOR UPDATE SKIP LOCKED for work queues. Avoid advisory lock soup.
Index rows you lock via predicates. Prevent seq scan + lock all rows.

C - Housekeeping

Monitor long xact and replication slots. Slots retain WAL.
Autovacuum tuned for update-heavy tables. Dead tuples block visibility.
Set idle_in_transaction_session_timeout. Kill abandoned sessions.

FAQs

Is repeatable read enough?

Often for reports; serializable for strict invariants across concurrent writers.

Transactions Basics - boundaries
Long Transactions - WAL retention

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Concurrency Best Practices

Buscar en la documentación

Concurrency Best Practices