When creating an index concurrently, I'm reaching IO limitations and it's impacting the production system. Is there a way to throttle the index creation?

I'm on 9.6, will hopefully be on 11 or 12 in the near future.

Previous question: Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?

  • Which platform are you on? – Colin 't Hart Mar 13 '20 at 21:40
  • the platform i am on is ubuntu – John Bachir Mar 13 '20 at 22:57

ionice -p PID ... on Linux can in theory change the I/O scheduling class and priority of a running process. In that case you can start the index build, find it's PID and "ionice it". Have the commands to find the PID and ionice ready to run in separate windows and the impact should be very short lived.

  • I read that this isn't possible because the relevant processes that would need to be throttles are the same processes used by other operations/queries... I can't find where I read that now though. – John Bachir Mar 13 '20 at 23:03
  • 1
    Don’t think so: on Postgres 9.6 it’s a single process per connection. If you find the process that’s creating the index in pg_stat_activity you can ionice it. – Colin 't Hart Mar 14 '20 at 0:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.