koldfront

Using LISTEN/NOTIFY in PostgreSQL #haskell #lantern #postgresql

The search functionality of my little NNTP engine for blogging, Lantern, uses PostgreSQL's full text search.

I have just made a VIEW that makes 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 on INSERT/UPDATE or whether it would be better to make the view MATERIALIZED.

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 INSERT/UPDATE/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 LISTEN/NOTIFY 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.