from Hacker News

An early look at Postgres 14: Performance and monitoring Improvements

by bananaoomarang on 5/22/21, 3:47 PM with 245 comments

  • by e1g on 5/22/21, 7:40 PM

    Another exciting feature in PG14 is the new JSONB syntax[0], which makes it easy to update deep JSON values -

      UPDATE table SET some_jsonb_column['person']['bio']['age'] = '99';
    [0] https://erthalion.info/2021/03/03/subscripting/

  • by matthewbauer on 5/22/21, 5:02 PM

    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.

    Do any other entrenched software projects come to mind? The only thing comparable I can think of are Git and Linux.

  • by eikenberry on 5/22/21, 7:59 PM

    Any progress on high availability deployments yet? Or does it still rely on problematic, 3rd party tools?

    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.

  • by gigatexal on 5/22/21, 5:27 PM

    From the article:

    And 200+ other improvements in the Postgres 14 release!

    These are just some of the many improvements in the new Postgres release. You can find more on what's new in the release notes, such as:

        The new predefined roles pg_read_all_data/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
    the killing of queries when the client disconnects is really nice imo -- the others are great too

  • by andrewstuart on 5/22/21, 8:46 PM

    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?

    I see no mention of addressing transaction id wraparound, but these are in the release notes:

    Cause vacuum operations to be aggressive if the table is near xid or multixact wraparound (Masahiko Sawada, Peter Geoghegan)

    This is controlled by vacuum_failsafe_age and vacuum_multixact_failsafe_age.

    Increase warning time and hard limit before transaction id and multi-transaction wraparound (Noah Misch)

    This should reduce the possibility of failures that occur without having issued warnings about wraparound.

    https://www.postgresql.org/docs/14/release-14.html

  • by efxhoy on 5/22/21, 6:04 PM

    > Automatic cancellation of long-running queries if the client disconnects

    Sweet! I often screw up a query and need to cancel it with

      pg_cancel_backend(pid)
    because Ctrl-C rarely works. With this I can just ragequit and reconnect. Sweet!

  • by bredren on 5/22/21, 7:49 PM

    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:

    Episode website: https://www.dataengineeringpodcast.com/postgresql-data-wareh...

    Direct: (apple) https://podcasts.apple.com/us/podcast/data-engineering-podca...

  • by edoceo on 5/22/21, 4:42 PM

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

  • by wiradikusuma on 5/22/21, 4:59 PM

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

  • by Waterluvian on 5/22/21, 5:26 PM

    Tangential to this topic:

    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?

  • by rargulati on 5/22/21, 6:58 PM

    What's going to be the vitess of Postgres? Seems to be the "last" missing piece? Or is that not a focus and fit for PG?

  • by arunitc on 5/23/21, 6:19 AM

    Delete From "APCRoleTableColumn" Where "ColumnName" Not In (Select SC.column_name From (SELECT SC.column_name, SC.table_name FROM information_schema.columns SC where SC.table_schema = 'public') SC, "APCRoleTable" RT Where SC.table_name = RT."TableName" and RT."TableName" = "APCRoleTableColumn"."TableName");

    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

    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.

  • by lmarcos on 5/22/21, 8:19 PM

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

  • by hmmokidk on 5/22/21, 6:44 PM

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

  • by deedubaya on 5/22/21, 5:54 PM

    It would be nice to not need pgbouncer

  • by dragonwriter on 5/22/21, 7:22 PM

    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.

  • by jabl on 5/22/21, 5:54 PM

    Seems zheap didn't make it this time either?