PostgreSQL slows to a crawl with LWLock SubtransSLRU waits: too many subtransactions, savepoints and EXCEPTION blocks

#Symptoms

• Sudden drop in throughput; many sessions in pg_stat_activity wait with wait_event_type = 'LWLock' and wait_event like SubtransSLRU / SubtransBuffer (names differ by version).
• CPU is high but queries that are normally fast take seconds.
• A hot standby used for reads becomes extremely slow while the primary looks fine.
• The problem appears during peaks and vanishes when a particular long-running transaction ends.

#What's going on

A subtransaction is created by:

• SAVEPOINT (explicitly, or by drivers/ORMs that wrap each statement in a savepoint),
• PL/pgSQL BEGIN ... EXCEPTION ... END blocks (each entry starts a subtransaction),
• some frameworks' "nested transaction" features.

Each backend caches up to 64 subtransaction IDs in shared memory. Beyond 64 in one transaction, it "overflows", and other sessions checking row visibility must look up parent transaction IDs in the pg_subtrans SLRU, a small shared cache with LWLocks. If at the same time there is a long-running transaction (keeping the snapshot horizon old), lookups go far back, miss the cache and hit the LWLocks hard. Every session slows down.

On a hot standby the effect is worse, because visibility checks there depend on subtransaction information more often.

#Step 1: confirm the wait

SELECT wait_event_type, wait_event, state, COUNT(*)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend'
GROUP  BY 1, 2, 3
ORDER  BY 4 DESC;

SLRU statistics (PostgreSQL 13+):

SELECT name, blks_hit, blks_read, blks_written, blks_zeroed
FROM   pg_stat_slru
WHERE  name ILIKE '%subtrans%';

blks_read climbing fast for Subtrans during the incident confirms cache misses.

#Step 2: find the long transaction

SELECT pid, usename, application_name, state, xact_start,
       now() - xact_start AS xact_age, backend_xmin, LEFT(query, 120) AS query
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_start
LIMIT  10;

Also check replication slots and standbys with hot_standby_feedback = on, which can hold the horizon back:

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT application_name, backend_xmin FROM pg_stat_replication;

The incident ends when that transaction ends. Terminating it is the quick fix:

SELECT pg_terminate_backend(<pid>);

Warning

Terminating a session rolls back its transaction. Confirm what it is (a forgotten BEGIN in a SQL client, a stuck batch job, a report) before killing it.

#Step 3: find the code creating many subtransactions

Subtransactions aren't listed per session directly. Look for patterns:

• Driver settings: the PostgreSQL JDBC driver's autosave=always creates a savepoint around every statement. Npgsql, psycopg and ORMs have similar features (savepoints for "nested" transactions, retry-on-error modes).
• PL/pgSQL loops with EXCEPTION blocks:

-- Anti-pattern: one subtransaction per row
FOR r IN SELECT * FROM staging LOOP
  BEGIN
    INSERT INTO target VALUES (r.*);
  EXCEPTION WHEN unique_violation THEN
    NULL;
  END;
END LOOP;

Replace with set-based SQL that avoids exceptions:

INSERT INTO target SELECT * FROM staging ON CONFLICT DO NOTHING;

• Logging: temporarily log statements from suspect applications, or search code for SAVEPOINT, EXCEPTION WHEN, autosave, nested=True.

If you have pg_stat_statements:

SELECT calls, LEFT(query, 100) FROM pg_stat_statements
WHERE  query ILIKE 'SAVEPOINT%' OR query ILIKE 'RELEASE SAVEPOINT%'
ORDER  BY calls DESC LIMIT 10;

Millions of SAVEPOINT calls confirm a driver or ORM pattern.

#Step 4: fix

ALTER DATABASE app SET idle_in_transaction_session_timeout = '5min';

#Step 5: verify

After the change, during peak:

• No SubtransSLRU/SubtransBuffer waits in pg_stat_activity samples.
• pg_stat_slru subtrans blks_read grows slowly.
• SAVEPOINT call counts in pg_stat_statements drop.

#Prevent it next time

• Ban per-row exception handling in PL/pgSQL code reviews.
• Review driver and ORM transaction settings for hidden savepoints.
• Set idle_in_transaction_session_timeout everywhere and monitor transaction age.
• Sample pg_stat_activity wait events every few seconds into your monitoring; LWLock storms are obvious there.