PostgreSQL 16 Beta 1

[craigkerstiens]

For being "boring and stable" technology, Postgres 16 includes 200 features, which is on par with each of prior years releases. If you're looking for the full set of features it's available here - https://www.postgresql.org/docs/16/release-16.html.

Personal favorites on the list include:

- load_balance_hosts, which is an improvement to libpq so you can load balance across multiple Postgres instances.

- Logical replication on standbys

- pg_stat_io which is a new view that shows IO details.

[Fgehono]

Could you elaborate on the load balancing?

Is this a replacement for PG bouncer and similar?

[garyclarke27]

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

[tensor]

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.

[sophacles]

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.

[pawelduda]

I have one use case where this functionality would be huge in terms of performance, patiently waiting for it

[cryptonector]

You can use https://github.com/twosigma/postgresql-contrib/blob/master/m... and write triggers to update your views or mark them as needing asynchronous refreshes. It's not as automatic as what you'd like, but it's a lot better than having to keep waiting.

[pritambaral]

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

[seanhunter]

> - 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 
    limit 10

you need to put the alias 'a' to placate existing postgres even though it's never actually used, which never made any sense to me.

[MuffinFlavored]

Not a bad list!

https://commitfest.postgresql.org/19/1741/ (index skip scan/loose index scans) would be very welcomed... I think. Not sure how many people run into it in the wild.

It says "target version: 16" but "returned with feedback" and hasn't been bumped in 14 months. :( First opened in 2018.

https://wiki.postgresql.org/wiki/Loose_indexscan

[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...

[itsthecourier]

COPY from files up to 300% faster

Load Balancing from client libs

Support for CPU acceleration using SIMD for both x86 and ARM architectures, including optimizations for processing ASCII and JSON strings

new pg_stat_io view that provides information on I/O statistics

[tracker1]

Have to say, I find the SQL/JSON support in Postgres (jsonb) is probably the single biggest killer feature IMO. I can't believe MS (still) doesn't have an implementation (their SQL to JSON functions suck exponentially by comparison).

Nice to see the continued advancement and progression all around.

[cjblomqvist]

FYI: MS is releasing support for a native JSON data type in next release.

But yeah, obviously they're behind in that area!

[sureglymop]

Is MSSQL used often in production? And where are all the windows servers this would run on?

[h1fra]

Postgres getting good updates at slow but steady pace over many decades is really impressive.

[tuetuopay]

This is an exciting release to be sure. Very happy to see that the CPU improvements include ARM, diversity is good!

Given the current release pace, I'd love to have another upgrade path than `pg_dump | psql`. That would remove a great deal of friction in prod.

[hans_castorp]

> I'd love to have another upgrade path than `pg_dump | psql`

pg_upgrade

[jeltz]

There is pg_upgrade and logical replication. I have used both and they both work excellently. Logical replication can be a bit of a hassle to set up though, especially if the database is huge.

[jasfi]

I look forward to seeing some benchmarks. Especially to see the effect of the SIMD optimizations.

[LunaSea]

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

[phamilton]

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.

[rektide]

Bunch of interesting loosenings ups on logical replication. Allowing standbys to also have subscribers; great for fan out! Bunch of perf improvements (honestly a bunch of it is removing rather onerous limitations I didn't know about!) At the end there seems to be a developer flag for allowing seeming bidirectional replication too.

[klysm]

Glad to see the move off of autoconf to meson.

[jeltz]

I am personally very excited to see that work on direct IO has started. It is a huge undertaking so I do not expect any production ready thing any time soon but nice to see that some code landed.

[anarazel]

Yeah- we've been working on that for quite a while, and it'll take a few more years to finish it. I hope we can get some important parts into 17 though.

FWIW, there have been smaller prerequisites merged into 15 already, and 16 has a number of improvements that are part of that work. E.g. the more scalable relation extension (making COPY scale much better), and the related buffer mapping changes, come from the AIO effort.

Personally I think the feature is using asynchronous IO and direct IO support is part of that :)

[darksaints]

Meanwhile, PG15 is still in preview mode on Azure

[justinclift]

PG 15 has an EOL of Nov 2027, so you should be ok to stick with it for a while. ;)

[djbusby]

It can be. We still running PG11 and 13 in production. Not obligated to upgrade - especially when the software is rock solid.

[Fgehono]

Whenever you do a one day full of updates and fix small issues but also gain new features and performance, just remember there is someone who uses some super old db no one wanna touch it again and the migration of that old thing will take weeks!