Full-text search

Mortar ships a thin wrapper over Postgres FTS — enough for typical “search a notes / blog / docs table” workloads. For real search-engine workloads (relevance tuning, faceting, typo tolerance), run Meilisearch as a sidecar.

When Postgres FTS is enough

• < 1M rows in the table
• Latin-script content
• Ranking by ts_rank is acceptable (frequency-based, no BM25)
• No typo tolerance / fuzzy matching required

When to switch to Meilisearch / Elastic

• 1M rows or query latency > 100ms

• Multilingual content where you want unified ranking
• Faceted search (filter by tag + range query)
• Typo tolerance, synonyms, dictionary-based stemming

See architecture § sidecar for the recommended deployment topology (Meilisearch on the same node behind a private DNS name, fed by a background job consuming pg_notify row changes).

Enabling FTS on a table

Mortar tables store rows as JSONB. FTS uses a functional GIN index on the to_tsvector of one JSONB key:

CREATE INDEX idx_my_table_fts
  ON app_rows
  USING GIN (to_tsvector('simple', data->>'content'));

Replace 'simple' with the FTS config name (see § Languages below) and 'content' with whichever JSONB key holds your searchable text.

Mortar’s auto-migration does NOT create FTS indexes — they’re table-and-column-specific, so you create them manually once the table schema is stable.

Languages

Postgres ships these FTS configs:

Chinese tokenization

Postgres has no built-in Chinese parser. Two options:

• zhparser (recommended) — based on SCWS, fast, works on most cloud Postgres (Aliyun RDS / Tencent CDB / Supabase).
• pg_jieba — based on jieba, more permissive matching, slower.

Setup:

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;

Then index + query with 'chinese' as the config name.

Query via Mortar

The standard GET /v1/{tenant}/db/tables/{table}/rows endpoint accepts two extra query params:

?fts=<query>&fts_column=<json_key>&fts_lang=<config_name>

• fts — the search string, treated as a plainto_tsquery (each word is AND-ed; phrase quoting is escaped).
• fts_column — JSONB key inside data. Default content.
• fts_lang — Postgres FTS config name. Default simple.

Example: search the posts table for “rust async”:

GET /v1/abc-tenant/db/tables/posts/rows?fts=rust+async&fts_column=body&fts_lang=english

Returns the same {rows: [...]} shape as the unfiltered List, but ordered by ts_rank(...) DESC, created_at DESC.

Query via SDK

// @mortar/client
import { createClient } from '@mortar/client'
const mortar = createClient({ url, apiKey })

const { rows } = await mortar.from('posts').search({
  query: 'rust async',
  column: 'body',
  lang: 'english',
  limit: 20,
})

The TS SDK forwards the search call as the query params above.

Combining with regular filters

The Mortar HTTP API doesn’t yet compose ?fts= with WHERE data->>... filters — if you need that, point Mortar at a compute function that calls Postgres directly with a hand-written SQL query. A ?filter= composable layer is planned once we settle on a query DSL.

Limitations

1. Single column at a time. FTS over multiple JSON keys requires building a tsvector from a ||’d expression; index it as:

   CREATE INDEX ON app_rows USING GIN (
     to_tsvector('english',
       coalesce(data->>'title', '') || ' ' ||
       coalesce(data->>'body', ''))
   );

   …and pass fts_column=title_body against a generated column. Not exposed via Mortar’s API yet — drop down to raw SQL.

2. Ranking is frequency-only. ts_rank doesn’t model document length normalization as well as BM25; long documents score artificially high. For BM25-style ranking use ts_rank_cd or move to Meilisearch.

3. No fuzzy matching. Mortar doesn’t enable pg_trgm; if you need it run CREATE EXTENSION pg_trgm and roll your own query.

4. GIN index cost. Each insert pays an indexing cost (~constant); high write-rate tables (>1k inserts/sec) may want the lazy gin_pending_list_limit tuning. See Postgres docs.

Hybrid search (FTS + vector)

For semantic search combining text relevance with embedding similarity, see rag-cookbook.