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

PostgreSQL 16 Beta 1

postgresql.org

Post reply on HN
Print view View original

31–40 of 62 posts

Re: PostgreSQL 16 Beta 1

#31

Post by dalyons »

Nextgrid wrote: post #22
dalyons wrote: post #18

Earlier quoted context omitted.

IMO the biggest reason folks use pgbouncer is not for load balancing (which it can do, -ish) but instead for connection pooling. Postgres connections are expensive for the db server (one process per connection not one thread) so if you have say thousands of web application pods you need to use pgbouncer or similar as a proxy to multiplex those thousands of connections down onto a more manageable number (~200). So no,…

Out of curiosity, if the problem of connections being expensive is solvable by PGBouncer-style connection multiplexing, why doesn't Postgres just do that by itself?

I believe there has been talk and perhaps progress towards building it into PG, but i cant find the dev thread right now, and i dont know what the status of that is.

interestingly enough this is what Oracle does AFAIK. They are also process-per-conn & have an optional sidecar proxy thingy that you can run on your oracle host to do the pooling. I would rather it be built more tightly into the rdbms but thats not a terrible solution.

Re: PostgreSQL 16 Beta 1

#32

Post by dalyons »

dingledork69 wrote: post #29
Nextgrid wrote: post #22

Earlier quoted context omitted.

Out of curiosity, if the problem of connections being expensive is solvable by PGBouncer-style connection multiplexing, why doesn't Postgres just do that by itself?

Because pgbouncer's transaction-based pooling, which is what the previous poster was referring to, breaks a few postgres features. This is fine for most applications, but not all. See the table on https://www.pgbouncer.org/features.html

yeah good callout. I'd would be totally fine with a server mode, or connection option, that opts you into pooling but then disables the incompatible features. You choose pooled mode if you need it, and the tradeoffs are documented.

Re: PostgreSQL 16 Beta 1

#33

Post by simonw »

"parallel execution of the string_agg and array_agg aggregate functions" caught my eye - that feels like one of those minor improvements that might enable some pretty interesting new applications.

array_agg is particularly interesting, because it lets you implement patterns like this: https://til.simonwillison.net/sqlite/related-rows-single-que...

Re: PostgreSQL 16 Beta 1

#34

Post by simonw »

sophacles wrote: post #14
garyclarke27 wrote: post #5

Postgresql is amazing but I must say the last few updates have been a bit disappointing. Still waiting for automatic incremental updates for materialized views - been worked on for several years but still not released! https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://github.com/sraoss/pg_ivm

On the one hand, I totally understand. On the other, it's pretty confidence-inspiring that they don't put stuff in until they're sure it's ready.

Yeah, that's one of my favourite characteristics of PostgreSQL: if a feature makes it into a release, you can be VERY confident that it works well even across weird corner-cases.

Re: PostgreSQL 16 Beta 1

#35

Post by anarazel »

Nextgrid wrote: post #22
dalyons wrote: post #18

Earlier quoted context omitted.

IMO the biggest reason folks use pgbouncer is not for load balancing (which it can do, -ish) but instead for connection pooling. Postgres connections are expensive for the db server (one process per connection not one thread) so if you have say thousands of web application pods you need to use pgbouncer or similar as a proxy to multiplex those thousands of connections down onto a more manageable number (~200). So no,…

Out of curiosity, if the problem of connections being expensive is solvable by PGBouncer-style connection multiplexing, why doesn't Postgres just do that by itself?

If it were easy to do well in-core, I think we'd do it immediately. Unfortunately the interesting pooling modes in pgbouncer also break a few things - which would likely not be acceptable in core postgres. Avoiding such breakage requires non-trivial architectural adjustments.

Historically connection state and "process state" have been tightly coupled, for good server-side pooling they have to be divorced. While good pooling is doable with the current process model (passing the client file descriptor between processes using SCM_RIGHTS), it's much harder with processes than with threads - this is one of the reasons I think we will eventually need to migrate to threads.

Eventually I want to get to a point where we have a limited number of "query execution workers" that handle query execution, utilized by a much larger number of client connections (which do not have dedicated threads each). Obviously it's a long way to go to that. Ah, the fun working on an complicated application with a ~35 year history.

There also are use cases for pgbouncer that cannot be addressed on the server-side - one important one is to run pgbouncer on "application servers", to reduce the TCP+TLS connection establishment overhead and to share connections between application processes / threads. That can yield very substantial performance gains - completely independent of server side pooling support.

Re: PostgreSQL 16 Beta 1

#36

Post by danmcs »

Nextgrid wrote: post #22
dalyons wrote: post #18

Earlier quoted context omitted.

IMO the biggest reason folks use pgbouncer is not for load balancing (which it can do, -ish) but instead for connection pooling. Postgres connections are expensive for the db server (one process per connection not one thread) so if you have say thousands of web application pods you need to use pgbouncer or similar as a proxy to multiplex those thousands of connections down onto a more manageable number (~200). So no,…

Out of curiosity, if the problem of connections being expensive is solvable by PGBouncer-style connection multiplexing, why doesn't Postgres just do that by itself?

There were changes made to idle sessions in postgres 14.0 that were supposed to reduce the resource usage of open but idle connections.

Crunchydata mentioned it on their blog a while back (https://www.crunchydata.com/blog/five-tips-for-a-healthier-p...) and the pg 14 release notes mention a few changes to idle sessions (https://www.postgresql.org/docs/release/14.0/)

I don't know if they were sufficient that pgbouncer is no longer necessary, haven't had a need to try it.

Re: PostgreSQL 16 Beta 1

#37

Post by samwillis »

tensor wrote: post #13
garyclarke27 wrote: post #5

Postgresql is amazing but I must say the last few updates have been a bit disappointing. Still waiting for automatic incremental updates for materialized views - been worked on for several years but still not released! https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... https://github.com/sraoss/pg_ivm

To be fair view update is a very hard problem in computer science. It's not as though it's just a lack of time to implement it.

https://materialize.com/ are doing interesting things with this - although not inside Postgres (they use a few bit of Postgres as components).

Re: PostgreSQL 16 Beta 1

#38

Post by paulddraper »

seanhunter wrote: post #30
pritambaral wrote: post #21

My list of unexpected but very welcome changes: - pg_hba.conf and pg_ident.conf can include other files - Logical replication apply can use non-PK btree indexes - Integer literals in non-decimal bases - Underscores in numeric literals - Subqueries in the FROM clause can omit aliases - Addition and subtraction of timestamptz values - pg_upgrade can override new cluster's locale and encoding

> - Subqueries in the FROM clause can omit aliases This is great. It never made any sense to me that this was required. For people who are unaware, say you want to understand a table a natural way of doing it might be select * from the_table order by some_metric desc limit 10 so you'd think you can do the same for queries like select * from ( select blah blah blah the rest of the query ) a order by some_metric desc l…

It never made sense.

And FYI this comes from ANSI SQL.

Re: PostgreSQL 16 Beta 1

#39

Post by tracker1 »

dalyons wrote: post #18
Fgehono wrote: post #10

Earlier quoted context omitted.

Could you elaborate on the load balancing? Is this a replacement for PG bouncer and similar?

IMO the biggest reason folks use pgbouncer is not for load balancing (which it can do, -ish) but instead for connection pooling. Postgres connections are expensive for the db server (one process per connection not one thread) so if you have say thousands of web application pods you need to use pgbouncer or similar as a proxy to multiplex those thousands of connections down onto a more manageable number (~200). So no,…

That's pretty much been my take as well. Been looking at CockroachLabs (CockroachDB hosted cloud), and been kind of hard to get any detail if pgbouncer is beneficial for this, since I'm planning to connect via node_compat via Cloudflare Workers, and have kind of decided to punt the issue and connect directly for now in development, and closer to prod release do some tests with/without pgbouncer at each cloud location for the larger prod cluster instances for release.

Not the same service(s) as PG even if it's the same protocol, so I know it's really beneficial for connection queueing WRT my scenario for PG, but no idea on the CDB side.

Re: PostgreSQL 16 Beta 1

#40

Post by LunaSea »

phamilton wrote: post #17
LunaSea wrote: post #11

Can't wait to have it available in RDS in 2030.

AWS has gotten much better in recent years. Pg15 is available on Aurora as of last month. That's about 6-7 months after it was released, a significant improvement over years past.

Yup, but the lack of access to beta versions also means that we will basically get access to access to PostgreSQL 16 in RDS by next year around this time.
Post reply on HN