Entropy-Aware Data Preservation with PostgreSQL Triggers

Draft written 3 September 2026 Est. 7 min read

The short answer: put the retention rule where the data is. In this PostgreSQL project, inserting a state snapshot fires a trigger that scores it and routes it to discard, compress, preserve or archive before the transaction finishes. No client can skip the classification, because no client performs it. The honest caveat comes first, though: despite the project's name, the score is not Shannon entropy and uses no logarithm — it is a weighted change-density ratio, and this article explains exactly what it computes.

The problem: time-based retention throws away the wrong rows

Systems that snapshot their own state produce data faster than anyone wants to store it. The default answer is a schedule: delete anything older than N days. That rule is indifferent to content. A snapshot recording a burst of corruption events and a snapshot identical to the one before it are the same age, so they meet the same fate.

The question the project set out to test: can the database decide for itself which of its own snapshots are worth keeping?

What the score actually computes

Start here, because the name oversells the mathematics. calculate_entropy(snapshot_id) is:

entropy = (SUM(change_weight) × COUNT(state_changes)) / snapshot_size_mb

Each recorded change is assigned a weight by fn_change_weight() — CREATE 1, UPDATE 2, DELETE 3, CORRUPTION 5 — so destructive and anomalous events count for more than routine ones. Multiplying the summed weight by the change count rewards snapshots that are both heavy and busy, and dividing by size in megabytes turns it into a density: how much consequential change per megabyte stored.

That is a reasonable retention heuristic. It is not information entropy. There is no probability distribution and no logarithm anywhere in the function. Calling it entropy promises rigour the formula does not deliver, and naming it something like change density would have described it honestly. Being precise about this matters more than the name sounding impressive.

The approach: classification as a write-time side effect

Inserting a snapshot fires ai_state_snapshots_after_insert, one of twelve triggers in the schema. That calls calculate_entropy(), stores the result in entropy_metrics, and passes it to decide_preservation().

The decision function does not hard-code its thresholds. It matches the score against rows in a preservation_rules table, each row carrying a minimum and maximum score and the decision it implies, and takes the highest matching band. Retention policy is therefore data: changing it is an UPDATE, not a deployment. A fallback covers scores that fall through every band — below 0.01 is discarded, anything else preserved — so a snapshot always receives a decision.

The decision and a generated human-readable reason are written to preservation_decisions. Nine further audit triggers route every write across the schema through fn_audit_log() into audit_logs, so a classification can be reconstructed after the fact. A PHP 8 dashboard reads three views — snapshot_summary, entropy_trends and preservation_stats — and charts them with Chart.js.

The whole thing is 10 tables, 10 PL/pgSQL functions, 12 triggers and 3 views.

Why in the database rather than in application code

The classification is a property of the data, so it holds regardless of which client wrote the row: the PHP app, a psql session, a future service, an import script. In application code the same rule would have to be duplicated into every writer, and would be silently skipped by any writer that forgot. Triggers make the guarantee structural.

The audit trail benefits in the same way. Because logging is also a trigger, there is no code path that can mutate the schema without being recorded.

Tradeoffs

Write latency for a guarantee. Scoring happens inside the transaction, so every insert pays for it. A batch job would keep writes fast but would leave a window in which snapshots are unclassified.
Logic in SQL is harder to test. PL/pgSQL has no unit-test culture to match a Python suite. The compensation is that it cannot be bypassed.
Invisible behaviour. Triggers act at a distance. A developer reading the insert statement sees nothing of the four functions it will fire, which is a real maintenance cost and the standard objection to this pattern.
Rules in a table, thresholds by judgement. Policy is pleasingly editable, but the specific band boundaries were chosen by hand and never validated against outcomes.

Limitations

The metric is a weighted change-density ratio, not Shannon entropy — the most important caveat, and the reason this article leads with it.
The domain is synthetic. "Universe state" snapshots are an academic framing and the system was exercised against sample data, not a production workload.
Decisions are fixed thresholds, not a learned policy: predictable and inspectable, but not adaptive.
It runs locally against PostgreSQL, with no hosted demo, and the bundled interface ships a single hard-coded demo login rather than real user management.
Proposed, not built: computing a genuine Shannon entropy over the distribution of change types as a second column, so the heuristic can be compared against the real measure; and validating the thresholds against retention outcomes rather than judgement.

What I would take to the next system

Two things transferred. First, put invariants where they cannot be bypassed — the same instinct that later made me validate an LLM's product IDs against a retrieved candidate set instead of asking the model to behave. Second, name a metric after what it computes. A borrowed name from information theory made the project sound more rigorous and made it harder for anyone, including me, to reason about what the number meant.