4

Let's say I have a 1 million record table. Each record is uniquely identifiable by id. If the table was divided into 10 cursors and 10 requests were made by a web client for each cursor to say change the last name field to upper, and 10 cores were available, would the work get done 10x faster? Or any time faster, compared to a straight for .. loop regular processing of the entire table?

I know PostgreSQl can't run parallel queries that make changes. I'm trying to get around that by using code instead. Note that these records are independent. All changes are strictly particular to the record.

2
  • 1
    Which Postgres version ? For PG v9.6, we have parallel query postgresql.org/docs/9.6/static/parallel-query.html. Please provide more details about your question. – Luan Huynh Jul 14 '17 at 4:14
  • 1
    The focus of my question is about emulating parallel write queries to take advantage of all the cores and threads that are available these days with modern CPUs. – Peter Dixon Aug 12 '17 at 4:06
5

Actually, PostgreSQL 9.6 can run parallel (read) queries. Parallel writes aren't supported yet.

The approach you suggest would work, but some care would be required to ensure it was consistent, since each cursor would be a different session with a different snapshot of the database.

You could instead just run 10 UPDATEs from 10 different clients. Same effect, simpler, and probably faster. Just make sure the targeted row ranges don't overlap.

2
  • So would using UPDATEs kick off another process in Postgres? And rather importantly, how much extra RAM do you think would be needed for this approach? – Peter Dixon Jul 14 '17 at 22:43
  • @PeterDixon One process per session (connection), ram usually minimal unless deferred constraints, after triggers, etc in use – Craig Ringer Jul 15 '17 at 10:06

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.