Don't Use Your Production Database as a Log

A production database should describe the current state of the world: the data your systems need to function right now. It's tempting to also make it remember the past — keeping a column that records when something changed, or never overwriting a row so the old version sticks around. This is usually a mistake. If you need history, the right move is to record an actual log of state changes, kept separate from the database that runs production.

Mixing the two gives you the worst of both worlds. Your production schema gets more complicated and harder to migrate. It becomes harder to tell which data actually affects production versus what's only there for posterity. Performance is typically worse, and you're paying to keep historical data on your most expensive, most replicated systems. Meanwhile the log you get out of this is a bad log, because you almost never capture every state change — only the fragments you happened to design for.

The half-measures

Two patterns show up over and over.

The first is using a timestamp instead of a boolean — deleted_at instead of is_deleted, say. This isn't really harmful on its own, which is exactly why it's dangerous^[3]: it gives a false sense of having solved the history problem. It only works for booleans, and only for the specific ones you remembered to do it for. Worse, it records only the latest transition. I've frequently wanted to know when a row changed, only to find it had changed again after the moment I cared about, so the timestamp I needed was already gone.

The second is making records write-only — never updating a row, always inserting a new version. This is genuinely useful when production itself needs the old versions. But when it doesn't, you've made everything harder for no production benefit: every query now has to select the latest version of each row, which is especially painful when you want multiple results, and performance degrades as the table fills with dead history^[4].

The crux

Here's the part I expect some disagreement on. I think even the non-harmful version of these patterns is a bad idea, because it's a partial solution to a problem that has a clean full solution. If you actually want history, a full log is much nicer — and it turns out to be surprisingly easy to get. A full log gives you every version and every change time of every column, not just the last change time of the booleans you remembered. Capturing it also keeps your production database simpler, faster, and cheaper, because the history lives somewhere else: a slower, less expensive, less replicated system that's fine for data you query rarely.

To be clear about the boundary, this isn't an argument against created_at and updated_at. For plenty of systems those columns are genuinely enough, and you shouldn't build anything fancier. The argument is about what to do once you need more than that. At that point the temptation is to accrete more half-measures — another timestamp here, a history flag there — around a handful of columns. That's the moment to go all the way instead.

It's easier than it sounds

In Postgres, the temporal_tables extension is a set of functions and triggers that let you keep writing normal updates to your real table while every change is logged to a separate table with a validity time. There are other extensions too, plus a temporal tables feature in recent core Postgres. Because the trigger-based version is nothing but functions and triggers, you can even attach it only to a replica, so the history exists entirely outside your main production database.

At a scale where it's worth the effort — which it isn't for most startups — you can skip triggers and stream the write-ahead log into another system, building the structured log there and reducing load on production. A common shape is Change Data Capture: Postgres → Debezium → Kafka → Clickhouse. The catch is that you have to configure it to keep the raw change events rather than deduplicating down to the final state, which is the default in a lot of setups.

There are edge cases. With the triggers, your log tables need to contain every column that has ever existed, since a missing column is silently dropped from the log, and you need to drop constraints, because historical rows aren't necessarily valid under the current schema. For the CDC path the details are more involved — the main thing I know is that the consumer must never get blocked — and I'll leave the specifics as an exercise for anyone operating at that scale. And as with any replication, you can do real damage if your replication clients fall too far behind.

Where this comes from

I ran into this on a system that integrated with client systems, where we regularly needed to answer questions like "when was this user account created?" or "what did the original version of this job application look like?" — and could only sometimes answer them from our database. We seriously considered a large rewrite to make everything write-only, then abandoned it once we realized the queries would be a nightmare, the performance would be abysmal, and the temporal-table triggers solved every one of our problems far more simply.

My direct experience here is with Postgres. I'm fairly confident SQL Server supports the same idea, and I'd guess most databases can do something equivalent, especially since the Postgres version needs nothing more than functions and triggers.