Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pgagroal: High-performance connection pool for PostgreSQL

github.com

Post reply on HN
Print view View original

11–20 of 39 posts

Re: Pgagroal: High-performance connection pool for PostgreSQL

#11

Post by api »

skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.”

We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this?

I guess we do in the form of CockroachDB or Yugabyte, but Postgres has been so aggressively evangelized try selling someone on those. I know of one or two companies that use them but it’s a tough sell.

There are tons of overpriced Postgres managed cloud offerings and consultancies, so my guess is that there is a heavy financially motivated astroturf campaign.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#12

Post by qaq »

api wrote: post #11
skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.” We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this? I guess we do in the form o…

You do realize that prob 80% of Yugobyte code is PostgreSQL?

Re: Pgagroal: High-performance connection pool for PostgreSQL

#13

Post by bayesian_horse »

api wrote: post #11
skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.” We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this? I guess we do in the form o…

Some people feel like Postgres with its decades of history is the safer bet. It's a known commodity. Or at least more known than CockroachDB and Yugabyte.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#14

Post by gmac »

api wrote: post #11
skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.” We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this? I guess we do in the form o…

First: have you used Postgres, and at what scale? Because that hasn't been my experience of it.

Second: do you know something that's comparable but better?

Re: Pgagroal: High-performance connection pool for PostgreSQL

#16

Post by rkrzr »

api wrote: post #11
skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.” We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this? I guess we do in the form o…

As a long-time happy user of Postgres, I am happy to answer your questions:

> The popularity and mindshare of Postgres really puzzles me.

Why? Postgres really is "The World's Most Advanced Open Source Relational Database", just like it says on the tin.

> It’s so arcane and full of weird failure modes and gotchas.

Not really. It actually is the best-documented open source database that behaves just like they say in the manual. The Postgres manual is really quite wonderful, can be read back-to-back, like a good book.

> Why can’t we have SQL like this?

This has more to do with SQL itself than with Postgres. SQL as a standard has evolved over so many decades that the latest version of the spec has literally thousands of pages [citation needed, couldn't quickly find a link]. Implementing all of this, is an enormous task, and no relational DB implements all of it.

> I guess we do in the form of CockroachDB or Yugabyte, but Postgres has been so aggressively evangelized try selling someone on those. I know of one or two companies that use them but it’s a tough sell.

Postgres covers ~95% of all use cases of your average software company. Specialized DBs are really only useful in that last ~5% of cases.

> There are tons of overpriced Postgres managed cloud offerings and consultancies, so my guess is that there is a heavy financially motivated astroturf campaign.

Postgres is quite easy to run yourself, completely free of charge. There really isn't any "financially motivated astroturf campaign" that I can discern.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#17

Post by kasey_junk »

jpgvm wrote: post #5
skrause wrote: post #4

"PostgreSQL's model of one process per connection doesn't scale, so let's build a connection pooler that uses one process per connection." Certainly sounds like a strange concept. How well does it scale with thousands of connections?

Not the author but PG connection scalability being blamed on per-process model is somewhat misunderstood. You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Instead the poor scalability stems from the amount of resources allocated to each connection. If postgres was to switch to threads without changing this architectur…

Pretty good discussion of Postgres’ process bottlenecks: https://www.citusdata.com/blog/2020/10/08/analyzing-connecti...

Summary: The resource per process issue is isolated to just the transaction isolation mechanism and can likely be improved but you are never getting around the context switching and memory locality issues of the per process model.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#18

Post by api »

gmac wrote: post #14
api wrote: post #11

Earlier quoted context omitted.

The popularity and mindshare of Postgres really puzzles me. It’s so arcane and full of weird failure modes and gotchas. It feels like something from a 1980s mainframe that would come with a bound “field manual.” We can have file systems like zfs that for all but maybe the largest and most exotic use cases are bulletproof and relatively easy to administrate. Why can’t we have SQL like this? I guess we do in the form o…

First: have you used Postgres, and at what scale? Because that hasn't been my experience of it. Second: do you know something that's comparable but better?

I should have been more detailed.

Postgres works really well in single node configurations as long as you read up a bit on the weirdness around vacuuming. Usually you don't have to worry unless you are running write-heavy loads at large scale.

HA Postgres is where things get frustrating and hairy. It's 2021. I should be able to set up a master-master multi-node database by running a second node and telling it where the first node is, and then you're done. Master with read-only backup should be similarly easy. If I shut down the "lead" node a backup should become leader automatically. I should be able to set this up in a day and not worry that it will fail to fail over properly in a real scenario because there are algorithms now like Raft consensus that are provably correct (provided you meet their requirements).

IMHO this kind of easy clustering is a table stakes feature for anything billing itself as an "enterprise capable" database.

The fact that HA Postgres requires me to think in 2021 is what makes me hate the thing. As near as I can tell this problem exists to make me buy managed HA Postgres at 1000% markup over compute/storage.

I can't complain too much because it's free and it is very good for the things it does well. It's just puzzling to me that there is no canonical turn-key solution for this after so many years of so many people requiring it.

Implementing Raft is far far easier than a lot of the other stuff that has been implemented in Postgres.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#19

Post by hagbarddenstore »

iruoy wrote: post #10
maxpert wrote: post #9

> This report describe pgagroal in relationship to 3 other PostgreSQL connection pool implementations, which we will call "a", "b" and "c". Why? Just why! Please put up names rather than saving faces for other poolers!

Also make the benchmark code open source, so that we can run it on our own hardware and judge it's relevancy.

Uh... https://www.postgresql.org/docs/current/pgbench.html ?

That's what's being used...

Re: Pgagroal: High-performance connection pool for PostgreSQL

#20

Post by bigbillheck »

maxpert wrote: post #9

> This report describe pgagroal in relationship to 3 other PostgreSQL connection pool implementations, which we will call "a", "b" and "c". Why? Just why! Please put up names rather than saving faces for other poolers!

> Why? Just why! Please put up names rather than saving faces for other poolers!

It's Larry Ellison's fault: https://en.wikipedia.org/wiki/David_DeWitt

Post reply on HN