Soft Deletes in PostgreSQL: Patterns That Won't Haunt You
Soft deletes sound simple: instead of removing a row, you mark it as deleted and move on. Then six months later, your unique constraint stops working, your full-table indexes are three times larger than they need to be, and a junior developer's ORM query accidentally returns deleted records to paying users. The "simple" solution now owns a corner of your on-call rotation.
The deleted_at column is not wrong. It is just the beginning of a conversation, not the end of one. This article walks through three patterns — ordered by complexity and payoff — so your team can make a deliberate choice instead of inheriting a mess.
Why Naive Soft Deletes Break in Production
Before the patterns, a quick audit of what goes wrong.
Unique constraints become useless. Suppose users must have a unique email per tenant. Once you soft-delete a user and try to re-register the same email, PostgreSQL sees two rows with the same value and rejects the insert — even though one row is logically gone.
Indexes carry dead weight. A B-tree index on users(tenant_id, email) holds every soft-deleted row ever written. In a multi-tenant SaaS product with moderate churn, deleted rows can easily outnumber live ones within a year.
Queries leak. Every query that touches a soft-deletable table must include WHERE deleted_at IS NULL. Forget it once — in a join, a subquery, a raw reporting query — and you surface stale data. Over time, this becomes a game of whack-a-mole.
None of these problems are unsolvable. They just require you to move beyond the bare column.
Pattern 1: Filtered Indexes
Filtered (partial) indexes are the lowest-effort, highest-leverage upgrade to the naive approach. They tell PostgreSQL to index only the rows that match a condition.
-- A unique constraint that only applies to live rows
CREATE UNIQUE INDEX idx_users_email_active
ON users (tenant_id, email)
WHERE deleted_at IS NULL;
-- A covering index for common queries — excludes deleted rows entirely
CREATE INDEX idx_users_tenant_active
ON users (tenant_id, created_at DESC)
WHERE deleted_at IS NULL;
With the filtered unique index in place, re-registering a previously deleted email works correctly. The deleted row is invisible to the constraint. The filtered covering index stays lean because PostgreSQL simply does not write deleted rows into it.
When to use this: Early-stage SaaS products, teams already using an ORM that adds WHERE deleted_at IS NULL automatically, or any schema where soft deletes affect a minority of rows. This is the right first step for almost every team.
Limitation: Queries still need the WHERE deleted_at IS NULL clause. Filtered indexes speed up correct queries; they do not protect against queries that forget the clause entirely.
Pattern 2: Row-Level Security (RLS)
Row-Level Security lets you encode the "hide deleted rows" rule inside PostgreSQL itself, at the table level. Application code — whether ORM, raw SQL, or a third-party reporting tool — cannot bypass it unless it deliberately overrides the policy.
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- A policy applied to every role except a privileged admin role
CREATE POLICY hide_deleted_users
ON users
AS RESTRICTIVE
FOR ALL
TO app_role
USING (deleted_at IS NULL);
Now every SELECT, UPDATE, and DELETE issued by app_role automatically filters deleted rows. There is no WHERE clause to forget. A junior developer's ad-hoc query, a new microservice, a BI tool connected with the application credentials — all of them see only live data.
For administrative operations (audit logs, compliance exports, restore workflows), you connect with a separate privileged role that bypasses the policy, or you add an explicit BYPASSRLS grant.
When to use this: Multi-tenant SaaS platforms where data isolation is a compliance requirement, teams using multiple data access paths (ORM + raw SQL + analytics tools), or any product where a data leak has legal or contractual consequences.
Limitation: RLS adds a small planning overhead per query. It also requires careful role design — mixing application roles and admin roles in the same connection pool is an operational footgun. Test with EXPLAIN ANALYZE to confirm the planner is handling policies efficiently.
Pattern 3: Partitioning by Status
For tables that accumulate millions of rows — event logs, audit trails, usage records — soft deletes create a different kind of pain: query performance degrades not because of missing indexes, but because PostgreSQL must scan or filter a massive heap regardless of how good your indexes are.
Table partitioning by a status column cleanly separates live and deleted rows into distinct physical segments.
CREATE TABLE events (
id BIGSERIAL,
tenant_id UUID NOT NULL,
payload JSONB,
status TEXT NOT NULL DEFAULT 'active',
created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (status);
CREATE TABLE events_active PARTITION OF events FOR VALUES IN ('active');
CREATE TABLE events_deleted PARTITION OF events FOR VALUES IN ('deleted');
When you soft-delete a row, you UPDATE events SET status = 'deleted' WHERE id = $1. PostgreSQL moves the row from events_active to events_deleted. Queries against live data hit only events_active — a fraction of the total dataset. The deleted partition can be indexed differently, archived to cheaper storage, or dropped wholesale when retention policy allows.
When to use this: High-volume event-driven systems, append-heavy tables, or any table where the ratio of deleted to live rows is expected to be large over time.
Limitation: Row updates that change the partition key (i.e., soft-deleting a row) are internally a DELETE + INSERT. This is more expensive than a simple column update and can cause table bloat if not paired with appropriate autovacuum tuning. This pattern is also operationally heavier — it earns its complexity only when the scale justifies it.
Choosing the Right Pattern
| Pattern | Best for | Key trade-off |
|---|---|---|
| Filtered indexes | Most teams, early stage | Queries must still filter manually |
| Row-level security | Multi-tenant, compliance-sensitive | Role management complexity |
| Status partitioning | High-volume, retention-heavy tables | Costlier writes, more ops overhead |
These patterns are not mutually exclusive. A mature SaaS product might use filtered indexes on most tables, RLS as a safety net across all of them, and partitioning selectively on two or three high-volume tables.
Why This Matters for Your Project
The schema decisions you make at launch compound over time. A naked deleted_at column works fine at a thousand rows; it quietly taxes every engineer on your team at ten million. Whether you are building a new SaaS product or scaling an existing one, the cost of adopting filtered indexes and RLS early is measured in hours. The cost of retrofitting them later — migrating indexes on live tables, restructuring role permissions, back-filling partitions — is measured in weekends. Pick your pattern deliberately, implement it once, and let PostgreSQL enforce the rules so your application code does not have to.
