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

31–39 of 39 posts

Re: Pgagroal: High-performance connection pool for PostgreSQL

#31

Post by anarazel »

anarazel wrote: post #30
jpgvm wrote: post #5

Earlier quoted context omitted.

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…

> You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Not really. The page-table overhead of a 100k processes alone is going to a eat up a lot of memory - and you don't have that with threads. And with something like postgres the page tables aren't going to be tiny, even with explicit use of huge pages for applicable all…

(Although the sibling post pointing to my postgres connection scalability post is right - processes are not the major bottleneck at the moment, and certainly not with < PG 14)

Re: Pgagroal: High-performance connection pool for PostgreSQL

#32

Post by jpgvm »

anarazel wrote: post #31
anarazel wrote: post #30

Earlier quoted context omitted.

> You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Not really. The page-table overhead of a 100k processes alone is going to a eat up a lot of memory - and you don't have that with threads. And with something like postgres the page tables aren't going to be tiny, even with explicit use of huge pages for applicable all…

(Although the sibling post pointing to my postgres connection scalability post is right - processes are not the major bottleneck at the moment, and certainly not with < PG 14)

Yeah I agree wholeheartedly. What I was trying to get across was that simply s/processes/threads isn't going to make a massive difference to postgres if that was the only change you were to make.

Your analysis seems pretty on-point with what I expected, i.e snapshot scalability and other postgres specific arch being the major bottlenecks for higher connection counts.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#33

Post by anarazel »

jpgvm wrote: post #32
anarazel wrote: post #31

Earlier quoted context omitted.

(Although the sibling post pointing to my postgres connection scalability post is right - processes are not the major bottleneck at the moment, and certainly not with < PG 14)

Yeah I agree wholeheartedly. What I was trying to get across was that simply s/processes/threads isn't going to make a massive difference to postgres if that was the only change you were to make. Your analysis seems pretty on-point with what I expected, i.e snapshot scalability and other postgres specific arch being the major bottlenecks for higher connection counts.

FWIW, a good chunk of the snapshot scalability changes are in PG 14, so the situation has gone a bit further towards s/processes/threads/ being the primary bottleneck. It depends quite a bit on whether you're write-mostly (bottlenecks almost completely unrelated to processes vs threads) or read-mostly. And in the latter case whether it's a context switch heavy workload (processes vs threads may be a significant factor), or not (processes vs threads can be a significant factor, but less likely).

Re: Pgagroal: High-performance connection pool for PostgreSQL

#34

Post by oliwarner »

bigbillheck wrote: post #28
oliwarner wrote: post #27

Earlier quoted context omitted.

This is an [interesting] joke, right?

No? Oracle started putting clauses in their sales contracts limiting publishing benchmarks because they performed poorly in a published report.

I mean in this context. Seems implausible you could sneak a DeWitt Clause into a FOSS license.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#35

Post by paulryanrogers »

anarazel wrote: post #29

I do not get why it's a good idea for a connection pooler to go for a process model. The whole "isolation and stability" argument is doubtful for postgres itself, but it's imo pretty much nonsensical for a pooler.

Could be simplicity and ease of implementation. I recall Pgpool was also separate processes, and relied on resetting open connections between sessions.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#36

Post by paulryanrogers »

api wrote: post #18
gmac wrote: post #14

Earlier quoted context omitted.

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 t…

What other DBs deliver single day, enterprise ready deployments with HA multi master?

Do they include ACID and CAP tradeoffs similar to PostgreSQL?

Is their query language as rich and transferable as SQL?

Re: Pgagroal: High-performance connection pool for PostgreSQL

#37

Post by paulryanrogers »

endisneigh wrote: post #25
Scarbutt wrote: post #23

Earlier quoted context omitted.

This forum is heavily anti-other-dbs and pro-postgres so you won't get objective answers here. FWIW, at least three DBAs that know both well have told me that postgres is an operational nightmare compared to mysql.

In which ways?

Pg transactions seem more likely to deadlock compared to MySQL, IME. Its replication story was also worse for many years, though it's catching up.

Pg connection limits are also lower because of resource allocation. And lack of query hints can really hurt when Pg's planner goes sideways.

That said InnoDB's online DDL lagged Pg for quite a while. Generally feels like they're both getting better and more alike all the time.

Re: Pgagroal: High-performance connection pool for PostgreSQL

#39

Post by api »

paulryanrogers wrote: post #36
api wrote: post #18

Earlier quoted context omitted.

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 t…

What other DBs deliver single day, enterprise ready deployments with HA multi master? Do they include ACID and CAP tradeoffs similar to PostgreSQL? Is their query language as rich and transferable as SQL?

CockroachDB is pretty close. I wouldn't say I'd go fully into production in one day but the failover behavior is Raft based and easy to understand. I could prototype an enterprise cluster in a few hours without descending into the arcane bowels of secret pg_incantations and "I think this is set up right but not sure if it will actually recover" clunky shit.
Post reply on HN