5 ms·
I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
by pksunkara 8mo ago
I think a stored generated column allows you to create an index on it directly. Isn't it better approach?
- rpsw 8mo agoI assume it would increase the storage usage, which they say they are trying to avoid in that example.
- tczMUFlmoNk 8mo agoThe 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!
- pksunkara 8mo agoThanks, 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.
- zenmac 8mo ago>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?
- masklinn 8mo agoThat'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.