Posted inDatabase

Soft Delete Pattern in Database: Safe Record Deletion, Recovery, and Index Optimization with PostgreSQL and MySQL

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Problem With Permanent Deletes

Early in my career, I worked on a SaaS product where a support agent accidentally deleted a customer’s account. No backups were recent enough. The data was gone. That incident alone changed how I approach data deletion forever.

Hard deletes — DELETE FROM users WHERE id = 42 — are irreversible by default. In production systems, that’s a liability. Users click the wrong button. Scripts have bugs. Admins mistype WHERE clauses. When the data is gone, so is your credibility with the customer.

I’ve worked with MySQL, PostgreSQL, and MongoDB across a dozen different projects. Each database handles data lifecycle differently. But for relational systems, soft delete is what I reach for whenever a feature needs a “delete” button. It’s saved me more than once.

What Soft Delete Actually Means

Soft delete means marking a record as deleted instead of physically removing it. The row stays in the database — you just flag it so application queries skip over it.

The most common implementation uses a nullable timestamp column:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

When a user is “deleted”, you set deleted_at = NOW(). When they’re active, it stays NULL. Every query that should respect deletions adds WHERE deleted_at IS NULL.

Some teams prefer a boolean is_deleted BOOLEAN DEFAULT FALSE. I’d push back on that. The timestamp version gives you when the deletion happened — which matters for auditing, GDPR 30-day retention windows, and debugging production incidents at 2am.

Core Columns You Should Add

  • deleted_at TIMESTAMP NULL — when the record was soft-deleted
  • deleted_by INTEGER NULL REFERENCES users(id) — who deleted it (optional but useful)

Skip a separate is_deleted boolean. It’s redundant, and it’s another column that can get out of sync with deleted_at. Just check deleted_at IS NULL and move on.

Implementing Soft Delete in PostgreSQL

Schema Setup

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  total       NUMERIC(10, 2) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMP NULL
);

Soft Delete and Restore Queries

-- Soft delete an order
UPDATE orders
SET deleted_at = NOW()
WHERE id = 101 AND deleted_at IS NULL;

-- Restore a soft-deleted order
UPDATE orders
SET deleted_at = NULL
WHERE id = 101;

-- List only active orders
SELECT * FROM orders
WHERE deleted_at IS NULL
ORDER BY created_at DESC;

-- List only deleted orders (admin recovery screen)
SELECT * FROM orders
WHERE deleted_at IS NOT NULL
ORDER BY deleted_at DESC;

Partial Index — The Key to Performance

PostgreSQL supports partial indexes — indexes that only cover rows matching a condition. For soft delete, you index only the active rows and ignore the deleted ones entirely:

-- Index only active records — much smaller, much faster
CREATE INDEX idx_orders_user_active
ON orders (user_id)
WHERE deleted_at IS NULL;

-- For time-based queries on active records
CREATE INDEX idx_orders_created_active
ON orders (created_at DESC)
WHERE deleted_at IS NULL;

Without this, a full index on user_id includes deleted rows. The query planner scans them all before filtering. On a table of 5 million rows where 80% are soft-deleted, that’s four million wasted reads per query. The partial index keeps the working set down to the ~1 million active rows. MySQL doesn’t support partial indexes natively — which is one of the reasons I prefer PostgreSQL for soft-delete-heavy schemas.

Using a View for Cleaner Application Code

Scattering WHERE deleted_at IS NULL across every query is a maintenance hazard. One engineer forgets it, and deleted records leak into the UI. A view centralizes that filter:

CREATE VIEW active_orders AS
SELECT * FROM orders
WHERE deleted_at IS NULL;

-- Application queries become simpler
SELECT * FROM active_orders WHERE user_id = 5;

PostgreSQL lets you make this an updatable view. One caveat: make sure your entire team knows the view exists and uses it consistently. A stray direct query against orders is harder to catch in code review.

Implementing Soft Delete in MySQL

Schema and Queries

MySQL syntax is nearly identical to PostgreSQL here:

CREATE TABLE products (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL DEFAULT NULL,
  INDEX idx_deleted_at (deleted_at)
);
-- Soft delete
UPDATE products SET deleted_at = NOW() WHERE id = 55;

-- Restore
UPDATE products SET deleted_at = NULL WHERE id = 55;

-- Active records only
SELECT * FROM products WHERE deleted_at IS NULL;

MySQL Partial Index Workaround

MySQL doesn’t support partial indexes. The closest workaround is a composite index with deleted_at first. Queries that filter on WHERE deleted_at IS NULL AND user_id = ? will hit the left-most column first:

-- Composite index: deleted_at + user_id
ALTER TABLE orders ADD INDEX idx_soft_user (deleted_at, user_id);

MySQL 8.0.13 added functional indexes, which opens up a cleaner option — a generated column that acts like a partial index filter:

-- MySQL 8.0.13+: generated column as a partial index substitute
ALTER TABLE orders
  ADD COLUMN is_active TINYINT(1) GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) STORED,
  ADD INDEX idx_active_user (is_active, user_id);

The generated column stores 1 for active rows and NULL for deleted ones. Index it, and you get filtering behavior close to what PostgreSQL partial indexes do natively.

Practical Tips From Real Projects

Unique Constraints Need Special Handling

Here’s a trap that bites a lot of teams. Say you have a UNIQUE constraint on users.email. A user soft-deletes their account, then re-registers with the same email address. The constraint blocks the new signup — even though the old row is “deleted”.

In PostgreSQL, the fix is a partial unique index:

-- Enforce uniqueness only on active (non-deleted) rows
CREATE UNIQUE INDEX uq_users_email_active
ON users (email)
WHERE deleted_at IS NULL;

In MySQL, one option is to move the email to a deleted_users archive table on deletion. Another is to include deleted_at in the unique key — though that breaks if the same email gets soft-deleted twice.

Foreign Keys and Cascading

Soft deletes don’t cascade automatically the way ON DELETE CASCADE does. Soft-delete a user, and their orders are still sitting there marked active. You need to handle propagation explicitly. My standard approach:

  • For parent-child relationships, soft-delete the parent and all children in a single transaction.
  • Use application-level logic or a trigger to propagate the soft delete down the chain.
-- Atomic: soft-delete user and all their orders together
BEGIN;
  UPDATE orders SET deleted_at = NOW() WHERE user_id = 42 AND deleted_at IS NULL;
  UPDATE users SET deleted_at = NOW() WHERE id = 42;
COMMIT;

Purging Old Soft-Deleted Records

Soft deletes aren’t free on storage. A table that accumulates soft-deleted rows for years will bloat, slow down backups, and complicate index maintenance. I run a periodic cleanup job to hard-delete records that have been soft-deleted for more than 90 days — adjust that window to match your data retention policy or GDPR obligations:

-- PostgreSQL: hard-delete records soft-deleted more than 90 days ago
DELETE FROM orders
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '90 days';

MySQL equivalent:

DELETE FROM orders
WHERE deleted_at IS NOT NULL
  AND deleted_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

Schedule this as a cron job or database event. Always test on staging first. On large tables, batch the deletes — say 10,000 rows at a time — to avoid holding table locks for too long.

ORM Integration

You don’t have to wire this up manually. Sequelize, Django ORM, Laravel’s Eloquent, and SQLAlchemy all have soft delete support — either built-in or via a small plugin. In Python with SQLAlchemy:

from sqlalchemy import Column, DateTime, func
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)
    deleted_at = Column(DateTime, nullable=True, default=None)

    def soft_delete(self, session):
        self.deleted_at = func.now()
        session.flush()

    def restore(self, session):
        self.deleted_at = None
        session.flush()

    @classmethod
    def active(cls, session):
        return session.query(cls).filter(cls.deleted_at.is_(None))

The SQLAlchemy-SoftDelete library and Django’s custom managers can apply the filter globally at the model level. That means every query on that model automatically excludes deleted rows — no one on the team can forget the WHERE clause, because it’s never their responsibility to add it.

When Soft Delete Is the Wrong Choice

Soft delete doesn’t fit every situation. Three cases where I’d skip it:

  • High-volume event or log tables — rows are immutable by design. Archive or partition instead of flagging deletions.
  • Extremely large tables — if 80% of records end up soft-deleted, every active query is filtering out most of the table. An archive table is a cleaner split.
  • Strict GDPR right-to-erasure requirements — soft delete alone doesn’t satisfy Article 17. You still need a purge mechanism that actually removes personal data. Soft delete buys you a recovery window; it doesn’t replace real erasure.

Making It Stick

One deleted_at column. That’s the whole schema change. But it’s bought me the ability to recover misfired deletions in under five seconds — one UPDATE, job done. The partial index in PostgreSQL keeps read performance solid as deleted rows pile up. The 90-day purge job prevents the table from bloating indefinitely.

None of it works if the filter is applied inconsistently. Lock it down at the infrastructure level — views, ORM query scopes, or PostgreSQL row-level security. Do it once, do it right, and the rest of the team never has to think about it again.

Share:
Tags: