Simple question (I think!)
They've been available since pg10, but I only recently discovered transition tables yesterday. Essentially they allow you to convert row-level triggers into a single statement-level trigger. It's great! I tried this example from EDB, then came up with my own and indeed I can see the performance boost.
Before I go crazy and modify all my applicable triggers, I'm just wondering about these REFERENCING tables that are now available in the trigger. Are they "free"? As in, is postgres just exposing something that was already existing in memory? Or are they actually proportionally expensive to your batch size and could require higher work_mem values to offset the time gain from calling fewer triggers?
I should probably test this myself by inspecting memory consumption with and without these transition tables. But if anyone knows the answer off-hand that'd be great!
The rows for transition tables are stored in memory and spill to disk if the transition table gets too large. So transition tables consume resources on the database server. Still, it can be more efficient than a trigger function call per row.