9 ms·
PostgreSQL 16
- jfbaro 3y agoCongratulations to all PG community!
- pella 3y agoRelease Notes: https://www.postgresql.org/docs/16/release-16.html https://www.postgresql.org/docs/16/release-16.html
- pritambaral 3y agoPrevious discussion from Beta 1 announcement: https://news.ycombinator.com/item?id=36070261 https://news.ycombinator.com/item?id=36070261 (154 points, 60 comments, 3 months ago)
- olavgg 3y agoDo I still have to use pg_upgrade when I'm just upgrading from RC-1?
- jkatz05 3y agogit 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.
- deleted 3y ago[deleted]
- data_ders 3y agocan 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 3y agoThese are the two relevant patches that I know of (there might be more): 1. https://github.com/postgres/postgres/commit/3838fa269c15706df2b85ce2d6af8aacd5611655 https://github.com/postgres/postgres/commit/3838fa269c15706d... 2. https://github.com/postgres/postgres/commit/121d2d3d70ecdb2113b340c5f3b99a61341291af 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 3y agoI 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.anarazel.de https://postgr.es/m/20221029025420.eplyow6k7tgu6he3@awork3.a...
- data_ders 3y agoanytime 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-reports/roads-and-bridges-the-unseen-labor-behind-our-digital-infrastructure/ https://www.fordfoundation.org/work/learning/research-report...
- ZiiS 3y ago[flagged]
- pas 3y agoschools 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.
- zozbot234 3y agoSchool systems are not underfunded. They're underperforming despite being overly funded. We're throwing good money after bad.
- patmorgan23 3y agoTeachers 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)
- 3y ago
- datadrivenangel 3y ago> Add SQL/JSON constructors and identity functions This will be a nice quality of life addition!
- MuffinFlavored 3y ago> 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 3y agoidentity 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);
- jamesgresql 3y agoI'm really excited about the COPY FROM improvements! Can't wait to test them with some big data.
- pella 3y agoRelease artworks : https://wiki.postgresql.org/wiki/Artwork#16_.282023-09-14.29 https://wiki.postgresql.org/wiki/Artwork#16_.282023-09-14.29
- whalesalad 3y agowoah til there is release art (did openbsd start this trend?)
- brianwawok 3y agoAnyone 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 3y agoI’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 3y agoHow 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.
- salojoo 3y agoHave you tried pg_repack?
- brianwawok 3y agoNo but someone else suggested it. If it works on google cloud I might be in business.
- ellisv 3y agoIf 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?
- 3y ago
- theandrewbailey 3y agoThis 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 3y agoPerformance
- CameronNemo 3y agoOur 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.
- klysm 3y ago9.6 for new projects is insane no?
- CameronNemo 3y agoInertia is a helluva drug.
- McGlockenshire 3y ago> 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.)
- paulddraper 3y agoYes
- mdaniel 3y agohttps://www.crunchydata.com/blog/active-active-postgres-16 https://www.crunchydata.com/blog/active-active-postgres-16 may interest you, also, which showed up recently https://news.ycombinator.com/item?id=37510260 https://news.ycombinator.com/item?id=37510260
- paulddraper 3y agoI 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.
- literalAardvark 3y agoExplain for parameterized queries (which is indeed very important due to the way the planner works) used to work well with prepare+execute.
- ptrwis 3y agoI can't wait for direct I/O (now behind debug_io_direct setting).
- esaym 3y agoCurious 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.
- mattashii 3y ago> 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?
- dzolob 3y agoIs native transparent encryption somewhere on the radar?