Soft Deletes, Audit Trails, and Data Retention in PostgreSQL: Getting It Right Before You Scale

A support ticket comes in: “My invoice disappeared.” You check the database. It’s gone — actually gone, DELETE FROM invoices WHERE id = ... gone, from a cleanup script someone ran eighteen months ago. No audit table. No deleted_at column. No way to tell who deleted it, when, or why. You can restore from a backup, maybe, if the backup predates the delete and postdates everything the customer did after it. That’s the best case. The worse case is that nobody even notices until a compliance audit asks for a record you can’t produce.

This article is about avoiding that conversation entirely. Soft deletes, audit trails, and data retention are usually treated as three separate concerns — a column here, a logging library there, a cron job somewhere else — but they’re really one decision: how does your database remember what happened, and for how long? Get that decision right while your tables have thousands of rows, and it costs you an afternoon. Get it wrong and revisit it once your tables have hundreds of millions of rows, and it costs you a migration project with downtime windows and stakeholder meetings.

The direct answer

If you need a working default today: add created_at and updated_at timestamp columns to every table, replace hard deletes with a deleted_at nullable timestamp (soft delete), and capture row-level changes in a dedicated audit table populated by a trigger. Pair that with a retention policy that maps each category of data to a retention period, a storage tier, and any compliance tags it needs. That combination — soft delete column, audit trigger, retention policy — is the baseline that survives contact with scale. Everything below explains why, and what to do when the baseline isn’t enough.

Why these three things are actually one decision

Soft deletes, audit logs, and retention policies all answer variations of the same question: what happened to this row, and can we still see it? If you soft-delete a row but never log who deleted it or when, you have half an audit trail. If you log every change but never decide how long to keep that log, you’ll eventually have a multi-terabyte table nobody’s allowed to touch. If you set a retention policy without deciding what “deleted” even means in your schema, you don’t know what you’re retroactively destroying.

They’re coupled at the schema level, too. Your soft-delete column interacts with your indexes. Your audit table interacts with your foreign keys. Your retention policy interacts with your partitioning strategy. Designing them independently is how teams end up with three inconsistent, half-working systems instead of one coherent one.

Hard delete vs. soft delete: the real tradeoff

A hard delete is a DELETE statement. The row is gone, the storage is eventually reclaimed, and unless something captured the row beforehand, the data is unrecoverable from the database itself. The tradeoff is table size stays minimal and queries never need to filter out dead rows — but you lose the ability to answer “what did this look like before,” you lose recovery options if the delete was a mistake, and you risk ghost rows in related tables if your foreign key rules aren’t airtight.

A soft delete replaces the DELETE with an UPDATE that sets a deleted_at timestamp (or an is_deleted boolean, though the timestamp is strictly more useful since it also tells you when). The row stays in the table; your application filters it out of normal queries. This preserves data integrity and gives you a recovery path, which is why it’s the recommended default over hard deletes for business data.

The tradeoff you’re accepting with soft deletes is that your table never actually shrinks from user-initiated deletions, every query that should only see “active” rows needs a WHERE deleted_at IS NULL clause (or a view that bakes that in), and you need a real plan for what “soft-deleted” rows eventually become — because “never truly delete anything” is not a retention policy, it’s an accident waiting for a compliance request.

Foreign keys complicate both options. If orders soft-deletes but payments has a hard foreign key to it, you haven’t lost referential integrity — the row’s still there — but if a hard delete of a parent row is ever genuinely needed, your ON DELETE rule decides what happens downstream. CASCADE should only be used when you’re genuinely confident about the blast radius, because it will delete every dependent row without asking twice; RESTRICT (or an equivalent guard) is the safer default when deleting the parent would silently destroy historical records or break reporting. Test these rules against realistic sample data before you trust them in production, because a cascade rule that looked reasonable in a design doc can delete far more than anyone expected once real relationships are in play. Foreign key constraints matter here precisely because they make the database itself refuse to create orphans — a payment with no order, a subscription pointing at a user that no longer exists — rather than relying on application code to remember.

Soft delete implementation pattern

The mechanical pattern is simple to state and easy to get subtly wrong:

ALTER TABLE invoices
  ADD COLUMN deleted_at TIMESTAMPTZ;

-- "Delete" becomes:
UPDATE invoices
SET deleted_at = now()
WHERE id = $1;

-- Every query that means "active rows only":
SELECT * FROM invoices
WHERE deleted_at IS NULL;

Use TIMESTAMPTZ, not TIMESTAMP — storing timezone-naive timestamps is one of those decisions that looks harmless until your team spans time zones or your hosting region changes. A nullable deleted_at is preferable to a is_deleted BOOLEAN because it carries strictly more information for the same storage cost: you get the “is it deleted” boolean for free (deleted_at IS NOT NULL) plus the timestamp of when, at no extra column.

The part that bites teams later is indexing. If 95% of your rows are soft-deleted (common in tables that accumulate drafts, cancellations, or expired records) and your application only ever queries active rows, a plain index on, say, (customer_id) forces the planner to wade through dead rows it will discard. A partial index fixes this directly:

CREATE INDEX idx_invoices_active_customer
  ON invoices (customer_id)
  WHERE deleted_at IS NULL;

This index only contains active rows, so it stays small and fast regardless of how many soft-deleted rows pile up behind it. Run EXPLAIN ANALYZE on your hot queries before and after adding a partial index — if the planner isn’t picking it up, check that your WHERE clause matches the index predicate exactly, since Postgres won’t use a partial index for a query it can’t prove satisfies the predicate.

A view that encapsulates the filter is worth the five minutes it takes to write:

CREATE VIEW active_invoices AS
  SELECT * FROM invoices WHERE deleted_at IS NULL;

It won’t save you from every mistake, but it reduces the number of places a missing WHERE deleted_at IS NULL can leak soft-deleted rows into a report or an API response.

Audit trail / audit log design — a worked example

A soft delete tells you a row is gone and when. It doesn’t tell you who deleted it, what it looked like before, or what else changed along the way. That’s the job of an audit log, and the most portable way to build one in plain PostgreSQL is a trigger writing to a shadow table.

CREATE TABLE audit_log (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name    TEXT NOT NULL,
  row_id        UUID NOT NULL,
  action        TEXT NOT NULL,       -- 'INSERT', 'UPDATE', 'DELETE'
  changed_by    UUID,                -- app-level user id, nullable for system actions
  changed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  old_data      JSONB,
  new_data      JSONB
);

CREATE INDEX idx_audit_log_row ON audit_log (table_name, row_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

CREATE OR REPLACE FUNCTION fn_audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, row_id, action, changed_by, old_data)
    VALUES (TG_TABLE_NAME, OLD.id, TG_OP, current_setting('app.current_user_id', true)::UUID, to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, row_id, action, changed_by, old_data, new_data)
    VALUES (TG_TABLE_NAME, NEW.id, TG_OP, current_setting('app.current_user_id', true)::UUID, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, row_id, action, changed_by, new_data)
    VALUES (TG_TABLE_NAME, NEW.id, TG_OP, current_setting('app.current_user_id', true)::UUID, to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_invoices_audit
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

Set app.current_user_id at the start of each request or transaction (SELECT set_config('app.current_user_id', $1, true)) so the trigger can attribute changes to a real person instead of just “something changed”. Since soft deletes are UPDATEs, not DELETEs, this same trigger captures them automatically — the deleted_at transition shows up as an UPDATE row in audit_log with the before/after state, which is exactly what “who deleted what and when” means in a soft-delete world.

This is one of four legitimate mechanisms for capturing changes, and it’s worth knowing the other three so you pick on purpose rather than by default:

• Timestamp polling — periodically query rows where updated_at > last_checked. Simple, but it misses hard deletes entirely (a deleted row has no updated_at to poll) and isn’t real-time.
• Triggers + shadow audit table — the pattern above. Near real-time, catches deletes, works entirely inside Postgres, but adds write amplification: every write to the source table now triggers a second write to the audit table.
• LISTEN/NOTIFY — Postgres’s built-in pub/sub. It’s real-time and cheap on the database, but it is not a durable audit store: notifications are at-most-once, there’s no replay for anything missed while a listener was offline, and each payload is capped at 8000 bytes. Use it to wake up a worker process, not to record history.
• Logical replication via the write-ahead log (WAL) — Postgres streams every change from its WAL through a replication slot to a consumer like Debezium. This is near-zero overhead on the source table, real-time, and catches deletes, because it reads the actual commit stream rather than querying table state.

CDC via WAL deserves a closer look because it solves a problem triggers don’t: it reconstructs not just the current state but every state a row passed through, in commit order, which a point-in-time snapshot can never do. If you need a downstream system — a search index, a warehouse, a cache — to mirror your Postgres data with full fidelity including deletes, logical replication is the mechanism built for that job, whereas a trigger-based audit table is the mechanism built for in-database history you can query with plain SQL. They’re not mutually exclusive; plenty of systems use triggers for the queryable audit trail and WAL-based CDC for downstream replication, because they answer different questions.

Data retention: deciding how long to keep any of this

An audit table with no retention policy is a liability with a timestamp column. Decide retention per category of data, not globally, because an access log and a financial audit trail don’t carry the same weight. A workable retention design maps each log or record type to three attributes: how long you keep it, where it lives while you keep it, and what compliance regime it’s tagged for. As a hypothetical template to illustrate the shape of such a mapping: audit logs kept for N years and moved to cold storage after M days, tagged against whatever compliance regimes apply to you; access logs kept for a shorter M days; error logs kept for some other M days, moved to warm storage after a shorter interval, tagged accordingly. Treat this purely as a template to adapt, not values to copy — your actual retention obligations depend on your jurisdiction, industry, and contracts, and must be confirmed with legal counsel rather than inferred from an example like this.

Three forces generally set the retention period: what compliance regimes require, how people actually query the data in practice for operational debugging, and how long incident investigations tend to run. Where those three disagree — compliance wants years, engineers want a much shorter operational window — that’s exactly why tiering exists: keep a short hot window for operational access, and archive the rest somewhere cheaper but still retrievable.

GDPR’s right-to-erasure deserves a specific caution: it can conflict with audit retention requirements, since “delete this person’s data” and “keep seven years of audit logs” are not automatically compatible, and reconciling them is a legal and architectural question specific to your situation — this article can flag the tension but can’t resolve it for you generically, so treat it as a conversation with counsel, not a schema decision you make solo.

Storage tiering and the archival lifecycle

Not all retained data needs to be equally accessible. A hot/warm/cold tiering model keeps recently-written, frequently-queried data on fast storage, moves aging data to cheaper warm storage, and eventually archives the oldest data to cold storage with compression. The economics can be dramatic: a tiered hot/warm/cold architecture can cut storage cost substantially compared with keeping the same volume entirely in high-performance “all-hot” storage, for data most of which is rarely, if ever, read again.

The catch with cold storage is restoration latency. Glacier-class cold tiers used for older compliance data can take hours to restore a file, which is fine for a compliance audit with a scheduled response window but useless for an engineer trying to answer a page at 2 a.m.. Design your tiering so nothing time-sensitive ever lands exclusively in a slow-restore tier.

One practical way to make retention cheaper in PostgreSQL is to partition a large, append-heavy table like audit_log by time range — monthly, for example. With this structure, “archive data older than N months” becomes dropping or detaching a partition rather than running a slow DELETE against a monolithic table, which avoids both the long-running query and the bloat it would otherwise leave behind. A detached partition can then be exported and compressed with whatever tooling you already use before moving it to cold storage.

Indexing, replication, and getting data to the warehouse

Every index you add to a high-write table like audit_log has a cost on every insert, so index deliberately: an index on (table_name, row_id) for “show me this record’s history” and one on changed_at for retention sweeps are usually enough — resist the urge to index every column just because a report might someday filter on it. Use EXPLAIN ANALYZE to confirm the planner is actually using what you built rather than assuming it.

When audit or operational data needs to reach a data warehouse, the cheap path is incremental sync on an updated_at cursor, and the cheap path has a sharp edge: it silently misses hard deletes, because a deleted row produces no updated_at to pick up on the next sync pass. This is precisely why teams needing near-real-time replication that includes deletes reach for CDC tooling such as Debezium paired with Kafka instead of cursor-based polling. If your audit table already captures deletes as rows (as the trigger pattern above does), a cursor-based sync of the audit table itself sidesteps this gap, since deletes show up as ordinary new rows rather than disappearing data.

Defaults worth adopting before you scale

Decision	Recommended default	Why it’s costly to change later
Timestamps	created_at, updated_at on every table	Retrofitting requires backfilling historical values you may not have
Deletes	Soft delete via deleted_at TIMESTAMPTZ, not hard DELETE	Converting hard-delete history into soft-delete history after the fact is impossible — the data’s gone
Change tracking	Trigger-based audit table from day one	Adding triggers to a live, huge table means a backfill with no source data for past changes
Foreign keys	RESTRICT by default; CASCADE only with tested confidence	A wrong cascade rule discovered in production can delete data you can’t get back
Large audit/log tables	Partition by month from the start	Partitioning an existing multi-hundred-million-row table without downtime is a major project, not a migration

That last row is the crux of the whole article. Renaming a column on a table with hundreds of millions of rows can’t be done casually — it takes real engineering effort or a downtime window, in a way that refactoring application code simply doesn’t. Deletion strategy, audit design, and retention policy are exactly this category of decision: cheap to set correctly now, expensive to retrofit later. Treat them as infrastructure decisions, not application features, and make them once, on purpose, before the table sizes make “once” your only option.

FAQ

Do I need both a soft-delete column and an audit table? Yes, if you need to know who did something and when, not just that it happened. A deleted_at column tells you a row is inactive and roughly when it became inactive; it doesn’t by itself tell you who deleted it or what else was changing around it. An audit trigger captures the deleted_at transition along with everything else, which is why the two are typically implemented together rather than as alternatives.

Should audit log writes happen in the same transaction as the original change? A trigger-based audit table writes in the same transaction by construction, which guarantees the audit row only exists if the original change committed. That consistency is one of the main reasons to choose triggers over an out-of-band mechanism for in-database audit history, even though it adds write amplification to every transaction.

Is logical replication a replacement for a trigger-based audit table? No — they solve different problems. CDC via the WAL is built for replicating changes to external systems in commit order with minimal overhead; a trigger-based audit table is built for in-database, SQL-queryable history. Many systems use both for different purposes.

What happens to audit logs under a right-to-erasure request? This is a genuine tension between erasure obligations and audit retention requirements, and the right answer depends on your specific legal exposure — it is not a purely technical decision, and this article can’t give you a generic schema fix for it.