Postgresql – Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database

indexperformancepostgresql

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?