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

171–180 of 254 posts

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

#171

Post by keeperofdakeys »

wiradikusuma wrote: post #3

I'm thinking of using Postgres for a project, but a DBA friend told me operationally it's more challenging than MySQL. Unfortunately, he can't elaborate. Does anyone have real work experience? Or is it based on outdated "PG must manually vacuum frequently"?

Postgres has some disadvantages that can pop up on certain workloads (eg. bloat) but so does MySQL. And most of those limitations are only when you've got long open transactions, trying to hammer it IO wise, or you're making really big databases (100GB-1TB or more). However for both Postgres and MySQL there is plenty of documentation about these problems, and how to resolve them. So you'll never be "stuck" with issues.

In general I find postgres "just works" a lot more than MySQL. MySQL has a really bad habit of sticking with bad defaults for a long period, while having better configuration available. On the other hand postgres devs actively remove/change defaults so you're always getting the best it has to offer.

If you pick one, and you don't like it there are plenty of tools to change between them. If you're curious you could even deploy both of them.

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

#172

Post by megous »

derefr wrote: post #165
megous wrote: post #123

Earlier quoted context omitted.

In that case this does not help. SELECT json['a']; will not return the value of the string in {"a":"ble"} (like it does in Javascript), but a JSON encoding of that string, so '"ble"'. You'll still not be able to do simple comparisons like `SELECT json_col['a'] = some_text_col;` Superficial familiarity, but it still behaves differently than you expect. Is there even a function that would convert JSON encoded "string"…

> Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it. Oddly, no, there's no specific function for taking a root-level scalar JSON term (like '"foo"'::jsonb), and extracting said scalar to its equivalent native Postgres type. You can still do it (for extracting to text, at least), but you have to use a 'vacuous' path-navigation to accomplish it, so i…

Thanks for the idea. This is a bit shorter:

    SELECT '"foo"'::jsonb #>>'{}';
But yeah:

    SELECT jsonb_col['prop1']#>>'{}' FROM ...;
looks a bit meh. And custom right unary operators are on the way out, so one can't even create one for this use case.

Anyway, for fun:

    create function deref_jsonb(jsonb) returns text as $$ begin return $1#>>'{}'; end $$ language plpgsql;

    CREATE OPERATOR # ( leftarg = jsonb, function = deref_jsonb );

    select '"sdfasdf"'::jsonb #;

    select jsonb_col['a']# FROM somewhere;
:)

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

#173

Post by ilikepi »

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

The native JSON data type was introduced with PG 9.2 in 2012.

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

#174

Post by nine_k »

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…

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.

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

#175

Post by Mike8435234 »

bradleyjg wrote: post #130
vosper wrote: post #75

Earlier quoted context omitted.

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

It’s frustrating to need a run-time team for a piece of infrastructure, especially one sold as IaaS. It’s totally understandable that you’d need developers to have expertise in patterns and anti-patterns, as well as needing an expert to set things up in the first place, but you shouldn’t have to have a dedicated ES monitoring / tuning / babysitting team like Oracle DBAs of yore. That you do, means it isn’t there yet…

ES doesn't need a "run-time team". It just works.

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

#176

Post by [deleted] »

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

[deleted]

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

#177

Post by zaius »

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.

It’s no coincidence - the names for Cockroach and GIMP were coined by the same person https://en.m.wikipedia.org/wiki/Spencer_Kimball_(computer_pr...

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

#178

Post by [deleted] »

zdragnar wrote: post #117
meesterdude wrote: post #110

Earlier quoted context omitted.

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

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.

[deleted]

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

#179

Post by FridgeSeal »

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…

To provide an opposing viewpoint here: ES and it’s monstrous API and resourcing requirements are a pain to manage and run. It’s a product that has pivoted in so many directions that it’s just become a bit of a mess. I don’t want a full-text search engine that also has graphs, ML, some bizarre scripting feature, log management, etc all stapled in on top. Geospatial and other analytic stuff I’d rather use a dedicated OLAP db like Redshift or ClickHouse.

I’m currently evaluating typesense vs ES for a fts project and typesense is winning so far by simply be “not painful” to deal with.

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

#180

Post by FridgeSeal »

Mike8435234 wrote: post #175
bradleyjg wrote: post #130

Earlier quoted context omitted.

It’s frustrating to need a run-time team for a piece of infrastructure, especially one sold as IaaS. It’s totally understandable that you’d need developers to have expertise in patterns and anti-patterns, as well as needing an expert to set things up in the first place, but you shouldn’t have to have a dedicated ES monitoring / tuning / babysitting team like Oracle DBAs of yore. That you do, means it isn’t there yet…

ES doesn't need a "run-time team". It just works.

It absolutely does not “just work”, there’s so much to configure and then get-right for your use-case that you almost certainly need people with a solid understanding of the JVM + ES. Let alone fixing it when something inevitably breaks.
Post reply on HN