5 ms·

I'm 46 now. I remember being shocked at Postgres's heavy connection model when I was 23. I gather things haven't improved since?

saisrirampur 2mo ago
It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 https://techcommunity.microsoft.com/blog/adforpostgresql/imp.... However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot. Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.
- bradfitz 2mo ago
  In other words, it's still super heavy if it's forking a process per connection. I find it ridiculous that PgBouncer even needs to exist. Postgres should be doing this.
  - kmeaw 2mo ago
    Why Postgres should be doing this? Not every client creates a lot of connections and spinning up PgBouncer is easy. On the other hand, debugging async multithreaded complex code is hard.
    - paulryanrogers 2mo ago
      Postres project also once lacked replication, calling it unnecessary to the core effort. Now it has two means of replication in core and I'd argue is better for it, especially after suffering through both Pgpool and Slony. I hope they do develop a native, threaded pooling, even if it were incompatible with some libraries or extensions.
hinkley 2mo ago
You were coming into the field just as companies were fielding their first reasonable answers to the Threading Model that Java put forward, which was sort of Windows' but with extra features. Even Solaris choked on Java. HP UX did worse and I can't recall if SGI was worse or better than HP. But getting compatible with Java shook a lot of companies up, in how they handled concurrency. Postgres and SQLite were being designed at that same time but by industry veterans. People who had been deploying high load systems before any of this threading nonsense was around. And they were supporting people running on old hardware.
- bradfitz 2mo ago
  Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall https://metacpan.org/pod/Sys::Syscall, first out 2005-08-01) doing raw system calls because libc on the distros of the time (at least Debian) didn't have epoll support yet. So in 2005 I didn't expect Postgres to do super well here, but it's 21 years later and we're still pgbouncin'. It's just kinda sad.

saisrirampur 2mo ago

It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/improving-postgres-connection-scalability-snapshots/1806462 https://techcommunity.microsoft.com/blog/adforpostgresql/imp.... However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot. Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.

bradfitz 2mo ago
In other words, it's still super heavy if it's forking a process per connection. I find it ridiculous that PgBouncer even needs to exist. Postgres should be doing this.
- kmeaw 2mo ago
  Why Postgres should be doing this? Not every client creates a lot of connections and spinning up PgBouncer is easy. On the other hand, debugging async multithreaded complex code is hard.
  - paulryanrogers 2mo ago
    Postres project also once lacked replication, calling it unnecessary to the core effort. Now it has two means of replication in core and I'd argue is better for it, especially after suffering through both Pgpool and Slony. I hope they do develop a native, threaded pooling, even if it were incompatible with some libraries or extensions.

hinkley 2mo ago

You were coming into the field just as companies were fielding their first reasonable answers to the Threading Model that Java put forward, which was sort of Windows' but with extra features. Even Solaris choked on Java. HP UX did worse and I can't recall if SGI was worse or better than HP. But getting compatible with Java shook a lot of companies up, in how they handled concurrency. Postgres and SQLite were being designed at that same time but by industry veterans. People who had been deploying high load systems before any of this threading nonsense was around. And they were supporting people running on old hardware.

bradfitz 2mo ago
Yeah, I remember writing epoll libraries for Perl (https://metacpan.org/pod/Sys::Syscall https://metacpan.org/pod/Sys::Syscall, first out 2005-08-01) doing raw system calls because libc on the distros of the time (at least Debian) didn't have epoll support yet. So in 2005 I didn't expect Postgres to do super well here, but it's 21 years later and we're still pgbouncin'. It's just kinda sad.