1

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?

Improve this question
2
  • Which platform are you on?
    – Colin 't Hart
    Commented Mar 13, 2020 at 21:40
  • the platform i am on is ubuntu
    – John Bachir
    Commented Mar 13, 2020 at 22:57

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 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
    Commented Mar 13, 2020 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
    Commented Mar 14, 2020 at 0:16

Your Answer

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

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