Suppose your product catalog lives in Postgres and search is ILIKE '%term%'. It works, nobody complains, and the table is small. Then hedphones returns nothing, the query plan turns into a sequential scan, and you start reading about Meilisearch.
The indexing part is genuinely easy. docker compose up, create the index, push documents, done. The guide linked below walks the whole build and every number in it comes from two runs against postgres:18.6-alpine and getmeili/meilisearch:v1.56.0 over 20,002 rows, verified 2026-11-03.
What is not easy is the part nobody writes about: the worker that keeps the two in step.
The bug is a timestamp, not a query
Postgres now() is the transaction timestamp. A single INSERT ... SELECT of 20,000 rows gives every row the exact same updated_at — checked directly against that seed, 20,000 rows, one distinct value.
Now write the obvious cursor:
WHERE updated_at > $1 ORDER BY updated_at LIMIT 1000
First page indexes 1,000 rows. The cursor becomes the shared timestamp. The second page returns nothing. No error, no log line, 19,000 documents silently missing. Switch to >= and you get the mirror bug: the same 1,000 rows forever while the worker spins, which is how a worker stalled at 500 documents during this build.
The fix is a row comparison on (updated_at, id) with a composite index and a strict >. The id breaks the tie, and the total order survives a bulk load. It does not survive commit order — which is the part I had wrong until lycore pointed it out in the comments.
There is a second trap underneath it. pg parses timestamptz into a JavaScript Date, which keeps milliseconds. Select the column without a cast and you hand back a value slightly earlier than the rows you just read, so the next page starts inside the previous one — reported progress that never advances. updated_at::text as the cursor format is the whole fix.
The case the tuple does not cover
now() is the transaction timestamp, not the commit time. A transaction that starts at 09:00, writes one row and commits at 09:04 stamps that row with 09:00. If the worker read past 09:00 at 09:02, the row sits behind the cursor forever. (updated_at, id) orders rows by timestamp; commit order is a different sequence, so the tie-breaker does not rescue it.
Two fixes, neither free. Overlap: read from cursor - interval '5 minutes' and upsert by primary key, which is idempotent and only narrows the hole to transactions longer than the overlap. A scheduled rebuild: at 20,000 documents a reset and reindex takes about a second, so a nightly rebuild clears what slipped through, and that is the cheapest correct answer while the index is small.
An outbox does not remove it either. A bigserial hands out ids in insert order, not commit order, so a lower id can still commit after a higher one. Past the size where rebuilds are cheap, the cursor has to come from the WAL — logical replication — because that is the only sequence that matches commit order.
What you actually gain
The same four queries on both engines, counts identical across both runs:
-
wireless headphones— 31 hits in Postgres withplainto_tsquery, 1,112 in Meilisearch. -
hedphones— 0 versus 578. -
mechas a prefix — 0 versus 1,123.
The second and third rows are the real story, and they are not the same story. The prefix gap is a query shape: to_tsquery('english', 'mech:*') gets Postgres the same 1,123. The typo gap is structural. Substring matching also returns 0 because the missing character is in the middle, and edit distance is not in Postgres core — you would add pg_trgm, a second index and a second scoring model.
The 31-versus-1,112 gap on two words is a design choice, not a bug either way: strict AND is easier to trust, term dropping is easier to use.
The cost is disk, and it is 4.3x
Roughly 330 bytes per document in Postgres against 1,400 in Meilisearch. At 20 million documents that is 6.6 GB of Postgres index against 28 GB of Meilisearch — size the disk for the second number.
The trade is fine once you notice the index is disposable. Reset the cursor to to_timestamp(0) and a full rebuild of 20,000 documents takes about a second. A corrupt read model is a one-minute problem, not a restore-from-backup problem, and that is the property that makes the whole architecture worth it.
Full build: schema, trigger, keyset worker, search endpoint with allowlisted filters, and the measured latency tables — including why timing Postgres through psql \timing in docker exec gave numbers an order of magnitude too high.
Build a Self-Hosted Search API with Meilisearch and PostgreSQL
Top comments (2)
The timestamp precision trap is a good reason to keep the cursor opaque across the application boundary. One additional edge case I would test is a transaction that obtains an earlier updated_at but commits after the worker has advanced past it; the (timestamp, id) tie-breaker alone does not cover commit ordering. Do you use an overlap-and-deduplicate window or an outbox/change stream for that case, and how are deletions propagated?
Thanks for your comment, you're right, and the tuple does not cover it. now() is the transaction timestamp: a transaction that starts at 09:00 and commits at 09:04 stamps its row 09:00, so if the worker read past 09:00 at 09:02 the row stays behind the cursor. (updated_at, id) orders rows, not commits.
No overlap window and no outbox in the worker. What it has is a one-second rebuild at 20k documents, so: a five-minute overlap with an upsert by primary key plus a nightly rebuild for whatever slips through. A bigserial outbox does not help, a lower id can commit later. Past the size where rebuilds are cheap, the cursor has to come from the WAL.