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.
An early look at Postgres 14: Performance and monitoring Improvements
71–80 of 254 posts
Re: An early look at Postgres 14: Performance and monitoring Improvements
#72Postgres 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…
Postgres is good, even great, but this is hyperbole. Postgres has its downsides, autovacuum being one of them.
Re: An early look at Postgres 14: Performance and monitoring Improvements
#73Earlier quoted context omitted.
One thing is that Postgres doesn’t let you just upgrade to a new major version, as it doesn’t update the format of the on-disk binary database files; you must replicate from an existing node or dump/restore. MySQL upgrades the previous version when a new version is installed (which can cause problems, but is certainly “easier”).
Pg_upgrade [0] is an official part of postgres and does the binary inplace upgrade for you. You should obviously test before running in production, but it has worked perfectly for us when upgrading a 10+TB cluster from pg11 to pg13 [0] https://www.postgresql.org/docs/current/pgupgrade.html
Not totally ideal if you're using containers as it requires the binaries of the old and new version, unless I'm missing something.
Re: An early look at Postgres 14: Performance and monitoring Improvements
#74Postgres 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…
I think anyone who has worked a lot with MSSQL would disagree with Postgres being "so much better". It's only really in the last few years that postgres has pulled ahead, MSSQL was lot more feature rich and performant for a decade.
MSSQL ? As in Microsoft SQL Server? I have heard this argument a lot and all the comparisons I have seen are specific benchmarks on specialized hardware. My own personal experience wasn’t anything like the benchmarks
Re: An early look at Postgres 14: Performance and monitoring Improvements
#75Postgres 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…
> Do any other entrenched software projects come to mind?
Elasticsearch is underrated here, IMO. Yes, there are alternatives for simple fulltext search. But there’s a lot more it can do (adhoc aggregations incorporating complex fulltext searches, with custom scripted components; geospatial; index lifecycle management) and if you’re using those features, there’s nothing else comparable.
It’s pretty stable, too, once you’ve got the cluster configured. We don’t have outages due to problems with Elasticsearch.
Re: An early look at Postgres 14: Performance and monitoring Improvements
#76Earlier quoted context omitted.
I think the point is that git isn't "so much better" than mercurial, while pgsql has had a lead on mysql for quite some time on a lot of technical measurements.
Postgresql does not have real, maintained with each change, clustered index. That itself makes it worse for many workloads than MySQL
"Is table a heap with indexes on the side or is table a tree with other indexes on the side (i.e. 'clustered index')" is a more complicated discussion.
The former makes it possible to have MVCC (and thus gives you snapshot isolation and serializability) and makes secondary indexes perform faster, at the cost of vacuum or Oracle-style redo/undo/rollback segments with associated "Snapshot too old" issues.
The latter pretty much forces use of locking even for read so queries block each other (but don't require vacuum or something), makes clustering key selective queries perform faster than secondary index ones and makes you think really hard about the clustering key.
It's not really a feature you would have, but a complicated design tradeoff.
Re: An early look at Postgres 14: Performance and monitoring Improvements
#77Earlier quoted context omitted.
I think anyone who has worked a lot with MSSQL would disagree with Postgres being "so much better". It's only really in the last few years that postgres has pulled ahead, MSSQL was lot more feature rich and performant for a decade.
MSSQL ? As in Microsoft SQL Server? I have heard this argument a lot and all the comparisons I have seen are specific benchmarks on specialized hardware. My own personal experience wasn’t anything like the benchmarks
By "few years" he has to mean 10 to 15 years. ;)
Re: An early look at Postgres 14: Performance and monitoring Improvements
#78Tangential 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?
I recommend the book "SQL Performance Explained" by Markus Winand: https://sql-performance-explained.com/ It covers all major databases and is a good start to dive into database interna and how to interpret output from query analyzers. Other than that, I highly recommend joining the mailing list and IRC (#postgresql on libera.chat). Lots of valuable tricks being shared there by people with decades of experience.
Did freenode get renamed?
Re: An early look at Postgres 14: Performance and monitoring Improvements
#79What'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?
That would be Citus: https://www.citusdata.com/
Re: An early look at Postgres 14: Performance and monitoring Improvements
#80Another 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';