PostgreSQL Was Down Again: Rebuilding LLM Trace Ingestion

What we had built#

The old ingestion path was a reasonable system built for a workload that turned out not to exist.

When we designed it, the product was smaller, traces were smaller, and the question was “how do we ship observability this quarter,” not “how does this behave at fifty times the volume.” The choices below won on their merits at the time. The workload grew around them until the merits stopped mattering.

The product traces LLM calls. A single trace can carry full prompts, full completions, tool outputs, metadata; payloads of 10 to 20 MB were normal, not pathological. That data arrived through API Gateway into TypeScript Lambdas, which wrote to PostgreSQL first and ClickHouse second, in a sequential dual-write. SQS sat between stages for buffering.

PostgreSQL—one instance—held everything: auth, metadata, and the log storage itself. Every ingested trace became an ACID transaction involving TypeORM Active Record entities with cascading inserts across tree relations. On top of the text columns, we maintained trigram indexes so search stayed fast. Those indexes updated synchronously, inside the same transaction as the insert.

Each piece was defensible on its own:

• Lambdas meant no capacity planning for a spiky workload.
• One PostgreSQL instance meant one mental model for a small team.
• The dual-write meant ClickHouse could stay an optimization, not a commitment.
• Trigram indexes meant search worked without a search engine.

A 15 MB payload entering an OLTP transaction means long-held locks. Trigram index maintenance on massive text columns means those locks hold longer. Cascading ORM relations mean one logical trace becomes many round trips inside the same transaction. Cold starts meant each new Lambda paid a TLS handshake and connection setup tax before it could even join the queue of waiters. And the sequential dual-write meant ClickHouse, an analytical engine that should never have cared about PostgreSQL’s health, waited behind it on every write. A slow trace insert didn’t just delay the trace; it delayed the analytics row behind it and the auth query behind that.

None of this hurt at low volume. At low volume it’s indistinguishable from a good architecture.

We had built a transactional system for an analytical, append-heavy, write-once-read-aggregated workload.

December exposed the mismatch between the architecture and the workload.

Why it failed under load#

I put the post-mortem on the wall in less than 24 hours, after spending the night tracing PostgreSQL logs and Datadog dashboards. I later wrote about why writing it that fast was a mistake. Every step in the failure chain follows mechanically from the one before.

1. Lock. Under a burst of ingestion traffic, the writes to api_keys and log started colliding. Every API call validated a key; every trace wrote logs. These are the two hottest tables in the product, and they shared one instance. Long-running insert transactions (10 to 20 MB payloads, trigram index updates, cascades) held locks while shorter queries queued behind them. The lock queue is where the incident actually began; everything after was physics.

2. Timeout. The Lambdas processing these writes blocked waiting on the database. They didn’t fail fast; they blocked until the 300-second timeout killed them. AWS’s auto-scaling saw rising latency and did exactly what it’s designed to do: added concurrency. We scaled from about 12 concurrent executions to more than 60. Each new function opened new database connections and issued new queries against the locked tables. The scaling response was the attack.

3. Queue contention. Behind the Lambdas, SQS messages piled up. At the peak we counted 81 non-visible messages: consumed by functions that were now sitting in their 300-second timeout, locked out of visibility, neither processed nor available for retry. SQS’s visibility timeout is a sensible mechanism in a healthy system. In ours it produced invisible messages. Work was being “done” by functions that would be killed before finishing, invisible to any other consumer, while redelivery counters climbed toward the dead-letter threshold. The backlog grew while the system looked, from the outside, like it was working.

4. API degradation. The connection pool saturated (400 provisioned connections, 403 demanded) and the public API, which shared the database for auth and metadata, stopped responding. This is the step that turned an ingestion problem into a product outage: customers who weren’t even sending traffic couldn’t load the dashboard, because auth queries were queued behind trace inserts.

5. Collector hammering. The OTEL collector, seeing 401s from the auth path, retried. We had no circuit breaker on that path. The collector kept retrying and adding load to an authentication API that was already drowning, context-deadline-exceeded errors piling up on the client side while the server tried to serve lock-waiting queries.

The post-mortem distilled the boundary in one line:

An OLTP database should never slow down an OLAP database.

Auto-scaling made it worse: it reacted to latency by adding concurrency, which aggravated the contention.

The migration bets#

We had five months and a paying customer base growing 40% month over month. So the plan was a sequence of bets, each one scoped to the workload mismatch rather than to any technology preference.

Bet 1: S3 as the durable source of truth. Engineers should put the payload somewhere that cannot lose it and cannot lock before any other processing begins. An S3 object write is atomic, effectively unlimited in size, and durable by default. Once the payload is an object, everything downstream becomes replayable, and “replayable” is the property the old system lacked most painfully. In December, a failed write was a lost trace, because the transaction was the only copy. In the new model, a failed write is a retry against an object that is still sitting exactly where it landed.

Bet 2: Kafka as the notification and backpressure boundary. S3 holds the data; Kafka carries the pointer. This inverts the old model: instead of queue messages carrying 15 MB payloads through consumers with arbitrary timeouts, a small notification says “batch ready at this key.” Kafka gives us replay via offsets, consumer-level pacing, and a hard backpressure boundary that fails independently of the stores behind it. The December ghosts (messages stuck invisible inside dying functions) become impossible by construction: a Kafka consumer either processes the record and commits the offset, or the record waits, visible, for the next consumer.

Bet 3: Containers instead of Lambdas for the hot path. The 300-second timeout was not a configuration detail; it was the wrong execution model. A containerized worker keeps a persistent connection, processes streams continuously, and has no arbitrary ceiling on how long one unit of work may take. We were explicitly betting that Go’s predictable latency and streaming memory profile would compensate for losing the warm-start ergonomics and zero-capacity-planning of Lambdas. Lambdas still make sense for spiky, small, short work. Continuously streaming 20 MB payloads is none of those three.

Bet 4: Purpose-built stores per read workload. ClickHouse for analytics and monitoring queries, the workload it was born for, no longer waiting behind PostgreSQL. A dedicated engine for full-text search over the large text fields (we looked at Meilisearch first and landed on Quickwit, which indexes straight from Kafka and stores its segments on S3), because ClickHouse should not be storing megabytes of prompt text inline, and a trigram index is not a search engine. Blobs stay in S3; the databases hold URLs and metadata.

Each bet removed a failure mode from December. S3-first removes the long transaction. Kafka removes the payload-carrying queue with its visibility timeouts and invisible messages. Containers remove the arbitrary timeout that turned latency into connection storms. Split stores remove the coupling that let a trace insert take down an auth query. We were deleting failure modes, not shopping for technology. On a different workload—small transactional records at human-speed traffic—the old architecture would still be fine. The decisions were durability before processing, notification instead of payload-carrying queues, long-lived workers for long-lived work, and one store per query shape.

Write durable first. Notify second. Process last.

The bets had a price. Kafka is a distributed system we now operate instead of a queue we rented. Redis became a coordination dependency with its own failure modes. The processor and the API are two runtimes to keep honest about one contract. We traded one database that paged us for four systems that mostly don’t, plus a standing infrastructure bill and a steeper onboarding curve for anyone joining the team. On this workload, at this volume, that trade wins clearly. It is still a trade.

The migration#

The work ran from November 2025 to March 2026. We replaced the pipeline in independently shippable steps because the product had to remain available throughout.

First, both the collector and the HTTP API were moved to the same durable boundary: write the payload to S3, then publish a pointer to Kafka. Go processors normalized, split, and fanned that data out to ClickHouse and Quickwit. The Go concurrency patterns behind those services let each stage consume and scale independently. PostgreSQL returned to auth and metadata rather than carrying the ingestion workload. The old dual-write disappeared only after the new read paths were proven in production.

We kept the HTTP API in TypeScript to avoid disturbing SDK clients during the cutover, which left the contract implemented twice: once in TypeScript and once in Go.

What I would do differently#

Only two choices.

Add dead-letter topics from day one. A durable pipeline needs a durable place for work that cannot be processed. Without one, a poison message either retries forever or disappears after an operator intervenes. A dead-letter topic would preserve the payload pointer, failure reason, and consumer context so the failure is inspectable and replayable rather than anecdotal.

Generate the contract shared by TypeScript and Go. ParsedSpan, the type everything downstream consumes, was defined twice and kept aligned by tests and discipline. On March 12, a span-kind normalization fix crossed eleven files because the two implementations had diverged enough to corrupt an edge case. Protobuf, JSON Schema, or another generated and enforced schema would have made that mismatch impossible to ship.

We kept duplicate contracts to preserve the existing API and ship faster. That was a trade-off, but not every trade-off made in the name of legacy is worth taking. A migration should remove the boundary that creates the risk, not preserve it because it is already there.

What to reuse#

Three rules from this migration apply broadly.

Write durable first. Make the first write atomic and boring—S3, GCS, or a WAL—so downstream failures cost time, not data.

Design from reads. List the queries first, then give analytics, search, blobs, and transactional data the stores that suit them.

Make stages fail independently. If one stalled stage can take down the product, the boundary is in the wrong place.

Hot take: PostgreSQL is not the default#

This outage happened because one OLTP instance handled authentication, metadata, trace storage, search indexes, and ingestion. PostgreSQL can do a remarkable amount; it should not do every job. One failure domain was serving incompatible workloads.

Running several focused systems is not as hard as people make it sound. Object storage, a broker, an analytical store, and a search engine each have a bounded job. The hard part is choosing those boundaries and enforcing the contracts between them.

If you are read-heavy, expect meaningful traffic, or are not building around transactional domain invariants, do not default to PostgreSQL. Start with the query shapes and the failure domains. PostgreSQL may still be right for auth, metadata, or a small transactional core. It is not the universal default.

The remaining articles in this series go deeper where this one stayed wide: how we coordinate out-of-order spans across 24-hour traces with Redis timestamps, how we predict Kafka record sizes analytically instead of marshal-and-pray, when packing three services into one Go binary pays off, the five ClickHouse schema decisions behind the analytics stack, and why Quickwit, not ClickHouse, stores your prompt text.

PostgreSQL was down again. But this time, we had Kafka in front of it, Go behind it, and ClickHouse beside it. The data still flowed.

Architectural debt compounds. December 19 was the invoice; the five months after were the repayment plan. The next article starts with the out-of-order problem, which turned out to be harder than the outage.