34 ms·
An early look at Postgres 14: Performance and monitoring Improvements
- efxhoy 5y ago> 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!
- znep 5y agoI agree this is a great addition, but FWIW it isn't normal for ^C to not work in psql. Perhaps you are using some other client that doesn't support aborting queries properly, or have something on the network between you and the server behaving poorly and dropping connections?
- efxhoy 5y agoIt's psql through an ssh-tunnel to RDS on AWS, postgres 10.6 usually. But I've had the same experience on other versions and locally too. The problem usually isn't that it doesn't work ever, just that it can take a very long time, especially if the query is reading some crazy amount of data. I've always found pg_cancel_backend() to be almost instant though.
- salmo 5y agoSounds like it's ^C on a client that doesn't trap SIGTERM and cleanup. Probably something they're working on.
- edoceo 5y agoWow! Memory stats! Repeat query stats! The perfect database gets more perfecter! I'm looking forward to using PG for another 20 years.
- wiradikusuma 5y agoI'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"?
- agustif 5y agoAre you going to operate it our just rent out some cloud service? Postgres by itself doesn't have a great horitzontal scaling strategy as of now I think. You need Citus or somt like that on top, maybe your friend was referencing that?
- yannoninator 5y agoperhaps your DBA friend was operating PG themselves? nowadays postgres in the cloud does all of this for you.
- unnouinceput 5y agoYour DBA friend is stuck in 2000's. Let dinosaurs die and you go with PGSQL because is superior to MySQL on everything. And don't take my word for it, see for yourself here: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems https://en.wikipedia.org/wiki/Comparison_of_relational_datab... And MySQL is an Oracle product these days, go with MariaDB instead as this one is a MySQL fork made by the original papa of MySQL.
- tfigment 5y agoLacks first class temporal tables. Maybe not important to you and not on that list so do we dismiss that.
- CapriciousCptl 5y agoYou can fiddle with the autovacuum daemon[1,2] but we've never really had to. These days we just run AWS RDS when it counts or a dedicated VPS when it doesn't and things go fine-- [1,2] https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/13/routine-vacuuming.html https://www.postgresql.org/docs/current/planner-stats.html https://www.postgresql.org/docs/current/planner-stats.html The main issue we get is the 1 connection = 1 process issue although there are ways to mitigate that (namely pgbouncer).
- matthewbauer 5y agoPostgres 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.
- jjeaff 5y agoMySQL 8 is not that far behind in feature parity. And is ahead when it comes to scalability. So I don't see postgres as necessarily standing alone.
- paozac 5y agoMySQL's lack of DDL transactions is a serious shortcoming.
- ezekiel68 5y agoYou claim that MySQL 8 is ahead when it comes to scalability. What are the bases of this claim? When I see comparisons or entire systems that rely on a database (that is, not micro-benchmarks) such as the TechEmpower web framework benchmarks [0] , I notice that the 'Pg' results cluster near the top, with the "My" results showing up further down the rankings. I understand this isn't version 14 of the former versus version 8 of the latter. But it makes me wonder what the basis of your claims is. [0] https://www.techempower.com/benchmarks/ https://www.techempower.com/benchmarks/
- isbvhodnvemrwvn 5y agoAren't those run on a single node DB server? And the queries don't really seem realistic at all, e.g. single query test fetches 1 out of 10 000 rows, with no joins at all. Fortunes fetches 1 out of 10 rows. This seems extremely trivial.
- merb 5y ago
- Waterluvian 5y agoTangential 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?
- nerdbaggy 5y agoDjango has built in explain support which can guide you on the right track https://docs.djangoproject.com/en/3.2/ref/models/querysets/#django.db.models.query.QuerySet.explain https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...
- etxm 5y agoI’d start w ‘EXPLAIN query’, if you arent familiar with the output there, you can put it on PEV and get a visualization. https://tatiyants.com/pev/#/plans https://tatiyants.com/pev/#/plans
- snissn 5y agoagree!! this page is so helpful
- fabian2k 5y agoEXPLAIN ANALYZE in Postgres will give you the query plan, learning to understand that output is very useful to figure out why a query is slow. If the query isn’t slow, you can look into Django, but the DB is often a good first guess in these cases.
- purerandomness 5y agoI recommend the book "SQL Performance Explained" by Markus Winand: https://sql-performance-explained.com/ 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.
- gigatexal 5y agoFrom 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
- jabl 5y agoSeems zheap didn't make it this time either?
- cett 5y agoI would love to see it delivered
- pella 5y agoZHEAP Status: https://cybertec-postgresql.github.io/zheap/ https://cybertec-postgresql.github.io/zheap/ - 12-10-2020: "Most regression tests are passing, but write-speeds are still low." - wiki: https://wiki.postgresql.org/wiki/Zheap https://wiki.postgresql.org/wiki/Zheap
- deedubaya 5y agoIt would be nice to not need pgbouncer
- I_am_tiberius 5y agoIndeed! Postgres 14 improves scalability of concurrent connections but I doubt cloud db providers will adjust their max. connections limit.
- hmmokidk 5y agoAre we still going to need PgBouncer when there are a large number of connections?
- jpgvm 5y agoFor now yes. The idle connection changes help but it's still inefficient. I would like to see connection pooling functionality merged into core PG at some point. Eliminate the need for network hop/IPC and enable better back-pressure etc.
- rargulati 5y agoWhat'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?
- ksec 5y agoI think vitess has some long term goal to also support Postgre.
- osser 5y agoThere are no plans right now.If the Postgres community (or a Postgres user) would like to take this project up, the best way to proceed would be to do it as a fork of Vitess. Once the implementation has been proven in production, a “merge” project can be planned to bring the fork back into upstream.
- jpgvm 5y agoVitess for PostgreSQL will probably just be... Vitess. The concepts behind Vitess are sufficiently general to simply apply them to PostgreSQL now that PostgreSQL has logical replication. In some ways it can be even better due to things like replication slots being a good fit for these sorts of architectures. The work to port Vitess to PostgreSQL is quite substantial however. Here is a ticket tracking the required tasks at a high level: https://github.com/vitessio/vitess/issues/7084 https://github.com/vitessio/vitess/issues/7084
- yed 5y agoThat would be Citus: https://www.citusdata.com/ https://www.citusdata.com/
- threeseed 5y agoWhich is now owned by Microsoft so except to see enterprise support disappear. Instead you are likely to be forced to use a cloud hosted PostgreSQL instance in order to get HA/clustering.
- qaq 5y ago
- dragonwriter 5y agoLots 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.
- e1g 5y agoAnother 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/ https://erthalion.info/2021/03/03/subscripting/
- xfalcox 5y agoWow is this for real? That is such a big quality of life change! Happy to see it!
- megous 5y agoNot much different from some_jsonb#>>'{some,path}' and once you add the need to convert out of jsonb to text, you'll not be saving any characters either. At least for queries. For updates, it looks nice I guess.
- zdragnar 5y agoI think the difference is familiarity. It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much harder to remember unfamiliar syntaxes and grammars, and easier to confuse similar looking operations with each other.
- megous 5y agoIn that case this does not help. SELECT json['a']; will not return the value of the string in {"a":"ble"} (like it does in Javascript), but a JSON encoding of that string, so '"ble"'. You'll still not be able to do simple comparisons like `SELECT json_col['a'] = some_text_col;` Superficial familiarity, but it still behaves differently than you expect. Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it. So all you can do is `SELECT json_col['a'] = some_text_col::jsonb;` and hope for the best (that string encodings will match) or use the old syntax with ->> or #>>.
- bredren 5y agoIf 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-warehouse-episode-186/ https://www.dataengineeringpodcast.com/postgresql-data-wareh... Direct: (apple) https://podcasts.apple.com/us/podcast/data-engineering-podcast/id1193040557?i=1000521665801 https://podcasts.apple.com/us/podcast/data-engineering-podca...
- eikenberry 5y agoAny 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.
- rusbus 5y agoI found running a 6-node Patroni cluster on Kubernetes to be a surprisingly pain-free experience a couple of years ago
- tpetry 5y agoI have been looking at patroni for years. But i still do not feel compatible using it in a production environment. If something fails it will be really really hard to fix it, but i have the same feeling for almost all these complex kubernetes operator doing a lot of magic work to have a simple solution.
- threeseed 5y agoIf you want HA use AWS RDS, Azure Citus, GCP Cloud SQL. Otherwise use MySQL, Oracle, MongoDB, Cassandra etc if you want to run it on your own. Any other database that invested in a native and supported HA/clustering implementation.
- tluyben2 5y agoCockroachdb or Yugabyte work well for some cases you might use postgres for.
- edoceo 5y agoFrom the old days it's way better. Both Logical and streaming replication is only a few lines, few commands kind of thing. Logical for streaming to read only replicas and streaming for fail-over. My client-app still needs to know try-A then try-B (via DNS or config)
- lmarcos 5y agoAll I want is to be able to use Postgres in production without the need of pgbouncer.
- mixmastamyk 5y agoCare to elaborate? Having each tool handle its job sounds like a good strategy.
- pgaddict 5y agoIt's not clear to me if the OP want's to run without any connection pool (incl. a built-in one), or just without a separate one. In an ideal world PostgreSQL would handle infinite number of connections without a connection pool. Unlikely in practicem though. There are good practical reasons to actually limit the number of connections: (a) CPU efficiency (optimal number of active connections is 1-2x number of cores) (b) allows higher memory limits (c) lower risk of connection storms (d) ... probably more Some applications simply ignore this and expect rather high number of connections, with the assumption most of them will be idle. Sometimes the connections are opened/closed frequently, making it worse. Eliminating the need for a connection pool in those cases would probably require significant changes to the architecture, so that e.g. forking a process is not needed. But my guess is that's not going to happen. A more likely solution is having a built-in connection pool which is easier to configure / operate. Separate connection pools (like pgbouncer) are unlikely to go away, though, because being able to run them on a separate machine is a big advantage.
- matsemann 5y agoNever had the use for it or even heard of it, guess it depends on usage patterns? I've mostly worked with longlived java servers, and there having an internal db pool has been standard since forever, so no need for another layer.
- andrewstuart 5y agoIt 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 https://www.postgresql.org/docs/14/release-14.html
- petergeoghegan 5y agoCo-author of that feature here. Clearly it doesn't eliminate the possibility of wraparound failure entirely. Say for example you had a leaked replication slot that blocks cleanup by VACUUM for days or months. It'll also block freezing completely, and so a wraparound failure (where the system won't accept writes) becomes almost inevitable. This is a scenario where the failsafe mechanism won't make any difference at all, since it's just as inevitable (in the absence of DBA intervention). A more interesting question is how much of a reduction in risk there is if you make certain modest assumptions about the running system, such as assuming that VACUUM can freeze the tuples that need to be frozen to avert wraparound. Then it becomes a question of VACUUM keeping up with the ongoing consumption of XIDs by the system -- the ability of VACUUM to freeze tuples and advance the relfrozenxid for the "oldest" table before XID consumption makes the relfrozenxid dangerously far in the past. It's very hard to model that and make any generalizations, but I believe in practice that the failsafe makes a huge difference, because it stops VACUUM from performing further index vacuuming. In cases at real risk of wraparound failure, the risk tends to come from the variability in how long index vacuuming takes -- index vacuuming has a pretty non-linear cost, whereas all the other overheads are much more linear and therefore much more predictable. Having the ability to just drop those steps if and only if the situation visibly starts to get out of hand is therefore something I expect to be very useful in practice. Though it's hard to prove it. Long term, the way to fix this is to come up with a design that doesn't need to freeze at all. But that's much harder.
- arunitc 5y agoDelete 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.
- croh 5y agoHave you checked performance using different algorithms like hash-join, merge-join and nested-loop ?
- tpetry 5y agoCan you share the explain analyze output of the query?
- aidos 5y agoIt’s a little hard to parse that on mobile but it looks like you’re doing correlated subqueries against the dB schema for each row in the table you’re deleting from. As others have said, explain analyze will show you what’s going on. I’m fairly sure this query would be fixed by flipping and / or adding an index. 5k records is nothing to pg.
- davidrowley 5y agoIf I remember correctly, SQL Server will convert NOT IN to anti-join. PostgreSQL currently does not do that due to NOT IN being incompatible with anti-joins in regards to NULL values. There's room for improvement there by detecting if NULLs can exist or not, and converting if they can't. If you don't need the NOT IN weirdness around NULL values then I'd suggest you just use a NOT EXISTS. That'll allow something more efficient like a Hash Anti Join to be used during the DELETE. Something like: Delete From "APCRoleTableColumn" Where Not EXISTS (Select 1 From information_schema.columns SC INNER JOIN "APCRoleTable" RT ON SC.table_name = RT."TableName" Where RT."TableName" = "APCRoleTableColumn"."TableName" AND SC.column_name = "APCRoleTableColumn"."ColumnName" AND SC.table_schema = 'public'); Is that faster now?