6 ms·

Postgres is my go-to RDBMS, but I do have one serious complaint: Connections are too expensive. This is a side-effect of the old-school one-process-per-connect

Postgres is my go-to RDBMS, but I do have one serious complaint: Connections are too expensive. This is a side-effect of the old-school one-process-per-connection architecture that Postgres uses. MySQL (ick) easily handles thousands of connections on small servers; with Postgres you will need a LOT of RAM to sustain the same, RAM that would be better served as cache. I've found (at least, for my current app) that the number of connections defines my scaling limit. I can't add more appserver instances or their combined connection pools will overflow the limit. And that limit is disappointingly small. It's not so painful that I want to reach for another RDBMS, but I'm bumping into this problem way too early in my scale curve.

dpc_pw 7y ago
It might be possible write a small proxy in Rust, that would run next to a PG instance, accepted connections using `async` and then forwarded queries using some limited size connection pool.
- folmar 7y ago
  ... if you are very keen on writing a pgpool replacement.
- stickfigure 7y ago
  These are active connections running transactions - unfortunately there's no substitute for just having another connection.
  - dpc_pw 7y ago
    If there is a lot of connections actually running transactions, then I'd expect the db will become overwhelmed due to number of transaction, and per-connection overhead is not going to be a problem. How I understood the problem is thousands of connections, of which most do a query from time to time only. Judging by the other comments, it seems solutions like that are already available.
    - stickfigure 7y ago
      Most of my transactions are relatively long-running (minutes) and mostly idle, so the db can handle the transaction load. There honestly doesn't seem to be a good solution to this problem. I have reorganized my app a bit to try to keep the transactions shorter (mostly checkpointing) but it's using architecture to solve a fundamentally technical problem. I wouldn't have this problem with MySQL. Switching from processes to threads (or some other abstraction) per connection isn't likely to show up in Postgres anytime soon, so I guess I'm willing to live with this... but I'm not going to pretend that Postgres is without some serious downsides.
- cryptonector 7y ago
  See PostgREST!
anarazel 7y ago
> with Postgres you will need a LOT of RAM to sustain the same The per-connection memory overhead is not actually that high. If you configure huge_pages, it's on the order of ~1.5MB-2MB, even with a large shared_buffers setting. Unfortunately many process monitoring tools (including top & ps) don't represent shared memory usage well. Most of the time each process is attributed not just the process local memory, but all the shared memory it touched. Even though it's obviously only used once across all processes. In case of top/ps it's a bit easier to see when using huge pages, because they don't include huge pages in RSS (which is not necessarily great, but ...). Edit: expand below On halfway recent versions of linux /proc/$pid/smaps_rollup makes this a bit easier. It shows shared memory separately, both when not using huge pages, and when doing so. The helpful bit is that it has a number of 'Pss*' fields, which is basically the 'proportional' version of RSS. It divides repeatedly mapped memory by the number of processes attached to it. Here's an example of smaps_rollup without using huge pages: cat /proc/1684346/smaps_rollup 56444bf26000-7fff2d936000 ---p 00000000 00:00 0 [rollup] Rss: 1854392 kB Pss: 235614 kB Pss_Anon: 1420 kB Pss_File: 274 kB Pss_Shmem: 233919 kB Shared_Clean: 10700 kB Shared_Dirty: 1837560 kB Private_Clean: 0 kB Private_Dirty: 6132 kB Referenced: 1853428 kB Anonymous: 2664 kB LazyFree: 0 kB AnonHugePages: 0 kB ShmemPmdMapped: 0 kB FilePmdMapped: 0 kB Shared_Hugetlb: 0 kB Private_Hugetlb: 0 kB Swap: 0 kB SwapPss: 0 kB Locked: 0 kB You can see that RSS claims 1.8GB of memory. But the proportional amount of anonymous memory is only 1.4MB - even though "Anonymous" shows 2.6MB. That's largely due to that memory not being modified between postmaster and backends (and thus shared). Nearly all of the rest is shared memory that was touched by the process, Shared_Clean + Shared_Dirty. With huge pages it's a bit easier: cat /proc/1684560/smaps_rollup 55e67544d000-7ffecebc9000 ---p 00000000 00:00 0 [rollup] Rss: 13312 kB Pss: 1671 kB Pss_Anon: 1397 kB Pss_File: 274 kB Pss_Shmem: 0 kB Shared_Clean: 10656 kB Shared_Dirty: 1292 kB Private_Clean: 0 kB Private_Dirty: 1364 kB Referenced: 12312 kB Anonymous: 2656 kB LazyFree: 0 kB AnonHugePages: 0 kB ShmemPmdMapped: 0 kB FilePmdMapped: 0 kB Shared_Hugetlb: 1310720 kB Private_Hugetlb: 2379776 kB Swap: 0 kB SwapPss: 0 kB Locked: 0 kB

dpc_pw 7y ago

It might be possible write a small proxy in Rust, that would run next to a PG instance, accepted connections using `async` and then forwarded queries using some limited size connection pool.

folmar 7y ago
... if you are very keen on writing a pgpool replacement.
stickfigure 7y ago
These are active connections running transactions - unfortunately there's no substitute for just having another connection.
- dpc_pw 7y ago
  If there is a lot of connections actually running transactions, then I'd expect the db will become overwhelmed due to number of transaction, and per-connection overhead is not going to be a problem. How I understood the problem is thousands of connections, of which most do a query from time to time only. Judging by the other comments, it seems solutions like that are already available.
  - stickfigure 7y ago
    Most of my transactions are relatively long-running (minutes) and mostly idle, so the db can handle the transaction load. There honestly doesn't seem to be a good solution to this problem. I have reorganized my app a bit to try to keep the transactions shorter (mostly checkpointing) but it's using architecture to solve a fundamentally technical problem. I wouldn't have this problem with MySQL. Switching from processes to threads (or some other abstraction) per connection isn't likely to show up in Postgres anytime soon, so I guess I'm willing to live with this... but I'm not going to pretend that Postgres is without some serious downsides.
cryptonector 7y ago
See PostgREST!

anarazel 7y ago

> with Postgres you will need a LOT of RAM to sustain the same The per-connection memory overhead is not actually that high. If you configure huge_pages, it's on the order of ~1.5MB-2MB, even with a large shared_buffers setting. Unfortunately many process monitoring tools (including top & ps) don't represent shared memory usage well. Most of the time each process is attributed not just the process local memory, but all the shared memory it touched. Even though it's obviously only used once across all processes. In case of top/ps it's a bit easier to see when using huge pages, because they don't include huge pages in RSS (which is not necessarily great, but ...). Edit: expand below On halfway recent versions of linux /proc/$pid/smaps_rollup makes this a bit easier. It shows shared memory separately, both when not using huge pages, and when doing so. The helpful bit is that it has a number of 'Pss*' fields, which is basically the 'proportional' version of RSS. It divides repeatedly mapped memory by the number of processes attached to it. Here's an example of smaps_rollup without using huge pages: cat /proc/1684346/smaps_rollup 56444bf26000-7fff2d936000 ---p 00000000 00:00 0 [rollup] Rss: 1854392 kB Pss: 235614 kB Pss_Anon: 1420 kB Pss_File: 274 kB Pss_Shmem: 233919 kB Shared_Clean: 10700 kB Shared_Dirty: 1837560 kB Private_Clean: 0 kB Private_Dirty: 6132 kB Referenced: 1853428 kB Anonymous: 2664 kB LazyFree: 0 kB AnonHugePages: 0 kB ShmemPmdMapped: 0 kB FilePmdMapped: 0 kB Shared_Hugetlb: 0 kB Private_Hugetlb: 0 kB Swap: 0 kB SwapPss: 0 kB Locked: 0 kB You can see that RSS claims 1.8GB of memory. But the proportional amount of anonymous memory is only 1.4MB - even though "Anonymous" shows 2.6MB. That's largely due to that memory not being modified between postmaster and backends (and thus shared). Nearly all of the rest is shared memory that was touched by the process, Shared_Clean + Shared_Dirty. With huge pages it's a bit easier: cat /proc/1684560/smaps_rollup 55e67544d000-7ffecebc9000 ---p 00000000 00:00 0 [rollup] Rss: 13312 kB Pss: 1671 kB Pss_Anon: 1397 kB Pss_File: 274 kB Pss_Shmem: 0 kB Shared_Clean: 10656 kB Shared_Dirty: 1292 kB Private_Clean: 0 kB Private_Dirty: 1364 kB Referenced: 12312 kB Anonymous: 2656 kB LazyFree: 0 kB AnonHugePages: 0 kB ShmemPmdMapped: 0 kB FilePmdMapped: 0 kB Shared_Hugetlb: 1310720 kB Private_Hugetlb: 2379776 kB Swap: 0 kB SwapPss: 0 kB Locked: 0 kB