PostgreSQL全文搜索列需多久重建索引？如何高效更新tsvector索引？

Hey there! Let's break down your two PostgreSQL full-text search questions one by one:

First off, the short answer is: you usually don't need to manually rebuild full-text indexes on a regular schedule. PostgreSQL's full-text indexes (like GIN or GIST) are designed to automatically handle inserts/updates/deletes as data changes. That said, there are specific scenarios where rebuilding makes sense:

• After bulk data loads: If you're importing hundreds of thousands or millions of rows at once, it's way more efficient to create the index after the bulk insert, rather than letting it update incrementally during the load. If you already had an index before the bulk load, dropping and recreating it post-load will be faster and result in a more compact index.
• When index bloat becomes an issue: Over time, especially with frequent updates/deletes, GIN indexes (common for full-text) can accumulate dead space. You can check for bloat by comparing the index size (pg_indexes_size('your_index_name')) to the actual data it's indexing, or by looking at pg_stat_user_indexes—if idx_tup_read is way higher than idx_tup_fetch, that might indicate bloat slowing down queries. If bloat is significant (say, the index is 2-3x larger than it should be), rebuilding will reclaim space and improve performance.
• Post-major PostgreSQL version upgrade: Sometimes after upgrading to a new major version (like 13 → 14), PostgreSQL recommends rebuilding indexes to take advantage of new optimizations or fix compatibility issues.

As a general rule, don't rebuild indexes on a fixed schedule—only do it when you observe performance degradation or bloat through monitoring.

Manually running UPDATE after every insert/update is error-prone and inefficient. Here are two better approaches:

1. Use Triggers (for flexible logic) #

Triggers automatically update the tsvector column whenever the source data (like your name column) changes. This is the standard approach if you need to combine multiple columns or apply custom weights:

First, create a trigger function:

CREATE OR REPLACE FUNCTION update_tsv_name()
RETURNS TRIGGER AS $$
BEGIN
  NEW.tsv := setweight(to_tsvector('english', NEW.name), 'A');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then create the trigger to fire on inserts and updates:

CREATE TRIGGER trigger_update_tsv_name
BEFORE INSERT OR UPDATE OF name ON your_table_name
FOR EACH ROW EXECUTE FUNCTION update_tsv_name();

This way, every time you insert a row or update the name column, the tsv column gets updated automatically—no manual UPDATE needed.

2. Use Generated Columns (PostgreSQL 12+) #

If your tsvector logic is straightforward (just a single column with a fixed weight), PostgreSQL 12 and above support generated columns which are computed automatically and stored with the table. This is even simpler than triggers:

ALTER TABLE your_table_name
ADD COLUMN tsv tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', name), 'A')) STORED;

The STORED keyword means the value is saved to disk, so queries don't have to compute it on the fly. The best part? PostgreSQL handles all updates automatically whenever the name column changes—no triggers to manage.

Which to choose? #

• Use generated columns if your logic is simple and you're on PostgreSQL 12+. It's less code and easier to maintain.
• Use triggers if you need to combine multiple columns (e.g., name + description), apply different weights to different fields, or add custom logic (like excluding certain words).

内容的提问来源于stack exchange，提问作者kamal