Comment by pksunkara
19 hours ago
I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
19 hours ago
I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
The article explains why they want to avoid this option:
> Starting at version 14, PostgreSQL supports generated columns - these are columns that are automatically populated with an expression when we insert the row. Sounds exactly like what we need but there is a caveat - the result of the expression is materialized - this means additional storage, which is what we were trying to save in the first place!
Thanks, missed that part. I would still be interested in knowing how much additional storage that adds, if the OP is interested in updating the article.
>I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
Is it also possible to create index (maybe partial index) on expressions?
That's the first solution (a function based index), however it has the drawback of fragility: a seemingly innocent change to the query can lead to not matching the index's expression anymore). Which is why the article moves on to generated columns.
I assume it would increase the storage usage, which they say they are trying to avoid in that example.