We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance.
Sometimes we need to update/recreate one of the indexes we have, for example to remove bloat or tweak it to include/exclude a column. We do not want to have any downtime, and at the same time avoid doing these things between 4 and 6 am when there is less load.
What we usually do is:
- Create new index concurrently (
CREATE INDEX CONCURRENTLY ...)
- Wait some time
- Restart applications using the db
- Wait some more
- Drop the old index, hoping that the query planner will use the new index properly.
Is there some better way to handle index creation/rebuild?
As you can guess waiting an unspecified time and restarting the services that connect to the database is nonoptimal, and if you are unlucky the query planner still wont use the new index for a while which might be very bad for our application.
Our servers run PostgreSQL 9.3 on CentOS 6, we use pgbouncer to handle connections and our application using the db is coded in go.
On example just recently where I got bitten by this was when adding a column to an index on a table storing pictures:
-- table (some irrelevant columns not included) CREATE TABLE pictures ( id serial primary key, user_id integer not null, type type not null, -- enum with 2 different values picture_url varchar not null ); -- old index CREATE INDEX pictures_user_id_idx ON pictures (user_id); -- new index CREATE INDEX CONCURRENTLY pictures_user_id_type_idx ON pictures (user_id, type); -- drop old index DROP INDEX pictures_user_id_idx;
Right after dropping the old index the queries went very slow. I restarted the services that connect to the database and after a little while it was back to normal.