5 ms·

> PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines From the submission: "Improved Query Pa

by grzm 9y ago

> PostgreSQL also used to be (is?) single-threaded, which limited performance of a single query on multi-core machines From the submission: "Improved Query Parallelism - Quickly conquer your analysis" Query parallelism was introduced in 9.6 and expanded in 10.

anonetal 9y ago
Yes, I was just reading through that. The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work. This makes sense for PostgreSQL, but has some fundamental limitations (e.g., it requires duplicated copies of a hash table to parallelize a hash join).
- halayli 9y ago
  PostgreSQL uses shared memory, it doesn't copy the hash table.
  - anarazel 9y ago
    In the specific case of hashjoins, it does build them independently right now. There's a patch to rectify that though, by putting the hashtable also into shared memory. The coordination necessary to make multi phase batch joins and other such funny bits work, unfortunately made it infeasible to get into 10.
    - halayli 9y ago
      I stand corrected, it definitely reconstructs the hash table in each process.
      - anarazel 9y ago
        FWIW, here's the patchset to fix that: https://commitfest.postgresql.org/15/871/ https://commitfest.postgresql.org/15/871/
        halayli 9y ago
        Thanks! I remember reading this thread a while back and I thought it made it in.
- endorphone 9y ago
  >The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work. So...it isn't single threaded then? I mean that is exactly how the most advanced competitors operate (Oracle, SQL Server) as well -- a given connection stays on one thread, with the advantages that confers, unless the planner decides to parallelize.
  - adzm 9y ago
    To be technical, MSSQL uses its own bespoke scheduling, and will preempt the thread for io. All io is nonblocking. The physical thread can vary for this reason. PGSQL really does use synchronous io and a single thread though. The former is probably more scalable but the latter has been serving PGSQL fine, too.
    - tychver 9y ago
      I think bitmap heap scans have had concurrent IO for quite a while now? There's the effective_io_concurrency setting for it.
- jeffdavis 9y ago
  No, processes don't create fundamental limitations. They can still share memory, it's just an "opt-in" choice. Postgres processes share memory for all kinds of things. Hash tables may be duplicated, but not due to any fundamental limitations.

by grzm 9y ago

anonetal 9y ago

Yes, I was just reading through that. The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work. This makes sense for PostgreSQL, but has some fundamental limitations (e.g., it requires duplicated copies of a hash table to parallelize a hash join).

halayli 9y ago
PostgreSQL uses shared memory, it doesn't copy the hash table.
- anarazel 9y ago
  In the specific case of hashjoins, it does build them independently right now. There's a patch to rectify that though, by putting the hashtable also into shared memory. The coordination necessary to make multi phase batch joins and other such funny bits work, unfortunately made it infeasible to get into 10.
  - halayli 9y ago
    I stand corrected, it definitely reconstructs the hash table in each process.
    - anarazel 9y ago
      FWIW, here's the patchset to fix that: https://commitfest.postgresql.org/15/871/ https://commitfest.postgresql.org/15/871/
      - halayli 9y ago
        Thanks! I remember reading this thread a while back and I thought it made it in.
endorphone 9y ago
>The server is still single-threaded though -- they are getting the parallelism by starting multiple processes to do independent chunks of work. So...it isn't single threaded then? I mean that is exactly how the most advanced competitors operate (Oracle, SQL Server) as well -- a given connection stays on one thread, with the advantages that confers, unless the planner decides to parallelize.
- adzm 9y ago
  To be technical, MSSQL uses its own bespoke scheduling, and will preempt the thread for io. All io is nonblocking. The physical thread can vary for this reason. PGSQL really does use synchronous io and a single thread though. The former is probably more scalable but the latter has been serving PGSQL fine, too.
  - tychver 9y ago
    I think bitmap heap scans have had concurrent IO for quite a while now? There's the effective_io_concurrency setting for it.
jeffdavis 9y ago
No, processes don't create fundamental limitations. They can still share memory, it's just an "opt-in" choice. Postgres processes share memory for all kinds of things. Hash tables may be duplicated, but not due to any fundamental limitations.