Search beside the database
Put full-text search on its own Search Engine, so a busy query stops taking CPU and cache from checkouts and writes.
Give search its own index on a Search Engine and link the App Server to it, so the Database stops answering searches.
Move up when#
Database CPU climbs as the Search share of the mix grows, and every other query slows with it.
Search latency is high even though the Database has room for point reads.
You want to rank results or match typos, which a
LIKEquery cannot do cheaply.
The Design#
Drag to pan. Hold Ctrl or Cmd and scroll to zoom.
| Where | Setting | Value | Why |
|---|---|---|---|
| App Server | Instance size | small | Small gives the app room to run the query it builds for each search. |
| Database | Instance size | small | The Database keeps the rows. Once a Search Engine is linked it stops answering searches. |
| Search Engine | Product | elasticsearch | Elasticsearch takes shards, replicas, and a heap choice when the index grows. Meilisearch is smaller and faster, but its results drift from the query as the index grows. |
| Search Engine | Refresh interval | 1 | A new document is searchable within a second, so no search answers from an old index. A longer interval saves indexing CPU and returns staler results. |
Research this Design needs: Full-text search, Full-text search.
Users call the App Server. Search queries go to the Search Engine, and everything else still goes to the Database. The Database keeps the rows and stays the source of truth. The index is a copy built for lookup.
Why it works#
A Search Engine keeps an inverted index, which maps each word to the documents that hold it, so a query reads a few lists instead of scanning rows. Once a Search Engine is linked, the Database stops answering searches at all, and the Database load bar falls.
The Refresh interval is the lever that matters. A new document is not searchable until the next refresh, so a short interval is fresh and costs indexing CPU, and a long one answers more queries a second but returns staler results. Shards and replicas on Elasticsearch trade fan-out against query capacity.
What it costs#
You now keep two copies of the data. If the app writes to both in one request, a crash between the two writes leaves them disagreeing, and the game counts that as stale search results. A Transactional outbox or change data capture closes the gap. Meilisearch is cheaper and faster, but it has no shards, replicas, or heap, and its relevance falls as the index grows.
In Quorum#
Place a Search Engine after Full-text search. The pattern is on Full-text search and the inverted index, and the glossary defines the inverted index. The next rung stores data that does not fit rows: Document and wide-column stores.