How to Choose and Implement Full‑Text Search: Postgres FTS, Meilisearch, and DuckDB Patterns

Why this matters

Projects that need searchable text must balance developer velocity, query relevance, resource costs, and operational complexity. PostgreSQL's built-in FTS is reliable for transactional apps, Meilisearch is a lightweight search engine tuned for developer ergonomics and relevance, and analytic engines like DuckDB can simplify large-scale offline indexing pipelines. This guide gives practical examples, ingestion patterns, tuning tips, and tradeoffs so you can pick and implement the right solution.

Quick decision guide

• Pick PostgreSQL FTS when you already store content in Postgres, want ACID consistency, and need moderate search scale without extra infra.
• Pick Meilisearch when you want low-latency, relevance-tuned search with ranking controls, and can operate a separate search service.
• Consider DuckDB / analytic workflows when you need large-batch indexing, offline analysis, or cheap local experimentation before pushing indices to production search services.

PostgreSQL full-text search: practical setup and tips

Use Postgres FTS when you need transactional consistency and want to avoid a separate search service. The common pattern is a computed tsvector column + GIN index, with updates done in triggers or via periodic refresh for append-mostly datasets.

CREATE TABLE documents (
  id serial PRIMARY KEY,
  title text,
  body text,
  body_tsv tsvector
);
 
-- populate tsvector (can be done in a trigger on INSERT/UPDATE)
UPDATE documents
SET body_tsv = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
 
-- fast searches over large text columns
CREATE INDEX idx_documents_body_tsv ON documents USING GIN (body_tsv);
 
-- query example (boolean AND semantics)
SELECT id, title, ts_rank_cd(body_tsv, query) AS rank
FROM documents, to_tsquery('english', 'search & terms') AS query
WHERE body_tsv @@ query
ORDER BY rank DESC
LIMIT 20;

Tuning notes for Postgres:

• Use a trigger to keep the tsvector column current if latency matters; use batch refresh for append-heavy workloads to avoid write amplification.
• GIN indexes are fast for lookup but can be large; consider GIN fastupdate and maintenance settings, or BRIN for very large append-only tables with sequential relevance by id/time.
• Control dictionaries and stop words with to_tsvector/to_tsquery language options. Custom text normalization (lowercasing, stemming) is applied by the chosen configuration.

Meilisearch: developer-friendly search service

Meilisearch is a dedicated search engine focused on relevance, typo-tolerance, and developer experience. It's a good fit when you want feature-rich ranking controls and a separate service to scale independently of your primary database.

# create an index
curl -X POST 'http://localhost:7700/indexes' \
  -H 'Content-Type: application/json' \
  --data '{ "uid": "documents" }'
 
# add documents (batch)
curl -X POST 'http://localhost:7700/indexes/documents/documents' \
  -H 'Content-Type: application/json' \
  --data '[{"id":1,"title":"Hello world","body":"Sample text"}]'
 
# search
curl -X POST 'http://localhost:7700/indexes/documents/search' \
  -H 'Content-Type: application/json' \
  --data '{ "q": "search terms", "limit": 20 }'

Tuning and operational notes for Meilisearch:

• Design your document schema so that fields you want ranked are top-level and mapped to searchable attributes.
• Meilisearch supports custom ranking rules and synonyms; tune them to your UX (e.g., prefer title matches, then body).
• Run Meilisearch as a separate service with its own persistence; your indexing pipeline should handle retries and idempotency.

DuckDB and analytic-first search workflows

Analytic engines (the example pattern people explore with DuckDB) are useful for large-batch processing: cleaning, tokenizing, and producing ready-to-load index payloads. Use them to prepare or prototype indices, run offline relevance experiments, or to build search indices from large data dumps cheaply on local or cloud compute.

Practical tips when using an analytic DB in your pipeline:

• Perform heavy text normalization (stemming, stop-word pruning, n-gram extraction) in batch and export compact index documents to your search engine of choice.
• Use partitioned exports and incremental timestamps to enable safe re-indexing or partial updates.
• Keep the analytic stage read-only relative to production; treat it as a reproducible transform step.

Ingestion patterns: examples and a reliable pipeline

Common architectures:

1. Single-node FTS (Postgres-only): store and search in Postgres. Simpler ops, limited horizontal search scaling.
2. Dual system (Postgres + Meilisearch): Postgres is source of truth; push changes to Meilisearch via background workers/CDC.
3. Batch analytic pipeline: export large datasets into DuckDB or another analytical engine, transform, then bulk-upload to search engine.

Example: a simple Python-style batch exporter that reads from Postgres and pushes to Meilisearch in batches. (This is an implementation pattern — adapt to your languages/frameworks.)

import psycopg2
import requests
 
DSN = 'postgresql://user:pass@localhost/db'
MEILI_URL = 'http://localhost:7700/indexes/documents/documents'
BATCH = 1000
 
conn = psycopg2.connect(DSN)
cur = conn.cursor(name='doc_cursor')  # server-side cursor to stream
cur.execute('SELECT id, title, body FROM documents')
 
batch = []
for row in cur:
    batch.append({'id': row[0], 'title': row[1], 'body': row[2]})
    if len(batch) >= BATCH:
        requests.post(MEILI_URL, json=batch)
        batch = []
 
if batch:
    requests.post(MEILI_URL, json=batch)
 
cur.close()
conn.close()

Pipeline best practices:

• Use idempotent pushes (upserts) or include a version/timestamp field so retries don’t corrupt the index.
• Batch writes to reduce API overhead but size batches to fit memory and network limits.
• Use CDC (logical replication or WAL tailing) for near-real-time sync when low latency is required.

Tradeoffs checklist

• Complexity: Postgres FTS = lower operational footprint; Meilisearch = extra service but more search features.
• Relevance & UX: Dedicated engines tend to offer finer ranking controls, typo tolerance, and better developer ergonomics.
• Scaling: Postgres scales vertically for FTS; separate search engines allow horizontal scaling and specialized memory/disk tradeoffs.
• Cost: Extra service equals extra instances and maintenance. Analytic workflows can reduce costs during index generation (spot/cloud ephemeral compute).
• Consistency: Single-store search avoids eventual consistency between DB and search index; dual-system setups must manage sync windows.

Note: For real-world, large-document benchmarks and build-time comparisons, see a community analysis inspired by this topic: DuckDB FTS vs PostgreSQL FTS vs Meilisearch (community benchmark).

Concise checklist before you pick

• Do you need strict transactional consistency for search results? Prefer Postgres-only.
• Is developer experience and ranking customization a priority? Consider Meilisearch.
• Are you doing large-scale offline transforms or experiments? Use an analytic stage (DuckDB-style) to prepare export files for indexing.
• Plan your update strategy: triggers for low-latency, CDC for near-real-time, batch jobs for bulk imports.

Conclusion

There is no one-size-fits-all search stack. Use Postgres FTS when you want simplicity and transactional consistency. Use Meilisearch when you want low-latency, relevance-first search and can run a dedicated service. Use DuckDB or similar analytic stages when you need to preprocess large datasets or prototype indexing strategies cheaply. Combine these patterns with robust, idempotent ingestion and monitoring to deliver reliable search at production scale.