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

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.
Related Engineering & AI Articles
AI
LLM Fine-Tuning vs. RAG: When to Pay for Training, When to Prompt
Choosing between LLM fine-tuning and Retrieval Augmented Generation (RAG) is a critical cost and accuracy decision. Most teams default to RAG, but that's not always the cheapest or most performant path for specialized tasks.
November 16, 2026
Engineering
Why Serverless WebSockets Drop AI Agent Connections (And How to Fix It)
Serverless functions like Next.js API routes promise infinite scale, but their ephemeral nature and connection limits silently kill long-lived WebSocket connections crucial for real-time AI agents. We've seen this manifest as dropped user sessions and incomplete agent responses.
November 15, 2026
Engineering
Why Next.js API Routes Still Cold Start (Even on the Edge)
Most teams building on Next.js assume Edge Functions solve cold starts. They don't, not entirely. We've seen critical API routes still hit 500ms+ latency spikes, even when deployed globally.
November 14, 2026