performance - Postgres tsvector_update_trigger sometimes takes minutes -

i have configured free text search on table in postgres database. pretty simple stuff, firstname, lastname , email. works , fast.

i experience looong delays when inserting new entry table, insert keeps running minutes , generates huge wal files. (we use wal files replication).

is there need aware of free text index? postgres maybe randomly restructuring performance reasons? index around 400 mb big.

thanks in advance!

christian

given size of wal files, suspect right index update/rebalancing causing issue. have wonder else going on.

i recommend against storing tsvectors in separate columns. better way run index on to_tsvector()'s output. can have multiple indexes multiple languages if need. instead of trigger takes, say, field called description , stores tsvector in desc_tsvector, recommend doing:

 create index mytable_description_tsvector_idx on mytable(to_tsvector(description)); 

now, if need consistent search interface across whole table, there more elegant ways of doing using "table methods."

in general functional index approach has fewer issues associated else.

now second thing should aware of partial indexes. if need to, can index records of interest. example, if of queries check last year, can:

 create index mytable_description_tsvector_idx on mytable(to_tsvector(description))   created_at > now() - '1 year'::interval;