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.

Improve this question
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, 2017 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, 2017 at 4:06

1 Answer 1

Reset to default
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.

Improve this answer
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, 2017 at 22:43
  • @PeterDixon One process per session (connection), ram usually minimal unless deferred constraints, after triggers, etc in use
    – Craig Ringer
    Jul 15, 2017 at 10:06

Your Answer

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

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