An early look at Postgres 14: Performance and monitoring Improvements • Hacker News Board

Re: An early look at Postgres 14: Performance and monitoring Improvements

#151

Post by andrewstuart » Sun, May 23, 2021, 12:43 AM UTC

petergeoghegan wrote: post #119

andrewstuart wrote: post #111

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 v…

Co-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 al…

Very interesting thanks for the update - how great is the Internet to hear directly from the developer!

It's a pity this wasn't listed in the announcement as I think alot of people are interested in this issue.

>> Long term, the way to fix this is to come up with a design that doesn't need to freeze at all.

Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?

Re: An early look at Postgres 14: Performance and monitoring Improvements

#152

Post by jasonwatkinspdx » Sun, May 23, 2021, 12:44 AM UTC

exceptione wrote: post #121

jasonwatkinspdx wrote: post #96

Earlier quoted context omitted.

There's a ton of room for improvement in the architecture of relational databases. This isn't a dig against Postgres, or ignoring how difficult it will be to get a new system to the same level of maturity. But databases designed natively for cloud/clustering, SSDs, (pmem soon perhaps), etc are quite a bit different. There's enormous simplifications and performance gains possible. There's been a lot of exciting work i…

You must be kidding me with the CosmosDB mention. It doesn't even have foreign key constraints. I have to work with it and I have never seen such a feature-poor dbms before.

I'm talking about the general category of everything built atop BW-Tree and the Deuteronomy architecture. Murat Demirbas's blog has nice summaries of the papers. CosmosDB is a brand that encompasses more than one database engine, but I used that term as most people aren't familiar with the literature on the topic.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#153

Post by salmo » Sun, May 23, 2021, 12:46 AM UTC

znep wrote: post #84

efxhoy wrote: post #39

> 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!

I 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?

Sounds like it's ^C on a client that doesn't trap SIGTERM and cleanup. Probably something they're working on.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#154

Post by astrange » Sun, May 23, 2021, 12:50 AM UTC

yakubin wrote: post #24

Supermancho wrote: post #13

Earlier quoted context omitted.

> Java for enterprise server software. Big corporations are horribly inefficient and Enterprise Software necessarily so from that...if you're saying Java is terrible by nature of it being the goto for enterprise, then that makes sense. It took 20 years for it to swap places with COBOL and I expect it will be something else in 20 more.

I don't work with Java, but I can think of a few advantages off the top of my head: - appreciation of backwards-compatibility (here it wins with Python); - great debuggers and performance tools (e.g. Java Flight Recorder or Eclipse Memory Analyzer); - easy deployment - you can just give someone a fat JAR (here it wins with all scripting languages, so Python, Ruby, PHP, or any other flavour of the month); - industry-g…

It wouldn't need as much research into efficient GCs if it was possible to write efficient programs in it. e.g. everything has a lock word, there's no value types or fixed length arrays, you have to allocate boxed integers.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#155

Post by Apaec » Sun, May 23, 2021, 12:54 AM UTC

conradfr wrote: post #143

pgaddict wrote: post #135

Earlier quoted context omitted.

That's hardly a PostgreSQL issue. If your container tech does not allow installing both old and new version of the binaries, it's a silly container tech.

Well I use the official Postgresql docker image ;) Also it looks like this is not new https://github.com/docker-library/postgres/issues/37

Note that the "official" PostgreSQL docker image is maintained by Docker Inc - not by PostgreSQL contributors/members.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#156

Post by salmo » Sun, May 23, 2021, 12:54 AM UTC

conradfr wrote: post #143

pgaddict wrote: post #135

Earlier quoted context omitted.

That's hardly a PostgreSQL issue. If your container tech does not allow installing both old and new version of the binaries, it's a silly container tech.

Well I use the official Postgresql docker image ;) Also it looks like this is not new https://github.com/docker-library/postgres/issues/37

Containerized DBs are great for dev work, toy projects, etc.

Notsomuch for production. I know... folks do it. But I wouldn't run anything on it that I wouldn't do w/ sqlite.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#157

Post by latch » Sun, May 23, 2021, 12:56 AM UTC

edoceo wrote: post #145

eikenberry wrote: post #89

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.

From 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)

But there's so much more to it than this, e.g. upgrading, failing over, point in time recovery, monitoring.

I manage both a cockroachdb cluster and a few PG setups. Out postgres' have streaming replication to a standby with barman running on the standby. They are night and day.

Sure 2021 PG is way better than 2010 PG. But relative to available options, it's much worse.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#158

Post by blondin » Sun, May 23, 2021, 1:02 AM UTC

bredren wrote: post #85

Waterluvian wrote: post #15

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?

Just in case someone’s reading this and isn’t also aware: Django Debug Toolbar offers somewhat interactive exploration of queries. It can also be used with Django Rest Framework via the browsable api. May be parent is looking for deeper insight than this but it is useful to do quick visual query inspection.

this.

django debug toolbar (or similar) should be the first thing you go to because these tools understand the django ORM well.

the other thing that comes to mind is enabling query timing in your django shell. i believe you might need an extension for this.

then you can look at the postgres itself. but i would keep it at the django layer at first because it might reveal something about the ORM.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#159

Post by petergeoghegan » Sun, May 23, 2021, 1:03 AM UTC

andrewstuart wrote: post #151

petergeoghegan wrote: post #119

Earlier quoted context omitted.

Co-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 al…

Very interesting thanks for the update - how great is the Internet to hear directly from the developer! It's a pity this wasn't listed in the announcement as I think alot of people are interested in this issue. >> Long term, the way to fix this is to come up with a design that doesn't need to freeze at all. Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?

> Very interesting thanks for the update - how great is the Internet to hear directly from the developer!

I see the names of a few people that also work on Postgres on this thread. We're not all that hard to get a hold of if you're a user that has some kind of feedback or question, for what it's worth. The culture is very open in that sense.

> Do you know if anyone is turning their attention to this or is it not currently being tackled by anyone?

This is one of the goals of the zheap project. I myself have some very tentative ideas for tackling it within the standard table access method, heapam. I have not specifically committed to working on it on any timeframe. I haven't completely convinced myself that the approach I'm thinking of is truly robust and practicable. It's pretty complicated, especially because I cannot really know what will break and need to be fixed until I spend significant effort on the implementation.

Re: An early look at Postgres 14: Performance and monitoring Improvements

#160

Post by Diggsey » Sun, May 23, 2021, 1:05 AM UTC

megous wrote: post #150

Diggsey wrote: post #139

Earlier quoted context omitted.

You may be right that it's easy to set up, but pgbouncer doesn't help with this problem most of the time. It's a problem that needs to be solved within postgres. There are three pooling modes: - Session pooling. Doesn't help with this issue since it doesn't reduce the total number of required connections. - Transaction pooling / statement pooling. Breaks too many things to be usable. (eg. prepared statements...) See…

Personally I don't expect this to be ever improved in PostgreSQL (ie. change from process per connection model to something else), so I design my multi-user apps so that everything works fine with session pooling (quick short sessions/connections to pgbouncer) and connections that wait for NOTIFY get made directly to postgresql server, and are also limited in number. And it works fine on low-resourced machines that I…

Switching to a threaded model would be a lot of work, but there is a simpler solution that retains most of the benefits: using a process-per-connection model for active connections only, and allowing a single process to have multiple idle connections.

I follow the mailing list because I'm interested in this exact issue. Konstantin Knizhnik sent a patch implementing a built-in connection pooler in early 2019 that uses a similar approach to what I just described. The work on that has continued to this day, and I'm hopeful that it will eventually be merged.