This JSON plucking is also much easier now with the json path expressions coming up in v12!

Can anyone share a link?

I've been excited about this idea of json-blob-field-to-computed-column for a solid year... I'm so stoked that it's coming to fruition!

Relating json/xml API response blobs to queryable columns is a huge and inefficient burden which I hope that these featuers can help to alleviate.

Why your application doesn't use a view? Or even better a table function?

At least in PG, a regular view must calculate everything at read time, which might mean a lot of duplicated calculations, and a materialized view has to be refreshed "manually", it doesn't auto-update piecemeal when the underlying data changes.

I've had pretty good luck with using table triggers to update materialized views and make everything "automatic". A little more work up front, but pretty easy to forget about it once it's in place.

A REFRESH of a materialized view still requires full re-computation of all values, no? It's better than regular views if you have more reads than writes, but still quite wasteful.

You are right. I've been dealing with this personally with PostGIS and storing large geometries. Ideally you split the geometries up in to many smaller geometries (using a built-in function), and it's much faster to calculate intersections, contained lengths, etc using those instead.

Many places online recommend storing this collection of divided geometries as a materialized view, but I recently had to move it to a separate real table because inserting a single new record would take 15 minutes to update the view (on an RDS 4xlarge database). It could at least update concurrently, so other reads didn't block, but now that the the divided geometries are stored in a separate table I can add new records in under 5 seconds usually.

I believe so. I would say it’s more useful for rollups or aggregations where real-time isn’t necessary.

Make a function ?

Do you mean an application layer function, or a dB one? And if a dB one, you also need to create an index to go with it. And then there are dragons that you don’t need in life. If an application one, then people need to know to call it, so, yeah.

I know there are things you can do, but having better tools available is always a win (especially when they remove later maintenance from the equation).

In DB. You can keep it with migrations. You can have a functional index without creating a new column.

Make a trigger

From the article:

> ” Such functionality was earlier usually achieved with triggers, but with generated columns this becomes much more elegant and cleaner.”

"much more elegant and cleaner" is a bit of an overstatement in my opinion...

Sounds like the generated column implementation is faster than a PL/pgSQL trigger.

https://news.ycombinator.com/item?id=21134706

I don’t have a link at hand from a discussion on the mailing list, but I’ll take Peter’s word for it.

I miss the why its more elegant and cleaner too.

"Indexes: Generated columns can be used in indexes, but cannot be used as a partition key for partitioned tables."

I also hope it can be used for a partition key in the future.