Progettazione di colonne tsvector e indici GIN

PostgreSQL full-text search compares a tsvector (the searchable document) against a tsquery (the search terms). The naive approach calls to_tsvector() on a raw text column at query time.

That works, but it has two costs at scale:

• CPU per row: parsing and stemming text on every scan is expensive.
• No usable index unless the index expression exactly matches the query expression.

The fix is to precompute the document once and store it, then index it. This lesson shows how to design that column and the GIN index so full-text queries stay sub-millisecond even on millions of rows.

Here is the pattern most people start with: store plain text, and build the tsvector on the fly.

The query below works correctly, but on a large table it triggers a sequential scan and re-parses body for every row. Each call to to_tsvector stems and normalizes the full document text.

The lesson's goal is to eliminate this per-row work entirely.

The cleanest modern design (PostgreSQL 12+) is a stored generated column. PostgreSQL computes the tsvector automatically whenever the row changes, so the document is always consistent with the source text.

Two rules to remember:

• The generation expression must be IMMUTABLE, which is why you pass the regconfig as a literal ('english') rather than relying on a session setting.
• Use coalesce() so a NULL field doesn't make the whole document NULL.

Not every field deserves equal importance. A match in the title usually matters more than a match deep in the body. setweight() tags lexemes with a label A, B, C, or D (A is highest).

These labels later let ts_rank score title matches above body matches. Bake the weighting into the generated column so it is computed once, not at query time.

A stored tsvector is still useless without an index. The right index type for full-text search is GIN (Generalized Inverted Index). GIN stores one entry per distinct lexeme pointing to the rows that contain it, which is exactly what @@ matching needs.

Because the column already holds a tsvector, the index is a plain column index, no expression required:

Now the query references the stored column directly. The planner can use the GIN index because the expression in the WHERE clause (search_doc) matches the indexed expression exactly.

No to_tsvector() per row, no sequential scan. Run EXPLAIN ANALYZE and you should see a Bitmap Index Scan on idx_articles_search_doc.

PostgreSQL supports two index types for tsvector. Choose deliberately:

• GIN: faster lookups, the default choice for search. Slightly larger and slower to build/update. Best when reads dominate.
• GiST: smaller and cheaper to update, but lossy, so it rechecks candidate rows and is slower for queries. Useful for very write-heavy or constantly-churning data.

For most search workloads, where you query far more than you write, GIN wins. Reach for GiST only when index-update cost is your bottleneck.

GIN indexes use a pending list to batch inserts (fastupdate = on by default). This speeds up writes, but a large pending list slows down reads because queries must scan it in addition to the main index.

For read-heavy search tables you can tune or disable this behavior. Disabling fastupdate makes each insert do more work but keeps queries consistently fast.

Generated columns arrived in PostgreSQL 12. On older versions, or when you need logic that isn't IMMUTABLE, you maintain the tsvector with a trigger.

The classic helper is tsvector_update_trigger, which fills a target column from named source columns. Note its limitation: it uses a single, fixed weight and a fixed config, so for per-field weighting you write a custom BEFORE trigger function instead.

A trigger only fires on future inserts and updates. Existing rows keep a NULL search_doc until you backfill them.

For a stored generated column, PostgreSQL backfills automatically when you add the column. For the trigger pattern, run a one-time UPDATE. On huge tables, do it in batches by primary-key range so you don't lock the whole table or bloat one giant transaction.

Always confirm the planner uses your GIN index instead of falling back to a sequential scan. Common reasons it won't: the query expression doesn't match the indexed expression, the table is tiny, or statistics are stale.

Run EXPLAIN (ANALYZE, BUFFERS) and look for a Bitmap Index Scan on your index name. If you see a Seq Scan with a Filter, the index isn't being used, fix the expression match or run ANALYZE.

You have a large, read-heavy articles table. You want full-text queries that match titles more strongly than body text, stay sub-millisecond, and never re-parse text at query time. Which design best meets all three goals?

You designed a high-performance full-text search column from end to end:

• Precompute the document in a STORED generated tsvector column so text is parsed once, not per query.
• Weight fields with setweight() (A for title, B for body) so ts_rank can score matches meaningfully.
• Index the column with GIN, the read-optimized inverted index for @@ matching; prefer GiST only for very write-heavy churn.
• Tune writes via fastupdate and gin_pending_list_limit when the pending list slows reads.
• Maintain pre-12 tables with a trigger and backfill existing rows in batches.
• Verify with EXPLAIN (ANALYZE, BUFFERS) that you get a Bitmap Index Scan, not a Seq Scan.