Database Ingestion Design: Lessons from the Trenches

Most teams separate their analytical and transactional workloads across different databases. Good practice. Clean separation of concerns.

But I once worked on a system that did both at the same time, on the same database instance.

That constraint taught me more about ingestion design than any tutorial ever did.

The rookie move is to ingest records as they arrive. Feels natural. Keeps things simple. But what you're actually doing is letting your ingestion process compete with your normal application transactions for the same database resources.

Even if you're managing connection pools carefully, everything consolidates at the database level eventually. And when ingestion is heavy, it starves everything else. Requests queue up, latency climbs, and the system feels broken even when nothing technically is.

Batching helps. But it shifts the pressure upstream. Now your ingestor needs buffer memory, and you have to think carefully about batch size relative to your DB write speed and available memory. Because if your ingestion rate consistently exceeds your write speed, batching doesn't solve the problem. It just relocates it.

The fix I landed on was simple: add wait time between batches. Just enough breathing room for the database to attend to other requests. Not scientific. No math. It worked.

But here is what I should have done.

• Record size = 2KB
• DB Write speed = 1,000 rec/s
• Batch size = 1,000 records (capped at write speed)
• Wait time = 1s between flushes
• Buffer capacity = 30,000 records

Scenario A:

• arrival rate = 1,200 rec/s
• Per cycle (flush + wait = 2s): Records in: 1,200 × 2 = 2,400 Records out: 1,000
• Net: +1,400 records every cycle
• Buffer fills in: 30,000 ÷ 1,400 ≈ 21 cycles (42 seconds)

Scenario B:

• arrival rate = 900 rec/s
• Per cycle (flush + wait = 2s): Records in: 900 × 2 = 1,800 || Records out: 1,000
• Net: +800 records every cycle
• Buffer fills in: 30,000 ÷ 800 ≈ 37 cycles (74 seconds)

Both scenarios eventually overflow. Scenario B just buys you more time. One way to temporarily relieve pressure is to occasionally increase the flush size beyond the normal batch cap, hogging the DB deliberately to drain the buffer faster. But that is a tradeoff — you are starving your app transactions to save the ingestor.

The sustainable fixes depend on your situation. If traffic is inherently bursty and unpredictable, increasing your DB instance capacity gives you more write throughput to absorb spikes. If you have control over how fast records enter the system, reducing arrival rate to 500 rec/s brings you to exact equilibrium. Records in per cycle match records out, the buffer never grows, and the DB still gets its 1 second of breathing room every cycle.

My fix worked because the arrival rate happened to be below the write speed. If it was not, the wait time would have made things worse, not better. I got lucky with the fundamentals. The estimation would have told me that upfront.

Sometimes you ship the practical fix and document the right one for later. Just make sure you eventually come back to understand why it worked.

Before you pick any solution, whether it is batching, replication, dedicated write instances, or queue-based ingestion, make sure you actually understand where the bottleneck lives.

That part isn't optional.