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:

  1. Create new index concurrently (CREATE INDEX CONCURRENTLY ...)
  2. Wait some time
  3. Restart applications using the db
  4. Wait some more
  5. 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.

  • Do you have any expressions in your indexes, or are they all simple column values? – Kassandry Aug 25 '15 at 1:57
  • The behaviour is the same regardless. – viblo Aug 25 '15 at 2:37
  • 1
    You would want to run ANALYZE on the table after index creation if anything else than plain columns are involved. Compare: dba.stackexchange.com/a/72673/3684 – Erwin Brandstetter Aug 25 '15 at 3:53
  • When you say anything else than plain columns, that means composite index like in my example as well? Anyway, I will try doing ANALYZE next time this comes up, and if it works (and you add it as an answer) I will accept it. – viblo Aug 25 '15 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.