When a database maintains materialized views, aggregation caches, and
join precomputation tables, a single DELETE can trigger hundreds of
cascading operations. We measured a single-row DELETE taking 1.6 seconds
on a modest cross-join (100 \u00d7 200 rows) \u2014 entirely due to trigger cascades.<\/p>\n\n\n\n
This post describes how we reduced that to 12 seconds for a full test
suite that previously took 58 seconds, by batching trigger side effects
and flushing them after lock release.<\/p>\n\n\n\n
Consider a query like:<\/p>\n\n\n\n
SELECT a.grp, SUM(a.val)\nFROM customers a, regions b\nGROUP BY a.grp<\/code><\/pre>\n\n\n\nThe database materializes the cross-join into a precomputed table (20,000 rows)
and maintains an aggregation cache (the GROUP BY result) on top of it. Both
are kept in sync via triggers:<\/p>\n\n\n\n
\n- INSERT\/DELETE on
customers<\/code> \u2192 incremental update of the precomputed join<\/li>\n\n\n\n- INSERT\/DELETE on the join table \u2192 incremental update of the aggregation cache<\/li>\n\n\n\n
- DELETE on the join table \u2192 cleanup check: if no rows remain for a group key,
delete the aggregation cache entry<\/li>\n<\/ul>\n\n\n\nWhen we delete one row from customers<\/code>, the cascade looks like:<\/p>\n\n\n\n\n- Trigger scans the join table, deletes 200 matching rows (one per region)<\/li>\n\n\n\n
- Each of those 200 deletions fires:<\/li>\n<\/ol>\n\n\n\n
\n- An incremental decrement on the aggregation cache (
-1<\/code> per row)<\/li>\n\n\n\n- A cleanup scan: “does this group key still have rows?”<\/li>\n<\/ul>\n\n\n\n
\n- Total: 600+ scans for a single source DELETE<\/li>\n<\/ol>\n\n\n\n
Each cleanup scan costs ~10ms (it must verify row existence via index lookup).
200 cleanup scans \u00d7 10ms = 2 seconds of pure overhead.<\/p>\n\n\n\n
The Fix: Batched Side Effects<\/h2>\n\n\n\n
The key insight: all 200 join-table deletions happen within a single scan.
Instead of firing triggers per row during the scan (while holding locks),
we collect the side effects and flush them after the scan completes and
locks are released.<\/p>\n\n\n\n
Three types of side effects are batched:<\/p>\n\n\n\n
1. Incremental Updates (Aggregation)<\/h3>\n\n\n\n
Before: 200 individual increment(-1)<\/code> calls on the aggregation cache,
each requiring a lock acquisition and value update.<\/p>\n\n\n\nAfter: Deltas are aggregated per target row during the scan. At flush time,
one increment(-200)<\/code> call per unique group key. For 3 group keys, that’s
3 calls instead of 200.<\/p>\n\n\n\n2. Invalidation Coalescing<\/h3>\n\n\n\n
Before: 200 individual “mark as stale” calls on computed columns, each
doing a binary search to find the affected sort position.<\/p>\n\n\n\n
After: A single “invalidate all” call per computed column. One O(1) bitmap
reset instead of 200 O(log n) binary searches.<\/p>\n\n\n\n
3. Trigger Cascade Deferral<\/h3>\n\n\n\n
Before: Each of the 200 deletions immediately fires cleanup triggers while
the source scan holds a write lock. The cleanup triggers try to scan other
tables \u2192 deadlock or severe lock contention.<\/p>\n\n\n\n
After: Deleted rows are collected in a batch. The batch is flushed after the
write lock is released. Cleanup triggers run lock-free, eliminating contention.<\/p>\n\n\n\n
The Deadlock Problem<\/h2>\n\n\n\n
The most subtle issue was defer ordering. In Go, deferred calls execute in
LIFO (last-in-first-out) order. If the trigger flush is deferred after the
lock acquisition, it executes before the lock release \u2014 while still holding
the write lock. Trigger handlers that scan other tables then deadlock.<\/p>\n\n\n\n
The fix: explicit lock release before the flush, with a guard flag to prevent
double-release in the deferred cleanup:<\/p>\n\n\n\n
\/\/ During scan: side effects collected, not executed\n\/\/ After scan completes:\n1. Release write lock (explicit, set guard flag)\n2. Flush batched side effects (triggers run lock-free)\n3. Deferred cleanup skips lock release (guard flag set)<\/code><\/pre>\n\n\n\nResults<\/h2>\n\n\n\n
Test suite: incremental invalidation with cross-join GROUP BY,
concurrent INSERT\/DELETE during scans, 10k-row window functions.<\/p>\n\n\n\nMetric<\/th> Before<\/th> After<\/th> Speedup<\/th><\/tr><\/thead> Test suite duration<\/td> 58s<\/td> 12s<\/td> 4.8x<\/strong><\/td><\/tr>Single DELETE<\/td> 1.6s<\/td> ~0.3s<\/td> 5x<\/strong><\/td><\/tr>Scans per DELETE<\/td> 600+<\/td> ~10<\/td> 60x<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe improvement comes entirely from reducing redundant work:<\/p>\n\n\n\n
\n- 200 individual increments \u2192 3 aggregated increments<\/li>\n\n\n\n
- 200 individual invalidations \u2192 1 bulk invalidation<\/li>\n\n\n\n
- 200 lock-contended trigger calls \u2192 1 lock-free batch flush<\/li>\n<\/ul>\n\n\n\n
Architecture Takeaway<\/h2>\n\n\n\n
The general principle: never execute triggers inside a scan loop<\/strong>.
Collect side effects during the scan, flush after the scan. This enables:<\/p>\n\n\n\n\n- Aggregation<\/strong>: 200 \u00d7 increment(-1) \u2192 1 \u00d7 increment(-200)<\/li>\n\n\n\n
- Deduplication<\/strong>: 200 cleanup checks on 3 unique keys \u2192 3 checks<\/li>\n\n\n\n
- Lock freedom<\/strong>: trigger handlers run without holding source locks<\/li>\n\n\n\n
- Vectorization potential<\/strong>: batched triggers can be further optimized
with IN-filter scans instead of per-row equality scans<\/li>\n<\/ul>\n\n\n\nThe infrastructure supports automatic trigger vectorization: trigger bodies
are analyzed at registration time, and if they match known patterns (e.g.,
scan-with-equality-filter-on-OLD-value + $update), a batch-aware version
is generated that replaces N individual scans with one IN-filtered scan.
This is currently implemented for the join-table DELETE pattern and will
be extended to cleanup and aggregation patterns.<\/p>","protected":false},"excerpt":{"rendered":"
When a database maintains materialized views, aggregation caches, andjoin precomputation tables, a single DELETE can trigger hundreds ofcascading operations. We measured a single-row DELETE taking 1.6 secondson a modest cross-join (100 \u00d7 200 rows) \u2014 entirely due to trigger cascades. This post describes how we reduced that to 12 seconds for a full testsuite that…<\/p>","protected":false},"author":2,"featured_media":8169,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_editorskit_title_hidden":false,"_editorskit_reading_time":0,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","_uag_custom_page_level_css":"","footnotes":""},"categories":[129,128],"tags":[],"class_list":["post-8168","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-memcp","category-programming","single-item"],"featured_image_urls_v2":{"full":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"thumbnail":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-150x150.png",150,150,true],"medium":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-300x200.png",300,200,true],"medium_large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-768x512.png",751,501,true],"large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-1024x683.png",751,501,true],"1536x1536":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"2048x2048":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"trp-custom-language-flag":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-18x12.png",18,12,true],"xs-thumb":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-64x64.png",64,64,true],"appku-shop-single":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-620x500.png",620,500,true]},"post_excerpt_stackable_v2":"
When a database maintains materialized views, aggregation caches, andjoin precomputation tables, a single DELETE can trigger hundreds ofcascading operations. We measured a single-row DELETE taking 1.6 secondson a modest cross-join (100 \u00d7 200 rows) \u2014 entirely due to trigger cascades. This post describes how we reduced that to 12 seconds for a full testsuite that previously took 58 seconds, by batching trigger side effectsand flushing them after lock release. The Problem: Death by a Thousand Scans Consider a query like: SELECT a.grp, SUM(a.val) FROM customers a, regions b GROUP BY a.grp The database materializes the cross-join into a precomputed table…<\/p>\n","category_list_v2":"MemCP<\/a>, Programming<\/a>","author_info_v2":{"name":"Carl-Philip H\u00e4nsch","url":"https:\/\/launix.de\/launix\/en\/author\/carli\/"},"comments_num_v2":"0 comments","uagb_featured_image_src":{"full":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"thumbnail":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-150x150.png",150,150,true],"medium":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-300x200.png",300,200,true],"medium_large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-768x512.png",751,501,true],"large":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-1024x683.png",751,501,true],"1536x1536":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"2048x2048":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4.png",1536,1024,false],"trp-custom-language-flag":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-18x12.png",18,12,true],"xs-thumb":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-64x64.png",64,64,true],"appku-shop-single":["https:\/\/launix.de\/launix\/wp-content\/uploads\/2026\/03\/1044c82c-4b0f-4e48-84c7-240020c1c8a4-620x500.png",620,500,true]},"uagb_author_info":{"display_name":"Carl-Philip H\u00e4nsch","author_link":"https:\/\/launix.de\/launix\/en\/author\/carli\/"},"uagb_comment_info":0,"uagb_excerpt":"When a database maintains materialized views, aggregation caches, andjoin precomputation tables, a single DELETE can trigger hundreds ofcascading operations. We measured a single-row DELETE taking 1.6 secondson a modest cross-join (100 \u00d7 200 rows) \u2014 entirely due to trigger cascades. This post describes how we reduced that to 12 seconds for a full testsuite that...","_links":{"self":[{"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/posts\/8168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/comments?post=8168"}],"version-history":[{"count":1,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/posts\/8168\/revisions"}],"predecessor-version":[{"id":8170,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/posts\/8168\/revisions\/8170"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/media\/8169"}],"wp:attachment":[{"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/media?parent=8168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/categories?post=8168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/launix.de\/launix\/en\/wp-json\/wp\/v2\/tags?post=8168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}