Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.
So, since I will be doing this on several databases, decided to write a script that will handle the work for me.
The script is written to be ran using psql, and it generates file (/tmp/upgrade_reindex.sql) that will do actuall reindexing.
It is not perfect, there are two known issues with it:
- It doesn't handle indexes used by exclusion constraints – reason is very simple – you can do “ALTER TABLE ADD PRIMARY KEY USING INDEX", or “… ADD UNIQUE USING INDEX", but you can't do so with exclusion constraints
- While index generation is not locking (using CONCURRENTLY), there are ALTER commands that can lock – they take just a short time, but getting index might take a while in environment with lots of activity
First thing – I can't do much about. Seconds – well, it's possible to handle using trick that pg_reorg is using (short statement_timeout, lock, long statement_timeout, alter – all in loop to retry on error with statement_timeout), but I haven't got yet to do it.
Lastly – it doesn't touch indexes in pg_catalog or information_schema – why? I don't like touching catalog. And if I'd have to (which looks like I might need to), I would just do it using plain old “REINDEX" – these indexes are usually very small, so it shouldn't be a problem.
SET search_path = pg_catalog; \SET QUIET \pset format unaligned \pset tuples_only ON \SET output_file /tmp/upgrade_reindex.sql \o :output_file SELECT E'\\set ON_ERROR_STOP 1'; WITH base_info AS ( SELECT pg_get_indexdef(i.indexrelid) AS index_create, format( 'DROP INDEX %I.%I', n.nspname, c.relname) AS index_drop, pg_get_constraintdef(con.oid) AS constraint_create, CASE WHEN con.conname IS NOT NULL THEN format( 'ALTER TABLE %I.%I DROP CONSTRAINT %I', tn.nspname, t.relname, con.conname ) ELSE NULL::TEXT END AS constraint_drop, c.relname AS index_name, n.nspname AS index_namespace, t.relname AS TABLE_NAME, con.contype AS constraint_type FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_constraint con ON i.indexrelid = con.conindid AND con.contype IN ('p', 'u', 'x') LEFT OUTER JOIN pg_class t ON con.conrelid = t.oid LEFT OUTER JOIN pg_namespace tn ON t.relnamespace = tn.oid WHERE n.nspname !~ '^(pg_|information_schema)' ), cmds AS ( SELECT constraint_type, index_create || COALESCE( ' [ constraint: ' || constraint_create || ']', '' ) AS cmd_comment, CASE WHEN constraint_type = 'x' THEN E'\\echo ' ELSE '' END AS prefix, regexp_replace( index_create, ' INDEX ' || quote_ident( index_name ) || ' ', ' INDEX CONCURRENTLY __upgrade_reindex ' ) AS cmd_create, COALESCE( constraint_drop, index_drop) AS cmd_drop, format( 'ALTER INDEX %I.%I RENAME TO %I', index_namespace, '__upgrade_reindex', index_name ) AS cmd_rename, CASE WHEN constraint_type = 'p' THEN format( 'ALTER TABLE %I.%I ADD PRIMARY KEY USING INDEX %I', index_namespace, TABLE_NAME, index_name ) WHEN constraint_type = 'u' THEN format( 'ALTER TABLE %I.%I ADD UNIQUE USING INDEX %I', index_namespace, TABLE_NAME, index_name ) ELSE NULL::TEXT END AS cmd_conadd FROM base_info ) SELECT CASE WHEN prefix = '' THEN '' ELSE E'\\echo This cannot be reindexed concurrently, because it is used by exclusion constraint\n' END || prefix || '-- ' || cmd_comment || E'\n' || prefix || cmd_create || E';\n' || prefix || cmd_drop || E';\n' || prefix || cmd_rename || E';\n' || COALESCE( prefix || cmd_conadd || E';\n', '' ) FROM cmds ; \o \echo 'Just type: \\i' :output_file