Re:New In Postgres 12: Generated Columns
by magicalhippo on Wednesday October 02, 2019 @07:26AM
(#21135252)
It's great for speeding up queries. Optimizations frequently require giving up on absolute purity. We use computed columns a lot for speeding up specific, frequently used queries.
One example is where we need to store a special identifier code. The identifier code is required by law to have a certain format, and part of it is the date the code was generated.
Users frequently want to view items with codes generated on a specific day. For making presentation and reporting easier, as well as significantly faster, we added a computed date column that extracts the date from the code and indexed it.
Keeping an extra field in sync in code would inevitably have lead to bugs, especially as we have several different codebases which could update the id code.
One example is where we need to store a special identifier code. The identifier code is required by law to have a certain format, and part of it is the date the code was generated.
Users frequently want to view items with codes generated on a specific day. For making presentation and reporting easier, as well as significantly faster, we added a computed date column that extracts the date from the code and indexed it.
Keeping an extra field in sync in code would inevitably have lead to bugs, especially as we have several different codebases which could update the id code.