We use indexed-views in MSSQL for this and they’re great for many kinds of things - but they also kinda become almost like TRIGGERs and might ruin your day if the execution-plan-generation gods feel like it.
…and the lack of a LEFT OUTER JOIN also kills it for a lot of things too.
One problem is that a Txn won’t commit until all secondary indexes are updated, but I’d prefer it if there was a way for a Txn to unblock its caller once the base-table update is saved; updates to secondary indexes could then continue asynchronously. (I know this is hardly an original idea, but I haven’t read why this can’t be done)
…and the lack of a LEFT OUTER JOIN also kills it for a lot of things too.
One problem is that a Txn won’t commit until all secondary indexes are updated, but I’d prefer it if there was a way for a Txn to unblock its caller once the base-table update is saved; updates to secondary indexes could then continue asynchronously. (I know this is hardly an original idea, but I haven’t read why this can’t be done)