Soft Deletes in Postgres: A Better Default Than `deleted_at`

Almost every system I have seen with soft deletes added them with a deleted_at timestamp column. It feels harmless. Six months later, every query in the codebase has WHERE deleted_at IS NULL, except the three or four that forgot, which now expose deleted data to users.

If you are about to add a deleted_at column, here is what I have learned about doing it less painfully.

Why soft delete exists at all

Legitimate reasons:

• Compliance and audit. "User deleted their account" needs to be retrievable for a specific time window.
• Reversibility. Letting a user undo a delete within some window.
• Cascading consequences. Hard-deleting a customer would cascade-delete all their data, and that is too aggressive.
• Data preserved for analytics. Even after "deletion," the row contributes to historical reports.

If none of these apply, do not add soft deletes. A real DELETE is simpler, cheaper, and harder to leak through.

The naive pattern and its problems

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

Now every read site has to filter:

SELECT * FROM users WHERE deleted_at IS NULL;

Problems:

1. Indexes are larger. They include deleted rows, even though no live query reads them.
2. Unique constraints break. A unique constraint on email no longer behaves the way the application expects, because two rows can share an email if one is deleted.
3. Foreign keys become awkward. Deleting a user does not remove the row, so child rows still pass FK validation but reference "deleted" parents.
4. Forgot to filter. Easy to miss in queries; the bug surfaces in production when a deleted user appears in a list.
5. Bloat over time. A soft-deleted row is still in the table. Over years, the table grows even if active data does not.

A better pattern

For most use cases, the cleanest soft-delete is:

1. deleted_at TIMESTAMPTZ NULL column.
2. Partial unique indexes that only consider live rows.
3. A view that exposes only live rows; queries use the view by default.
4. A scheduled job that hard-deletes rows past the retention window.

-- 1. The column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- 2. Partial unique index (replaces the old unique constraint)
DROP INDEX users_email_unique;
CREATE UNIQUE INDEX users_email_active_unique
  ON users (email)
  WHERE deleted_at IS NULL;

-- 3. The view
CREATE VIEW users_active AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- 4. Retention cleanup, scheduled
DELETE FROM users
WHERE deleted_at < now() - interval '90 days';

Now:

• Application queries users_active by default. If it forgets the filter, the view's filter kicks in.
• Email uniqueness applies only to active rows, allowing re-registration after deletion.
• The retention job hard-deletes rows older than 90 days, keeping the table from accumulating forever.

Queries that need to see deleted rows query users directly. The default is safe.

On row-level security

For stricter enforcement, row-level security can replace the view:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY users_active_only ON users
  FOR SELECT
  USING (deleted_at IS NULL);

-- Admin role bypasses RLS
CREATE POLICY users_admin_all ON users
  TO admin_role
  USING (true);

Now even ad-hoc SQL run by application roles cannot accidentally see deleted rows. This is heavier than a view but more robust against bugs.

Use with care: RLS can interact with planner choices and complicate query optimization. Add it when the data sensitivity justifies it.

When soft delete should not exist

The pattern I have removed several times: tables that started with soft deletes "just in case" and never had a real reason. A few signs:

• The retention window is "forever" because nobody set one.
• No part of the application reads deleted rows.
• The table has accumulated millions of soft-deleted rows over years.
• Queries are 95% slower than they need to be because indexes include all the cruft.

In these cases, the right move is to hard-delete the soft-deleted rows in batches and then remove the deleted_at column. The team almost always pushes back the first time the suggestion comes up; they have lived with the comfort of "we can recover anything" for so long that giving it up feels risky. After the migration, no one notices, and the database is faster.

The rule: soft delete is a feature, with maintenance costs. Earn the feature with a real use case, or do not add it.

What I add to every soft-delete migration

Whenever I add a deleted_at column, I also:

• Replace any affected unique constraints with partial unique indexes.
• Create the active-row view.
• Schedule the retention cleanup job.
• Document the retention window in a comment on the column.

COMMENT ON COLUMN users.deleted_at IS
  'Soft-delete marker. Rows older than 90 days are hard-deleted by users_cleanup nightly job.';

The comment is not just for humans; it is for the future engineer who is debugging why the table is growing or why retention is unclear. Putting the policy next to the column saves them the archaeology.