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

131–140 of 254 posts

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

#131

Post by edoceo »

petergeoghegan wrote: post #106
dimgl wrote: post #72

Earlier quoted context omitted.

Postgres is good, even great, but this is hyperbole. Postgres has its downsides, autovacuum being one of them.

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…

Thank you!!

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

#132

Post by pgaddict »

stickfigure wrote: post #82
matthewbauer wrote: post #5

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…

I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a loa…

It isn't solved, and no one claimed it to be solved. The scalability improvement is related to how we build MVCC snapshots (i.e. information which transactions are visible to a session). That may reduce the memory usage a bit, but it's more about CPU I think.

As for the per-connection memory usage, the big question is whether there really is a problem (and perhaps if there's a reasonable workaround). It's not quite clear to me why you think the issues in your case are are due to idle connections, but OK.

There are two things to consider:

1) The fixed per-connection memory (tracking state, locks, ..., a couple kBs or so). You'll pay this even for unused connections.

2) Per-process memory (each connection is handled by a separate thread).

It's difficult to significantly reduce (1) because that state would no matter what the architecture is, mostly. Dealing with (2) would probably require abandoning the current architecture (process per connection) and switching to threads. IMO that's unlikely to happen, because:

(a) the process isolation actually a nice thing from the developer perspective (less locking, fewer data races, ...)

(b) processes work quite fine for reasonable number of long-lived connections, and for connection pools address a lot of the other cases

(c) PostgreSQL supports a lot of platforms, some of which may not may not have very good multi-threading support (and supporting both architectures would be quite a burden)

But that's just my assessment, of course.

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

#133

Post by pgaddict »

btbuilder wrote: post #94
stickfigure wrote: post #82

Earlier quoted context omitted.

I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a loa…

I agree - the disparity between the cost of idle connections in Postgres vs MSSQL is hampering our ability to migrate.

Can you elaborate / quantify the memory requirements a bit? I don't have much experience with MSQQL in this respect, so I'm curious how big the difference is.

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

#134

Post by ccleve »

stickfigure wrote: post #82
matthewbauer wrote: post #5

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…

I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a loa…

Say more about the "poor replication story". I thought replication was pretty good. What's wrong with it?

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

#135

Post by pgaddict »

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.

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.

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

#136

Post by pgaddict »

jakebasile wrote: post #54
emilsedgh wrote: post #43

Earlier quoted context omitted.

EXPLAIN (ANALYZE, BUFFERS) Take the result of this and paste it into https://explain.depesz.com/ which will make it human readable. Understanding this is sometimes very easy, but if you want to understand what they _really_ mean, you can read depesz.com

Wow, how have I never heard of this tool?! Thanks a lot for the link!

Be careful as it actually runs the query, so if it's a DELETE/INSERT/UPDATE it'll change the data. So run it in BEGIN/ROLLBACK block.

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

#137

Post by pgaddict »

diminish wrote: post #63
emilsedgh wrote: post #43

Earlier quoted context omitted.

EXPLAIN (ANALYZE, BUFFERS) Take the result of this and paste it into https://explain.depesz.com/ which will make it human readable. Understanding this is sometimes very easy, but if you want to understand what they _really_ mean, you can read depesz.com

I use it frequently - but I wish there was a tool which went into the semantics somewhat.

What do you mean by "went into the semantics"?

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

#138

Post by dimgl »

petergeoghegan wrote: post #106
dimgl wrote: post #72

Earlier quoted context omitted.

Postgres is good, even great, but this is hyperbole. Postgres has its downsides, autovacuum being one of them.

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!

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

#139

Post by Diggsey »

megous wrote: post #105
stickfigure wrote: post #82

Earlier quoted context omitted.

I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a loa…

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 the table at https://www.pgbouncer.org/features.html for what features cannot be used with transaction pooling.

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

#140

Post by pgaddict »

mixmastamyk wrote: post #108
lmarcos wrote: post #102

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

Care to elaborate? Having each tool handle its job sounds like a good strategy.

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

Post reply on HN