PostgreSQL Was Down Again: Rebuilding LLM Trace Ingestion
In this piece
At 6 p.m. on December 19, 2025, the product was down again.
A burst of trace ingestion had become long-running PostgreSQL transactions; queries and Lambda workers queued behind their locks. Auto-scaling answered latency by adding workers against the same saturated database. Concurrent executions climbed past 60 from about 12. RDS had 400 connections provisioned and 403 demanded. Lambda p99 reached 300,000 ms, the function timeout.
The queue still accepted work; the public API did not. Customers’ SDKs got errors; the OTEL collector got 401s from an auth service starved of connections. One instance served trace inserts, authentication, and metadata, so an ingestion backlog became a product outage.
We knew the containment: stop the Lambda creating the inserts, let the database drain, let the queue absorb the surge. It worked, as before, and changed nothing. Append-heavy ingestion had to be isolated from authentication and metadata.
This is the post-mortem and the story of a five-month migration: an S3-backed pipeline in Go, using Kafka and serving ClickHouse and Quickwit. It is the first article in the series.
What we had built
The old path was reasonable for a workload that turned out not to exist. We designed it to ship observability this quarter, not to handle fifty times the volume.
The product traces LLM calls, including prompts, completions, tool outputs, and metadata, so payloads of 10 to 20 MB were normal. They arrived through API Gateway into TypeScript Lambdas, which wrote PostgreSQL first, ClickHouse second. SQS buffered work between stages.
Every trace was an ACID transaction of TypeORM entities cascading across tree relations, with trigram indexes on the text columns keeping search fast. All defensible at low volume: no capacity planning, one mental model, no search engine.
But we had built a transactional system for an analytical, append-heavy, write-once-read-aggregated workload.
A 15 MB payload makes the transaction do more work: cascading ORM round trips and synchronous trigram maintenance over megabytes of text. The longer the transaction takes, the longer it can hold locks that other queries need. The dual-write made ClickHouse wait behind PostgreSQL on every write.
Why it failed under load
The post-mortem went up in under 24 hours. I later wrote about why writing it that fast was a mistake. The chain is mechanical.
- T0Trace-ingestion burst reaches PostgreSQL
10–20 MB payloads, indexes, cascades
- T1Long trace transactions hold locks on api_keys and log
database waits
- T2Lambdas wait until their 300-second timeout
rising latency
- T3Auto-scaling grows concurrency from about 12 to more than 60
more blocked consumers
- T481 SQS messages become non-visible while functions wait
shared database saturates
- T5Connection demand reaches 403 against 400 provisioned connections
collector sees 401s
- T6Collector retries 401s against the blocked authentication path
One overloaded PostgreSQL instance turned long trace writes into lock contention, Lambda timeouts, invisible queue work, and failing authentication requests.
Locks. Writes to api_keys and log, the two hottest tables, collided: every API call validates a key, every trace writes logs. The lock queue is where the incident began; everything after was physics.
Timeouts, then scaling. The Lambdas didn’t fail fast; they blocked until the 300-second timeout killed them. Auto-scaling saw latency and did its job, taking concurrency from about 12 to past 60 and hammering the same locked tables. The scaling response was the attack.
Invisible work. SQS messages piled up: 81 non-visible, claimed by functions stuck in their 300-second waits, neither processed nor retryable. The backlog grew while the system looked like it was working.
Auth starvation. The pool saturated (400 provisioned, 403 demanded) and the public API stopped responding. Customers not even sending traffic couldn’t load the dashboard; their auth queries queued behind trace inserts.
Retries hammering. The OTEL collector, seeing 401s, retried with no circuit breaker, hammering an auth service already drowning.
One line from the post-mortem:
An OLTP database should never slow down an OLAP database.
The bets
We had five months and a customer base growing 40% month over month, so we made a sequence of bets on the workload mismatch.
S3 as the durable source of truth. Store the payload as an object before processing it. Once the object is written, it remains available for downstream retries. In December a failed write could mean a lost trace because the transaction was the only copy.
Kafka as the notification and backpressure boundary. S3 holds the data; Kafka carries the pointer. Consumers can process records at their own pace and replay them using offsets. The S3 write and Kafka publish are separate steps, so a successful object write alone does not guarantee that a notification reaches consumers.
Containers instead of Lambdas for the hot path. The 300-second timeout was the wrong execution model for our workload. A containerized Go worker can keep connections open and process streams without that Lambda execution limit. We accepted the work of running and scaling containers in exchange.
One store per read workload. ClickHouse for analytics, no longer waiting behind PostgreSQL. Quickwit for full-text search, which indexes from Kafka and keeps its segments on S3. ClickHouse shouldn’t hold megabytes of prompt text inline, and a trigram index is not a search engine. Blobs stay in S3; the databases hold URLs and metadata.
The price was real. Kafka is a distributed system we now operate. Redis became a coordination dependency. Two runtimes must agree on one contract. One database that paged us became four systems that mostly don’t, plus a standing bill and a steeper onboarding curve. On this workload the trade wins clearly. It is still a trade.
The migration
The work ran from November 2025 to March 2026, in shippable steps, because the product had to stay up.
The collector and HTTP API moved to the durable boundary: S3, then Kafka. Go processors normalized, split, and fanned the data out to ClickHouse and Quickwit (Go concurrency patterns). PostgreSQL returned to auth and metadata.
The dual-write went only after the new read paths proved out in production. The TypeScript API stayed, leaving the contract implemented twice.
What I would do differently
Two regrets.
Dead-letter topics from day one. A durable pipeline needs a durable place for unprocessable work; otherwise a poison message retries forever or vanishes on operator intervention. A dead-letter topic preserves the pointer, failure reason, and consumer context, which keeps the failure inspectable and replayable.
Generate the contract. ParsedSpan was defined twice, in TypeScript and Go, and kept aligned by tests and discipline. On March 12, a span-kind normalization fix crossed eleven files after the two implementations diverged enough to corrupt an edge case. A shared, generated and enforced schema would have reduced the risk of that drift.
Hot take: PostgreSQL is not the default
One OLTP instance had handled auth, metadata, trace storage, search, and ingestion. PostgreSQL can do a remarkable amount; it should not do every job. Focused systems each do a bounded job; the hard part is the boundaries and the contracts.
If you are read-heavy or aren’t building on transactional domain invariants, don’t default to PostgreSQL. It may still be right for auth, metadata, or a small transactional core. It is not the universal default.
The series goes deeper where this stayed wide: OTLP streaming in Go, Kafka’s record-size limit, the ClickHouse schema, out-of-order spans, three services in one Go binary. Next: the split that started it, why full-text search left PostgreSQL, then ClickHouse, for Quickwit.
The incident taught me to start with failure boundaries: if ingestion stalls, can a customer still authenticate?
Filed under
Next in Rebuilding an Ingestion Pipeline
We Put Full-Text Search in PostgreSQL, Then ClickHouse. Both Were the Wrong Place.
Part 2 continues the series.