[HN Gopher] PostgreSQL 16
___________________________________________________________________
PostgreSQL 16
Author : pella
Score : 477 points
Date : 2023-09-14 13:06 UTC (9 hours ago)
(HTM) web link (www.postgresql.org)
(TXT) w3m dump (www.postgresql.org)
| olavgg wrote:
| Do I still have to use pg_upgrade when I'm just upgrading from
| RC-1?
| jkatz05 wrote:
| 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.
| olavgg wrote:
| 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.
| yobert wrote:
| You might just need to run: alter
| database mystuff refresh collation version;
| paulddraper wrote:
| 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.
| pritambaral wrote:
| Previous discussion from Beta 1 announcement:
| https://news.ycombinator.com/item?id=36070261 (154 points, 60
| comments, 3 months ago)
| datadrivenangel wrote:
| > Add SQL/JSON constructors and identity functions
|
| This will be a nice quality of life addition!
| MuffinFlavored wrote:
| > 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
| zkomp wrote:
| 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);
| pella wrote:
| Release artworks :
| https://wiki.postgresql.org/wiki/Artwork#16_.282023-09-14.29
| whalesalad wrote:
| woah til there is release art (did openbsd start this trend?)
| jamesgresql wrote:
| I'm really excited about the COPY FROM improvements!
|
| Can't wait to test them with some big data.
| [deleted]
| McGlockenshire wrote:
| > 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.)
| mdaniel wrote:
| https://www.crunchydata.com/blog/active-active-postgres-16 may
| interest you, also, which showed up recently
| https://news.ycombinator.com/item?id=37510260
| paulddraper wrote:
| Yes
| pella wrote:
| Release Notes: https://www.postgresql.org/docs/16/release-16.html
| theandrewbailey wrote:
| 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.
| bonif wrote:
| Performance
| dzolob wrote:
| Is native transparent encryption somewhere on the radar?
| jfbaro wrote:
| Congratulations to all PG community!
| brianwawok wrote:
| 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).
| ellisv wrote:
| 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.
| brianwawok wrote:
| 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.
| ellisv wrote:
| If rewriting the table is part of a regular workflow, then
| database configuration can play a significant role in its
| performance. However I still contend that rewriting tables
| regularly is an anti pattern.
|
| Are you able to partition any of your tables so that you
| can VACUUM FULL the partitions individually?
| _bohm wrote:
| Not quite true. Regular VACUUM marks dead tuples as
| available for re-use, so the system can overwrite the dead
| tuples with fresh ones after a VACUUM. VACUUM FULL
| completely rewrites and repacks the table.
|
| https://www.postgresql.org/docs/current/sql-vacuum.html
| [deleted]
| jeff-davis wrote:
| Postgres has a free space map, which allows it to reuse the
| space of deleted tuples for new or updated tuples created
| in the same table. If no new/updated tuples are created in
| the table after the DELETE, you have fragmentation, which
| means the file remains large so the space can't be used for
| other tables (or other unrelated data residing in the same
| filesystem).
|
| 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.
| adql wrote:
| ...okay ? What's the use case where data shrinks ?
|
| 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
| paulddraper wrote:
| > What's the use case where data shrinks ?
|
| 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).
| efxhoy wrote:
| The nice thing about index bloat is REINDEX has a
| CONCURRENTLY option, no need to block writes.
| salojoo wrote:
| Have you tried pg_repack?
| jskrablin wrote:
| Maybe try pg_repack?
| anarazel wrote:
| Why are you regularly doing vacuum full instead of just
| vacuuming more aggressively?
| data_ders wrote:
| 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"?
| JelteF wrote:
| 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.
| anarazel wrote:
| 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...
| ptrwis wrote:
| I can't wait for direct I/O (now behind debug_io_direct setting).
| esaym wrote:
| 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.
| garenp wrote:
| 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?
| esaym wrote:
| This was before 2012, AWS did not exist. The company had to
| find rackspace in a data center. Which we couldn't. One of
| the funding customers "loaned" us a couple of slots in
| their on premise data center which fit only a bladecenter
| and single netapp. NFS had advantages, you could
| dynamically resize live mount points, etc. Plus as I
| mentioned, using postgresql did away with our performance
| issues.
| mattashii wrote:
| > 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?
| Tostino wrote:
| Re-read their post. It was a secondary system setup to just
| run these analytics which were loaded from the production
| system. No issues if the whole machine had to be rebuilt.
| esaym wrote:
| I probably meant to say "synchronous_commit", which is how
| data is written to disk from the WAL. If you want full data
| guarantees, with regular hard drives, you'd be looking at
| less than 200 transactions per second. You set
| synchronous_commit to off, and suddenly you can do 10k
| transactions per second. You can tune when the WAL gets
| flushed to disk based on time and/or size. So you can set
| the amount of recent data loss you are comfortable with.
|
| 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"
| ptrwis wrote:
| 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...
| mattashii wrote:
| The new debug_io_direct flag only triggers direct IO in
| very limited cases, and is only tangentially related to the
| AIO patchset discussed in that thread.
|
| 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.
| ptrwis wrote:
| I know, but it's still nice to see progress in this area.
| Even PG17 would probably be too early to expect this work
| to be finished.
| esaym wrote:
| Yes when direct io is brought up, it is usually followed
| with the "double buffering" argument. That is valid, but
| only if your disk speeds are well above 1,000MB/s. Outside
| of hardware like that, you are always going to be waiting
| on disk.
|
| 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"
|
| [0] https://lkml.org/lkml/2002/5/11/58
| ptrwis wrote:
| Have you read the entire thread you linked? People
| explained to Linus why direct IO is important to them.
| Besides, it's 20 years old and there were even no SSDs
| back then. The lack of direct IO in PG was one of (one
| of) the reasons why Uber moved to MySQL
| (https://www.uber.com/en-PL/blog/postgres-to-mysql-
| migration/, "The Buffer Pool" Section). With buffered IO
| you will likely store a lot of the same data in memory
| twice- once in DB's memory and then in page cache. Now
| you can just give the memory used by page cache directly
| to DB, because it knows better what and when it needs.
| data_ders wrote:
| 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...
| game_the0ry wrote:
| 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.
| brainzap wrote:
| 16 contains paid work
| ZiiS wrote:
| [flagged]
| pas wrote:
| schools either never worked or stopped working. after all,
| look, almost all the people who went to school don't give a
| fuck about underperforming underfunded school systems. (or
| healthcare or ... or if they care they are ignorant and
| clueless about what to do with the problem, and easily fell
| prey to political dogma of some group.)
|
| it's simply time to stop worrying about it.
| dijit wrote:
| The people underfunding our schools had their private
| schooling paid for by virtue of being unconscionably
| wealthy.
|
| The more you realise what has happened the more maddeningly
| upset you will get, so it's best not to think about it.
|
| Sufficed to say: you're wrong, additionally: dead wrong and
| it's not relevant for a topic about databases.
| zozbot234 wrote:
| School systems are not underfunded. They're underperforming
| _despite_ being overly funded. We 're throwing good money
| after bad.
| patmorgan23 wrote:
| Teachers are under funded in the US. In many states if
| your lucky teachers wages top out at the median income.
| And they often have little to no funding for class room
| decorations or much beyond very basic materials.
|
| If we don't pay our teachers decently how can we expect
| them to put in the effort to do the very difficult task
| of raising the next generation? (And yes teachers raise
| their students just as much as parents do)
| riku_iki wrote:
| They significantly increased major versions frequency,
| transition from 9 to 10 took 7 years, and now they release
| major version every year.
| clarkdave wrote:
| 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
| eestrada wrote:
| It seems like they fell more inline with Semantic
| Versioning when that format came in vogue. Semantic
| Versioning is what most devs expect now; it makes sense to
| communicate the version in a format that has a broadly
| understood meaning for devs.
| xslvrxslwt wrote:
| [flagged]
| dijit wrote:
| if they're getting more than $200 then it's already _way
| more_ money than I think.
|
| It's true that there is some money (after all, there is
| pgcon); and it's true that some people are paid to work on
| postgresql.
|
| However: it's additionally true that there are many
| volunteers, and that it's free for us to use, modify, hack on
| and so forth.
|
| coordinating that effort must be absolutely herculean.
| epcoa wrote:
| > It's true that there is some money
|
| This is such a misleading understatement. There is a ton of
| money. Many of the core Postgres contributors have fully
| remunerated employment where much of their time is
| dedicated to postgresql development. This is fully deserved
| and appropriate - but Tom Lane, et al are not working on
| this for free (that they still might if they had to do is
| another thing) but these are all highly compensated
| contributors. The total payroll spend on postgresql is
| distributed and decentralized but it is clearly in the
| multimillions.
|
| People should not be mislead about true costs regardless
| how they're meted out.
| simonw wrote:
| 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.
| dekobon wrote:
| Isn't the good part of the core team part of EnterpriseDB?
| lfittl wrote:
| You can see a break-down of the core team and major
| contributors here, as well as their current company
| affiliation:
| https://www.postgresql.org/community/contributors/
|
| (and as noted in the other comment, whilst EDB certainly
| makes important contributions, they are one of many)
| h0l0cube wrote:
| TIL Julian Assange was a contributor to PostgreSQL
| brand wrote:
| 3 of 7 work at EDB, and the core team doesn't drive the
| project roadmap. And EDB hackers fail to get patches in all
| the time, just like everyone else :)
| bdcravens wrote:
| Isn't the work in large projects often done by those paid by
| their employers on "company time"? For instance Bruce Momjian
| by EDB.
___________________________________________________________________
(page generated 2023-09-14 23:02 UTC)