PostgreSQL Lock Contention: Finding and Resolving the Disturbance

Good evening. Your queries appear to be waiting.

They are not slow. They are not processing. They are sitting in a queue, each waiting for a lock held by another transaction. The EXPLAIN plan is perfect. The indexes are present. The query takes 2ms when it runs. But it has been waiting for 47 seconds because somewhere in the system, a lock is being held longer than it should be.

Lock contention is uniquely frustrating because it is invisible to most diagnostic tools. EXPLAIN ANALYZE does not show lock waits — it only measures execution time after the lock is acquired. Application monitoring shows elevated latency but cannot distinguish "slow query" from "query waiting for a lock." Your p99 latency spikes from 15ms to 48 seconds and the dashboard tells you nothing useful about why.

The diagnosis requires looking at pg_locks and pg_stat_activity directly. This is not knowledge that most application engineers carry, which is why lock contention incidents tend to last longer than they should. By the time someone thinks to query pg_locks, the on-call engineer has already restarted the application twice.

I should like to prevent that sequence of events. If you will permit me, I shall walk through the full landscape of PostgreSQL locking — how to detect it, how to read it, what causes it, and how to prevent the most common incidents from occurring in the first place.

A brief anatomy of PostgreSQL locks

PostgreSQL uses a multi-granularity locking system. There are table-level locks, row-level locks, page-level locks, advisory locks, and transaction ID locks. For the vast majority of contention issues, you will be dealing with table-level and row-level locks. The others are worth knowing about but rarely the source of production incidents.

Table-level locks form a hierarchy of eight levels, from the permissive AccessShareLock (acquired by every SELECT) to the total exclusion of AccessExclusiveLock (acquired by ALTER TABLE, DROP TABLE, and VACUUM FULL). The higher the lock level, the more operations it conflicts with.

The critical insight: AccessExclusiveLock conflicts with everything, including SELECT. Any DDL statement that acquires this lock — ALTER TABLE, DROP TABLE, VACUUM FULL, LOCK TABLE — blocks all access to the table for its duration. In production, this is nearly always unacceptable.

I should note that most normal operations use locks at the bottom of this hierarchy. A typical OLTP workload of SELECT, INSERT, UPDATE, and DELETE acquires only AccessShareLock and RowExclusiveLock, which do not conflict with each other. You can have a thousand concurrent sessions reading and writing to the same table with no table-level contention. The trouble begins when DDL enters the picture — or when row-level contention concentrates on a small number of rows.

Reading pg_locks without losing your composure

The pg_locks view is the authoritative source for what is locked, by whom, and whether a session is waiting. It is also, if I may be frank, not the most welcoming view PostgreSQL has to offer. A busy system produces hundreds of lock entries, most of them irrelevant.

-- Understanding pg_locks output:
SELECT locktype, relation::regclass, mode, granted, pid,
       transactionid, virtualtransaction
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted, mode;

-- Key columns:
-- locktype: 'relation' (table), 'tuple' (row), 'transactionid', 'advisory'
-- mode: the lock level (AccessShareLock, RowExclusiveLock, etc.)
-- granted: true = lock held, false = lock waiting
-- pid: the backend process holding/requesting the lock

-- A granted=false row means that session is BLOCKED.
-- Find who is blocking it by looking for granted=true on the same relation.

The column that matters most is granted. A row with granted = false represents a session that is blocked — waiting for a lock it cannot acquire. Find those rows first, then trace backwards to find who holds the conflicting lock.

For production incidents, the following query is more immediately useful. It joins pg_locks with pg_stat_activity to show the blocked query, the blocking query, and how long the block has lasted:

-- Find blocked queries and what is blocking them:
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocked.wait_event_type,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state,
       now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
  AND gl.database IS NOT DISTINCT FROM bl.database
  AND gl.relation IS NOT DISTINCT FROM bl.relation
  AND gl.page IS NOT DISTINCT FROM bl.page
  AND gl.tuple IS NOT DISTINCT FROM bl.tuple
  AND gl.pid != bl.pid
  AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
ORDER BY blocked_duration DESC;

Run this the moment you suspect lock contention. The blocked/blocking relationship tells you exactly where to look. In many incidents, you will find a single session — often idle — holding a lock that cascades into dozens of blocked queries.

Following the chain

Lock contention is rarely a two-party affair. In production, you more often find chains: Session A blocks Session B, which blocks Sessions C through Z. The previous query shows direct relationships, but to see the full chain — and more importantly, to find the root cause at the top of it — you need a recursive query:

-- Find lock chains: A blocks B, B blocks C, etc.
WITH RECURSIVE lock_chain AS (
    -- Base: find directly blocked sessions
    SELECT blocking.pid AS root_pid,
           blocking.pid AS blocking_pid,
           blocked.pid AS blocked_pid,
           1 AS depth,
           ARRAY[blocking.pid] AS chain
    FROM pg_locks bl
    JOIN pg_locks gl ON gl.locktype = bl.locktype
      AND gl.database IS NOT DISTINCT FROM bl.database
      AND gl.relation IS NOT DISTINCT FROM bl.relation
      AND gl.page IS NOT DISTINCT FROM bl.page
      AND gl.tuple IS NOT DISTINCT FROM bl.tuple
      AND gl.pid != bl.pid
      AND gl.granted AND NOT bl.granted
    JOIN pg_stat_activity blocked ON blocked.pid = bl.pid
    JOIN pg_stat_activity blocking ON blocking.pid = gl.pid

    UNION ALL

    -- Recursive: follow the chain
    SELECT lc.root_pid,
           lc.blocked_pid AS blocking_pid,
           bl2.pid AS blocked_pid,
           lc.depth + 1,
           lc.chain || lc.blocked_pid
    FROM lock_chain lc
    JOIN pg_locks bl2 ON NOT bl2.granted
    JOIN pg_locks gl2 ON gl2.locktype = bl2.locktype
      AND gl2.database IS NOT DISTINCT FROM bl2.database
      AND gl2.relation IS NOT DISTINCT FROM bl2.relation
      AND gl2.pid = lc.blocked_pid
      AND gl2.granted
      AND bl2.pid != gl2.pid
    WHERE lc.depth < 10
      AND NOT bl2.pid = ANY(lc.chain)
)
SELECT root_pid, blocking_pid, blocked_pid, depth,
       a.query AS blocked_query,
       now() - a.query_start AS wait_duration
FROM lock_chain
JOIN pg_stat_activity a ON a.pid = blocked_pid
ORDER BY root_pid, depth;

The root_pid is the session at the top of the chain. That is the one to address. Terminating a session in the middle of the chain only unblocks the sessions directly behind it — everything still queued behind the root remains blocked.

In my experience, the root of a lock chain is almost always one of three things: an idle-in-transaction session, a long-running DDL statement waiting for its own lock, or a manual LOCK TABLE that someone thought was a good idea. It rarely is.

Row-level locks: the fine-grained mechanism

Table-level locks govern who can perform which operations on a table. Row-level locks govern which individual rows are currently being modified. These are distinct mechanisms, and understanding both is essential.

PostgreSQL's MVCC implementation means that two transactions can update different rows in the same table concurrently with no contention whatsoever. The table-level RowExclusiveLock does not conflict with itself — it only prevents DDL. The actual serialization happens at the row level: if two transactions attempt to update the same row, the second must wait for the first to commit or roll back.

-- Row-level locking: PostgreSQL's fine-grained mechanism.
-- Two UPDATEs to DIFFERENT rows in the same table: no conflict.
-- Two UPDATEs to the SAME row: the second waits.

-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Holds a row-level lock on accounts row id=1.
-- No table-level lock beyond RowExclusiveLock (which allows other writes).

-- Session 2 (concurrent):
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
-- Succeeds immediately. Different row, no conflict.

-- Session 3 (concurrent):
UPDATE accounts SET balance = balance + 200 WHERE id = 1;
-- WAITS. Same row. Must wait for Session 1 to COMMIT or ROLLBACK.

-- This is MVCC working correctly. It is not a bug.
-- But if Session 1 holds its transaction for 30 seconds,
-- Session 3 waits for 30 seconds. Multiply by 200 concurrent users...

This is correct behaviour. Row-level serialization is how PostgreSQL maintains consistency. The issue arises when this serialization concentrates on a small number of rows — the "hot row" problem — or when transactions hold row locks for longer than necessary.

The hot row problem

A hot row is a single row that many transactions update concurrently. The classic example is a counter: a view_count column, a balance, a last_login timestamp on a user record. Every request touches the same row. Every request must wait for the previous one to finish.

On a quiet system, each update takes 1ms — nobody notices. Under load, 500 concurrent requests serialize on one row. The 500th request waits for 499 updates to complete. At 1ms each, that is nearly 500ms of pure lock waiting. The query itself is instant. The wait is all contention.

-- The hot row problem: many transactions updating the same row.
-- Classic example: a counters table, a balance, a "last_updated" timestamp.

-- Anti-pattern: direct counter increment
UPDATE products SET view_count = view_count + 1 WHERE id = 42;
-- Every page view contends on the same row.
-- 500 concurrent requests = 500 transactions serialized on one row.

-- Better: batch counter updates
INSERT INTO product_view_events (product_id, counted_at)
VALUES (42, now());
-- Append-only. No row contention.
-- Periodically aggregate:
UPDATE products p
SET view_count = p.view_count + batch.cnt
FROM (
    SELECT product_id, count(*) AS cnt
    FROM product_view_events
    WHERE aggregated = false
    GROUP BY product_id
) batch
WHERE p.id = batch.product_id;

-- Best: use advisory locks for distributed counters (see below)

The fundamental solution is to stop updating a single row from many concurrent transactions. Append events instead and aggregate periodically. This converts row-level contention (serial) into insert throughput (parallel). PostgreSQL can handle thousands of concurrent inserts to the same table with no contention because each insert creates a new row — no row-level conflict exists.

I should acknowledge the counterpoint: the event-and-aggregate pattern adds complexity. You now have two representations of the same data, a background aggregation process, and potentially stale counts. For many applications, an approximate count that never blocks is preferable to a precise count that creates 500ms of tail latency. But if exact real-time counts are a hard requirement, this pattern may not suffice, and you may need to accept the serialization cost — or move the counter to a system designed for high-contention single-key updates, such as Redis.

Deadlocks: when waiting becomes circular

A deadlock occurs when two or more transactions each hold a lock that the other needs. Neither can proceed. Neither will release. PostgreSQL detects this situation automatically — after deadlock_timeout (default: 1 second), it checks for cycles in the lock wait graph and terminates one of the transactions with an error.

-- Deadlock: two transactions each waiting for the other's lock.

-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- ... some application logic ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for row 2

-- Session 2 (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
-- ... some application logic ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for row 1

-- Session 1 holds row 1, wants row 2.
-- Session 2 holds row 2, wants row 1.
-- PostgreSQL detects this after deadlock_timeout (default: 1 second)
-- and terminates one transaction with:
-- ERROR: deadlock detected

-- The surviving transaction proceeds. The terminated one must retry.

Deadlocks are not a sign that your database is broken. They are a sign that your application acquires locks in an inconsistent order. The fix is almost always to impose a consistent ordering:

-- Deadlock prevention: always lock rows in a consistent order.

-- Anti-pattern: lock order depends on application logic
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
-- If two transfers happen simultaneously (A->B and B->A), deadlock.

-- Fix: always lock the lower ID first
DO $$
DECLARE
    first_id int := LEAST(from_id, to_id);
    second_id int := GREATEST(from_id, to_id);
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = first_id;
    UPDATE accounts SET balance = balance + amount WHERE id = second_id;
END $$;
-- Consistent ordering eliminates deadlocks entirely.
-- This is not a PostgreSQL trick. It is a fundamental concurrency principle.

This principle — always acquire locks in the same order — eliminates deadlocks by construction. It is the same principle used in operating system kernel development, concurrent programming, and anywhere multiple locks must be held simultaneously. If every transaction that touches rows 1 and 2 always locks row 1 first, no circular wait can form.

A word about deadlock frequency: occasional deadlocks in a high-throughput system are normal and not cause for alarm. PostgreSQL handles them cleanly — one transaction gets an error, the other proceeds. Your application should retry the failed transaction. If you are seeing hundreds of deadlocks per hour, however, that is a design problem worth investigating. Check your application's lock ordering, look for long-running transactions that hold locks while performing external calls (API requests, file I/O), and ensure you are not locking more rows than necessary.

How migrations cause outages

This is the scenario I encounter most frequently in production incidents. A migration runs during business hours. It needs AccessExclusiveLock. It waits for all existing AccessShareLock holders to finish. While waiting, new queries queue behind the pending lock request. The migration has not even started, but it has already caused a pileup.

-- A seemingly innocent migration:
ALTER TABLE orders ADD COLUMN discount numeric DEFAULT 0;

-- PostgreSQL 10 and earlier: rewrites the entire table.
-- ACCESS EXCLUSIVE lock for the duration. All queries blocked.

-- PostgreSQL 11+: no rewrite for columns with non-volatile defaults.
-- Lock is held briefly. But the lock ACQUISITION can still block
-- if long-running queries hold AccessShareLock.

-- The migration waits for existing queries to finish.
-- New queries queue behind the migration.
-- Everything piles up.

The sequence is insidious because PostgreSQL's lock queue is FIFO with respect to conflicting lock levels. When the ALTER TABLE enqueues its AccessExclusiveLock request, it does not merely wait for existing AccessShareLock holders to finish — it also prevents new AccessShareLock requests from being granted. Every new SELECT queues behind the pending ALTER TABLE. The table becomes completely inaccessible even though the ALTER TABLE has not started executing.

This is why lock_timeout is essential for migrations. Without it, a migration that cannot acquire its lock will wait indefinitely, accumulating a growing queue of blocked queries. With lock_timeout = '3s', the migration fails fast, the queue drains, and you can retry during a quieter moment.

For migrations that add columns, the safe pattern avoids the problem entirely:

-- Safe migration pattern for adding a column:
-- Step 1: Set a short lock_timeout so we fail fast instead of blocking
SET lock_timeout = '3s';

-- Step 2: Add the column with no default (instant, minimal lock)
ALTER TABLE orders ADD COLUMN discount numeric;
-- If lock_timeout expires, we retry — no queue pileup.

-- Step 3: Backfill in batches (no DDL lock needed)
UPDATE orders SET discount = 0 WHERE id BETWEEN 1 AND 100000;
UPDATE orders SET discount = 0 WHERE id BETWEEN 100001 AND 200000;
-- ... repeat in manageable chunks

-- Step 4: Set the default for new rows (instant in PG 11+)
ALTER TABLE orders ALTER COLUMN discount SET DEFAULT 0;

-- Step 5: Add NOT NULL constraint if needed (PG 12+ can validate without full lock)
ALTER TABLE orders ADD CONSTRAINT orders_discount_nn
    CHECK (discount IS NOT NULL) NOT VALID;
-- NOT VALID: adds constraint without scanning existing rows (brief lock)

ALTER TABLE orders VALIDATE CONSTRAINT orders_discount_nn;
-- VALIDATE: scans rows with only ShareUpdateExclusiveLock (allows reads AND writes)

This pattern separates the DDL (brief lock) from the data backfill (no lock) and the constraint validation (weak lock). Each step acquires only the minimum lock level required, and the DDL steps use lock_timeout so they fail fast if the table is busy.

I will be forthcoming: this pattern is more complex than a single ALTER TABLE ADD COLUMN ... DEFAULT ... NOT NULL. On PostgreSQL 11+, the simple version works for many cases because non-volatile defaults no longer require a table rewrite. But the lock acquisition problem remains — the ALTER TABLE still needs AccessExclusiveLock, and on a table with many long-running queries, acquiring that lock can block everything. The safe pattern gives you control over retry behaviour, and that control is worth the extra steps.

The forgotten transaction problem

-- The silent killer: a forgotten transaction.

-- Session 1 (a developer debugging in psql):
BEGIN;
SELECT * FROM orders LIMIT 5;
-- ... gets distracted. Goes to lunch. Transaction still open.

-- Session 2 (a migration, 30 minutes later):
ALTER TABLE orders ADD COLUMN notes text;
-- Waiting... AccessExclusiveLock needs all AccessShareLocks to release.
-- Session 1's open transaction holds AccessShareLock on orders.

-- Session 3 (every application query):
SELECT * FROM orders WHERE id = 42;
-- Waiting... queued behind the ALTER TABLE.

-- One idle transaction blocks a migration.
-- The migration blocks every query.
-- The entire application is down.

This scenario — a developer's idle BEGIN blocking a production migration, which in turn blocks all application queries — is one of the most common production incidents in PostgreSQL. I have seen it happen at companies of every size, from three-person startups to teams with dedicated database administrators. The pattern is always the same: someone opens a transaction in psql, runs a quick query, gets pulled into a meeting, and forgets. Thirty minutes later, a migration tries to run.

It is entirely preventable with idle_in_transaction_session_timeout, which tells PostgreSQL to terminate sessions that have been idle inside a transaction for too long. Set it to 60 seconds for application connections. Perhaps longer for interactive sessions — but set it. The cost of terminating a forgotten transaction is trivially small. The cost of not terminating it is an outage.

I should note that idle transactions cause damage beyond lock contention. They prevent VACUUM from cleaning dead tuples created after the transaction began (because MVCC requires those tuples to remain visible to the old transaction). A forgotten transaction open for hours can cause table bloat to accumulate across the entire database, degrading performance long after the transaction is finally closed. This is the subject of a separate conversation about autovacuum, but the root cause is the same: transactions held open longer than necessary.

Creating indexes without blocking writes

-- CREATE INDEX blocks all writes (RowExclusiveLock conflicts with ShareLock):
CREATE INDEX idx_orders_status ON orders (status);
-- Duration: 45 seconds on a 50M row table.
-- 45 seconds of blocked writes.

-- CONCURRENTLY does not block writes:
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- Duration: 90 seconds (longer), but writes continue uninterrupted.
-- Trade-off: takes 2x longer, cannot run inside a transaction.

PostgreSQL's own documentation covers the mechanism thoroughly — the CREATE INDEX CONCURRENTLY reference is worth reading directly. It builds the index in two passes: first a scan of the table to build the index entries, then a second pass to add entries for rows that were modified during the first pass. During both passes, writes continue normally. The lock it acquires — ShareUpdateExclusiveLock — does not conflict with RowExclusiveLock, so INSERT, UPDATE, and DELETE all proceed unimpeded.

The trade-offs are real and worth understanding: it takes approximately twice as long as a regular CREATE INDEX, it cannot be run inside a transaction block, and it can fail partway through — leaving an invalid index behind.

-- What happens when CREATE INDEX CONCURRENTLY fails?
-- It leaves an INVALID index behind.

-- Check for invalid indexes:
SELECT indexrelid::regclass AS index_name,
       indrelid::regclass AS table_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE NOT indisvalid;

-- An invalid index still costs writes (maintained on INSERT/UPDATE)
-- but is never used for reads. The worst of both worlds.

-- Fix: drop and recreate
DROP INDEX CONCURRENTLY idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

An invalid index is actively harmful: PostgreSQL still maintains it on every write (costing I/O and CPU), but never uses it for reads. Always check for invalid indexes after a concurrent index build, and drop them promptly if found.

PostgreSQL 12 added REINDEX CONCURRENTLY, which is a welcome addition for rebuilding bloated or corrupted indexes without downtime:

-- PostgreSQL 12+ added REINDEX CONCURRENTLY:
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- Rebuilds the index without blocking reads or writes.
-- Useful for: bloated indexes, corrupted indexes, changing storage parameters.
-- Before PG 12, the only option was DROP + CREATE CONCURRENTLY.

For production databases, CONCURRENTLY should be your default for any index operation. The only exception is during initial data loading before the application is serving traffic, where the faster non-concurrent build is perfectly safe.

Foreign keys and their hidden locks

Foreign key constraints are among the most common sources of unexpected locking. Engineers add them (correctly) for data integrity and then discover that certain operations become slower or cause contention.

-- Foreign key checks acquire locks that surprise many engineers.

-- Parent table:
CREATE TABLE customers (id serial PRIMARY KEY, name text);
-- Child table:
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int REFERENCES customers);

-- When you INSERT into orders:
INSERT INTO orders (customer_id) VALUES (42);
-- PostgreSQL acquires a RowShareLock on customers to verify the FK exists.
-- This is brief and rarely a problem.

-- But when you UPDATE the referenced column in customers:
UPDATE customers SET id = 43 WHERE id = 42;
-- PostgreSQL must check all child rows. On a large orders table
-- with no index on customer_id, this becomes a sequential scan
-- while holding locks. On a table with 50M orders: catastrophic.

-- The fix is straightforward but easy to forget:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- Every foreign key column should have an index. No exceptions.
-- PostgreSQL does not create these automatically (MySQL does).

The rule is simple: every foreign key column should have an index on the referencing (child) side. PostgreSQL creates an index on the referenced (parent) side automatically — it is the primary key. But the referencing side gets no automatic index, and without one, operations like DELETE from the parent table or UPDATE of the parent's primary key require a sequential scan of the child table while holding locks.

On a small child table, this is imperceptible. On a child table with 50 million rows, it is a production incident. I have seen DELETE FROM customers WHERE id = 42 take 90 seconds because the orders table had no index on customer_id and PostgreSQL had to scan 50 million rows to verify no child records existed.

Check your schema. If you have foreign keys without corresponding indexes on the referencing columns, add them. This is one of the few pieces of advice I can offer without qualification.

Partitioned tables and lock amplification

Table partitioning introduces a subtlety: DDL operations on a partitioned table may need to acquire locks on the parent and all partitions. If you have 365 daily partitions, an ALTER TABLE acquires 366 AccessExclusiveLocks.

-- Partitioned tables change the locking picture.
-- DDL on a partitioned table may lock ALL partitions.

-- Adding a column to a partitioned table:
ALTER TABLE events ADD COLUMN source text;
-- Acquires AccessExclusiveLock on the parent AND every partition.
-- If you have 365 daily partitions, that is 366 locks.

-- Attaching a new partition is better:
ALTER TABLE events ATTACH PARTITION events_2026_03
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- Brief AccessExclusiveLock on the parent, but the new partition
-- was built independently and is already populated.

-- Detaching a partition (PG 14+):
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
-- CONCURRENTLY: avoids blocking queries on the parent table.
-- Without CONCURRENTLY: AccessExclusiveLock on the parent. Blocks everything.

The practical impact depends on your partition count and how busy each partition is. With a handful of partitions, this is manageable. With hundreds, the lock acquisition itself becomes a bottleneck — each partition's lock must be acquired in sequence, and any long-running query on any partition delays the entire operation.

PostgreSQL 14's DETACH PARTITION CONCURRENTLY was a significant improvement for partition maintenance. Before it, detaching a partition blocked all queries on the entire partitioned table. Now the operation proceeds without blocking reads or writes on other partitions.

Advisory locks: locking without the baggage

-- Advisory locks: application-level locking without table locks.
-- Useful for: rate limiting, distributed coordination, job processing.

-- Try to acquire lock (non-blocking):
SELECT pg_try_advisory_lock(12345);  -- returns true/false

-- Acquire and release explicitly:
SELECT pg_advisory_lock(12345);
-- ... do work ...
SELECT pg_advisory_unlock(12345);

-- Transaction-scoped (auto-release on COMMIT/ROLLBACK):
SELECT pg_advisory_xact_lock(12345);

Advisory locks are application-defined locks that do not correspond to any database object. They are, if you will permit the analogy, a private arrangement between your application and PostgreSQL — the database provides the locking mechanism, but the semantics are entirely yours to define.

They are useful for coordinating between application instances without contending with table-level locks: job processing (only one worker picks up a job), rate limiting, distributed mutexes, singleton processes.

-- Advisory locks for job processing: only one worker picks up each job.
-- This replaces SELECT FOR UPDATE SKIP LOCKED for simpler cases.

-- Worker claims a job:
SELECT id, payload
FROM jobs
WHERE status = 'pending'
  AND pg_try_advisory_lock(hashtext('job_' || id::text))
ORDER BY created_at
LIMIT 1;

-- If pg_try_advisory_lock returns false, the row is skipped.
-- No row-level lock contention between workers.
-- Lock releases when the session disconnects or explicitly unlocks.

-- For most job queues, SELECT FOR UPDATE SKIP LOCKED is preferable:
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Process the job...
UPDATE jobs SET status = 'complete' WHERE id = $1;
COMMIT;
-- SKIP LOCKED: rows locked by other transactions are skipped, not waited on.
-- No contention. No advisory lock bookkeeping. Built into PostgreSQL 9.5+.

Unlike table locks, advisory locks never block normal queries. They only block other advisory lock requests for the same key. This makes them safe for application-level coordination without risking the cascading blocking that table locks can cause.

The honest caveat: advisory locks are session-scoped by default. If your application crashes without explicitly releasing them, the lock persists until the database connection is closed. With a connection pooler like PgBouncer in transaction mode, the connection may be returned to the pool with the advisory lock still held — blocking other sessions that attempt to acquire the same lock. Use transaction-scoped advisory locks (pg_advisory_xact_lock) when working with connection poolers, or ensure explicit unlocking in your error handling paths.

Logging lock waits: your first line of defence

You cannot diagnose lock contention after the fact unless you have logging. By the time you connect to the database and run a diagnostic query, the contention may have resolved — leaving you with elevated latency graphs and no explanation.

-- Enable lock wait logging in postgresql.conf:
-- log_lock_waits = on        -- log when a lock wait exceeds deadlock_timeout
-- deadlock_timeout = 1s      -- how long before checking for deadlocks (and logging)

-- With these settings, any lock wait exceeding 1 second appears in the logs:
-- LOG: process 12345 still waiting for ShareLock on relation 16384
--      after 1000.123 ms
-- DETAIL: Process holding the lock: 12346.
-- STATEMENT: CREATE INDEX idx_orders_status ON orders (status);

-- This is your first line of defense for catching lock contention
-- in production without running manual queries.

With log_lock_waits = on, PostgreSQL writes a log entry for any lock wait that exceeds deadlock_timeout. The log includes the waiting process, the lock it wants, the process that holds it, and the SQL statement. This is often sufficient to diagnose a contention pattern without any real-time investigation.

I recommend enabling this on every production database. The logging overhead is negligible — it only fires when contention actually occurs. The diagnostic value is considerable.

A prevention checklist

-- 1. Set statement_timeout for application connections:
ALTER ROLE app_user SET statement_timeout = '30s';

-- 2. Set lock_timeout to avoid indefinite lock waits:
ALTER ROLE app_user SET lock_timeout = '5s';
-- Migrations fail fast instead of blocking the world.

-- 3. Set idle_in_transaction_session_timeout:
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
-- Kills forgotten open transactions after 60 seconds.

-- 4. Set deadlock_timeout (default 1s is usually fine):
-- ALTER SYSTEM SET deadlock_timeout = '1s';

-- 5. Monitor for long-running transactions:
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '5 minutes';

These settings prevent the most common lock contention scenarios:

1. statement_timeout prevents any single query from running indefinitely. A query that runs for 30 minutes is almost certainly either wrong or should be run as a background job, not an application request.
2. lock_timeout prevents migrations and DDL from waiting indefinitely for locks. When a migration cannot acquire its lock within 5 seconds, it fails with an error rather than blocking the world. Your deployment script can retry after a brief pause.
3. idle_in_transaction_session_timeout kills forgotten open transactions that block vacuum and DDL. This is the single most impactful setting for preventing the "forgotten BEGIN" incident.
4. deadlock_timeout controls how frequently PostgreSQL checks for deadlocks and when lock waits are logged. The default of 1 second is appropriate for most systems.
5. Active monitoring catches lock waits before they cascade into outages. Query pg_stat_activity for idle in transaction sessions, alert on long-running transactions, and review log_lock_waits output regularly.

Set these on your application's database role, not globally — you may want different timeouts for interactive sessions versus application connections. An analyst running a complex report needs a longer statement_timeout than a web request handler. A migration tool needs a shorter lock_timeout than a manual psql session. Role-level settings give you this granularity.

The discipline of short transactions

If there is a single principle that prevents more lock contention than any configuration setting, it is this: keep transactions short. Every lock is held for the duration of the transaction. A transaction that completes in 5ms holds its locks for 5ms. A transaction that makes an HTTP call to a third-party API, waits 2 seconds for a response, and then commits holds its locks for 2 seconds. Under concurrent load, that 400x difference in lock hold time is the difference between a healthy system and a queueing disaster.

Audit your application for transactions that perform external work: API calls, file I/O, sending emails, publishing messages to queues. Move those operations outside the transaction boundary. The pattern is straightforward — read from the database, commit, then perform the external work. If the external work fails and you need to compensate, use a separate transaction. This is slightly more complex than wrapping everything in a single transaction, but it reduces lock hold time by orders of magnitude.

I should be honest about the trade-off: shorter transactions mean you lose the atomicity guarantee across the database write and the external operation. If you insert a row and then the email fails, the row exists without the email. For many applications, this is acceptable — you can retry the email, or a background job picks it up. For some, it is not, and you need the longer transaction. But make that choice deliberately, understanding its cost in lock hold time, rather than defaulting to "wrap everything in BEGIN/COMMIT" because it feels safer.

When none of this is enough

There are legitimate scenarios where PostgreSQL's locking model is the bottleneck and no amount of tuning will resolve it. I would be a poor guide if I did not mention them.

Extremely high-frequency updates to a small number of rows — counters with thousands of increments per second, real-time bidding systems, leaderboards with continuous score updates — may genuinely need a data store optimized for single-key write throughput. Redis, with its single-threaded model that eliminates locking entirely, handles this pattern well. PostgreSQL's MVCC model, elegant as it is for general-purpose workloads, serializes updates to the same row. At sufficient concurrency, that serialization becomes the dominant cost.

Similarly, if your schema migrations require frequent DDL on large, heavily-queried tables, you may benefit from tools like strong_migrations (Ruby), django-migration-linter (Python), or pg-schema-diff that analyse migrations for lock safety before they run. These tools cannot solve every locking problem, but they catch the common ones — the ALTER TABLE that will acquire AccessExclusiveLock, the CREATE INDEX missing its CONCURRENTLY, the ADD COLUMN with a volatile default.

Know the limits. Not every problem is best solved within PostgreSQL. But in my experience, the vast majority of lock contention incidents are caused not by PostgreSQL's limitations but by incomplete understanding of its locking model — migrations run without lock_timeout, transactions left open while someone goes to lunch, counters updated with direct UPDATE instead of append-and-aggregate, foreign keys created without supporting indexes. These are knowledge problems. And knowledge problems, if you will permit me, are the very best kind — because they are solved by learning, not by spending.