Batch Job Resource Starvation

partial

Summary

When long-running batch jobs consume all available database connections or CPU capacity, they starve concurrent OLTP requests of the resources needed for interactive response times. Batch queries hold connections for minutes while scanning large datasets, causing OLTP p99 latency to spike into the seconds as requests queue behind full-table scans in the shared connection pool.

Description

Batch jobs and OLTP workloads share the same connection pool and database resource limits by default. A batch job executing a full-table export, aggregation, or ETL scan acquires a connection and holds it for the duration of the query : commonly 30 seconds to 10 minutes. A pool of 50 connections with 10 concurrent batch jobs (each taking 2 minutes) leaves only 40 connections for 500+ req/s of OLTP traffic. As OLTP requests queue for connections, p99 latency climbs from sub-100ms to 2–10 seconds. Upstream services begin timing out.

The resource contention is not limited to connections. Batch full-table scans on PostgreSQL or MySQL pollute the buffer pool (shared_buffers / innodb_buffer_pool) with large sequential reads, evicting hot OLTP pages. Subsequent OLTP queries that previously hit in-memory pages now generate physical disk I/O. This manifests as a sudden increase in disk read IOPS coinciding with batch job start time: a diagnostic pattern often missed because the batch job itself appears healthy.

CPU contention amplifies the effect on analytical databases like ClickHouse or Elasticsearch: batch aggregation queries consume 4–16 CPU cores during the merge phase. If the system runs at 60–70% CPU under normal OLTP load, a batch job consuming an additional 30–40% of CPU leaves no headroom. OLTP query execution times double or triple. HTTP request timeouts begin firing at the application tier.

The failure pattern is time-predictable if batch jobs run on a schedule (nightly exports, hourly aggregations). On-call engineers often notice the degradation repeats at the same clock time. However, because batch jobs are frequently treated as lower priority and their query plans appear correct, the root cause is often misdiagnosed as a database performance regression rather than resource partition failure.

Characteristics

Propagationfan out

Time to detect2–5 minutes via p99 latency alert on OLTP endpoints. Immediately if connection pool utilization monitoring is in place (alert at >80% pool occupancy). The signature is a sharp latency increase at a predictable clock time correlated with batch job start, which makes retrospective diagnosis straightforward.

Blast radiusOLTP query latency spikes across all endpoints that touch the shared database, not only the tables the batch job reads. Connection pool exhaustion is a global resource: once the pool fills, all new requests queue regardless of their query complexity. If the application uses a single database cluster for all services, batch contention in one service can degrade unrelated services sharing the cluster.

Triggers

·Batch job starts concurrently with peak OLTP traffic window
·Scheduled export or aggregation query acquires >20% of the shared connection pool
·Batch job issues a full-table sequential scan, evicting hot pages from the buffer pool
·Multiple batch jobs launched simultaneously (batch orchestration fan-out)
·Batch job with no query timeout runs longer than expected due to data growth

Detection Signals

latency spikeconnection exhaustioncpu saturationdisk saturation

Mitigation Strategies

Separate connection pools for batch and OLTP workloadspreventscomplexity: low

Create a dedicated connection pool (PgBouncer or application-level) for batch jobs, capped at 5–10 connections. OLTP pool retains its full allocation. Batch jobs are blocked from borrowing from the OLTP pool. This is the most effective and lowest-risk mitigation: it requires no database changes, only application-level pool configuration.

Query resource limits and workload isolationpreventscomplexity: medium

Vanilla PostgreSQL has no native per-query CPU quota. Bound batch queries per role with statement_timeout, lock_timeout, and a capped work_mem set via ALTER ROLE, and run batch through a separate connection pool with a low connection limit. Isolate CPU and I/O with OS-level cgroups on the batch worker, or move batch/analytics to a dedicated read replica so it never shares CPU with OLTP. (Greenplum resource groups and MySQL 8.0 Resource Groups offer CPU controls, but standard PostgreSQL does not.)

Batch job scheduling outside peak OLTP windowscomplexity: low

Schedule all batch jobs during off-peak hours (02:00–06:00 local time) when OLTP traffic is at <10% of peak. Combined with a batch job circuit breaker that aborts jobs if OLTP p99 exceeds 200ms during the batch window, this prevents runaway jobs from degrading production during edge cases.

Read replica isolation for batch workloadspreventscomplexity: medium

Route all batch read queries to a dedicated read replica or analytics replica. The replica is sized with higher memory and slower CPUs optimized for scan throughput rather than OLTP concurrency. No OLTP traffic touches the batch replica. Requires the batch job to tolerate replica lag (acceptable for most export and reporting workloads).

Recovery Steps

1.Identify active batch queries consuming connections via SELECT pid, query, state, query_start FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start ASC
2.Terminate the longest-running batch queries using SELECT pg_terminate_backend(pid) for batch PIDs
3.Verify OLTP connection queue drains by monitoring pool utilization: should drop below 50% within 30 seconds
4.Check if buffer pool eviction degraded OLTP hit rate; if so, allow 5–10 minutes for hot pages to reload
5.Reschedule the batch job with connection pool limits applied or redirect to a read replica
6.Add connection_limit parameter to the batch database role to enforce a hard cap going forward

Estimated recovery time: 2–5 minutes after batch query termination for connection pool to drain and OLTP latency to normalize. Buffer pool warming may require an additional 5–15 minutes if large batch scans evicted substantial in-memory pages.

Affected Systems

Patterns

read replicacqrscompeting consumers

Technologies

postgresqlmysqlclickhouseelasticsearch

Basis

Well-documented operational failure pattern with clear causal chain between resource sharing and OLTP degradation; specific connection count and latency thresholds are grounded in common PostgreSQL and MySQL default configurations