Defining GIN indexes

If you want to apply text search to a column or a group of columns, there are basically two choices:

• Create a functional index using GIN
• Add a column containing ready-to-use tsvectors and a trigger to keep them in sync

In this section, both options will be outlined. To show you how things work, I have created some sample data:

test=# CREATE TABLE t_fts AS SELECT comment 
 FROM pg_available_extensions;
SELECT 43 

Indexing the column directly with a functional index is definitely a slower but more space-efficient way to get things done:

test=# CREATE INDEX idx_fts_func ON t_fts 
        USING gin(to_tsvector('english', comment)); 
CREATE INDEX

Deploying an index on the function is easy, but it can lead to some overhead. Adding a materialized column needs more space, but will lead to better runtime behavior:

test=# ALTER TABLE t_fts ADD COLUMN ts tsvector; 
ALTER TABLE 

The only trouble is, how do you keep this column in sync? The answer is by using a trigger:

test=# CREATE TRIGGER tsvectorupdate 
 BEFORE INSERT OR UPDATE ON t_fts 
 FOR EACH ROW 
 EXECUTE PROCEDURE 
 tsvector_update_trigger(somename, 'pg_catalog.english', 'comment');

Fortunately, PostgreSQL already provides a C function that can be used by a trigger to sync the tsvector column. Just pass a name, the desired language, and a couple of columns to the function, and you are already done. The trigger function will take care of all that is needed. Note that a trigger will always operate within the same transaction as the statement making the modification. Therefore, there is no risk of being inconsistent.