0

During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time.

Is there any way to calculate indexes multithreaded for single inserts?

0

Is there any way to calculate indexes multithreaded for single inserts?

Unfortunately, no.

PostgreSQL backends are single-threaded, and a single connection can use only one backend.

There's work to enhance this, but it's a seriously difficult and big job because of PostgreSQL's process-based architecture.

The only real option is to split the insert its self into multiple concurrent connections, each doing part of the work.

  • Thank you Craig Ringer! About your last statement, does that mean that Postgres only partially alters the index on each write? – user32234 Jul 1 '14 at 13:33
  • 2
    @Gracchus the index is incrementally updated, yes. If possible drop it before & recreate it after a big bulk load if you don't mind the impact on concurrent readers. – Craig Ringer Jul 1 '14 at 14:05
  • In many cases, I have been able to greatly speed up tasks by splitting one insertion query into 32 that run concurrently on a 32 core machine. Postgres 9.6 has added some minimal support for parallelized queries, but I still unfortunately have to do most of mine manually (assisted with Python scripts). I hope Postgres improves in this area since even a single machine is becoming increasingly "horizontal" nowadays. – sudo Mar 14 '17 at 19:31

Your Answer

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