Turning Off the Triggers at Twenty Thousand Sales a Minute

For six versions, two triggers ran KC Star. Insert a sale, the insert trigger explodes it into dimension rows; change a dimension, the SCD2 trigger versions it and rebuilds the facts — synchronously, inside the writing transaction, every time. It is a beautiful model for correctness. It is a terrible model for volume. At twenty thousand sales a minute, paying a full fact rebuild inside every write transaction is how you turn a warehouse into a queue of blocked writers. V7 is the version where I turned the triggers off. This series is what replaced them: batch processing, feature flags, and automated monitoring.

Why per-row triggers hit a wall

The trigger model does its work eagerly and synchronously. Every insert into Sales pays for its own dimension explosion. Every dimension change pays for a hash recompute and a fact rebuild, right then, in the transaction that made the change. For a demo, or an operational store handling a sale every few seconds, that is invisible. The work is small and the transactions are short.

Now push the rate up. Twenty thousand sales a minute is more than three hundred a second. Each one, under the trigger model, does synchronous derivation work before its transaction can commit. The writes serialise behind their own analytics. Lock contention climbs, transaction times climb, and the operational system — the thing that actually needs to record sales — starves so the warehouse can stay instantly consistent. You are paying analytical latency on the operational write path, which is exactly backwards: the write should be cheap and the analytics should catch up.

The V6 fix scripts foreshadowed the other half of the problem. Because derivation was welded to the trigger, any data that arrived off the trigger path — a bulk load, a manual seed — silently skipped fact generation. The per-row trigger was both a scaling bottleneck and a fragile coupling. V7 breaks both by making derivation an explicit, batched, re-runnable operation.

The move: decouple write from derive

V7's core idea is to stop deriving facts during the write and start deriving them after, in batches. The operational tables get three new columns that make this possible:

ALTER TABLE Sales    ADD COLUMN BatchProcessed BOOLEAN DEFAULT FALSE;
ALTER TABLE Sales    ADD COLUMN BatchProcessedAt TIMESTAMP;
ALTER TABLE Sales    ADD COLUMN BatchId VARCHAR(50);
-- the same three columns land on SalesFact and SalesDim

BatchProcessed is the flag that changes everything. A sale is inserted with BatchProcessed = FALSE — the write does almost nothing, just records the sale and marks it unprocessed. Later, a batch job sweeps up the unprocessed sales, derives their dimensions and facts, and flips them to BatchProcessed = TRUE with a BatchProcessedAt timestamp and the BatchId that did the work. The write path is now cheap; the analytical work happens on its own schedule.

The function that feeds the batches is GetUnprocessedSales, and it carries a detail I want to highlight:

CREATE OR REPLACE FUNCTION GetUnprocessedSales(
    p_BatchSize INT DEFAULT 1000,
    p_MaxAgeMinutes INT DEFAULT 5
)
RETURNS SETOF Sales AS $$
    SELECT * FROM Sales
    WHERE BatchProcessed = FALSE
      AND CreatedDate <= now() - (p_MaxAgeMinutes || ' minutes')::interval
    ORDER BY CreatedDate ASC
    LIMIT p_BatchSize;
$$ LANGUAGE sql;

Two knobs. p_BatchSize bounds how much a single batch chews through — a thousand sales at a time by default. And p_MaxAgeMinutes, defaulting to five, is a deliberate delay: a sale is not eligible for batching until it is at least five minutes old. That age gate lets rapid corrections settle before the expensive derivation runs — if a sale is going to be edited moments after insertion, you would rather derive its facts once, after it stabilises, than churn them on every keystroke. The batch model trades instant consistency for a small, bounded staleness, and buys back enormous write throughput.

What V7 adds around the batch core

Turning off the triggers is the headline, but a batch pipeline needs scaffolding the trigger model never did, and V7 builds all of it. The rest of the series is that scaffolding:

• A batch has identity. Every run gets a BatchId, a BatchProcessingLog row, a start and end time, a processed count, and a deadline. That is part 2.
• Behaviour becomes configurable at runtime. A feature-flag system lets you turn capabilities on and off and retune settings without a deploy — part 3.
• The system watches itself. Automated monitoring scores the warehouse's health and even adjusts the batch schedule based on load — part 4.
• Old data ages out. Partitioning moves cold data off the hot tables — part 5.

The honest scale of the change

V7 is the largest jump in the project. By the changelog's own counts it goes from V6's 26 tables to 30, adds seven functions and a dozen indexes, and introduces whole subsystems — batch, features, monitoring, partitioning — that earlier versions had no equivalent of. It is also, not coincidentally, the version with the messiest history: the code was refactored into subfolders mid-development, which broke the schema and produced two passes of fix scripts I devote a whole post to. Big architectural jumps are where big scars come from, and I would rather show V7's than hide them.

Where the series goes

1. Turning off the triggers at twenty thousand sales a minute — this post.
2. A batch has a name, a log, and a deadline — batch identity, logging, and the orchestrator.
3. Feature flags inside the database — runtime configuration without deploys.
4. The scheduler that reads its own health — health-based batch scheduling.
5. Partitioning as a backup strategy — the two partition subsystems, and aging data out.
6. Two fix scripts and what they confess — the refactor that broke the schema, in two passes.
7. Running the warehouse — the V7 runbook — the maintenance and performance guidance, and the hand-off to V8.

The triggers are off. Next: what it means for a batch to have a name, a log, and a deadline.