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

161–170 of 254 posts

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

#161

Post by zdragnar »

whatshisface wrote: post #124
zdragnar wrote: post #117

Earlier quoted context omitted.

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.

I've learned almost all the slurs I know from comments or media sources complaining about them. It's the only place they're used in polite society.

It isn't really a surprise in this case since both ethnic Mongolians and those with Downs syndrome are not in many Americans' social circles.

"Almost all" does sound like a bit of a surprise, but thinking back on it the only one I know for a fact I heard for the first time outside of a corrective context was my elder uncles friends who enjoyed self-depreciating jokes, usually with slurs for eastern Europeans in them. I first heard those as a child and only realized years later they were offensive. Most others, I think, I honestly have no idea when i was first exposed to them.

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

#162

Post by megous »

Diggsey wrote: post #160
megous wrote: post #150

Earlier quoted context omitted.

Personally I don't expect this to be ever improved in PostgreSQL (ie. change from process per connection model to something else), so I design my multi-user apps so that everything works fine with session pooling (quick short sessions/connections to pgbouncer) and connections that wait for NOTIFY get made directly to postgresql server, and are also limited in number. And it works fine on low-resourced machines that I…

Switching to a threaded model would be a lot of work, but there is a simpler solution that retains most of the benefits: using a process-per-connection model for active connections only, and allowing a single process to have multiple idle connections. I follow the mailing list because I'm interested in this exact issue. Konstantin Knizhnik sent a patch implementing a built-in connection pooler in early 2019 that uses…

But how's that different from pgbouncer?

EDIT: I see, it would have session state restore, not just DISCARD like pgbouncer.

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

#163

Post by derefr »

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.

Wouldn't one big difference be that with this syntax, you can use bind-parameters / joined row-tuple fields / expression values as jsonpath keys?

ETA: no, actually, I was wrong — #>> takes text[], so you can already pass it an ARRAY[] literal containing expressions. It's just all the examples in the PG docs that use the IO syntax to represent the path, and then rely on an implicit cast to text[].

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

#164

Post by riku_iki »

petergeoghegan wrote: post #141
dimgl wrote: post #138

Earlier quoted context omitted.

Wow, this is actually incredible. One of my biggest gripes with Postgres is going to be solved. Thank you for sending this over!

Thanks. I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration. This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is t…

I am not very familiar with this topic, but need to maintain large and frequently updated DB, which requires periodic VACUUM FULL with full tables lock, so, does PG suffers from index bloat only and your fix solves it, or there is some other type of bloat for general table data too, which will still exists after your improvement?

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

#165

Post by derefr »

megous wrote: post #123
zdragnar wrote: post #114

Earlier quoted context omitted.

I think the difference is familiarity . It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much harder to remember unfamiliar syntaxes and grammars, and easier to confuse similar looking operations with each other.

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 it's extremely clumsy, and wastes the potential of the new syntax:

    SELECT '"foo"'::jsonb #>> (ARRAY[]::text[]);

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

#166

Post by petergeoghegan »

riku_iki wrote: post #164
petergeoghegan wrote: post #141

Earlier quoted context omitted.

Thanks. I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration. This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is t…

I am not very familiar with this topic, but need to maintain large and frequently updated DB, which requires periodic VACUUM FULL with full tables lock, so, does PG suffers from index bloat only and your fix solves it, or there is some other type of bloat for general table data too, which will still exists after your improvement?

It's not possible to give you a simple answer, especially not without a lot more information. Perhaps you can test your workload with postgres 14 beta 1, and report any issues that you encounter to one of the community mailing lists.

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

#167

Post by aseipp »

purerandomness wrote: post #16
jjeaff wrote: post #7

Earlier quoted context omitted.

MySQL 8 is not that far behind in feature parity. And is ahead when it comes to scalability. So I don't see postgres as necessarily standing alone.

No DDL transactions, no materialized views, the list is endless. There's almost no reason to pick MySQL for a new project.

MySQL has transactions for DDL changes since 8.0.

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

#168

Post by teej »

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…

Lecture 1 of that series is surprising and hilarious for a class about databases.

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

#169

Post by roenxi »

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, and then have a table-in-a-table JSON column.

It isn't so out there to think that ongoing calls for JSON support will lead Postgres to re-implement tables in JSON. We've already got people trying to build indexes on fields inside a JSON field.

This is needless complexity engineered by people who insist on relearning schemas from scratch, badly, rather than trusting the database people who say "you need to be explicit about the schema, do data modelling up front".

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

#170

Post by MichaelMoser123 »

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…

i think this should have been the headline. really thanks!
Post reply on HN