Choosing Text Search: PostgreSQL, DuckDB, and External Engines

Text search begins with a product question, not an index. Are people looking for a record they already recognize, browsing a catalogue, investigating event logs, or searching an analytical corpus? The answer determines how much relevance tuning, write isolation, filtering, and operational work the system needs.

This guide compares four approaches: PostgreSQL's native tsvector and tsquery, PostgreSQL installations with pg_search, DuckDB's full-text search extension, and Elasticsearch-like external engines. They overlap, but they are not interchangeable.

Start by naming the search workload

Keyword search usually tokenizes text, normalizes words, and matches those terms through an inverted index. Search systems differ in how they analyze terms, rank documents, handle misspellings, combine filters, and update an index after a write.

Three workload boundaries are especially useful:

• Transactional search finds application records near the database that owns them. Correctness after a write and simple operations often matter more than elaborate ranking.
• Analytical search combines text predicates with scans, aggregations, and files such as Parquet. It is usually batch-oriented and read-heavy.
• Search as a product surface needs tuned relevance, facets, highlighting, typo handling, availability planning, and independent query capacity.

Document count alone is not a decision rule. A small catalogue with multilingual typo-tolerant ranking may need a dedicated engine, while a large internal corpus with exact terms may work well in a database.

Comparison at a glance

Approach	Best workload	Relevance and query model	Writes and freshness	Operations
PostgreSQL tsvector/tsquery	Transactional records and straightforward keyword search	Lexeme matching, dictionaries, phrase and boolean operators, ts_rank variants	Updated in the same database transaction when modeled that way	One database and index to maintain
PostgreSQL with pg_search	PostgreSQL applications that need its supported search features	Extension-specific ranking and query capabilities	Depends on extension version, hosting, and index maintenance	Extension availability and upgrade path require review
DuckDB FTS	Read-heavy exploration and analytical text queries	Extension-provided tokenization and BM25-style scoring	Better suited to batch refreshes than concurrent transactional writes	In-process deployment, but extension/version compatibility matters
External engine	User-facing search with rich relevance and independent scaling	Broad analyzers, ranking controls, facets, highlights, and often typo handling	Index synchronization is a separate consistency problem	Separate cluster, monitoring, backups, and capacity planning

Native PostgreSQL full-text search

PostgreSQL full-text search represents indexed text as a tsvector and queries as a tsquery. A text-search configuration determines parsing, stop words, and dictionaries. This makes it a sound default for application search when users can search for normalized terms and the data already lives in PostgreSQL.

Store the vector in a generated column or maintain it with an explicit update path, then add a GIN index. The example below uses simple to make behavior predictable for identifiers and English-like terms; select a configuration that matches your language and content.

CREATE TABLE articles (
  id bigint PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  search_document tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(body, '')), 'B')
  ) STORED
);

CREATE INDEX articles_search_document_idx
  ON articles USING gin (search_document);

SELECT id, title,
       ts_rank(search_document, websearch_to_tsquery('simple', 'lake governance')) AS rank
FROM articles
WHERE search_document @@ websearch_to_tsquery('simple', 'lake governance')
ORDER BY rank DESC, id;

This is keyword retrieval, not a complete search product. Stemming and stop-word behavior are configuration-specific. Ranking can be adequate for internal search, but ts_rank does not automatically learn relevance from user behavior. Typo tolerance, synonyms, highlighting, and language handling require deliberate design, often with other PostgreSQL features or a different system.

Choose native PostgreSQL when:

• The primary records already live in PostgreSQL and search should share their transaction boundary.
• Queries are mostly terms, phrases, boolean conditions, and ordinary relational filters.
• A small operational surface is more valuable than independent search scaling.
• You can test rankings against representative queries rather than assuming a generic score is useful.

PostgreSQL with pg_search

pg_search is a PostgreSQL extension with its own index and query capabilities. Its appeal is keeping search close to application data while offering features that may be more suitable for user-facing retrieval than basic tsvector ranking.

Do not treat the extension name as a portable PostgreSQL feature. Extension versions, installation methods, managed-service support, index syntax, and available query features can differ. Confirm that the target environment supports the exact extension release, that backups and replicas handle its indexes, and that the upgrade process is documented before making it a production dependency.

Choose pg_search when:

• PostgreSQL remains the system of record and the extension's documented features meet the search requirements.
• Your platform supports the required version and you can rehearse upgrade and recovery procedures.
• You want to avoid synchronizing a separate search cluster, but accept an extension-specific operational dependency.

Choose another approach when the required analyzers, facets, relevance controls, or scaling model are outside the extension's supported surface.

DuckDB full-text search

DuckDB is an analytical database commonly embedded in a process or used for local and batch data work. Its FTS extension can create an index over text columns and return a relevance score. That makes it useful when search is part of an analytical workflow, such as filtering a document export before grouping by source or date.

The following is a compact pattern for a DuckDB build where the FTS extension is available. Extension installation, function names, and supported options should be checked against the documentation for the DuckDB version you deploy.

INSTALL fts;
LOAD fts;

CREATE TABLE documents (
  id INTEGER,
  title VARCHAR,
  body VARCHAR
);

PRAGMA create_fts_index('documents', 'id', 'title', 'body');

WITH ranked_documents AS (
  SELECT id, title,
         fts_main_documents.match_bm25(id, 'governed data lake') AS score
  FROM documents
)
SELECT id, title, score
FROM ranked_documents
WHERE score IS NOT NULL
ORDER BY score DESC;

DuckDB is not a drop-in replacement for a transactional search service. Its in-process and analytical design is well matched to controlled read workloads, but concurrent writes, online index maintenance, and high-availability service behavior need separate evaluation. Treat the FTS index as an artifact of the data snapshot and refresh process, not as an automatically current index for application edits.

Choose DuckDB FTS when:

• Text filtering is one step in an analytical query over local files or prepared tables.
• Data is refreshed in batches and readers can use a known database file or process.
• You value simple local deployment and can validate extension availability for each environment.

Elasticsearch-like external engines

Elasticsearch, OpenSearch, Solr, and similar systems are built around search indexes rather than transactional tables. They commonly provide configurable analyzers, facets, highlights, query parsing, relevance controls, and distributed deployment options. Exact capabilities vary by engine and version.

The cost is a second system. Data must reach the index through an application write path, change-data capture, a queue, or a scheduled job. Every route needs an answer for retries, out-of-order events, deletes, schema changes, replay, and what the user sees while the primary database and index disagree.

An external engine is appropriate when search is a visible product capability and requirements include several of the following:

• Relevance must be tuned and evaluated as a first-class feature.
• Users need facets, highlights, autocomplete, typo handling, or specialized language analysis.
• Search load needs to scale or fail independently from the transactional database.
• The team can operate index lifecycle, access control, monitoring, backup, and recovery.

External search is not automatically more accurate. It gives more controls, which need query sets, relevance judgments, and ongoing review to use well.

Relevance, filters, and evaluation

Ranked results are a product decision. Build a small evaluation set before choosing an engine: representative queries, expected documents, unacceptable results, and relevant filters. Include empty queries, unusual punctuation, common misspellings if supported, multiple languages, and permissions boundaries.

Use structured filters in the system that can enforce them reliably. A title match is not useful if it returns a document the requester cannot read. If permissions are indexed elsewhere, test revocation and delayed synchronization as carefully as ranking.

Measure a workload you recognize: index build time, write latency, freshness delay, query latency under expected concurrency, memory, and operational recovery. Published benchmarks rarely match tokenization, document size, hardware, filters, and data distribution in a real application.

A decision checklist

Answer these questions before selecting technology:

1. Is search transactional, analytical, or a user-facing product surface?
2. Which languages, tokenization rules, synonyms, and typo behavior are required?
3. Must a successful write be searchable immediately, eventually, or only after a batch refresh?
4. Which filters and permission rules must be enforced at query time?
5. Who owns relevance evaluation, index rebuilds, and recovery?
6. Can the service run and monitor a separate search system?
7. What evidence from representative queries would justify moving beyond native PostgreSQL?

Selection principle

Choose the simplest system that can meet the required retrieval behavior and operating model. Native PostgreSQL suits many transactional keyword searches. pg_search can fit where its supported extension surface is available and sufficient. DuckDB FTS suits controlled analytical reads. Use an external engine when search itself needs independent scale and deliberately managed relevance, not because a benchmark or label makes it sound necessary.