Skip to content
Corentin GS

We Put Full-Text Search in PostgreSQL, Then ClickHouse. Both Were the Wrong Place.

№46 · · ·859 words ·4 min read
In this piece

The logs table stored trace IDs, users, token counts, latency, prompt input, model output, metadata, and variables. We used that one table to show a trace, graph cost by day, filter a feature’s recent spans, and find text a user remembered typing three weeks ago.

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.

So we assigned each workload to a store. PostgreSQL keeps transactional product data, ClickHouse keeps bounded analytical projections, Quickwit indexes complete text, and S3 keeps durable payload objects.

Why the first two attempts failed

In PostgreSQL, every extra byte and index update extended a transaction on the instance that also served authentication. The first article traces that outage. PostgreSQL remains well suited to product records with transactional invariants, but multi-megabyte telemetry belongs outside its write path and search corpus.

ClickHouse already held the monitoring read model. Its columnar layout, (FeatureId, Date, TimestampMs) ordering, and materialized views suit feature-scoped time ranges and aggregates.

Moving complete text there left the retrieval problem in place. Full bodies enlarge analytical rows without giving ClickHouse a corpus-wide term index, ranking model, or search pagination contract.

The schema now makes the boundary explicit. InputTruncated, OutputTruncated, MetadataTruncated, and VariablesTruncated are display previews. The Go path caps them at 1,024 runes; the TypeScript path uses 500-character *Truncated fields and omits full fields above 10,000 characters. ClickHouse ILIKE searches previews, and Quickwit searches complete text.

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.

WorkloadQuestionStoreStored form
Transactional product dataCan this auth or metadata change commit consistently?PostgreSQLSmall relational records and invariants
Exact trace lookup and filtered monitoringWhich traces for this feature and time range match structured filters?ClickHouseIDs, timestamps, dimensions, token/cost/latency fields, bounded previews
Full-text retrievalWhich span or trace contains these words or phrases in its complete text?QuickwitText documents keyed by span, trace, feature, time, and field type
Payload retrieval and replayWhere is the original or per-span payload?S3Raw telemetry, transformed batches, and individual span JSON

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

The processing pipeline reads a transformed NDJSON batch from S3 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.

PROCESSINGProcessing pipeline
ANALYTICSClickHouseanalytics
TEXT SEARCHQuickwitfull text
PAYLOADS3 span JSONpayload
One trace, three stores

The processing pipeline sends each trace projection to the store that serves its query.

For a phrase in an output, the application scopes a Lucene query to feature_id and output, then receives matching IDs. It 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.

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.

The operational bill is part of the design

Operating Quickwit means managing retries, duplicate results, record limits, and query escaping:

  • Delivery. otel.search.documents travels through Kafka with at-least-once semantics, so search intersects the result sets for requested field types and deduplicates IDs before asking ClickHouse for rows.
  • Size. The processor uses a 900 KiB default batch budget and splits large fields before publishing.
  • Queries. Search has an explicit candidate cap. The Lucene builder scopes queries by feature_id and text_type, rejects wildcard-only input, caps values, and escapes special characters.

Each store also needs its own retention window. Trace IDs connect the surviving projections. When an old Quickwit document expires, ClickHouse can still explain spend and latency. The UI must tell users which capability each expiry removes.

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:

  1. Full text is much larger than the bounded structured projection. The product needs more than a display preview.
  2. Retrieval differs from analytics. Users need term or phrase matching, ranking, and search-specific pagination.
  3. A query profile shows the cost. Full-corpus indexing or scanning harms writes, memory, latency, or retention.
  4. Search can return pointers. Stable IDs let ClickHouse hydrate the structured trace view.
  5. The team can operate the new boundary. It must manage retries, duplicates, query escaping, record limits, and expiry.

Profile a representative query against a production-like corpus and retention window. 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, then store the projection that answers it. After the split, a text-search failure does not block the product’s transactional write path.

Next in Rebuilding an Ingestion Pipeline

Kafka Rejects Records Over 1 MB. LLM Spans Do Not Care.

Part 3 continues the series.