Build a Self-Hosted Search API with Meilisearch and PostgreSQL

Postgres holds the rows, Meilisearch answers the queries, and one worker moves data between them without losing a record when a bulk INSERT gives 20,000 rows the same updated_at. This guide builds that pipeline: the schema and the trigger, the keyset cursor that fixes both tie-breaking and the JavaScript Date truncation bug, the three index settings that decide whether typo tolerance and filtering work at all, and a measured head-to-head of what each engine returns for the same four queries. Every number below comes from two runs against postgres:18.6-alpine and getmeili/meilisearch:v1.56.0 over 20,002 rows. Verified 2026-11-03.

What we are building

A search API over a product catalog, in three moving parts:

• PostgreSQL — the write model. Every product lives here, plus a one-row search_sync table that records how far the indexer got.
• Meilisearch — the read model. Documents, ranking rules, filters, highlight snippets, behind a master key.
• A sync worker — pages through products ordered by (updated_at, id), ships each page, waits for the indexing task, then commits the cursor.

The HTTP layer stays thin on purpose: it forwards q, filter and sort to Meilisearch and returns the result. Postgres never serves a search query in this design.

What you will learn

• Why the cursor must be a (updated_at, id) tuple, and the two ways a single timestamp stalls a worker
• How a BEFORE UPDATE trigger keeps the cursor honest with no application code
• Which settings turn on typo tolerance, filtering and sorting — and the exact error you get when you forget one
• What a strict tsquery and Meilisearch actually return for a typo, a prefix and a two-word query, measured
• How the two engines compare on index build time, footprint and memory for 20,000 documents

Prerequisites

• Docker with Compose v2, on the local daemon
• Node.js 24+ (verified on Node 26.10.0) — the worker only needs fetch and top-level await
• About 1 GB of RAM free: both containers idle at roughly 115 MiB combined at this data size

Project layout

search-api/
  docker-compose.yml
  .env                 # POSTGRES_PASSWORD, MEILI_MASTER_KEY
  sql/
    01-schema.sql
    02-seed.sql
  sync.mjs             # Postgres -> Meilisearch
  server.mjs           # HTTP search endpoint

1. Run both engines

services:
  db:
    image: postgres:18.6-alpine
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?set it in the env file}
      POSTGRES_DB: catalog
    volumes:
      - db-data:/var/lib/postgresql/18/docker
    ports:
      - "127.0.0.1:5432:5432"

  meili:
    image: getmeili/meilisearch:v1.56.0
    environment:
      MEILI_MASTER_KEY: ${MEILI_MASTER_KEY:?set it in the env file}
      MEILI_ENV: production
    volumes:
      - meili-data:/meili_data
    ports:
      - "127.0.0.1:7700:7700"

volumes:
  db-data:
  meili-data:

Both ports bind to loopback, so nothing is public until you put a proxy in front. And postgres:18.6-alpine keeps its cluster in /var/lib/postgresql/18/docker — mount that exact path (it is PGDATA in the image), not the older /var/lib/postgresql/data, or the volume stays empty across recreates.

Generate two secrets and put them in .env next to the compose file as POSTGRES_PASSWORD and MEILI_MASTER_KEY:

openssl rand -hex 16   # POSTGRES_PASSWORD
openssl rand -hex 32   # MEILI_MASTER_KEY
docker compose up -d
docker compose ps

Meilisearch refuses to start without MEILI_MASTER_KEY when MEILI_ENV=production, which is the behaviour you want: a search engine that accepts unauthenticated writes is a data-loss incident waiting for a bot. Confirm it is up and note the version:

curl -s -H "Authorization: Bearer $MEILI_MASTER_KEY" http://127.0.0.1:7700/version
# {"commitSha":"47567abc0e7e...","commitDate":"unknown","pkgVersion":"1.56.0"}

2. Schema: rows plus one cursor

-- sql/01-schema.sql
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  description text NOT NULL,
  category    text NOT NULL,
  price       numeric(10,2) NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);

-- The cursor index. Column order matters: it matches the WHERE clause below.
CREATE INDEX products_updated_at_idx ON products (updated_at, id);

CREATE FUNCTION touch_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
begin
  new.updated_at = now();
  return new;
end
$$;

CREATE TRIGGER products_touch
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- Where the indexer left off. Exactly one row.
CREATE TABLE search_sync (
  id              int PRIMARY KEY CHECK (id = 1),
  last_updated_at timestamptz NOT NULL DEFAULT to_timestamp(0),
  last_id         bigint NOT NULL DEFAULT 0
);
INSERT INTO search_sync (id) VALUES (1);

The trigger is the part people skip, and it is the part that keeps everything honest: no UPDATE path has to remember to bump updated_at, so a row edited from psql, from a migration or from a background job all land in the next sync page. It was exercised directly: UPDATE products SET price = 42 WHERE id = 42 returns UPDATE 1 and moves the timestamp to 2026-11-03 09:02:43.579888+00.

3. Seed 20,000 rows

-- sql/02-seed.sql
INSERT INTO products (name, description, category, price)
SELECT
  initcap((ARRAY['wireless','adjustable','waterproof','mesh','lightweight','silent'])[1 + (i % 6)])
    || chr(32) || (ARRAY['headphones','monitor arm','webcam','keyboard','watch','speaker'])[1 + (i % 6)]
    || chr(32) || (ARRAY['Pro','Studio','Lite','Max'])[1 + (i % 4)],
  'built for daily use, described in the words customers actually type',
  (ARRAY['audio','input devices','outdoor','office'])[1 + (i % 4)],
  round((10 + random() * 290)::numeric, 2)
FROM generate_series(1, 20000) AS i;

Run it twice and the second insert adds 20,000 more rows — the identity column keeps counting. Seed it and you get 20,000 products on a deliberately narrow vocabulary: six adjectives, six nouns, four suffixes, four categories. The measured tables further down come from a 20,002-row table built by the same generator with a different word mix, so your absolute hit counts will not match mine. The behaviours do: a typo still returns zero, and the bulk load still shares one timestamp.

One property of this bulk load matters more than the row count: updated_at defaults to now(), and now() is the transaction timestamp, so every row in a single INSERT ... SELECT gets the exact same value. Checked against this exact seed: 20,000 rows, one distinct updated_at. That is the fact that breaks naive sync workers, and section 5 comes back to it.

4. Create the index and three settings that matter

curl -s -X POST -H "Authorization: Bearer $MEILI_MASTER_KEY" -H "Content-Type: application/json" \
  http://127.0.0.1:7700/indexes \
  -d "{\"uid\":\"products\",\"primaryKey\":\"id\"}"

curl -s -X PATCH -H "Authorization: Bearer $MEILI_MASTER_KEY" -H "Content-Type: application/json" \
  http://127.0.0.1:7700/indexes/products/settings \
  -d "{\"searchableAttributes\":[\"name\",\"description\"],\"filterableAttributes\":[\"category\"],\"sortableAttributes\":[\"price\"]}"

Both calls return 202 with a taskUid, not the finished state. Nothing is searchable until that task reports succeeded — see section 6 for the waiter.

Three list-valued settings decide what the index can do, and the defaults for the rest are worth knowing since they explain results later:

Setting	Value used here	What it unlocks
searchableAttributes	name, description	Which fields are matched; order inside the array is the attribute ranking order
filterableAttributes	category	filter=category = "audio". Any other attribute returns an error, not an empty result
sortableAttributes	price	sort=["price:asc"]. Same rule: not declared, not sortable

Defaults you inherit, read back from GET /indexes/products/settings on 1.56.0: typoTolerance.enabled is true with minWordSizeForTypos of oneTypo: 5 and twoTypos: 9 (so a word needs five characters before one typo is forgiven), pagination.maxTotalHits is 1000, and prefixSearch is indexingTime. Those three numbers are why a typo on a short word does nothing and why deep offset paging stops at a thousand hits.

5. The sync worker: keyset pagination that survives a bulk load

// sync.mjs — drains products into Meilisearch, one page at a time
import pg from "pg";
const { Pool } = pg;

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const MEILI = process.env.MEILI_URL;
const KEY = process.env.MEILI_MASTER_KEY;
const INDEX = "products";
const PAGE = 1000;

async function meili(path, method = "GET", body) {
  const res = await fetch(MEILI + path, {
    method,
    headers: { Authorization: `Bearer ${KEY}`, "Content-Type": "application/json" },
    body: body === undefined ? undefined : JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`${res.status}: ${await res.text()}`);
  return res.json();
}

async function waitTask(uid) {
  for (;;) {
    const task = await meili(`/tasks/${uid}`);
    if (task.status === "succeeded") return task;
    if (task.status === "failed") throw new Error(JSON.stringify(task.error));
    await new Promise((r) => setTimeout(r, 50));
  }
}

async function syncPage() {
  const { rows: state } = await pool.query(
    "SELECT last_updated_at::text AS ts, last_id FROM search_sync WHERE id = 1",
  );
  const cursor = state[0];

  const { rows } = await pool.query(
    `SELECT id, name, description, category, price::float8 AS price,
            updated_at::text AS updated_at, deleted_at
       FROM products
      WHERE (updated_at, id) > ($1::timestamptz, $2::bigint)
      ORDER BY updated_at, id
      LIMIT $3`,
    [cursor.ts, cursor.last_id, PAGE],
  );
  if (rows.length === 0) return 0;

  const upserts = rows.filter((r) => r.deleted_at === null)
    .map(({ deleted_at, ...doc }) => doc);
  const deletes = rows.filter((r) => r.deleted_at !== null).map((r) => r.id);

  const tasks = [];
  if (upserts.length > 0) tasks.push(await meili(`/indexes/${INDEX}/documents`, "POST", upserts));
  if (deletes.length > 0) tasks.push(await meili(`/indexes/${INDEX}/documents/delete-batch`, "POST", deletes));
  for (const task of tasks) await waitTask(task.taskUid);

  const last = rows[rows.length - 1];
  await pool.query(
    "UPDATE search_sync SET last_updated_at = $1::timestamptz, last_id = $2 WHERE id = 1",
    [last.updated_at, last.id],
  );
  return rows.length;
}

for (;;) {
  const n = await syncPage();
  if (n === 0) break;
  console.log(`indexed ${n} rows`);
}
await pool.end();

Three details carry the design:

• (updated_at, id) > ($1, $2) is a row comparison, and Postgres can use a composite index for it. It gives a total order even when thousands of rows share one timestamp.
• updated_at::text is the cursor format. Text keeps the full microsecond value; the default timestamptz parser does not.
• The cursor is written only after the Meilisearch task reports succeeded. A crash between send and commit re-indexes one page, which is idempotent, instead of losing a page, which is not.

Trap 1: a timestamp-only watermark

Take the obvious version of the query, WHERE updated_at > $1 ORDER BY updated_at LIMIT 1000, and run it against a table where all 20,000 rows share one now(). After the first page the cursor is that shared value, so the second page returns nothing — 1,000 of 20,000 rows indexed, no error. Use >= to fix that and you get the mirror bug: the same 1,000 rows come back forever and the worker spins. Both variants were hit while building this: the worker stalled at 500 documents with the >= form and silently stopped short with >.

The tuple fixes both. The id breaks the tie, the strict comparison stays correct, and the first page of a bulk load no longer decides whether the rest of the table exists.

Trap 2: the cursor that rounds itself down

pg parses timestamptz into a JavaScript Date, which stores milliseconds. Select updated_at without the cast, use that value as the next $1, and you send back a timestamp slightly earlier than the rows you just read — so the next page starts inside the previous one. The symptom is a worker that reports progress and never advances past 1,000 documents.

The benchmark table has the worst case for it: all 20,002 rows carry digits below the millisecond, so updated_at <> updated_at::timestamptz(3) is true for every single row. A realistic value looks like 2026-10-27 09:03:55.199863+00, which a Date turns into 2026-10-27 09:03:55.199.

Casting in SQL and passing the string back is the whole fix. It also removes a class of timezone bugs, since the round trip never leaves Postgres text format.

6. Run the sync

npm init -y && npm pkg set type=module && npm install [email protected]
DATABASE_URL="postgres://postgres:$POSTGRES_PASSWORD@127.0.0.1:5432/catalog" \
MEILI_URL="http://127.0.0.1:7700" \
MEILI_MASTER_KEY="$MEILI_MASTER_KEY" \
node sync.mjs

indexed 1000 rows
indexed 1000 rows
...
indexed 2 rows

21 pages for 20,002 rows. Set PAGE to 5000 instead and the same table takes four requests: measured at 0.309 / 0.231 / 0.232 / 0.225 s per 5,000-document task, 0.99 s wall for the whole batch including the task waits. Bigger pages are faster per document and slower to fail — 1,000 is a sane default for anything that also serves queries while indexing.

Run it again with no changes and it exits immediately: the cursor is at the end, syncPage() returns 0. Insert one row and the next run indexes exactly one.

7. The search endpoint

// server.mjs
import { createServer } from "node:http";

const MEILI = process.env.MEILI_URL;
const KEY = process.env.MEILI_MASTER_KEY;
const CATEGORIES = new Set(["audio", "input devices", "outdoor", "office"]);

createServer(async (req, res) => {
  const url = new URL(req.url, "http://127.0.0.1");
  if (url.pathname !== "/search") return res.writeHead(404).end();

  const body = {
    q: url.searchParams.get("q") ?? "",
    limit: Number(url.searchParams.get("limit") ?? 10),
    attributesToHighlight: ["name"],
  };

  const category = url.searchParams.get("category");
  if (category) {
    if (!CATEGORIES.has(category)) return res.writeHead(400).end("bad category");
    body.filter = `category = "${category}"`;
  }
  const sort = url.searchParams.get("sort");
  if (sort === "price:asc" || sort === "price:desc") body.sort = [sort];

  const upstream = await fetch(`${MEILI}/indexes/products/search`, {
    method: "POST",
    headers: { Authorization: `Bearer ${KEY}`, "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  res.writeHead(upstream.status, { "Content-Type": "application/json" });
  res.end(await upstream.text());
}).listen(3000, "127.0.0.1", () => console.log("listening on 3000"));

The allowlists are not decoration. Meilisearch filter syntax is a small expression language, and pasting a raw query parameter into filter lets a caller write category = "audio" OR id > 0 and read the whole index. Sort direction has the same problem from the other side: an undeclared attribute in sort is an error, but only if you did not let the caller declare it first.

A real response, trimmed, for q=wireless headphones&sort=price:asc&limit=2:

{
  "hits": [
    {
      "id": 15634,
      "name": "Wireless headphones Pro",
      "category": "office",
      "price": 24.51,
      "_formatted": { "name": "<em>Wireless</em> <em>headphones</em> Pro" }
    },
    {
      "id": 4103,
      "name": "Wireless headphones Studio",
      "category": "audio",
      "price": 33.02,
      "_formatted": { "name": "<em>Wireless</em> <em>headphones</em> Studio" }
    }
  ],
  "processingTimeMs": 4,
  "estimatedTotalHits": 1112
}

estimatedTotalHits is an estimate, not count(*), and _formatted carries <em> markers around the matched terms. If you render name instead of _formatted.name you lose the highlighting; if you render _formatted without escaping, you inherit an XSS surface from document text — the tags are the only markup Meilisearch adds, but the text between them is whatever was in the row.

8. Same queries, two engines

Postgres gets a functional GIN index over the same text, which is the setup any team would reach for first:

CREATE INDEX products_fts_idx ON products
  USING gin (to_tsvector('english', name || ' ' || description));

Four queries, run against both engines, twice. The counts were identical in both runs:

Query	Postgres (plainto_tsquery)	Meilisearch 1.56
wireless headphones	31	1,112
noise cancelling	1,805	1,805
hedphones (typo)	0	578
mech (prefix)	0	1,123
wireless headphones filtered to category=audio	not measured	120

The prefix gap is a query shape, not an engine limit

The honest version of that table matters, because the first reading of it (“Postgres returns 0 for prefixes”) is wrong. Swap plainto_tsquery for to_tsquery('english', 'mech:*') and Postgres returns 1,123 — the same number Meilisearch returns. A tsquery prefix operator gets you the same recall on this data, with the same dictionary.

What plainto_tsquery gives you instead is strict AND: every lexeme must be present. That is why wireless headphones returns 31 rows in Postgres and 1,112 in Meilisearch — Meilisearch drops query terms it cannot match rather than returning nothing, and ranks the partial matches lower. Whether that is better depends on your users: a strict engine is easier to trust, a dropping engine is easier to use.

The typo gap is real

hedphones returns 0 in Postgres and 578 in Meilisearch. No query rewrite saves it: substring matching (ILIKE '%hedphones%') also returns 0, because the missing character is in the middle. Edit distance is the only thing that finds it, and Postgres does not have it in core — you would add pg_trgm and rank by similarity(), which is a separate index, a separate scoring model and a separate set of tuning knobs. Meilisearch ships typo tolerance on by default: oneTypo: 5 means words of five or more characters get one forgiven edit, which is exactly why a six-character misspelling lands.

9. What it costs to run both

Two runs, same data, same images, indexes rebuilt from scratch each time:

Metric	Run 1	Run 2
Postgres GIN build (20,002 rows)	0.645 s	0.720 s
Meilisearch indexing: 4 x 5,000 docs, including the settings task	1.029 s	0.992 s
Postgres heap	4,694,016 B	4,702,208 B
Postgres GIN index	753,664 B	753,664 B
Postgres heap + GIN	6,602,752 B	6,610,944 B
Meilisearch index size on disk	28,139,520 B	27,922,432 B
Meilisearch internal document store	17,416,192 B	17,268,736 B
RAM at rest (docker stats, after indexing)	24.3 MiB + 90.6 MiB	same containers

The footprint ratio is about 4.3x: roughly 330 bytes per document in Postgres against 1,400 in Meilisearch. That difference buys the typo tolerance and the prefix index, and it is the number to use when you size a disk — 20 million documents is 6.6 GB of Postgres index against 28 GB of Meilisearch.

Latency, measured without fooling yourself

Measurement	Run 1	Run 2
Postgres execution time, 31-hit query (EXPLAIN ANALYZE)	0.212 ms	0.232 ms
Postgres execution time, zero-hit query	0.062 ms	0.067 ms
Meilisearch processingTimeMs	0-3	0-1
Meilisearch median round trip (four queries)	1.91-3.62 ms	1.61-2.47 ms

The first version of this benchmark timed Postgres with psql \timing inside docker exec and got a median of 3.5-4.2 ms — an order of magnitude above the engine, almost all of it process startup and container overhead. The numbers in the table come from EXPLAIN ANALYZE, which reports server-side execution only. If you benchmark a database through a CLI wrapper and then compare it to an HTTP API, you have measured the wrappers.

Even corrected, that is not a clean head-to-head: Postgres reports execution of a plan it already cached, Meilisearch reports the whole request. What survives both corrections is the shape of the answer — on 20,000 documents, a single-machine Meilisearch answers in low single-digit milliseconds and the extra network hop costs more than the difference between the engines. Reindex speed is where Meilisearch pays: one second of indexing per 20,000 documents is cheap, and it stays cheap because every update is an incremental task, not a rebuild.

10. Keeping it in step

The worker is a one-shot process, not a daemon: it drains the table and exits. That makes a systemd timer the simplest scheduler — one run per minute, no overlap window, no idle process holding a connection.

# /etc/systemd/system/search-sync.service
[Unit]
Description=Meilisearch sync worker
After=docker.service

[Service]
Type=oneshot
WorkingDirectory=/opt/search
EnvironmentFile=/etc/search.env
ExecStart=/usr/bin/node sync.mjs

Forgets and deletions are handled by the same pass: the trigger fires on UPDATE, the worker sees deleted_at set, and it sends the ids to delete-batch instead of the documents. Hard deletes need a tombstone table — DELETE leaves nothing for a cursor to find, and no worker can page through rows that no longer exist.

Two properties make this cheap to operate. Meilisearch indexes are disposable: reset the cursor with UPDATE search_sync SET last_updated_at = to_timestamp(0), last_id = 0 and the next run rebuilds all 20,000 documents in about a second — so a corrupt index is a one-minute problem, not a restore-from-backup problem. And the cursor lives in Postgres, inside the same transaction boundary as the data, which means the source of truth can never disagree with itself about how far it got.

11. Common problems

Attribute "nonsense" is not filterable. Available filterable attribute patterns are: "category" — returned as invalid_search_filter with HTTP 400. The attribute exists in the documents but was never declared in filterableAttributes. Meilisearch will not filter on a field it did not index for filtering, and it tells you exactly which ones it did.

The worker indexes 1,000 rows and then reports nothing more. Either the cursor was written without ::text and rounded down into the page it just read (trap 2), or LIMIT and the cursor disagree. Check SELECT last_updated_at FROM search_sync against a row you know was skipped: if the cursor is earlier than the row, the Date round trip ate the microseconds.

The worker stops after the first page of a bulk load. Timestamp-only watermark (trap 1). The first page sets the cursor to a timestamp shared by every remaining row, and > excludes all of them.

Fewer hits than expected. searchableAttributes is ["name","description"] here, so category is filterable but not searchable — searching audio matches document text, not the field. Add it to the array and reindex.

Results stop at 1,000. That is pagination.maxTotalHits, a default of 1.56.0, not a bug. Real pagination past it needs filter ranges on a sortable attribute.

Typos are ignored on short words. minWordSizeForTypos.oneTypo is 5, so watchs gets no forgiveness at five characters while headphnes does at nine. Lower it and short-word false positives climb.

12. Checklist before you ship

• products has a BEFORE UPDATE trigger and a (updated_at, id) index
• Cursor is selected and stored as text, compared as a row tuple
• searchableAttributes, filterableAttributes, sortableAttributes set explicitly, and the settings task awaited
• Cursor committed only after the Meilisearch task reports succeeded
• filter and sort built from allowlists, never from raw query parameters
• Soft deletes covered; hard deletes have a tombstone plan
• Reindex path tested: reset the cursor, confirm the full table comes back

Related guides

• Redis vs Valkey: Which Should You Use for Your Next Project?
• PostgreSQL vs MySQL for SaaS Applications
• Managed PostgreSQL vs PostgreSQL on a VPS
• Docker Compose vs Kubernetes for Small Projects

The pattern generalises beyond search: any read model you derive from Postgres wants a tuple cursor, a trigger that keeps it honest, and a rebuild path measured in seconds. Meilisearch is a good example because it fails loudly — the wrong cursor does not corrupt your data, it just stops, which is the failure mode you want at 3 a.m.