4 ms·

Threads does not offer any major performance advantage, performance of processes vs threads is virtually the same. The reason the PostgreSQL project is moving t

• codesnik 3mo ago
  unless you're spawning them for new connections.
  • jeltz 3mo ago
    Some, but not that much. Switching PostgreSQL to a threaded model will not magically make spawning connections fast. PostgreSQL connections are quite heavyweight. The reason to use threads is almost entirely about ease of development, not about performance. If you use shrared memory like PostgreSQL does you need to write your own allocators, etc. So much you get for free if you use threads.
• malisper 3mo ago
  > Threads does not offer any major performance advantage This is very not true. When it comes to parallel queries, a process model adds a ton of overhead. You can't pass pointers between processes because the address space is different. This adds a ton of overhead in a bunch of different places. For example when doing a parallel hash join, Postgres will have each worker build a local hash table. Then it will take all the tuples out of the local hash table and copy them through shared memory to the leader who will then construct a new hash table. This duplicates a lot of work as you have to hash the tuples multiple times. A lot of getting to Clickhouse level performance was making better use of parallelism.
  • jeltz 3mo ago
    Ok ... you know PostgreSQL supports hash tables in shared memory, right? PostgreSQL could in theory share those if we wanted to. The issue is just that coding anything which uses shared memory is a lot of work. Additionally the reasons PostgreSQL does not offer Clickhouse performance has very little to do with parallelism. PostgreSQL plans to move to threading but the efforts around imporving OLAP performance are almost entirely unrelated.
    • dupontcyborg 3mo ago
      i may be missing context, but shared memory across processes, without ipc?
      • Giefo6ah 3mo ago
        There's nothing special about threads vs processes in Linux. mmap works the same, the challenge is to map the same file. You can share a path, pass a file descriptor via fork or unix domain socket, among other techniques.
        • LoganDark 3mo ago
          That induces disk I/O overhead (even if it somehow doesn't impact IPC performance)
          • drdexebtjl 3mo ago
            The file doesn’t have to be disk-backed.
            • LoganDark 3mo ago
              Don't you need something mounted for that?
              • yxhuvud 3mo ago
                No, you can use a memfd.
                • tankenmate 3mo ago
                  And `/dev/shm/` (which postgres uses by default on most Unix platforms)
          • outpost_mystic2 3mo ago
            It doesnt. Processes can share memory
  • mort96 3mo ago
    Passing pointers is not significantly faster than passing offsets into a shared memory pool.
    • menaerus 3mo ago
      Sorry, what? Passing a pointer is a matter of wrapping the value into the CPU register. OTOH passing an offset into a shared memory is a write to main memory so several magnitudes slower.
      • mort96 3mo ago
        Passing a pointer within one thread requires putting the pointer into a register. Passing a SHM offset within one process requires putting the offset into a register. Passing a pointer between threads requires going through the memory system and letting cache coherence algorithms sort out the data sharing between cores (with or without a futex lock/unlock depending on implementation). Passing a SHM offset between processes requires going through the memory system and letting cache coherence algorithms sort out the data sharing between cores (with or without a context switch to the kernel depending on implementation). It's not that different.
• SigmundA 3mo ago
  MSSQL can handle 32k open connections no need to run a pooler in front of it, can PG do 32k connections and a process for each? MSSQL shares cached query plans between connections including jitted code, PG cannot do that and the changes needed to make the plans cross process portable would be extensive while sharing between threads is just normal code sharing between threads.