Rebuilding large GIN indexes

takes time. A lot of time. With rebuilding, I mean when you for example have to UPDATE all your tsvector fields due to a change in tsearch2 configuration. (Yes, I had to do that because I had a slightly incorrect tsearch2 configuration for the archives search database). So don't do it. Instead use the fact that PostgreSQL has nice transactional DDL and do something like this: [HTML_REMOVED] BEGIN TRANSACTION;

CREATE TABLE messages_new AS SELECT id,txt,to_tsvector(txt) AS fti

FROM messages;

CREATE INDEX messages_new_fti ON messages_new USING gin(fti);

ANALYZE messags_new;

ALTER TABLE messages RENAME TO messages_old;

ALTER TABLE messages_new RENAME TO messages;


DROP TABLE messages_old;

ALTER INDEX messages_new_fti RENAME TO messages_fti; ``` (apologies for any typos, I didn't bother to actually type these commands into the database again, and I lost my cut-and-paste of what I ran)

This way, the messages table can still serve up searches without any disruption to the searches at all. And creating the new index is a lot faster than updating the existing one if you have to touch all rows.


I speak at and organize conferences around Open Source in general and PostgreSQL in particular.


Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
Postgres Vision 2016
Oct 11-Nov 13, 2016
San Francisco, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan


PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
PGDay UK 2016
Jul 05, 2016
London, UK
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
May 17-21, 2016
Ottawa, Canada
Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
More past conferences