I have just made a
tsvector for each article, and searching is then done by
SELECT'ing from that view.
Recently using the view directly became a little too slow to my liking
(above 1 second), so I discussed with myself whether to include the
tsvector as a field on the article table with a trigger to update it
UPDATE or whether it would be better to make the view
Although the way the
tsvector is constructed hasn't changed much
recently, I felt that going the materialized view way was easier - it
doesn't take that long to update it all, and then I don't have to
think about old fields that are out of date.
So I added a trigger that would refresh the view on
DELETE on the table. That works nicely, but it does
give me an annoying pause when posting a new article, which is long
enough for my brain to go "Is something wrong?" before it completes.
I could move the refreshing to a cron job that would refresh the view once in a while, but since posting is quite irregular that feels like a waste.
Instead I looked into the
functionality in PostgreSQL - and changed the trigger from refreshing
the materialized view to executing a
NOTIFY on a channel, and then I
created a little
program that runs
continuously listening on the channel and refreshing the view when it
receives an event.
The only aber dabei is that if the little program isn't running when a
NOTIFY is sent, the event is lost. But I'll just start it before
everything else, and it should be fine.