Comment by aljgz
4 hours ago
Indexed views are much faster than trying to achieve the same result with triggers. Triggers have serious concurrency limitations, and you do recalculations even when the fields you depend on are not touched.
Indexed views are not much worse than indexes. Of course, when they refer to other tables there are underlying data lookups, but in our experience when we moved from triggers to indexed views, large scale data ingestion went way faster.
Where we used it: While revamping a large scale sales program, we stored the warehouse in/out in one table, and several things like current stock were calculated using indexed views.
Bonus: Using Snapshot concurrency control, you can do many things concurrently, and only when they both updates to a certain product in the same store you'll get the second transaction failing (which could be retried on the backend).
The fact that they are completely in-sync with your data is amazing.
PG has had incremental view maintenance on the horizon for many years. I expect it to remain on the horizon for a long time.
What you're describing is amazing, and I wish I had it available to us. We've hand rolled far too many triggers to achieve the same thing, with all the expected problems you'd assume. I'm sure it could be abused/misused, but a batteries-included approach like that would be huge.