Skip to content

Database-Trigger Synchronization

In-database sync mechanism — instantiates Access-Optimized Redundant Representation

Uses a database trigger to update the redundant copy inside the same transaction as the source write, so the copy is never out of step — at the cost of slowing every write.

Database-Trigger Synchronization keeps a copy current by attaching a trigger to the source table, so that any write to the source also updates the copy — atomically, inside the very same transaction. Its defining move is synchrony: the copy is maintained as an inseparable part of the write, so there is no divergence window at all. Either both the source change and the copy update commit together, or neither does. That buys exactness — the copy is always precisely in step — but the cost lands on every write, which now carries the copy's maintenance, and the logic lives inside the database rather than in the application. It is the right tool for small, exact, always-current derived values, and the wrong one for anything large or high-fan-out.

Example

A discussion forum shows a reply_count next to every thread. Computing it live means a COUNT(*) over the posts table on each render of the thread list — cheap alone, ruinous at list scale. Instead an AFTER INSERT/AFTER DELETE trigger on posts increments or decrements the parent thread's reply_count in the same transaction as the post itself. The list now reads the maintained counter directly, and it is always exact — a reply and its count can never be seen apart, because they commit together. The cost surfaces only under contention: a thread going viral serializes many concurrent replies on that one thread row as each transaction updates the shared counter.

How it works

  • Trigger on the source event. A trigger fires on insert/update/delete of the source row and writes the derived copy as part of the same statement.
  • Commit atomically together. The copy update shares the source transaction, so they succeed or roll back as one — no window in which they disagree.
  • Read the maintained value directly. Consumers read the copy, trusting it is exactly current because it can never lag the source.

Tuning parameters

  • Trigger scope — which events fire it (inserts only, or updates and deletes too); narrower scope means less write overhead but less coverage.
  • Work per trigger — how much the trigger does. Tiny, bounded updates (a counter, one field) are safe; heavy recomputation makes every write expensive.
  • Transaction coupling — fully in-transaction (exact, but adds latency and lock contention) vs. deferred within the transaction.
  • Contention handling — how hot-row updates are managed, since a shared derived value serializes concurrent writers.

When it helps, and when it misleads

Its strength is exactness with zero staleness for small derived values — counters, denormalized fields, audit copies — where a lagging copy would be a bug. It misleads at scale: every write pays the trigger, hot rows serialize, and cascading triggers can deadlock, a family of costs summed up as write amplification.[1] It also hides behaviour — the copy "just updates," so an engineer debugging a mysteriously slow write may never think to look for a trigger. The classic misuse is maintaining large or high-fan-out projections this way, where the synchronous cost belongs in an asynchronous pipeline instead. The discipline is to keep trigger work tiny and bounded and to move anything heavy to change-data-capture or a background sync.

How it implements the components

Database-Trigger Synchronization realizes the synchronization side of the archetype in its strictest, synchronous form:

  • synchronization_rule — the trigger updates the copy as an atomic, in-transaction consequence of each authorized source write.
  • freshness_and_staleness_bound — because maintenance shares the write transaction, the staleness bound is effectively zero: the copy is never behind the source.

It does not define the representation it maintains (Denormalized Field Generation or Materialized View do), propagate changes asynchronously at scale or across systems (that's Change-Data-Capture Propagation), or repair a copy that has somehow gone wrong (that's Reconciliation Workflow).

Notes

Trigger logic is invisible from the application's code path — the copy updates itself, which is convenient right up until someone tracing a slow write, a deadlock, or an unexpected value has to discover that a trigger exists at all. Document trigger-maintained copies where the reading and writing code lives, not only in the database, or the maintenance becomes folklore.

References

[1] Write amplification — one logical write causing several physical writes (here, the source row plus every derived copy the trigger touches, plus any triggers those fire in turn). It is the core scaling cost of synchronous maintenance and the reason trigger work must stay small and shallow. ↩