ETL Pipeline Lock Contention on Source Database

When a bulk ETL job reads from the production OLTP database, it competes for shared resources: buffer pool, WAL, CPU, and transaction snapshot slots : causing OLTP query latency to increase during the ETL window. Beyond direct I/O contention, an open long-running ETL transaction prevents autovacuum from advancing its horizon, causing table bloat to accumulate and dead tuple count to grow. OLTP reads slowed by bloated tables compound the initial contention, producing a degradation window that outlasts the ETL job itself.

ETL jobs that read directly from OLTP databases create contention on multiple resource dimensions simultaneously. The most immediate is buffer pool pollution: a full-table sequential scan for export reads every page of the target table into shared_buffers, evicting hot OLTP pages (recently-accessed index pages, frequent row lookups). After the ETL scan completes, subsequent OLTP queries that previously hit in-memory pages must now read from storage. For a table with a 10 GB buffer pool footprint and an ETL scan that reads 50 GB, the ETL effectively flushes the OLTP hot set and forces a cold-start on shared_buffers after completion.

The autovacuum blocking effect is more subtle and longer-lasting. PostgreSQL autovacuum cannot reclaim dead tuples that were visible to any open transaction snapshot. An ETL job that holds a transaction open for 30 minutes while scanning holds a snapshot that prevents autovacuum from advancing past the transaction start time. During this 30-minute window, all OLTP DELETE and UPDATE operations generate dead tuples that cannot be reclaimed. For a table with 10,000 updates/minute, 30 minutes produces 300,000 dead tuples that cannot be vacuumed. Post-ETL, autovacuum runs aggressively to catch up, competing with OLTP I/O for the next 10–30 minutes. OLTP queries slow further because they must skip over dead tuples in table pages, reducing effective page density.

Lock escalation is the third failure mode. Some ETL patterns use table-level operations for performance (TRUNCATE + INSERT instead of incremental updates, ALTER TABLE ADD COLUMN to add a computed column, CREATE INDEX CONCURRENTLY that still takes an initial brief lock). These operations request locks that conflict with OLTP AccessShare locks. If the ETL job holds a transaction lock and then requests an exclusive lock (even briefly for a single operation within a longer pipeline), it must wait for all active OLTP transactions to complete before the exclusive lock is granted. Simultaneously, all new OLTP transactions that attempt to acquire AccessShare on the same table are blocked behind the pending exclusive lock, producing a cascading read lockout identical to the materialized view refresh contention failure mode.

Logical replication slots used by CDC pipelines (Debezium, pglogical) create another variant of this problem: an inactive or lagging CDC slot holds a replication slot that prevents WAL from being removed. If the slot falls behind by hours, WAL accumulates without bound, potentially filling the disk and crashing the database entirely.