Full-Text Search on File Metadata with PostgreSQL #

To make uploaded files searchable by name, caption and tags, store a tsvector column derived from your text fields, index it with GIN, and query it with a tsquery ranked by ts_rank — all without leaving PostgreSQL. Combine that with JSONB containment filters and you get relevance-ordered search over a media catalogue in single-digit to low-tens-of-milliseconds, with no second datastore to keep in sync.

This article sits inside metadata indexing and search within backend validation and cloud storage architecture, and assumes the schema and index groundwork from how to index file metadata in PostgreSQL. Everything below is about the text side specifically: turning human words into lexemes, and lexemes back into ranked rows.

When to use this approach #

• Users search by free text — filename, title, caption, tag — and exact-match WHERE filename = $1 is visibly wrong to them.
• Your metadata already lives in Postgres and the catalogue is under roughly 50 million rows. Below that, a GIN index on a tsvector beats the operational cost of running a separate search engine.
• You need text relevance and structured filters in one query: “clips tagged interview, longer than two minutes, matching annual report”. Splitting those across two systems means paging one from the other, which is where result sets go wrong.

Reach for a dedicated engine instead when you need typo tolerance across the whole corpus, faceted counts over tens of millions of documents, or sub-millisecond p99 — Postgres will do all three badly at that scale.

Prerequisites #

1. PostgreSQL 12 or newer. Generated columns arrived in 12; on 11 and earlier you need a BEFORE INSERT OR UPDATE trigger to maintain the vector.
2. A table with one row per uploaded object, holding the text fields you want searchable plus a jsonb column for the extracted facts described in storing image dimensions and duration metadata.
3. CREATE privileges on that table, and enough maintenance window (or CREATE INDEX CONCURRENTLY) to build a GIN index.
4. Node 20+ and pg 8.11+ if you are following the TypeScript examples.

Implementation #

Store a generated tsvector that concatenates your searchable fields with weight classes, index it with GIN, then query it with websearch_to_tsquery so users get a syntax they already know from web search boxes.

-- 1. Base table for uploaded file records.
CREATE TABLE files (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    uuid NOT NULL,
  filename     text NOT NULL,
  title        text,
  tags         text[] NOT NULL DEFAULT '{}',
  metadata     jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- 2. A generated tsvector combining fields with relevance weights.
--    The regconfig argument ('english') is what makes this IMMUTABLE.
ALTER TABLE files ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')),                'A') ||
    setweight(to_tsvector('english', coalesce(filename, '')),             'B') ||
    setweight(to_tsvector('english', array_to_string(tags, ' ')),         'C') ||
    setweight(to_tsvector('english', coalesce(metadata->>'caption', '')), 'D')
  ) STORED;

-- 3. GIN serves the @@ operator. fastupdate=off trades slightly slower
--    inserts for predictable read latency.
CREATE INDEX CONCURRENTLY files_search_idx
  ON files USING GIN (search_vec) WITH (fastupdate = off);

-- 4. A second GIN index for the structured half of the query.
CREATE INDEX CONCURRENTLY files_metadata_idx
  ON files USING GIN (metadata jsonb_path_ops);

Line-by-line on the critical pieces #

• to_tsvector('english', …) parses text into tokens, runs each through the configuration’s dictionaries, and emits normalised lexemes: lowercased, stemmed (uploads → upload), with stop words discarded. The first argument names the text-search configuration; it is not optional here, and §“Configuration gotchas” explains why.
• setweight(…, 'A') tags every lexeme in that fragment with a class from A to D. A title hit should outrank a filename hit, so title takes A, filename B, tags C, and the free-text caption D. The letter is stored per lexeme, one byte, and ts_rank turns it into a multiplier at query time.
• coalesce(…, '') matters more than it looks: tsvector || NULL is NULL, so one missing title would blank the whole row’s vector and silently drop it from every search.
• STORED generated column recomputes on every INSERT and on any UPDATE that touches a referenced column. It cannot drift, and it cannot be written to by application code — which is exactly what you want when several workers upsert the same row.
• fastupdate = off disables GIN’s pending list. With it on (the default) a background insert can trigger a several-hundred-millisecond flush inside an unlucky user’s query; the parent topic covers that failure mode in detail.
• jsonb_path_ops is a compact GIN operator class supporting only @>. It builds roughly a third smaller than the default jsonb_ops and is the right choice when you only ever filter by containment.

What a tsvector actually stores #

A tsvector is not a copy of your text. It is a sorted, deduplicated array of lexemes, each carrying an optional list of integer positions and a weight letter. That representation is why the index is small — a 40-word caption typically produces a vector of 200 to 300 bytes — and it is also the source of the two most common surprises: substrings do not match, and word order is only recoverable through positions.

Three hard limits are worth knowing before you feed a whole document into a vector. A single lexeme longer than 2047 bytes is dropped with NOTICE: word is too long to be indexed. Positions above 16383 are clamped to 16383, so proximity ranking degrades on very long documents. And the vector itself cannot exceed 1 MiB.

Querying with ranking #

websearch_to_tsquery accepts the syntax users already type: bare words are ANDed, "quoted phrases" become phrase operators, or becomes |, and a leading - negates. Unlike to_tsquery it never raises a syntax error on unbalanced input, which makes it the only sane choice for a public search box.

SELECT f.id, f.filename, f.title,
       ts_rank('{0.1, 0.2, 0.4, 1.0}'::float4[], f.search_vec, q, 32) AS rank
FROM   files f,
       websearch_to_tsquery('english', $1) AS q
WHERE  f.tenant_id = $2
  AND  f.search_vec @@ q
  AND  f.metadata @> $3::jsonb        -- e.g. {"kind":"video"}
ORDER  BY rank DESC, f.created_at DESC, f.id DESC
LIMIT  20;

The 32 is the normalisation bitmask: it rescales the raw score to rank / (rank + 1), which bounds every result in [0, 1) and makes scores comparable between queries — useful when the UI shows a relevance bar or when you want to cut off results below a threshold. The explicit weight array is the default {D, C, B, A}, spelled out so a future reader can tune it without hunting the manual.

Calling it from TypeScript with the pg driver:

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export interface FileHit {
  id: string;
  filename: string;
  title: string | null;
  rank: number;
  snippet: string;
}

const SEARCH_SQL = `
  SELECT f.id::text,
         f.filename,
         f.title,
         ts_rank('{0.1, 0.2, 0.4, 1.0}'::float4[], f.search_vec, q, 32) AS rank,
         ts_headline('english', coalesce(f.title, f.filename), q,
                     'MaxFragments=1, MaxWords=18, MinWords=6') AS snippet
    FROM files f, websearch_to_tsquery('english', $1) AS q
   WHERE f.tenant_id = $2
     AND f.search_vec @@ q
     AND f.metadata @> $3::jsonb
   ORDER BY rank DESC, f.created_at DESC, f.id DESC
   LIMIT $4`;

export async function searchFiles(
  term: string,
  tenantId: string,
  filter: Record<string, unknown> = {},
  limit = 20,
): Promise<FileHit[]> {
  if (term.trim().length === 0) return [];
  const { rows } = await pool.query<FileHit>(SEARCH_SQL, [
    term,
    tenantId,
    JSON.stringify(filter),
    Math.min(limit, 100),
  ]);
  return rows;
}

// await searchFiles("annual report -draft", tenantId, { kind: "document" });
// => documents only, "draft" excluded, ordered by relevance, each with a snippet.

ts_headline re-parses the original text to build the highlighted snippet, so it costs roughly as much as to_tsvector did on write. Because it sits in the select list of a query that already has LIMIT 20, Postgres evaluates it 20 times, not once per matching row — never put it in a WHERE clause or a subquery that the planner can push down.

Configuration reference #

Matching beyond whole words #

@@ compares lexemes, so rep does not match report and raport does not match anything at all. Each fallback has a different index and a different cost, and the decision is worth making explicitly rather than reaching for a trigram index on everything.

For typeahead, append :* to the final term and build the query yourself rather than letting websearch_to_tsquery see it. For accent-insensitive matching, build a custom configuration once and use its name everywhere:

CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE TEXT SEARCH CONFIGURATION media_en (COPY = english);
ALTER TEXT SEARCH CONFIGURATION media_en
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, english_stem;

-- Now 'resume' finds 'résumé'. Both the column and every query must
-- use 'media_en' — mixing configurations silently returns zero rows.
SELECT to_tsvector('media_en', 'Résumé final');   -- 'final':2 'resum':1

Combining with JSONB and tenant filters #

Most real queries are text plus a structured predicate. Two separate GIN indexes let the planner build two bitmaps and BitmapAnd them, which works well when either predicate is selective. When the tenant filter is the selective one — the common case for multi-tenant catalogues, where the same query is issued against a table full of other tenants’ rows — put both in a single composite index using btree_gin:

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX CONCURRENTLY files_tenant_search_idx
  ON files USING GIN (tenant_id, search_vec);

That turns the tenant predicate into part of the index probe instead of a recheck over every matching row site-wide. It also pairs with the issuance controls in rate limiting presigned URL issuance: a tenant that cannot flood the bucket cannot flood the search index either.

Note what you should not fold into the vector. Derived facts with a fixed vocabulary — MIME type, codec, orientation — belong in typed columns or JSONB keys, not in text search. Beyond being slower to filter, MIME strings arriving from the browser are unreliable in the first place, as why browser MIME types are unreliable explains; index the type your server-side validation determined, in a column with a CHECK constraint.

Configuration gotchas #

ERROR: generation expression is not immutable #

You wrote to_tsvector(title) without the configuration argument. The single-argument form reads default_text_search_config at runtime, making it STABLE, not IMMUTABLE — and generated columns and index expressions both require immutability. The index equivalent is ERROR: functions in index expression must be marked IMMUTABLE. The fix is always the same: pass an explicit regconfig literal, to_tsvector('english', title).

ERROR: string is too long for tsvector #

The full message reads ERROR: string is too long for tsvector (1385034 bytes, max 1048575 bytes). Something large ended up in a text field — an OCR dump or a subtitle track. Truncate before vectorising with left(coalesce(body, ''), 200000) inside the generated expression. Truncation in the expression is safe; truncating the source column is not, because you lose data users can still read.

Adding the column locks the table #

ALTER TABLE … ADD COLUMN … GENERATED ALWAYS AS … STORED rewrites the whole table under an ACCESS EXCLUSIVE lock. On a 40 GB catalogue that is minutes of total unavailability, not seconds. Add the column to a new partition or a shadow table, backfill, and swap — or schedule it with the same care as the retention changes in S3 lifecycle rules for temporary uploads.

The match is fast and the query is slow #

@@ returning 40,000 rows is cheap; sorting all 40,000 by ts_rank is not, because ranking cannot be answered from the index — every candidate row must be fetched from the heap. If p95 climbs on common terms, add a cheap pre-filter (AND f.created_at > now() - interval '2 years'), or accept an approximate top-N by ordering on search_vec @@ q alone for the first page.

GIN is skipped on small tables #

Below a few thousand rows the planner picks a sequential scan and it is right to. Test EXPLAIN output against a realistically sized copy of production, never a fixture with 50 rows.

Verification #

Run the query under EXPLAIN (ANALYZE, BUFFERS) and read the node types, not just the total time:

EXPLAIN (ANALYZE, BUFFERS)
SELECT f.id, ts_rank(f.search_vec, q) AS rank
FROM   files f, websearch_to_tsquery('english', 'annual report') AS q
WHERE  f.search_vec @@ q
ORDER  BY rank DESC
LIMIT  20;

A plan that starts with Seq Scan on files and a Filter containing @@ means the index was not used at all — usually a configuration mismatch between column and query. Lock the behaviour in with a test that asserts on matches rather than timings:

import assert from "node:assert/strict";
import test from "node:test";
import { searchFiles } from "./search.js";

const TENANT = "8f7c0f9e-2f3a-4a19-9f0a-7b1c2d3e4f50";

test("stemming and negation behave as indexed", async () => {
  const stemmed = await searchFiles("revenues", TENANT);
  assert.ok(stemmed.some((r) => r.title?.includes("Revenue")),
    "expected the english stemmer to match revenues against Revenue");

  const negated = await searchFiles("report -draft", TENANT);
  assert.equal(negated.filter((r) => /draft/i.test(r.filename)).length, 0,
    "expected -draft to exclude every draft filename");

  const empty = await searchFiles("   ", TENANT);
  assert.deepEqual(empty, [], "blank input must not run a query");
});

Frequently Asked Questions