A high-throughput JSON ingestion pipeline, built one bottleneck at a time

This started as interview prep. A real-time data pipeline was coming up as a topic, and I figured I’d build one rather than read about it. The project that came out the other end is fastapi-dbuploader: an HTTP+gRPC ingestion service writing into Postgres via a pluggable broker, with full observability and Kubernetes deployment.

The architecture diagram you’d whiteboard at the end is unremarkable. The path that produced it was a series of “I assumed this was fast, it wasn’t” moments, each forcing a decision worth recording.

Starting simple, on purpose

Version one was the obvious thing: a FastAPI endpoint that accepted a JSON payload and wrote it to Postgres synchronously. It worked, and it capped out around 300 requests per second.

Skipping this stage and going straight to a buffered design would have been faster, but I wanted to measure where the wall was rather than guess. The endpoint wasn’t the slow part and Postgres wasn’t the slow part — the synchronous round-trip between them was, which meant the fix had to be on the ingestion contract rather than on the database.

Buffer in front of the database

The next iteration buffered to Redis Streams and returned 202 Accepted immediately. A separate writer process consumed the stream and wrote to Postgres. Throughput jumped, then stalled again in a different place — the endpoint was no longer the bottleneck, the writer was.

The status-code change was deliberate. 200 OK would have been a lie: at the moment the response goes out, the data isn’t durable in Postgres yet. 202 Accepted is the right semantics for async processing, and downstream consumers can reason about retries and idempotency accordingly.

Bulk-write strategy before any tuning

The writer-side bottleneck was per-row INSERT overhead, not Postgres throughput. Switching to COPY FROM STDIN with binary format took the writer from a few thousand rows/sec into the tens of thousands. Bulk loaders have used COPY for forty years for a reason, and any time more than a handful of rows are written in one logical batch, that’s the right primitive — I’d been avoiding it as overkill, which it wasn’t.

Two services, because they scale differently

Once the pipeline worked, I split it into two deployments: an ingestion service (CPU-light, parses + publishes) and a writer pool (I/O-heavy, batches + COPYs). Same repo, shared src/common/ package.

Scaling them together would have meant over-provisioning one and under-provisioning the other under any non-trivial load. Splitting them lets each scale on its own curve, and the split is cheap when made early and a real refactor when made late.

A pluggable broker

I added a BrokerClient protocol with implementations for Redis Streams and Kafka. Switching is one environment variable. Redis Streams is enough for most single-host workloads; the abstraction is cheap to put in if the seam is drawn in the right place from the start, and it lets the same codebase deploy into Kafka-native environments without a rewrite.

What the reviews caught

Every milestone went through automated code review and a security pass. The reviews caught what load tests didn’t: an auth bypass, a metric counter wired to the wrong module path, histogram buckets in the wrong unit, a silent failure swallowed several callers up. None of those would have surfaced under load — the system kept running, just incorrectly. Several had been quietly in place for entire milestones before the review caught them.

Was it worth it?

The end-state load test pushed tens of thousands of payloads per second through the full pipeline on a single laptop, with the load generator, the pipeline, Redis, Postgres, Grafana, Prometheus, Jaeger, and Loki all fighting for the same cores. The full optimisation journey from 300 req/s to that number is documented in the repo’s load testing report, which reads in retrospect as a record of my assumptions getting corrected.

I didn’t take the job. The repo stays around as my reference answer to “how would you design X” — every choice in it was made in response to a measured problem rather than in anticipation of a hypothetical one.

dmorel69/fastapi-dbuploader — Python, FastAPI, gRPC, Redis Streams, Postgres