Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An early look at Postgres 14: Performance and monitoring Improvements

pganalyze.com

Post reply on HN
Print view View original

141–150 of 254 posts

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

#141

Post by petergeoghegan »

dimgl wrote: post #138
petergeoghegan wrote: post #106

Earlier quoted context omitted.

Although the article doesn't mention it, index bloat will be far better controlled in Postgres 14: https://www.postgresql.org/docs/devel/btree-implementation.h... One benchmark involving a mix of queue-like inserts, updates, and deletes showed that it was practically 100% effective at controlling index bloat: https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZ... The Postgres 13 baseline for the benchmark/te…

Wow, this is actually incredible. One of my biggest gripes with Postgres is going to be solved. Thank you for sending this over!

Thanks.

I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration.

This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is that we let the workload figure out what cleanup is required on its own, in an incremental fashion.

Another interesting detail is that there is synergy with the deduplication stuff -- again, very nonlinear behavior. Kind of organic, even. Deduplication was a feature that I coauthored with Anastasia Lubennikova that appeared in Postgres 13.

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

#142

Post by macdice »

threeseed wrote: post #100
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…

There are also technologies like NVMe over Fabric/RDMA, eBPF, XDP, io_uring etc which are just starting to get traction and are game changers for performance. None of which are being used. All of these require a different architecture so expect to see newer databases push things even further.

Peope are working on io_uring for PostgreSQL... watch this space... https://github.com/anarazel/postgres/tree/aio

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

#143

Post by conradfr »

pgaddict wrote: post #135
conradfr wrote: post #73

Earlier quoted context omitted.

Not totally ideal if you're using containers as it requires the binaries of the old and new version, unless I'm missing something.

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

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

#144

Post by efxhoy »

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?

It'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.

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

#145

Post by edoceo »

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)

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

#146

Post by fnord77 »

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…

people complain about java's verbosity, but I see that as a feature in places where there's a revolving door of consultants working on things. Everything is so explicit it is easy to see what some code does.

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

#147

Post by fnord77 »

radicalbyte wrote: post #33
yakubin wrote: post #24

Earlier quoted context omitted.

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…

- Fantastic battle tested ecosystem of libraries. - Stable cross platform (kills Python, Node here). - Lingua franca. Now I personally don't like Java - it feels crusty vs C# - but the libraries are amazing. You can also use something nice like Kotlin and you have all of the platform benefits with non of the crusty language issues.

I started using java 16 after a long hiatus from java 7 (instead doing rust and clojure) - I'm pretty happy with some of the new language features - lambdas, records, type inference, streams

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

#148

Post by ComputerGuru »

conradfr wrote: post #73
andruby wrote: post #26

Earlier quoted context omitted.

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.

It does not make sense to run a rdbms in a container in prod; the entire OS is your container.

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

#149

Post by robertfw »

whatshisface wrote: post #124
zdragnar wrote: post #117

Earlier quoted context omitted.

Really? Oracle actually makes a lot of sense to me for a database name (in the 'source of truth' sense, not in the prophet sense). Mongo, on the other hand, has definitely always had the racist/ablist slur as the first connotation for me.

I've learned almost all the slurs I know from comments or media sources complaining about them. It's the only place they're used in polite society.

Then perhaps count yourself lucky to not have had some of these used against you.

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

#150

Post by megous »

Diggsey wrote: post #139
megous wrote: post #105

Earlier quoted context omitted.

Setting up pgbouncer is not much headache and for for OLTP workloads, it works great. You can even see it in the graph, that best performance is when number of CPU cores = number of connections. And so will be memory use. :)

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 tend to use for everything.

Post reply on HN