Search that works without Elasticsearch
SQLite FTS5, trigram matching, and when to stop and hire a search engine.
The numbers below are from my own machine and my own workload. Treat them as a method, not a benchmark: the point is how the measurement was taken, not the exact figures it produced.
Where I started
The first version worked, which is exactly why it was hard to see how wrong it was. Everything looked fine in the demo, and everything looked fine until traffic arrived on a Tuesday afternoon and the graph started doing something I'd never seen before.
There is a version of this article that opens with theory. I decided against it, because the theory is easy to find and the motivation is not — so let's start with the thing that actually broke.
CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at DESC)
WHERE status = 'open';What went wrong
What surprised me most was how small the final change was. Most of the work was understanding the problem precisely enough that the fix became obvious, and that understanding took far longer than the fix itself.
What surprised me most was how small the final change was. Most of the work was understanding the problem precisely enough that the fix became obvious, and that understanding took far longer than the fix itself.
A tip for a practical recommendation.
The fix
The first version worked, which is exactly why it was hard to see how wrong it was. Everything looked fine in the demo, and everything looked fine until traffic arrived on a Tuesday afternoon and the graph started doing something I'd never seen before.
I want to be honest about the trade-offs here. The approach I settled on solves the problem I had, not every problem of this shape, and there is a clear point where you should reach for something more heavy-duty.
- Latency at p50, because most users live there
- Latency at p99, because someone always lives there
- Error rate, because a fast failure is still a failure
- Saturation, because averages hide the cliff
What I'd do differently
By the end of this post you should be able to reproduce the whole thing yourself in under an hour, including the parts I got wrong the first time — those are marked so you can skip them if you're in a hurry.
There is a version of this article that opens with theory. I decided against it, because the theory is easy to find and the motivation is not — so let's start with the thing that actually broke.
The numbers below are from my own machine and my own workload. Treat them as a method, not a benchmark: the point is how the measurement was taken, not the exact figures it produced.