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

221–230 of 254 posts

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

#221

Post by jpeter »

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…

What exactly makes Postgres better than MySql? There seem to be certain design decisions like WAL or process per connection that cause problems at scale

https://eng.uber.com/postgres-to-mysql-migration/

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

#222

Post by Tostino »

jpeter wrote: post #221
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…

What exactly makes Postgres better than MySql? There seem to be certain design decisions like WAL or process per connection that cause problems at scale https://eng.uber.com/postgres-to-mysql-migration/

That article really isn't a good critique of Postgres.

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

#223

Post by da39a3ee »

megous wrote: post #99
xfalcox wrote: post #88

Earlier quoted context omitted.

Wow is this for real? That is such a big quality of life change! Happy to see it!

Not much different from some_jsonb#>>'{some,path}' and once you add the need to convert out of jsonb to text, you'll not be saving any characters either. At least for queries. For updates, it looks nice I guess.

Would you be able to give a bit of context for the limitations of the new syntax that you’re pointing out? Could they be overcome (and if so why did it ship like this) or are they inevitable?

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

#224

Post by pgaddict »

nine_k wrote: post #174
pgaddict wrote: post #132

Earlier quoted context omitted.

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…

I wonder if the amount of RAM used by a new process can be reduced. Code and other RO segments are shared anyway, so it's only basically the new heap and various buffers. Reducing this amount would also run Postgres in more constrained environments.

There are two parts of this - the memory allocated by OS and internally.

At the OS level, we can't really do much, I'm afraid :-( I don't think we're wasting too much memory there, exactly because a lot of the memory is shared between processes. Which also makes it difficult to determine how much memory is actually used by the processes (the sharing makes the various metrics in ps/top are rather tricky to interpret).

As for the internal memory, it's a bit more complicated. We need a little bit of "per process" memory (per-backend entries in various internal data structures, etc.) - a couple dozen/hundred kBs, perhaps. It's hard to give a clear figure, because it depends on max_locks_per_transaction etc. This is unlikely to go away even if we switched to threads, because it's really "per session" state.

But then there are the various caches the processes keep, memory used to run queries etc. Those may be arbitrarily large, of course. The caches (with metadata about relations, indexes etc.) are usually a couple MBs at most, but yes, we might share them between threads and save some of this memory. The price for that would be the need for additional synchronization / locking, etc. The memory used to run queries (i.e. work_mem) is impossible to share between threads, of course.

There's a blog post by Andres Freund with more details: https://www.citusdata.com/blog/2020/10/08/analyzing-connecti...

Overall, there's very little chance PostgreSQL switch to threads (difficulty of such project, various drawbacks, ...). But I do agree having to run a separate connection pool may be cumbersome, etc. There was a proposal to implement integrated connection pool, which would address at least some of those problems, and I wouldn't be surprised if it happened in foreseeable future.

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

#225

Post by nsajko »

yakubin wrote: post #10
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…

Fortran for linear algebra software. Excel for business spreadsheets. Java for enterprise server software.

> Fortran for linear algebra software.

Not an expert, but it is my understanding that Julia is becoming an ever more serious competitor day by day.

> Excel for business spreadsheets.

Honest question, what does LibreOffice miss compared to Excel? In any case, (again not an expert) spreadsheets seem quite inferior to a combination of Julia, CSV and Vega (Lite); although there are certainly more people that are familiar with operating Excel.

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

#226

Post by toomanybeersies »

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

JSON in Postgres is a bit like a nail gun. Used correctly, it's incredibly useful. But in inexperienced hands (and lacking good technical leadership), it's easy to shoot yourself in the thigh.

You don't even need JSONB to commit war crimes on a Postgres database. There's many things that Postgres can do, but probably shouldn't be done:

- Storing "foreign keys" in an array column, instead of using a join table

- Storing binary files as base64 encoded strings in text columns

- Using a table with `key` and `value` string columns instead of using redis

- Pub/sub using NOTIFY/LISTEN - Message queueing

- Other forms of IPC in general

- Storing executable code

- God tables

Even when trying to use Postgres appropriately, plenty of engineers don't get it right: unnecessary indices, missing indices, denormalised data, etc.

This isn't unique to Postgres, or relational databases in general. Any form of storage can and will be used to do things it's not designed or appropriate for. You can use as easily use S3 or Elasticsearch for message queuing, and can even find official guides to help you do so. Go back 20 years or so, and you can find implementations of message busses using SOAP over SMTP.

The problem isn't JSONB (or any other feature). It's bad engineering. Usually it's an incarnation of Maslow's Hammer: when all you have is a hammer, everything looks like a nail.

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

#227

Post by megous »

da39a3ee wrote: post #223
megous wrote: post #99

Earlier quoted context omitted.

Not much different from some_jsonb#>>'{some,path}' and once you add the need to convert out of jsonb to text, you'll not be saving any characters either. At least for queries. For updates, it looks nice I guess.

Would you be able to give a bit of context for the limitations of the new syntax that you’re pointing out? Could they be overcome (and if so why did it ship like this) or are they inevitable?

I don't think it's a limitation, it's just by design. a['b'] is equivalent to a->'b' not to a->>'b', otherwise deep references (a['b']['c']) would not work because first a['b'] would return text and not jsonb value.

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

#228

Post by toomanybeersies »

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.

Let's add Git and Kafka to that list.

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

#229

Post by btbuilder »

gher-shyu3i wrote: post #216
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.

Why are you migrating out of curiosity? Price reasons?

Yes, we have multiple RDS instances and wish to reduce costs.

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

#230

Post by yakubin »

nsajko wrote: post #225
yakubin wrote: post #10

Earlier quoted context omitted.

Fortran for linear algebra software. Excel for business spreadsheets. Java for enterprise server software.

> Fortran for linear algebra software. Not an expert, but it is my understanding that Julia is becoming an ever more serious competitor day by day. > Excel for business spreadsheets. Honest question, what does LibreOffice miss compared to Excel? In any case, (again not an expert) spreadsheets seem quite inferior to a combination of Julia, CSV and Vega (Lite); although there are certainly more people that are familiar…

> Not an expert, but it is my understanding that Julia is becoming an ever more serious competitor day by day.

And Julia uses BLAS which is written in Fortan.

Post reply on HN