PostgreSQL 16
postgresql.org
postgresql.org
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...
(and as noted in the other comment, whilst EDB certainly makes important contributions, they are one of many)
I used to be paid full time to mostly work on a Linux kernel subsystem. This discussion is silly.
This will be a nice quality of life addition!
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
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);
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.)
What good does EXPLAIN do if you're not running the same (parameterized) queries that your app does? Very cool.
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.
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?
From the docs: "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty"
That sounds like an anecdote from the time before SSDs.
> "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. "
If your application is told that the transactions were committed, but your DB actually hasn't, you got a problem, methinks.
Thing is DB work is all about tradeoffs, so you'll never be free of them.
As the parent explained, PostgreSQL actually lets you make a very good trade: you get full cached level performance but lose only a couple of transactions at most, instead of data corruption. If that's not ok for you, the leash can be tightened to lose nothing, but then your hardware had better keep up with your platform.
Yes, I stated "regular hard drives" :)
Note that the documentation on the config flag explicitly warns about not using it in production:
> Currently this feature reduces performance, and is intended for developer testing only.
Also note that very few things will actually do IO during commit - the only IO that I can think of are 1.) the WAL-logging of the commit (often small, a few 100 bytes at most), and 2.) replying to the COMMIT command (10s of bytes at most). It is quite unlikely that this will see much performance benefit from IO_DIRECT without further infrastructure inside PostgreSQL around io_uring and other async kernel IO apis.
From Linus himself[0]
"The thing that has always disturbed me about O_DIRECT is that the whole interface is just stupid, and was probably designed by a deranged monkey on some serious mind-controlling substances"
But back to my first question, I am still curious what your workload is that you feel will benefit from direct io :)
I use io-uring with O_DIRECT at work and the performance graphs of TLB pressure are beautiful.
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.
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).
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.
Are you able to partition any of your tables so that you can VACUUM FULL the partitions individually?
The data that will be "not removed" will just be used by new data.
Only real use case is "we've loaded way too many data, removed it, and want to recover that space because we will never need it", and that is not enough to matter, as usually database have its own filesystem and most filesystems can't be shrunk online so any shrinking needs downtime
Parent literally provided one: deleting a column.
Another is that it's very easy in PostgreSQL to bloat indexes. Load a bunch of data. Update (or delete) that data and now your index is bloated.
The only resolution is to REINDEX (or VACUUM FULL).
The nature of fragmentation means that you need to move a lot of data around to actually make that file smaller. In Postgres, that's typically done with VACUUM FULL. The problem of fragmentation is not unique to Postgres, it's a fundamental issue; but perhaps other systems are able to move the data around in a less disruptive way.
If you just delete a column, that creates a different type of fragmentation within the tuples themselves (e.g. you delete the middle column, and the tuple itself doesn't shrink, it just ignores that middle column). You are right that can be a problem. Postgres could be improved to rewrite tuples to eliminate the wasted space from deleted columns in the middle, which would probably be (computationally) worth it to do if it's already performing cleanup on the page.
Error message: The database cluster was initialized with CATALOG_VERSION_NO 202306141, but the server was compiled with CATALOG_VERSION_NO 202307071.
alter database mystuff refresh collation version;Can't wait to test them with some big data.
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.