How One Open Postgres Transaction Can Slow a Database

On 8 October 2024, GitLab.com was degraded for seventeen minutes because of one database session. A DELETE launched from a Rails console ran for more than twenty minutes inside an open transaction; it was not a session sitting idle for that whole period. The long transaction held back cleanup while write traffic created old row versions, often called dead tuples, that still occupied storage. GitLab's incident review put the consequence in one line: "When we have a long-running query in Postgres this will lead to dead-tuples and performance degradation site-wide." Almost two years later, GitLab began rolling out a cluster-wide 30-minute transaction_timeout, which ends transactions that remain open too long, in a change that links back to this incident.

How can a session that is doing nothing slow down every query? An open transaction can preserve old row versions, retain locks, occupy a pooled connection and keep transaction IDs old enough to become dangerous. The sections below follow one illustrative orders database through that chain, then use public incidents as evidence that each mechanism has caused real outages.

The operational baseline is straightforward. Set idle_in_transaction_session_timeout and, on Postgres 17 or later, transaction_timeout. Give schema migrations a short lock_timeout and retry them. Keep network calls outside transactions. Monitor long transactions, prepared transactions, replication slots and standby feedback because each can stop vacuum from advancing.

The running example: one open transaction, three symptoms#

The example values below are illustrative. At 10:00 an operator opens a transaction in an orders application, updates order 42, and walks away without COMMIT or ROLLBACK. BEGIN starts the transaction; COMMIT makes its changes permanent and releases its locks; ROLLBACK discards the changes and also releases the locks.

Postgres never updates a row in place#

When Postgres updates a row, it doesn't overwrite it. It writes a new version of the row and marks the old one as ended. Each version records the transaction that created it (xmin) and the transaction that ended it (xmax). A query sees whichever version was current as of its snapshot, which is how readers and writers avoid blocking each other. The documentation states the consequence: "an UPDATE or DELETE of a row does not immediately remove the old version of the row… the row version must not be deleted while it is still potentially visible to other transactions."

Here is order 42 after two changes, as the table actually stores it:

One logical row, three physical versions. The two dead versions occupy table space and may leave index entries; a heap-only tuple (HOT) update can avoid new index entries when no indexed column changes and the new version fits on the same page. Scans still pay to step past unreclaimed heap versions. Vacuum removes versions that no transaction could need and makes their space reusable.

The horizon: what vacuum is allowed to remove#

"No transaction could still need" is the whole problem. Vacuum can only remove a dead version if it ended before the oldest snapshot that any session in the database might still use. That boundary is called the xmin horizon, and any session holding an old snapshot or an old transaction ID holds it back for every table in its database.

The opening timeline used an idle writer called Session A. The next diagram is a separate snapshot example, so its reader is Session R. Under REPEATABLE READ, the snapshot begins with the first query and then stays fixed for the transaction. Session B performs the updates while autovacuum tries to reclaim the old versions.

The horizon is conservative. Session R never saw the intermediate paid version, yet vacuum keeps it because its ending transaction is newer than the global boundary. That simple rule lets vacuum avoid reasoning about every snapshot individually.

The Postgres documentation groups horizon holders into three broad sources: active or prepared transactions, replication slots, and standby activity reported to the primary. Operators usually split those into the four checks below because each lives in a different view or needs a different response.

One subtlety decides whether an idle session hurts you. In the default READ COMMITTED isolation level, each statement takes a fresh snapshot, so a read-only transaction that is idle between statements holds no snapshot at all: its backend_xmin is empty. A transaction that has written anything is different, because it has a transaction ID, and that ID holds the horizon until it commits or rolls back. That is exactly what GitLab's session looked like:

While the horizon is held, every update and delete across the database leaves dead versions that vacuum can see but can't remove. Vacuum reports this directly in its log, as rows that "are dead but not yet removable", along with how old its removal cutoff was. Tables that churn suffer first. Brandur Leach described a job queue that processed about 50 jobs a second: while another team kept an analytical transaction open, dead rows in the queue climbed toward 100,000, and the time to lock the next job rose from under 0.01 seconds to over 0.1. Every worker scanned past the same growing pile of dead versions to find a live one.

The last detail matters for recovery: vacuum makes dead space reusable but doesn't give it back to the operating system. A table that bloated during the incident stays large until it is rewritten, which is why a horizon held for hours can leave effects for weeks.

Lock queues: why one waiting migration blocks everything#

The second family of incidents involves locks, and it surprises people because the query that causes the outage isn't running yet.

Every query takes a lock on the tables it touches. A plain SELECT takes the weakest lock, which conflicts only with ACCESS EXCLUSIVE, the lock most forms of ALTER TABLE need. Locks are held until the end of the transaction. And lock requests wait in a queue: Postgres's lock manager grants a request immediately only "if it does not conflict with any existing or waiting lock request", so that "conflicting requests are granted in order of arrival." A request that is merely waiting still blocks everything that arrives after it and conflicts with it.

Put those rules together with an idle transaction and a migration:

GoCardless lost about 15 seconds of API availability to exactly this during a planned migration, and described the mechanism: "As AccessExclusive locks conflict with every other type of lock, having one sat in the queue blocks all other operations on that table." Their rule afterwards: "Set lock_timeout in your migration scripts to a pause your app can tolerate. It's better to abort a deploy than take your application down."

The session holding the lock doesn't have to be an application. Vacuum itself can be the blocker. An autovacuum that is running to prevent wraparound "is not automatically interrupted" by conflicting lock requests, as the documentation notes. In July 2015, a DROP TRIGGER in Joyent's Manta storage service queued behind exactly such a vacuum, which was itself sleeping in its cost-based delay, and about 22% of all requests failed for roughly ten hours (postmortem). In November 2021, a scheduled job at Duffel that created table partitions queued behind the same kind of vacuum and took the API down for 2 hours and 17 minutes. Duffel's conclusion was the right one: "autovacuum isn't really the process to blame – our application issuing DDL statements, without appropriate timeouts, was the problem."

The fix is to let the migration fail fast and try again:

SET lock_timeout = '50ms';       -- give up if the lock isn't free almost at once
SET statement_timeout = '5s';    -- and never run long while holding it
ALTER TABLE orders ADD COLUMN refunded_at timestamptz;
-- on "canceling statement due to lock timeout": wait a moment, then retry

PostgresAI's write-up of this pattern observes that such retries usually succeed "after a millisecond or two – once all transactions started before our DDL attempt has finished." GitLab's migration helpers do the same at scale, retrying DDL with short lock timeouts that grow between attempts. Before running one, it's also worth checking pg_stat_activity for anything already holding the table open.

It also helps to know which statements need the strongest lock. Several common ones don't anymore:

Transaction ID wraparound#

The third family is the rarest and the most severe. Transaction IDs are 32-bit numbers, and the documentation spells out why that matters: when the counter wraps around, "all of a sudden transactions that were in the past appear to be in the future." To prevent that, vacuum periodically marks old row versions as "frozen", meaning visible to everyone forever, and the documentation's rule is that every table in every database must be vacuumed "at least once every two billion transactions."

Postgres enforces this with a series of thresholds, measured by how many transaction IDs are left before wraparound:

The bars share one scale. The first protective vacuum begins at 200 million transactions of age; the final two bars sit close to the roughly 2.147-billion danger boundary, which is why a linear chart makes them look almost equal.

This version check was performed in September 2026. PostgreSQL 18.6 was the current stable release. PostgreSQL 19 was at Beta 4, and its draft release notes raised the warning point from 40 million to 100 million XIDs remaining. Treat the PostgreSQL 19 value as pre-release until a final release ships; the 3-million write stop remains the last-resort boundary described by the PostgreSQL 18 documentation.

At the last threshold the database is effectively down for writes. The error message changed in Postgres 17 to describe this accurately: "database is not accepting commands that assign new transaction IDs to avoid wraparound data loss." The current documentation says "it is not necessary or desirable to stop the postmaster or enter single user-mode." It also warns that VACUUM FULL requires an XID and cannot help in this state; VACUUM FREEZE is unnecessary extra work. Run a plain database-wide VACUUM.

The public wraparound outages were caused less by long transactions than by vacuum not keeping up with write volume:

Mandrill's timeline holds the most useful lesson: the warning sign was visible three months early, as transaction ID age climbing under peak load. Monitoring transaction ID age with alerts well before the database's own thresholds (AWS suggests a warning at 500 million and an alarm at 1 billion) turns a surprise outage into a planned maintenance task.

Transactions that wait on the network#

A forgotten console is the easy case to picture. The harder one to spot is application code that opens a transaction, does some database work, calls an external service, and commits when the call returns. While the call is in flight, the transaction holds its locks, holds the horizon if it has written anything, and holds a database connection.

Transaction pooling, the usual way to share a small number of Postgres connections among many application processes, makes this worse. PgBouncer in transaction mode assigns a server connection to a client only for the duration of a transaction, which is efficient until a transaction waits on something slow: then that connection is unavailable to everyone. PgBouncer's default pool size is 20 connections per database and user.

GitLab's development guidelines state the rule directly: "Ideally, a transaction should only contain database statements." They list what doesn't belong inside one: triggering background jobs, sending emails, calling HTTP APIs, and using a different database connection. When a network call and a database change must happen together, the durable way is to record the intent in the database (in the same transaction as the change) and make the call afterwards, from a worker that can retry it. That is the transactional outbox, covered in the workflow-engine mechanics chapter.

Replication slots#

A replication slot makes the primary keep whatever a consumer hasn't read yet: write-ahead log for physical replicas and, for logical replication, the catalog information needed to decode it. The documentation's warning is short: slots "persist across crashes and know nothing about the state of their consumer(s)." A consumer that stops reading makes the primary keep WAL until the disk fills, and a slot's xmin holds the vacuum horizon like a long transaction does, "in extreme cases" to the point of wraparound.

Postgres has added limits over time, all off by default. Since version 13, max_slot_wal_keep_size caps how much WAL a slot can hold before it is invalidated; its default of -1 means unlimited. Version 17 added columns showing when a slot went inactive and why it was invalidated, and version 18 added idle_replication_slot_timeout. Gunnar Morling documented a slot that grew even on an idle Amazon RDS database, because RDS writes a heartbeat every five minutes into 64 MB WAL segments, and suggested alerting whenever a slot retains more than about 100 MB.

The timeouts, and what each one does#

Postgres now has a timeout for every part of this problem. The table was checked against PostgreSQL 18 documentation in September 2026; PostgreSQL 19 was still at Beta 4. The settings differ in what they measure and in what happens when they fire:

A few details from the documentation decide how to use them. Setting statement_timeout in postgresql.conf "is not recommended because it would affect all sessions"; set it per role or per database instead. lock_timeout is pointless if it is as long as statement_timeout. If transaction_timeout is shorter than either of the other two, "the longer timeout is ignored." Prepared transactions are not subject to transaction_timeout. And idle_session_timeout should be used carefully behind a connection pooler, which may not expect its connections to be closed.

As for values: GitLab's published database settings document a 15-second statement_timeout for application sessions and a 60-second idle_in_transaction_session_timeout; it is also adding a 30-minute transaction_timeout across the cluster. Crunchy Data suggests 30 to 60 seconds as a reasonable default statement_timeout. Christophe Pettus suggests five minutes as a backstop for idle_in_transaction_session_timeout on an OLTP system, then tightening it per role. Use a generous cluster-wide backstop and tighter limits for application roles that should finish quickly.

Watching for it#

Each of these failures is visible before it becomes an outage, if you look at the right columns:

backend_xid and backend_xmin answer different questions, so inspect both. PostgreSQL's greatest() ignores a NULL when another argument is present; the WHERE clause below keeps rows where at least one value exists.

-- Sessions: check both the snapshot and the transaction ID
SELECT pid, usename, state, xact_start,
       greatest(age(backend_xmin), age(backend_xid)) AS horizon_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY horizon_age DESC
LIMIT 5;
 
-- Replication slots, prepared transactions, and standby feedback
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_age
FROM pg_replication_slots ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;
 
SELECT gid, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
 
SELECT application_name, age(backend_xmin) AS xmin_age
FROM pg_stat_replication ORDER BY age(backend_xmin) DESC NULLS LAST;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

GitLab's alert looks for any session, other than autovacuum workers, that isn't idle and whose transaction started more than 60 seconds ago; idle in transaction counts as not idle. Whatever thresholds you choose, alert on the age of the oldest horizon holder and on database transaction ID age, not only on errors: none of the failures in this piece produces an error until it is already an outage.

A checklist#

Sources#

• PostgreSQL 18 documentation: Routine vacuuming, Client connection defaults (timeouts), Explicit locking, ALTER TABLE, Monitoring statistics, pg_replication_slots, and logical decoding concepts; PostgreSQL 19 Beta 4 release notes; release notes for 9.6, 14, and 17
• PostgreSQL source, lock manager README
• GitLab: incident 18695 (2024), transaction guidelines, migration style guide, and the transaction_timeout rollout (2026)
• GoCardless, Zero-downtime Postgres migrations: the hard parts; PostgresAI, Zero-downtime schema migrations need lock_timeout and retries (2021)
• Joyent, Manta postmortem, 27 July 2015; Duffel, Understanding an outage: concurrency, vacuum, and PostgreSQL (2021)
• Sentry, Transaction ID wraparound in Postgres (2015); Mailchimp, What we learned from the recent Mandrill outage (2019); BattleMetrics, PostgreSQL transaction ID wraparound (2022); Metronome, Root cause analysis: PostgreSQL multixact member exhaustion (2025)
• Brandur Leach, Postgres job queues and failure by MVCC (2015); Laurenz Albe, Reasons why VACUUM won't remove dead rows; Gunnar Morling, The insatiable Postgres replication slot (2022)
• PgBouncer, Features and Configuration; AWS, An early warning system for transaction ID wraparound in RDS