back
84 comments
anytime a huge multi-decades-old FOSS project lands a milestone, I can't help but equate it to something like a moon landing.

So much (unpaid) work and thought goes into stewarding open software. Kudos to the whole team. Software infra is just as important as bridges and roads -- here's hoping we can fund it at least as well, for humanity's sake. [1]

[1]: https://www.fordfoundation.org/work/learning/research-report...

PostgreSQL is also one of the most impressive projects out there in terms of being community maintained, as opposed to many large FOSS projects which have some kind of corporate backing employing the majority of the core team.
Isn't the good part of the core team part of EnterpriseDB?
Isn't the work in large projects often done by those paid by their employers on "company time"? For instance Bruce Momjian by EDB.
Yes, and Andres (literally the “main hat” they wear at Microsoft is work on PostgreSQL, and Tom, etc. I don’t understand why pointing out what should be obvious gets people to lose their mind here and is such a controversy. PostgreSQL like Linux is a massively commercially sponsored project - that there are some volunteers doesn’t change that.

I used to be paid full time to mostly work on a Linux kernel subsystem. This discussion is silly.

Agreed - just think about the billions, possibly trillions of economic value (jobs, shareholder value, utility to society, etc) that a project like postgres or ruby on rails has created.
They significantly increased major versions frequency, transition from 9 to 10 took 7 years, and now they release major version every year.
They just changed the versioning scheme; it used to be that e.g. 9.3 -> 9.4 was a major version (i.e. can't be upgraded in-place). Starting with PG 10 major versions are now 10 -> 11, etc. I don't believe the major release cadence itself changed that much
16 contains paid work
Release artworks : https://wiki.postgresql.org/wiki/Artwork#16_.282023-09-14.29
woah til there is release art (did openbsd start this trend?)
Previous discussion from Beta 1 announcement: https://news.ycombinator.com/item?id=36070261 (154 points, 60 comments, 3 months ago)
> Add SQL/JSON constructors and identity functions

This will be a nice quality of life addition!

> SQL/JSON constructors

Like these?

> Adds SQL/JSON constructors, including JSON_ARRAY(), JSON_ARRAYAGG(), JSON_OBJECT(), and JSON_OBJECTAGG().

Not sure what SQL/JSON identity functions relate to

identity probably refers to the ’IS’:

SELECT js, js IS JSON OBJECT "object?", js IS JSON ARRAY "array?", js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?" FROM (VALUES ('[{"a":"1"}, {"b":"2","b":"3"}]')) foo(js);

> bidirectional logical replication

Just to make sure, this is what we used to call multi-master, right?

(This is not a "why did they change it" post. Do not make it into a "why did they change it" post.)

Yes
I am so glad psql got \bind.

What good does EXPLAIN do if you're not running the same (parameterized) queries that your app does? Very cool.

Explain for parameterized queries (which is indeed very important due to the way the planner works) used to work well with prepare+execute.
I can't wait for direct I/O (now behind debug_io_direct setting).
Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the DBAs and tell them to tune the DB. They'd say there's nothing more to do, we need faster disks. So he'd end up in front of me on the sysop team asking if we had any faster disks laying around (we didn't and buying more wasn't in the budget).

Since it was NFS, you could just use tcpdump and watch what DB2 was doing on the wire. It was happily poking away sending and receiving packets all 1K in size (the current configured DB block size) with peak read and and write speeds of about 11MB/s. Since the DBAs didn't want to change settings on a production DB, I set up a testing environment, begged them to play with the direct io and block size settings on this new instance and figure out the best performance. When I checked back days later, it was set up exactly the same, "we follow best practices, use 1K block size and force direct io".

I ended up creating a VM under the guise of "we need a data warehouse" with 1/4 the cpus and ram as the DB2 machines and installed postgresql 9.2. Did a minimum amount of tuning, mostly just turning off fsync for WAL writes, then spent a week filling it up with 5TB of data and 15 billion rows from the production DB. Ran one of our analytic queries that had grown to taking 30 hours on DB2, it ran in 6 hours. The packet sizes over NFS were 32-64MB in size and getting peak speeds of 180-220MB/s on the wire.

The 1k packets you saw probably correspond to the default block size being used for the DB, that is a vestige of using spinning disks. That you were using NFS or any kind of networked filesystem is what I'd say is a performance hostile environment. Did no one think of just not using NFS?
> Did a minimum amount of tuning, mostly just turning off fsync for WAL writes

That is not something I would suggest to people on production systems, as that would give you a good chance of data loss when the system halts. So, out of interest, were there any circumstances why turning off WAL fsync was considered a good choice in your situation?

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...
can anyone point to the COPY FROM improvements mentioned that can result in up to 300% performance improvements is it the line in the release notes about "ASCII string detection"?
These are the two relevant patches that I know of (there might be more):

1. https://github.com/postgres/postgres/commit/3838fa269c15706d...

2. https://github.com/postgres/postgres/commit/121d2d3d70ecdb21...

It causes much less CPU overhead on the receiving side of a copy when receiving big JSON blobs.

I think the 300% item is "Allow more efficient addition of heap and index pages". The source of the improvement is a number of related improvements around relation extension, see https://postgr.es/m/20221029025420.eplyow6k7tgu6he3@awork3.a...
Is native transparent encryption somewhere on the radar?
Anyone know more about the "vacuum" improvements?

To make my database fast, I often have to do a vacuum full on some key tables. Which is basically a freeze all access to the table, and copy byte by byte to a new physical file. So as your data size doubles, the vacuum full time doubles. Have a table that is so big I basically can't vacuum full it anymore (in an acceptable amount of downtime).

Maybe try pg_repack?
As other commenter mentioned, you can use pg_repack. We also had similar issue and we configured it to run every month and it works flawlessly.
Oh interesting. In my case I am on google hosted postgres, so I’ll need to double check it works there:
I’m not familiar with the VACUUM changes but the situation you’re describing suggests something is wrong with the table definition or database configuration.
How do you figure?

Postgres docs are quite clear. Table space is not reclaimed without a vacuum full. So delete a column in a big table? you are storing that data forever.

Why are you regularly doing vacuum full instead of just vacuuming more aggressively?
Do I still have to use pg_upgrade when I'm just upgrading from RC-1?
git diff REL_16_RC1..REL_16_0 doesn't show any changes that would require a pg_upgrade (at least from my read), so you should be able to upgrade without it.
I just tested, I had to use pg_upgrade.

Error message: The database cluster was initialized with CATALOG_VERSION_NO 202306141, but the server was compiled with CATALOG_VERSION_NO 202307071.

Congratulations to all PG community!
I'm really excited about the COPY FROM improvements!

Can't wait to test them with some big data.

This is great!

But I just installed the latest Debian with Postgres 15, haha. I don't even think I'm using any features past 11 (websearch_to_tsquery), so I'll need to research anything new that might be useful to me.

Our infra team decided to deploy v12 in the last year... But only for GitLab. We are still deploying with v9.6 for some other new projects.
9.6 for new projects is insane no?
Performance