EnorexaEnorexa
Back to blog

Engineering

Why In-Database pg_cron Jobs Desync Read Replicas (And How to Fix It)

November 11, 20262 min readBy Enorexa Team

Running scheduled database maintenance via pg_cron sounds deceptively elegant. You eliminate an external cron daemon, bypass Lambda timeout headaches, and keep your batch logic next to your data. Then your primary database executes an UPDATE across 850,000 tenant records at midnight, WAL generation explodes to 45MB per second, and your read replicas fall three minutes behind production traffic. If your customer-facing dashboard routes read queries through read replicas, your users are suddenly looking at phantom state and stale billing tallies.

The WAL Avalanche: Why Read Replicas Fall Behind

PostgreSQL physical streaming replication is single-threaded on the replica replayer process. When pg_cron executes a massive bulk update inside a single transaction, the primary node writes hundreds of megabytes of Write-Ahead Log (WAL) records in seconds. The primary handles this because NVMe storage absorbs burst IOPS easily. But the replica must replay those records sequentially. If a long-running analytical query is running on the replica at the same time, replica conflict resolution either cancels the query or stalls WAL replay entirely (max_standby_streaming_delay default of 30s). Your replication lag metric climbs vertically.

Cursor Chunking and Explicit Pauses: Taming Vacuum and WAL

The fix is never to let a pg_cron task run an unbounded mutation. Instead of running UPDATE accounts SET status = 'archived' WHERE last_login < NOW() - INTERVAL '90 days', wrap the operation in a PL/pgSQL block that limits row updates to 5,000 records per batch using keyset pagination. Commit each chunk explicitly and inject a mandatory PERFORM pg_sleep(0.05); pause. This deliberate micro-pause gives the replica replayer thread breathing room to clear pending WAL frames and keeps table bloat from overwhelming autovacuum workers.

Offloading Heavy Mutators to Decoupled Workers

For workloads exceeding 2 million row mutations, in-database scheduling becomes an operational liability. Heavy batch jobs belong in dedicated background runners consuming idempotent task queues, allowing rate-limiting based on real-time replication lag telemetry. At Enorexa, we design lean database architectures and high-throughput background automation pipelines that preserve single-digit millisecond query latencies. If replication desync or database locking is degrading your production systems, talk to us to re-architect your data pipelines before your next scale milestone.