ETL Pipeline Lock Contention on Source Database
SEV-3, Limited ImpactFan-Out propagation · concurrency · Affects 0 scenario(s)
Severity Classification
Classified as PARTIAL based on failure mode severity. The fan out propagation pattern increases risk of broad impact beyond the initial failure point.
Propagation Chain
Origin component
ETL Pipeline Lock Contention on Source Database begins at the source component. Trigger: Full-table ETL scan executed during business hours when OLTP traffic is at >50% of peak.
Immediate (T+0) · Signal: Latency Spike
Downstream dependents
Failure propagates to directly dependent components via synchronous calls or shared resources. Latency increases and error rates rise on affected dependencies.
5–15 minutes via OLTP p99 latency alert. Autovacuum delay is detectable via SELECT age(backend_xmin) FROM pg_stat_activity WHERE query LIKE '%ETL%' and will show the ETL transaction holding the oldest xmin. WAL accumulation from lagging CDC slots is detectable via SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots. · Signal: Latency spike, connection timeout, or error rate increase on dependents
Downstream of dependents (fan-out)
Failure spreads to multiple downstream systems simultaneously. Retry storms may amplify load on the failing component.
Within minutes of initial failure · Signal: Multiple services reporting elevated error rates
Blast Radius
OLTP query latency increases on all tables touched by the ETL scan (buffer pool eviction) and then spreads to all OLTP operations on the same PostgreSQL instance (autovacuum contention, dead tuple accumulation). The bloat accumulation effect can persist for 1–4 hours after the ETL job completes, meaning the blast radius in time is longer than the ETL job duration.
Contributing Factors
This operational trigger enables ETL Pipeline Lock Contention on Source Database: Full-table ETL scan executed during business hours when OLTP traffic is at >50% of peak
This operational trigger enables ETL Pipeline Lock Contention on Source Database: ETL job opens a transaction and holds it open for the duration of the scan (not using cursor-based streaming)
This operational trigger enables ETL Pipeline Lock Contention on Source Database: CDC replication slot falls behind and accumulates WAL without alerting
Remediation Plan
Terminate the long-running ETL transaction via SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_xmin IS NOT NULL AND age(backend_xmin) > interval '5 minutes'
Effort: Minutes to hours (on-call response)
Monitor autovacuum catching up by querying pg_stat_user_tables n_dead_tup: expect a decrease within 10 minutes after ETL is terminated
Effort: Minutes to hours (on-call response)
Check for lagging CDC replication slots and evaluate whether to drop and re-establish them
Effort: Minutes to hours (on-call response)
Configure the ETL job to connect to a dedicated read replica that receives no OLTP traffic. All buffer pool pollution, CPU consumption, and table scan I/O is isolated to the replica. Autovacuum on the primary is unaffected because the ETL transaction is not open on the primary. The read replica must have acceptable lag for the ETL use case (typically minutes, acceptable for daily export or batch processing workloads).
Effort: 1 day to 1 week
Replace full-scan ETL reads with cursor-based streaming: DECLARE etl_cursor CURSOR FOR SELECT * FROM large_table ORDER BY id; FETCH 10000 FROM etl_cursor; ... CLOSE etl_cursor. Open the cursor without a surrounding long transaction: use AUTOCOMMIT between batches. This limits the oldest open transaction age to the duration of one batch fetch (seconds rather than minutes), allowing autovacuum to advance normally. Reduces autovacuum blocking from the full ETL duration to near-zero.
Effort: 1 day to 1 week
Configure an alert when any replication slot's WAL lag exceeds 5 GB (SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots). If a CDC consumer is unavailable for >4 hours and WAL accumulation threatens disk capacity, drop the replication slot (SELECT pg_drop_replication_slot(slot_name)) to allow WAL cleanup. The CDC pipeline must restart from a snapshot when it recovers. Alert at 2 GB to provide response time before the disk fill threshold.
Effort: 1 day to 1 week
Configure alerts for: latency spike, disk saturation, cpu saturation. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.
Effort: 1–2 sprints
This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.