阅读中文版本

Build a Full-Text Search API on PostgreSQL

A complete full-text search API using PostgreSQL tsvector, GIN indexes, weighted ranking, highlight snippets, and query-parsing tests.

full-text-search-api/
├── src/search.ts
├── src/index-doc.ts
├── src/ranking.ts
├── db/schema.sql
├── tests/search.test.ts
└── package.json

ALTER TABLE docs ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')), 'B')) STORED;

CREATE INDEX docs_search_idx ON docs USING GIN (search);

SELECT id, title, ts_rank_cd(search, query) AS rank
FROM docs, websearch_to_tsquery('english', $1) query
WHERE search @@ query ORDER BY rank DESC LIMIT 20;

ts_headline('english', body, query, 'MaxFragments=2, StartSel=<mark>, StopSel=</mark>') AS snippet

const q = tokens.slice(0, -1).join(' & ') + ' & ' + last + ':*';

Project overview

Full-text search written naively is a wildcard query, and a wildcard query is a full table scan with extra steps. This build uses a generated vector column, a real index, and a ranked query so search runs on an index rather than on the whole table.

You will add a generated tsvector column that weights the title above the body, build a GIN index over it, parse a user query into a tsquery, rank results, and return highlighted snippets with a prefix match for typeahead.

The weighting is what makes results feel relevant: a term in the title should outrank the same term buried in the body, and encoding that preference in the column is cheaper than sorting in application code.

Highlighting is a presentation concern, but doing it in the database with ts_headline keeps the matched terms aligned with the ranking, which avoids the mismatch that appears when two different parsers disagree.

Each phase adds one measurable improvement: the column, the index, the ranking, the snippets, and the prefix path, so search gets faster and better at every step.

The project targets teams that have already tried a wildcard search and watched it degrade as the table grew. It replaces that with an indexed, ranked query, and it insists on checking the query plan so the performance claim is verified rather than assumed from the mere presence of an index.

What you will build

The API provides five capabilities, each with a test against a seeded corpus:

• Generated tsvector column
• GIN index
• Ranked query
• Highlighted snippets
• Prefix typeahead

A weighted vector column gives relevance a home in the schema; a GIN index turns matching into an index lookup; ranked queries order by score; highlighted snippets show why a row matched; and the prefix path powers typeahead without a scan.

The design leans on the database rather than reimplementing search in application code, because the index and the ranking are the parts that are hard to get right and the parts a database already does well.

Architecture and data model

A generated tsvector column weights the title above the body. A GIN index accelerates @@ matching. The search endpoint parses the query into a tsquery, ranks with ts_rank_cd, and returns highlighted fragments from ts_headline.

A generated tsvector column folds the title and body into a weighted vector, with the title set to a higher weight. A GIN index over that column answers containment queries without scanning the table.

The query path parses the user input into a tsquery, matches with the containment operator, ranks with the built-in rank function, and returns highlighted fragments. Each of those is a database feature rather than a hand-rolled ranking loop.

Because the vector is generated from the source columns, it cannot drift out of sync with the content. Updating a document updates the vector automatically, which removes an entire class of stale-index bugs.

For typeahead, the last term is converted to a prefix match while the earlier terms stay strict, so partial input still reaches the index rather than degrading to a scan.

Weighting lives in the column definition, so relevance is a property of the stored data rather than a runtime decision. A weight change is therefore a schema change reviewed like any other, and the ranking cannot silently differ between two queries that forgot to set the same weights.

Prerequisites and dependencies

You need PostgreSQL and a corpus whose language configuration you can choose deliberately.

• Node.js 20 LTS or later
• PostgreSQL 15+
• Familiarity with SQL and indexes

The dependencies are limited to the database driver and the HTTP layer, which keeps the ranking logic where the index is:

• pg (^8.12.0) — PostgreSQL driver
• express (^4.21.0) — HTTP endpoint

pnpm init
pnpm add pg express
pnpm add -D typescript tsx vitest

Set the text search configuration to match the corpus language, because a mismatch silently degrades stemming and produces poor results without any error. Keep the configuration name in code so the index and the queries cannot disagree.

Directory structure

Separate the schema and column definition, the query parser, the ranking call, and the highlighting so each can be tested against a small fixture:

full-text-search-api/
├── src/search.ts
├── src/index-doc.ts
├── src/ranking.ts
├── db/schema.sql
├── tests/search.test.ts
└── package.json

Phase 1: Generated tsvector column

Add a stored generated column combining weighted title and body.

ALTER TABLE docs ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')), 'B')) STORED;

A generated column stays in sync automatically and is indexable.

Add a generated vector column that concatenates the weighted title and body. Weights are chosen once here; changing them is a schema change and should be treated as a deliberate relevance decision.

Phase 2: GIN index

Index the tsvector column for fast matching.

CREATE INDEX docs_search_idx ON docs USING GIN (search);

Search without the index scans every row.

Index the column so matches use the index instead of scanning. Confirm the index is actually used with a query plan, because a search that looks correct but scans is a performance bug waiting for a larger table.

Phase 3: Ranked query

Parse input and rank matches with cover density.

SELECT id, title, ts_rank_cd(search, query) AS rank
FROM docs, websearch_to_tsquery('english', $1) query
WHERE search @@ query ORDER BY rank DESC LIMIT 20;

Ranking puts the best match first instead of relying on insertion order.

Parse the user query into a term query and rank with the built-in rank function, so ordering reflects term proximity and weight rather than insertion order.

Phase 4: Highlighted snippets

Return context fragments with the matched terms wrapped.

ts_headline('english', body, query, 'MaxFragments=2, StartSel=<mark>, StopSel=</mark>') AS snippet

Snippets let the UI show why a result matched.

Return highlighted fragments from the headline function so the client can show why each row matched. Keeping highlighting in the database avoids paraphrasing the query in a second parser.

Phase 5: Prefix typeahead

Append :* to the last token to match prefixes.

const q = tokens.slice(0, -1).join(' & ') + ' & ' + last + ':*';

Typeahead should match partial words as the user types.

Support prefix matching for the final term so typeahead finds partial words, while keeping earlier terms strict to avoid an explosion of partial matches.

Testing

Seed a fixed corpus and assert ranking order, that a stop-word-only query returns nothing, that highlighting wraps the terms, and that prefix matching finds a partial word.

Seed a fixed corpus and assert the ranking order, because ranking is the feature and a test that only checks membership would not notice it breaking.

Assert that a stop-word-only query returns nothing, which pins the language configuration and catches the common mistake of matching noise.

Assert that highlighting wraps the matched terms and that the prefix path finds a partial word, since both are easy to break while refactoring the query builder.

Assert that the query plan uses the index for a term match, because a correct result set produced by a sequential scan will not survive the corpus growing.

Seed the corpus with documents that differ only in where a term appears, so the ranking test can prove that a title match outranks a body match rather than merely that both match. A corpus without that distinction cannot catch a weight dropped by accident.

Assert too that a document containing only stop words yields no match, and that the prefix path finds a partial last term, because both are easy to break while refactoring the query builder.

pnpm vitest run

Deployment and operations

Language configuration must match the corpus language; a mismatch silently produces poor results. Monitor index size and rebuild with REINDEX CONCURRENTLY after bulk loads.

Keep the language configuration consistent between the index and the queries, and monitor index size after bulk loads, since a large unmaintained index slows writes as well as reads.

Rebuild the index concurrently after a bulk import, so a refresh does not lock the table and stall the write path.

Set a statement timeout on search queries, because a pathological query against a large corpus is a denial-of-service vector if it can run unbounded.

Expose search latency separately from other endpoints, so a slow query can be attributed to search rather than to the application as a whole.

Watch write latency as well as search latency, because a generated vector column and its index are updated on every insert, and a large unmaintained index quietly slows the write path that feeds it. Rebuild concurrently after bulk loads so a refresh never locks the table behind a long import.

Security considerations

Use parameterized queries and never build a tsquery from raw SQL. Cap query length and rate-limit expensive wildcard searches.

Treat the query string as input, not as syntax: build the term query from parsed tokens rather than interpolating raw user text, so an operator-looking payload cannot alter the query structure.

Cap the result count and the snippet length, because an unbounded result set is a memory and bandwidth problem that a single caller can trigger.

Keep the search configuration and any dictionary in version control, since these shape what the index considers a word and drift is hard to see.

Do not expose internal document scores or index internals to clients, because they reveal corpus statistics that are not meant to be public.

Never interpolate the raw query into the term query, because that lets a caller inject operators and reshape the search rather than search it. Parse the input into tokens and build the query from those, so the worst a malicious string can do is fail to match.

Observability

Log the parsed tsquery, result count, rank of the top hit, and latency. Alert when the index is not used, which shows up as multi-second scans.

Track query latency, index size, and the share of queries that use the index; a rising scan share is the early signal that the index is falling behind the data.

Log slow queries with their normalized shape rather than the raw text, so a pattern can be recognized without recording what users searched for.

Chart the zero-result rate, because a rising rate usually means the corpus or the language configuration changed rather than that the query is wrong.

Measure the time to rebuild the index after a bulk load, since that duration determines how long the search path is in a degraded state.

Measure the share of queries answered by the index rather than a scan, because that ratio is the real health of the search path; a query that returns the right rows by scanning looks fine until the table doubles. Pair it with a slow-query log keyed by normalized shape so a bad pattern is recognizable without storing what users typed.

Troubleshooting

Search returns nothing for valid terms. The text search config used during indexing differs from the one used at query time.

Index not used. The query wrapped the tsvector column in a function, defeating the GIN index; compare the column directly to a tsquery.

If a query returns nothing for a word that is clearly present, check the language configuration and the stemming first, then the index, because an unindexed column answers correctly only by scanning.

If ranking looks arbitrary, confirm the weights and the rank function are the ones you expect, since a default weight silently equalizes title and body.

If search suddenly returns nothing, check the language configuration before the index, because a configuration mismatch produces no error and simply stops matching; then confirm the generated column still exists, since a schema change can silently remove it.

Limitations and next steps

• No fuzzy matching or typo tolerance.
• One language configuration per column.
• Ranking is lexical, not semantic.

The API searches a single language configuration at a time, so a genuinely multilingual corpus would need per-language columns and a language-aware query.

There is no relevance tuning beyond the static weights, so per-field boosting at query time is not supported.

Fuzzy matching and synonyms require an extension or a separate dictionary, which this build deliberately leaves out to keep the ranking explainable.

The typeahead path matches prefixes on the last term only, so multi-word prefix matching is not attempted.

Relevance tuning is limited to static field weights, so a corpus where importance depends on recency or popularity would need additional columns folded into the vector, which the example leaves as an exercise. Suggestion beyond prefix matching would likewise require an additional index and is out of scope.

There is also no spelling correction or related-term suggestion, so a query containing a typo simply returns fewer results instead of proposing a near match that would have found them.