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
renicetheCREATE INDEXprocess? 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
ionicethe process? Will that work?
(io)niceto alter the behavior of just a single database operation, as a DB won't spawn a process for such operations.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?nicevalue 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.ioniceit, and useCREATE INDEX ... CONCURRENTLYto reduce the lock level. Note though thationicewon't stop the background writer flushing out pages from the index, the cache churn, etc so it's an imperfect solution.ionicea backend. PostgreSQLfork()s backends for each connection, it's a multi-processing architecture.