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

201–210 of 254 posts

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

#201

Post by tpetry »

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.

I have not tried it but PgMustard is stating that they make the query plans more explainable and hint for the problems in your query.

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

#202

Post by pharmakom »

oblio wrote: post #116
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…

Yeah, to list a bit: - scaling is non-trivial (you can't just add a node and have PostgreSQL automagically Do The Right Thing™) - you can only have so many connections open to the database, causing issues with things such as AWS Lambda - I don't remember if this was changed, but I got the impression a while ago that having dynamic DB users was a bit cumbersome to set up (plug PostgreSQL to AD/LDAP)

On the lambda point, RDS proxy is a good solution if using AWS.

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

#203

Post by developer2 »

pgaddict wrote: post #132
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…

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

And this right here is why PostgreSQL will never overtake MySQL and its forks. The entire industry is sick of these excuses regarding process-per-client instead of a proper multi-threaded model. There may have been a valid argument for this 15 years ago, but not anymore.

Your definition of "reasonable number of long-lived connections" is anything but reasonable. Then "connection pools address a lot of the other cases", when a connection pool/bouncer is unwanted, unwarranted, and just adds another point of failure that needs to be deployed and maintained.

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

#204

Post by NoInkling »

ccleve wrote: post #134
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…

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

There's some stuff here with some links you can follow: https://rbranson.medium.com/10-things-i-hate-about-postgresq...

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

#205

Post by ako »

roenxi wrote: post #169
e1g wrote: post #80

Another 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/

Postgres is bowing to the inevitable, JSON support is too much in demand. But this is going to be a classic example of bad design. Databases are a bad place to be storing JSON, which is a good interface and a bad storage standard. It is pretty easy to see how JSON will play out: some bright young coder will use JSON because it is easier, then over the course of 12 months discover the benefits of a constrained schema,…

I use the json features of postgres to turn json into relation data. Store all json messages received in a table, then use a materialized view to extract the relevant parts into columns. Works well, and lets me keep the original data around.

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

#206

Post by Izkata »

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?

If you only have the Django queryset and not the SQL, you can generate pseudo-sql using "print(queryset.query)".

Note that this isn't valid SQL, just an approximation, because Django doesn't generate a single SQL string, but uses the underlying library's parameterization. So you'll have to fiddle with quotes and such to get SQL you can run the EXPLAIN on that's mentioned in the other replies.

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

#207

Post by aidos »

arunitc wrote: post #190

Delete 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 m…

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

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

#208

Post by cosmosgenius »

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?

All django query have a .explain with it. Which is similar to runnning an explain in the DB, but less detailed.

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

#209

Post by btbuilder »

pgaddict wrote: post #133
btbuilder wrote: post #94

Earlier quoted context omitted.

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.

Sure, SQL Server supports a maximum of 32767 connections each of which use around 128kB. Meaning that if you use the max connections you’ll need 4GB for the connection overhead.

We see no noticeable drop in performance with increased idle connection with our workload.

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

#210

Post by mnahkies »

oblio wrote: post #116
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…

Yeah, to list a bit: - scaling is non-trivial (you can't just add a node and have PostgreSQL automagically Do The Right Thing™) - you can only have so many connections open to the database, causing issues with things such as AWS Lambda - I don't remember if this was changed, but I got the impression a while ago that having dynamic DB users was a bit cumbersome to set up (plug PostgreSQL to AD/LDAP)

An external connection pooler like pgbouncer can alleviate some of the simultaneous connection limits

There are projects to automate the syncing of LDAP users to postgres but it would be nice if this was built-in.

However I get the impression that part of the reason these features aren't in the box is to limit scope creep in the main project.

Post reply on HN