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

181–190 of 254 posts

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

#181

Post by roenxi »

ilikepi wrote: post #173
roenxi wrote: post #169

Earlier quoted context omitted.

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.

I know. And it isn't a new argument either, I for one have been making it for years.

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

#182

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

#183

Post by jjazwiecki »

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.

I always thought it was a reference to valuable stuff picked from trash, which I understood to be slang from sanitation workers, but apparently that's local to the NYC area.

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

#184

Post by paulddraper »

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

> Postgres is bowing to the inevitable

I think PostgreSQL has always been very pragmatic. It's supported JSON natively since 9.2 (Sep 2012).

> Databases are a bad place to be storing JSON

You're right that "mature" features and projects have a very good understand of the schema. But not everything is that.

Suppose I want to collect info from the Github API about a bunch of repos. I can just store the entire JSON response in a table and then query it at my leisure.

There's also something to be said for contiguous access. Joining tons of little records together has performance problems. Composite types and arrays can also fill this void, but they both have their own usability quirks.

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

#185

Post by vosper »

FridgeSeal wrote: post #179
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…

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

> 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

Sure, so use something else. I do need (most all of) that at my work (plus the horizontal scaling), and there's no competition. I know we're not the only ones.

Also, there's nothing bizarre about the scripting feature. There are several options for scripting, it's very flexible, and it suits implementing custom logic when you need it.

And, I'm not saying ES is perfect! I'm saying that there's a set of use-cases that only ES (to my knowledge) can fulfil, and that's complex aggregations also involving complex full-text search, over tera/petabytes of data. Clickhouse can do aggregations, but doesn't have anything close to the search chops (again, to my knowledge).

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

#186

Post by throwaway2037 »

teej wrote: post #168
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…

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

I am not a student at CMU. Are these publicly available online?

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

#187

Post by Mike8435234 »

FridgeSeal wrote: post #180
Mike8435234 wrote: post #175

Earlier quoted context omitted.

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.

No more than any other database. I mean relative to SQL Server, Postgres, MongoDB or any other database. There's no extraordinary difficulty to manage ES above any other production system. It is very usable out of the box, and needs minimal tuning for many use cases. Of course some uses cases will require additional tuning and maintenance, sometimes quite a lot if you have a very large system, JUST LIKE ANY OTHER DATABASE SYSTEM.

In our case for a small website serving the general public (a few tens of thousands of requests per day) it just worked OOTB with hardly any tuning or maintenance at all.

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

#188

Post by hilbertseries »

exceptione wrote: post #121
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…

You must be kidding me with the CosmosDB mention. It doesn't even have foreign key constraints. I have to work with it and I have never seen such a feature-poor dbms before.

Foreign key constraints are not practical for distributed data stores. And are actually more controversial than you’d think for regular databases, due to their heavy performance cost.

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

#189

Post by jasonwatkinspdx »

throwaway2037 wrote: post #186
teej wrote: post #168

Earlier quoted context omitted.

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

I am not a student at CMU. Are these publicly available online?

Click schedule in the link in my above comment, or in any of the previous classes on the same topic. It's all online. They only restrict a handful of guest lectures, usually from the usual suspects like oracle or amazon.

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

#190

Post by arunitc »

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 milliseconds in MSSQL Server. The APCRoleTableColumn has only about 5000 records. The above query is to delete all columns not present in the schema from the APCRoleTableColumn table

I used to be a heavy MSSQL user. I do love Postgre and have switched over to using it in all my projects and am not looking back. I wish it was as performant as MSSQL. This is just one example. I can list a number of others too.

Post reply on HN