3 ms·
> Battle-tested at instacart If you don't mind me asking, for how long? I've found using EXECUTE in insert triggers to be very problematic - it consumes a txi
by ak4g 10y ago
> Battle-tested at instacart
If you don't mind me asking, for how long?
I've found using EXECUTE in insert triggers to be very problematic - it consumes a txid, AIUI, and every 200m inserts, a bunch of anti-wraparound autovacuums have to to scan allofthethings. My insert triggers have big blocks of IF/ELSIF branches (although it's straightforward enough to construct those in pl/pgsql, just requires an extra level of indirection when preparing the partitioning).
- akane 10y agoWe've been using the tool for around 5 months, but the same approach/triggers for over a year. I wasn't aware the IF/ELSIF trigger approach had potential advantages over EXECUTE, but will definitely look into it.
- anarazel 10y agoEXECUTE itself doesn't cause xid consumption. The increased cost over "plain" queries comes from re-parsing and re-planning queries.
- dfsegoat 10y agoAs a recent postgres convert (from mysql) -- would be interested to know more about the general pattern or examples which I might keep in mind for: My insert triggers have big blocks of IF/ELSIF branches
- akane 10y agoUpdate: Hey ak4g, the latest version of pgslice uses this approach. We're seeing roughly 20% less load on inserts with it (in the short time it's been in production). Thanks for the suggestion!