We Put Full-Text Search in PostgreSQL, Then ClickHouse. Both Were the Wrong Place.
In this piece
The logs table stored trace IDs, users, token counts, latency, prompt input, model output, metadata, and variables. We used the same table to show one trace, graph cost by day, filter a feature’s recent spans, and find text a user remembered typing three weeks ago.
We put those fields in one table because the schema looked tidy. Under load, that choice put incompatible queries on one database. LLM telemetry included large text payloads; the product needed exact lookup, analytics, and full-text retrieval.
We first added trigram indexes to PostgreSQL, then stored text beside the analytics rows in ClickHouse. The product still needed to retrieve arbitrary words from complete prompts and responses without increasing write costs or slowing dashboards.
Because one trace serves several read workloads, we assigned each workload to a store: PostgreSQL keeps transactional product data; ClickHouse keeps structured, bounded analytical projections; Quickwit indexes complete text for retrieval; S3 keeps durable payload objects. This split prevents one storage engine from handling four incompatible jobs.
This is the second article in the series. The first explains the outage that forced the migration.
The original design: a searchable transaction
The old path wrote multi-megabyte LLM traces into the same PostgreSQL instance that served authentication and metadata. We added trigram indexes for text lookup, then wrote traces to PostgreSQL first and ClickHouse second. TypeORM Active Record entities cascaded through trace relationships, so every extra byte and index update extended the transaction on the shared database.
At low volume, one database was a sensible shortcut. Under load, it mixed transactional product data, append-heavy telemetry, arbitrary-term retrieval, and analytical filters and aggregates.
The first article traces the outage in full. The storage consequence was direct: large trace writes, trigram maintenance, cascading relations, and the shared connection pool delayed authentication on the same PostgreSQL instance.
PostgreSQL remains well suited to product records with transactional invariants. Multi-megabyte telemetry belongs outside its transactional write path and search corpus.
ClickHouse kept the retrieval problem
ClickHouse already held the monitoring read model. Its columnar layout, (FeatureId, Date, TimestampMs) ordering, and materialized views suit feature-scoped time ranges, aggregates, and trace lookup.
Moving complete text there did not solve the retrieval requirement. A phrase search must inspect complete prompts and responses, return candidate span and trace IDs, then let the structured store filter and render the traces. Full bodies enlarge analytical rows without giving ClickHouse a corpus-wide term index, ranking model, or search pagination contract.
The schema makes the boundary explicit: InputTruncated, OutputTruncated, MetadataTruncated, and VariablesTruncated are display previews. The Go path caps previews at 1,024 runes; the TypeScript path uses 500-character *Truncated fields and omits full fields above 10,000 characters. ClickHouse ILIKE searches previews; Quickwit searches complete text.
Measure the actual query before adding a search engine: table scope, candidate rows, text bytes, peak memory, latency, write cost, and retention. The available migration records contain no exact ClickHouse FTS query or measurements of corpus size and peak memory, so this article makes no benchmark claim about ClickHouse.
Name the jobs before naming the stores
Calling the whole system “trace storage” hid the design. One ParsedSpan represents one logical event and produces several purpose-specific projections.
| Workload | Question | Store | Stored form |
|---|---|---|---|
| Transactional product data | Can this auth or metadata change commit consistently? | PostgreSQL | Small relational records and invariants |
| Exact trace lookup and filtered monitoring | Which traces for this feature and time range match structured filters? | ClickHouse | IDs, timestamps, dimensions, token/cost/latency fields, bounded previews |
| Full-text retrieval | Which span or trace contains these words or phrases in its complete text? | Quickwit | Text documents keyed by span, trace, feature, time, and field type |
| Payload retrieval and replay | Where is the original or per-span payload? | S3 | Raw telemetry, transformed batches, and individual span JSON |
Quickwit returns candidate span_id and trace_id values; ClickHouse applies structured filters and renders the trace view; S3 supplies the durable object for detailed inspection, evaluation, or replay. Stable feature_id, trace_id, and span_id values connect those projections without copying every ClickHouse filter into Quickwit.
Search finds candidates; analytics explains them
Quickwit finds text candidates; ClickHouse provides data for the trace screen. For a phrase in an output, the application scopes a Lucene query to feature_id and output, receives matching IDs, then asks ClickHouse to apply time, user, organization, model, duration, token, and cost filters.
That keeps Quickwit from becoming a shadow monitoring schema. New analytical dimensions do not require a reindex, because ClickHouse still owns them.
Search has an explicit candidate cap; users refine broad queries, operators raise it after measurement, or a separate export operation uses a distinct limit. A Quickwit outage disables complete-text filters while ClickHouse still lists bounded previews. The UI should tell users that full-text filtering is temporarily unavailable.
One span, several projections
The processing pipeline reads a transformed NDJSON batch from S3, normalizes timestamps, and fans three projections from each ParsedSpan out to ClickHouse rows, Quickwit documents, and individual S3 JSON objects.
The concurrency patterns behind this fan-out let each destination fail independently.
The processing pipeline sends each trace projection to the store that serves its query.
The pipeline sends otel.search.documents through Kafka with at-least-once delivery semantics. A span can yield separate input, output, metadata, and variables documents; the producer splits oversized documents into chunks. Search intersects the result sets for requested field types and deduplicates IDs before asking ClickHouse for rows.
Quickwit carries only the filters needed for text retrieval: feature ID and field type. ClickHouse remains responsible for user, organization, model, cost, and time filters.
The operational bill is part of the design
Operating Quickwit requires the team to manage retries, duplicate results, record limits, and query escaping. The processor uses a 900 KiB default batch budget and reserves record-framing margin, so the publisher splits large fields before publishing. Response-size and candidate caps bound search; the Lucene builder scopes queries by feature_id and text_type, rejects wildcard-only input, caps values, and escapes special characters. Quickwit search and ClickHouse reads must tolerate duplicate documents and rows, respectively.
Define retention separately for Quickwit indexes, S3 objects, ClickHouse partitions, and PostgreSQL product data.
Retention changes the price of each query
Set the Quickwit retention window by how long users need complete-text search, and set the ClickHouse period by monitoring needs. S3 objects remain for permitted replay; PostgreSQL follows product and compliance rules.
Trace IDs still connect the surviving projections. When an old Quickwit document expires, ClickHouse can still explain spend and latency; when a ClickHouse preview expires, S3 may still hold a payload. The UI must tell users which capability each expiry removes.
Explicit windows define the retention contract. For example: “Search for 30 days, analyze for 13 months, replay for 90 days.”
Criteria for adding another engine
Adding a separate engine requires the team to manage credentials, backups, upgrades, monitoring, cost, and incident procedures. Add one only when it removes a demonstrated workload conflict:
- Full text is much larger than the bounded structured projection. The product needs more than a display preview.
- Retrieval differs from analytics. Users need term or phrase matching, ranking, and search-specific pagination.
- A query profile shows the cost. Full-corpus indexing or scanning harms writes, memory, latency, or retention.
- Search can return pointers. Stable IDs let ClickHouse hydrate the structured trace view.
- The team can operate the new boundary. It must manage retries, duplicates, query escaping, record limits, and expiry.
Against a production-like corpus and retention window, profile a representative query with the intended feature scope, time range, result cap, and text fields. Measure scanned rows, bytes, peak memory, latency, and write cost; then decide whether ID-based hydration is enough.
Start with the query, its memory profile, and its retention window. Store the projection that answers it. After the split, a text-search failure does not block the product’s transactional write path.
Filed under
Explore this subject