Building a Search Index Alongside a Relational Database
For most client projects, we start with Postgres's built-in full-text search — a tsvector column, a GIN index, and a trigger to keep it updated. It's genuinely good for catalogs under a few hundred thousand rows and means one fewer system to operate. For a recipe platform with roughly 180,000 recipes, it held up fine for over a year.
The limits showed up around fuzzy matching and relevance tuning. Users searching 'chiken parmesan' expected results despite the typo, and ranking that weighted title matches over ingredient-list matches was awkward to express in Postgres's ranking functions. We moved search to Elasticsearch, keeping Postgres as the system of record and Elasticsearch as a derived, rebuildable index.
The hard part isn't standing up Elasticsearch — it's keeping the two in sync without either double-writing everywhere or falling behind. We used a change-data-capture approach: Postgres logical replication feeding a Debezium connector into Kafka, with a consumer that upserts documents into Elasticsearch. This decouples indexing from the request path entirely, so a write to Postgres never waits on Elasticsearch being healthy.
This did introduce eventual consistency we had to design around. A recipe published now might not appear in search for 1-3 seconds, which was fine for browsing but caused confusion when users searched immediately after creating their own recipe. We added a client-side optimistic insert so a user's own new content shows up instantly in their view, backed by the real index for everyone else.
We also built a full reindex job that can rebuild the entire Elasticsearch index from Postgres in about 25 minutes, which we run after any mapping change and keep as a safety net — since the index is derived data, we never worry about it being a source of truth we could lose.
