I have a large production PostgreSQL (9.1) database running on Ubuntu Linux. It is currently being queried and used. However there is another use I need later, and I will need to create a large (hundreds of gigabyte) index. I have run the CREATE INDEX ... in a screen session and that's running (for several days now), however this is affecting the performance of the rest of the database.

Is there anyway to create an index in postgres, but at a very low priority? So that it'll take longer to create the index, but the performance hit will be less.

  • Can I use renice the CREATE INDEX process? Will that work?
  • What about pausing the process for 1 out of every N (e.g. 2 or 3) minutes? Will that break things?
  • Can I ionice the process? Will that work?
  • You can't use (io)nice to alter the behavior of just a single database operation, as a DB won't spawn a process for such operations.
    – Sven
    Oct 8, 2015 at 9:36
  • I think the only thing you can try is CREATE INDEX CONCURRENTLY. This won't necessarily be slower or less obtrusive, but at least it avoids blocking changes on the same table. I also think cannot pause it - how would you achieve it in any case?
    – dezso
    Oct 8, 2015 at 9:48
  • 1
    I've tried to play with renicing a PostgreSQL process that was busy with creating an index - I could see no effect. With a nice value of 19, it still could use the most CPU, effectively slowing down some big inserts on other tables. It is possible that my tests were just wrong - wiki.postgresql.org/wiki/Priorities suggests renicing should work. However, as this very same page suggests, too, you are possibly experiencing not CPU but I/O contention - and in this case no priority can help you.
    – dezso
    Oct 8, 2015 at 10:04
  • 3
    You can ionice it, and use CREATE INDEX ... CONCURRENTLY to reduce the lock level. Note though that ionice won't stop the background writer flushing out pages from the index, the cache churn, etc so it's an imperfect solution. Oct 8, 2015 at 12:47
  • 3
    @Sven nonsense, you can ionice a backend. PostgreSQL fork()s backends for each connection, it's a multi-processing architecture. Oct 8, 2015 at 12:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.