An early look at Postgres 14: Performance and monitoring Improvements

Another exciting feature in PG14 is the new JSONB syntax[0], which makes it easy to update deep JSON values -<p><pre><code> UPDATE table SET some_jsonb_column[&#x27;person&#x27;][&#x27;bio&#x27;][&#x27;age&#x27;] = &#x27;99&#x27;; </code></pre> [0] <a href="https:&#x2F;&#x2F;erthalion.info&#x2F;2021&#x2F;03&#x2F;03&#x2F;subscripting&#x2F;" rel="nofollow">https:&#x2F;&#x2F;erthalion.info&#x2F;2021&#x2F;03&#x2F;03&#x2F;subscripting&#x2F;</a>

Postgres is one of those pieces of software that’s so much better than anything else, it’s really incredible. I wonder if it’s even possible for competitors to catch up at this point - there’s not a lot of room for improvement in architecture of relational databases any more. I’m starting to think that Postgres is going to be with us for decades maybe even centuries.<p>Do any other entrenched software projects come to mind? The only thing comparable I can think of are Git and Linux.

Any progress on high availability deployments yet? Or does it still rely on problematic, 3rd party tools?<p>Last time I was responsible for setting up a HA Postgres cluster it was a garbage fire, but that was nearly 10 years ago now. I ask every so often to see if it has improved and each time, so far, the answer has been no.

From the article:<p>And 200+ other improvements in the Postgres 14 release!<p>These are just some of the many improvements in the new Postgres release. You can find more on what&#x27;s new in the release notes, such as:<p><pre><code> The new predefined roles pg_read_all_data&#x2F;pg_write_all_data give global read or write access Automatic cancellation of long-running queries if the client disconnects Vacuum now skips index vacuuming when the number of removable index entries is insignificant Per-index information is now included in autovacuum logging output Partitions can now be detached in a non-blocking manner with ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY </code></pre> the killing of queries when the client disconnects is really nice imo -- the others are great too

It would be nice to hear how much of problem XID wraparound is in Postgres 14 - do the fixes below address it entirely or just make it less of a problem?<p>I see no mention of addressing transaction id wraparound, but these are in the release notes:<p>Cause vacuum operations to be aggressive if the table is near xid or multixact wraparound (Masahiko Sawada, Peter Geoghegan)<p>This is controlled by vacuum_failsafe_age and vacuum_multixact_failsafe_age.<p>Increase warning time and hard limit before transaction id and multi-transaction wraparound (Noah Misch)<p>This should reduce the possibility of failures that occur without having issued warnings about wraparound.<p><a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;14&#x2F;release-14.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;14&#x2F;release-14.html</a>

&gt; Automatic cancellation of long-running queries if the client disconnects<p>Sweet! I often screw up a query and need to cancel it with<p><pre><code> pg_cancel_backend(pid) </code></pre> because Ctrl-C rarely works. With this I can just ragequit and reconnect. Sweet!

If you’re interested in recent enthusiastic (nearly effusive) discussion of Postgres and more specifically it’s potential as a basis for a data warehouse, you might enjoy this episode of Data Engineering Podcast with Thomas Richter and Joshua Drake:<p>Episode website: <a href="https:&#x2F;&#x2F;www.dataengineeringpodcast.com&#x2F;postgresql-data-warehouse-episode-186&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.dataengineeringpodcast.com&#x2F;postgresql-data-wareh...</a><p>Direct: (apple) <a href="https:&#x2F;&#x2F;podcasts.apple.com&#x2F;us&#x2F;podcast&#x2F;data-engineering-podcast&#x2F;id1193040557?i=1000521665801" rel="nofollow">https:&#x2F;&#x2F;podcasts.apple.com&#x2F;us&#x2F;podcast&#x2F;data-engineering-podca...</a>

Wow! Memory stats! Repeat query stats! The perfect database gets more perfecter! I&#x27;m looking forward to using PG for another 20 years.

I&#x27;m thinking of using Postgres for a project, but a DBA friend told me operationally it&#x27;s more challenging than MySQL. Unfortunately, he can&#x27;t elaborate. Does anyone have real work experience? Or is it based on outdated &quot;PG must manually vacuum frequently&quot;?

Tangential to this topic:<p>If I have a Django + PG query that takes 1 second and I want to deeply inspect the breakdown of that entire second, where might I begin reading to learn what tools to use and how?

What&#x27;s going to be the vitess of Postgres? Seems to be the &quot;last&quot; missing piece? Or is that not a focus and fit for PG?

Delete From &quot;APCRoleTableColumn&quot; Where &quot;ColumnName&quot; Not In (Select SC.column_name From (SELECT SC.column_name, SC.table_name FROM information_schema.columns SC where SC.table_schema = &#x27;public&#x27;) SC, &quot;APCRoleTable&quot; RT Where SC.table_name = RT.&quot;TableName&quot; and RT.&quot;TableName&quot; = &quot;APCRoleTableColumn&quot;.&quot;TableName&quot;);<p>I know this is not an optimized SQL. But this takes about 5 seconds in Postgre while the same command runs in milliseconds in MSSQL Server. The APCRoleTableColumn has only about 5000 records. The above query is to delete all columns not present in the schema from the APCRoleTableColumn table<p>I used to be a heavy MSSQL user. I do love Postgre and have switched over to using it in all my projects and am not looking back. I wish it was as performant as MSSQL. This is just one example. I can list a number of others too.

All I want is to be able to use Postgres in production without the need of pgbouncer.

Are we still going to need PgBouncer when there are a large number of connections?

It would be nice to not need pgbouncer

Lots of good ops-y stuff, and, with my dev hat on, multirange types are just a whole layer of awesome on top of the awesome that range types already were.

Seems zheap didn&#x27;t make it this time either?