Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep Dive: Lock Queues and Fast-Path Locks — Why Some Lock Acquisition Is Nearly Free

PostgreSQL Locking Internals Performance

You run SELECT * FROM orders WHERE id = 42 on a table with a billion rows. The query takes 2 milliseconds. During that time, PostgreSQL acquired locks on at least two heap pages and one tuple, plus a table-level ACCESS SHARE lock. That seems like a lot of overhead for a simple read. But the query is fast because most of those locks never touched the shared lock table. They went through the fast-path mechanism.

On the other hand, you have a production incident where ALTER TABLE is blocked, and behind it a queue of 200 queries are piling up. Those queries are not just waiting, they are in the main lock table, and every new query that needs a conflicting lock gets appended to the queue.

The difference between these two scenarios comes down to how PostgreSQL manages locks internally: the fast-path optimization that makes routine locking invisible, and the main shared lock table where contention becomes visible and expensive.

The Lock Manager Architecture

PostgreSQL maintains locks in shared memory. Every backend process can examine and modify the lock state. The lock manager has two layers:

  1. Fast-path lock array: A per-backend array of lock entries stored in ProcArray. This is not in the shared lock table. It is a small local array in shared memory, sized by max_locks_per_transaction (default 64), where each backend can store lock records without touching any shared data structure.

  2. Main shared lock table (LOCK and PROCLOCK tables): The traditional lock table stored in shared memory, protected by a single LWLock (lightweight lock) partition. Accessing it requires acquiring the partition lock, searching hash tables, and potentially sleeping.

The fast-path mechanism was introduced in PostgreSQL 9.2. Before that, every single lock acquisition went through the shared lock table, which was a measurable bottleneck on systems with high concurrency.

How Fast-Path Locks Work

When a backend wants to acquire a lock, it first checks whether the lock qualifies for fast-path treatment. The rules are strict:

  1. The lock must be on a relation (table or index). Locks on pages, tuples, transaction IDs, advisory locks, and other object types do not use fast-path.

  2. The lock must be one of the weak modes: ACCESS SHARE, ROW SHARE, or ROW EXCLUSIVE. These are the modes acquired by SELECT, SELECT FOR UPDATE/SHARE, and INSERT/UPDATE/DELETE/MERGE respectively.

  3. The backend must not already hold a conflicting lock on the same relation through the main lock table. If you already have a strong lock on a table through the main table, additional weak locks also go through the main table.

  4. There must be room in the backend’s fast-path array (limited by max_locks_per_transaction).

If all conditions are met, the backend simply writes the lock entry into its own slot in the PROCARRAY structure in shared memory. No LWLock acquisition on the shared lock table. No hash table lookup. No contention with other backends. The cost is roughly equivalent to writing a few integers into a pre-allocated memory location.

This is why your SELECT on a billion-row table does not spend measurable time acquiring locks. The ACCESS SHARE lock goes through fast-path. Every page-level and tuple-level lock during the scan is handled similarly through the buffer manager’s per-buffer lwlocks, not the lock manager at all.

When Locks Enter the Main Table

A lock request must go through the main shared lock table when any of the fast-path conditions are not met:

  1. Strong lock modes: SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE. These are the modes used by DDL operations (CREATE INDEX, ALTER TABLE, TRUNCATE, etc.).

  2. Lock promotion: If a backend holds a fast-path ACCESS SHARE lock and then requests a stronger mode (e.g., SELECT FOR UPDATE on a table it was only reading), the existing fast-path lock must be “promoted” to the main table, and all future locks on that relation from this backend go through the main table.

  3. Fast-path array full: If a backend has exhausted its fast-path slots (max_locks_per_transaction), subsequent relation locks spill into the main table.

  4. Non-relation lock types: Page locks, tuple locks, transaction ID locks, advisory locks, and object locks always go through the main table.

  5. Conflict with a main-table lock: If another backend holds a strong lock on a relation through the main table, your fast-path lock request detects the conflict and enters the main table’s wait queue.

The Lock Wait Queue

When a backend requests a lock that conflicts with a lock held by another backend, it enters the lock wait queue. This is where cascading blocking happens.

The queue is FIFO within a lock group. Backends are queued per (lockable object, lock mode) pair. The key behaviors:

  • Only one wait at a time: A backend can only wait for one lock at a time. If it needs multiple locks, it acquires them sequentially.

  • Hard blocks vs soft blocks: A hard block occurs when a backend holds a conflicting granted lock. A soft block occurs when a backend is already waiting in the queue ahead of you for a conflicting mode. Both prevent your lock from being granted, but they have different implications for diagnostics.

  • Wakeup storms: When a lock is released, all waiting backends whose requests no longer conflict are woken up simultaneously. On a busy system with a long wait queue, this can cause a thundering herd. PostgreSQL mitigates this somewhat by having woken backends re-check the lock state before proceeding.

  • Deadlock detection: After waiting for deadlock_timeout (default 1s), the lock manager runs a deadlock check. It traverses the wait-for graph looking for cycles. If a cycle is found, one backend in the cycle is chosen as the victim and its transaction is aborted with SQLSTATE 40P01.

The fastpath Column in pg_locks

The pg_locks view has a fastpath boolean column that tells you whether a lock was taken via fast-path. This is useful for diagnostics:

SELECT locktype,
       mode,
       granted,
       fastpath,
       count(*)
FROM pg_locks
WHERE locktype = 'relation'
GROUP BY locktype, mode, granted, fastpath
ORDER BY mode, granted, fastpath;

On a typical OLTP workload, you will see the vast majority of granted locks are fast-path. Any non-fast-path granted lock on a relation is worth investigating: it means a DDL operation, an explicit LOCK TABLE, or a lock promotion happened.

Non-granted locks (where granted = false) are never fast-path. If a backend is waiting for a lock, it has already entered the main table.

Detecting Fast-Path to Main-Table Promotion

One of the subtle performance issues in PostgreSQL is lock promotion. Consider this pattern:

BEGIN;
-- This acquires ACCESS SHARE via fast-path
SELECT * FROM orders WHERE id = 42;

-- This promotes the fast-path lock to the main table
-- because ROW SHARE conflicts with ACCESS EXCLUSIVE
-- and the promotion path requires main-table tracking
LOCK TABLE orders IN SHARE MODE;

After the LOCK TABLE command, the original fast-path ACCESS SHARE lock is replaced by a main-table SHARE lock. All subsequent lock operations on orders from this backend go through the main table. The fast-path optimization is lost for the rest of the transaction.

This matters in ORM-driven applications where a transaction starts with a read and later escalates to a write or explicit lock. The promotion itself is not expensive, but the loss of fast-path for the rest of the transaction can add up.

Lock Contention on Foreign Keys

Foreign key checking is one of the most common sources of lock contention that people do not expect. When you INSERT into a child table, PostgreSQL acquires a FOR KEY SHARE lock on the referenced row in the parent table. This lock prevents the parent row from being DELETEd or having its key updated. The lock is on the tuple, not the relation, so it goes through the main lock table.

On a busy system with many concurrent inserts into a child table referencing the same parent row, all those inserts queue up on the parent row’s tuple lock. This shows up as locktype = 'tuple' with granted = false in pg_locks.

The mitigation: make sure foreign key columns have indexes on the referenced side. PostgreSQL does not automatically create an index on the referenced column. Without an index, the FK check does a sequential scan on the parent table, acquiring FOR KEY SHARE locks on every row it examines, not just the target row.

-- Check if FK references have indexes
SELECT conrelid::regclass AS child_table,
       conname AS constraint_name,
       confrelid::regclass AS parent_table,
       confkey AS parent_columns
FROM pg_constraint
WHERE contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = pg_constraint.confrelid
      AND i.indkey::int[] @> pg_constraint.confkey::int[]
  );

Practical SQL: Lock Queue Diagnostics

Find all currently blocked processes and what blocks them

SELECT blocked.pid          AS blocked_pid,
       blocked.query       AS blocked_query,
       blocking.pid        AS blocking_pid,
       blocking.query      AS blocking_query,
       blocked.wait_start  AS wait_started,
       now() - blocked.wait_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
CROSS JOIN LATERAL pg_blocking_pids(bl.pid) AS blocker_pids(blocking_pid)
JOIN pg_stat_activity blocking ON blocking.pid = blocker_pids.blocking_pid
WHERE blocked.pid != blocker_pids.blocking_pid;

Count locks by type, mode, and fast-path status

SELECT locktype,
       mode,
       granted,
       fastpath,
       count(*) AS lock_count
FROM pg_locks
WHERE pid != pg_backend_pid()
GROUP BY locktype, mode, granted, fastpath
ORDER BY lock_count DESC;

Backends that have exhausted their fast-path array

-- Backends with many non-fast-path relation locks may have
-- exhausted their fast-path array
SELECT pid,
       count(*) FILTER (WHERE locktype = 'relation' AND fastpath) AS fast_path_locks,
       count(*) FILTER (WHERE locktype = 'relation' AND NOT fastpath) AS main_table_locks,
       count(*) FILTER (WHERE locktype = 'relation') AS total_relation_locks
FROM pg_locks
WHERE granted
GROUP BY pid
ORDER BY main_table_locks DESC;

Lock wait queue depth per relation

SELECT relation::regclass,
       mode,
       count(*) AS waiting_count
FROM pg_locks
WHERE NOT granted
  AND locktype = 'relation'
GROUP BY relation, mode
ORDER BY waiting_count DESC;

The max_locks_per_transaction Parameter

This GUC controls the size of the fast-path array and the space allocated in the shared lock table. The default is 64, meaning each backend is allocated space for 64 distinct lockable objects in the shared lock table.

The fast-path array is sized proportionally: each backend gets max_locks_per_transaction fast-path slots. The shared lock table is sized as max_locks_per_transaction × (max_connections + max_prepared_transactions).

If you see “out of shared memory” errors related to locks, or if your workload routinely touches many tables in a single transaction (common with ORM-generated queries that join many tables), you may need to increase this parameter. It requires a restart.

Note: max_locks_per_transaction limits the number of distinct objects, not the total number of locks. You can lock millions of rows on one table without hitting this limit, because they all count as one lockable object (the relation) at the table level, and tuple locks are stored on disk in the heap, not in the shared lock table.

Key Takeaways

  • Fast-path locks make routine query locking nearly free. SELECT, INSERT, UPDATE, DELETE all acquire their relation-level locks through the fast-path array without touching the shared lock table.
  • Only weak lock modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE) qualify for fast-path. DDL operations always go through the main lock table.
  • The fastpath column in pg_locks tells you whether a lock used fast-path. Almost all your granted relation locks should show fastpath = true.
  • Lock promotion (from fast-path to main table) happens when you escalate within the same transaction, losing the fast-path optimization for the rest of that transaction.
  • Foreign key checks acquire tuple-level locks through the main lock table. Missing indexes on FK columns amplify this by locking scanned rows, not just the target row.
  • The lock wait queue is FIFO per lock group. A single blocked DDL can cascade into a long queue of waiting queries.
  • max_locks_per_transaction (default 64) controls both fast-path array size and shared lock table allocation. Increase it if you have transactions touching many tables.

What’s Next

Yesterday we covered DDL locking and which ALTER TABLE operations block reads. Tomorrow, we look at contention patterns beyond DDL: how foreign key checks, sequential scans under load, and hot row contention create lock bottlenecks that are harder to spot but just as damaging.

Previous: DDL Locking: Why Your ALTER TABLE Just Blocked Every Query on the Table