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

101–110 of 254 posts

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

#101

Post by petergeoghegan »

polskibus wrote: post #60
aidenn0 wrote: post #51

Earlier quoted context omitted.

I think the point is that git isn't "so much better" than mercurial, while pgsql has had a lead on mysql for quite some time on a lot of technical measurements.

Postgresql does not have real, maintained with each change, clustered index. That itself makes it worse for many workloads than MySQL

I would say that that's pretty dubious claim with modern versions of Postgres and MySQL/InnoDB, running on modern hardware. See for example this recent comparative Benchmark from Mark Callaghan, a well known member of the MySQL community:

https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs...

I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload.

Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.

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

#103

Post by gogopuppygogo »

jasonwatkinspdx wrote: post #96
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…

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…

Cockroach is the worst brand for a database ever.

Even Croach would be a massive branding improvement.

This is similar to how gimp is a terrible brand.

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

#104

Post by HideousKojima »

harikb wrote: post #74
eterm wrote: post #65

Earlier quoted context omitted.

I think anyone who has worked a lot with MSSQL would disagree with Postgres being "so much better". It's only really in the last few years that postgres has pulled ahead, MSSQL was lot more feature rich and performant for a decade.

MSSQL ? As in Microsoft SQL Server? I have heard this argument a lot and all the comparisons I have seen are specific benchmarks on specialized hardware. My own personal experience wasn’t anything like the benchmarks

MSSQL still has a few features that set it apart from Postgres. Off the top of my head are Filestream (basically storing files in the database while still having them accessible as files on the filesystem) and temporal tables without the need for extensions.

Personally if I were choosing the tech stack for my company I'd still go for Postgres though

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

#105

Post by megous »

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…

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. :)

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

#106

Post by petergeoghegan »

dimgl wrote: post #72
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…

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/test case (actually HEAD before the patch was committed, but close enough to 13) showed that certain indexes grew by 20% - 60% over several hours. That went down to 0.5% growth over the same period. The index growth much more predictable in that it matches what you'd expect for this workload if you thought about it from first principles. In other words, you'd expect about the same low amount of index growth if you were using a traditional two-phase locking database that doesn't use MVCC at all.

Full disclosure: I am the author of this feature.

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

#107

Post by jeff-davis »

vosper wrote: post #75
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…

> Do any other entrenched software projects come to mind? Elasticsearch is underrated here, IMO. Yes, there are alternatives for simple fulltext search. But there’s a lot more it can do (adhoc aggregations incorporating complex fulltext searches, with custom scripted components; geospatial; index lifecycle management) and if you’re using those features, there’s nothing else comparable. It’s pretty stable, too, once y…

I don't know about elasticsearch specifically, but I'm skeptical of special-purpose systems for databases.

They are great in some cases and terrible in others, and over time, use cases push database systems into their worst cases. Use cases rarely stay in the sweet spot of a special-purpose system.

That being said, if the integration is great, and/or the special system is a secondary one (fed from a general-purpose system), then it's often fine.

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

#110

Post by meesterdude »

gogopuppygogo wrote: post #103
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…

Cockroach is the worst brand for a database ever. Even Croach would be a massive branding improvement. This is similar to how gimp is a terrible brand.

I mean... the WORST? For me Mongo takes the cake, but oracle is up there too.
Post reply on HN