It’s Tuesday at 11:42 AM and the on-call Slack channel is lighting up. Checkout latency has crossed 3 seconds. The orders page takes 12 seconds to load for customer support reps. Marcus, a platform engineer who joined eight months ago, runs EXPLAIN ANALYZE on the main query and stares at the output: sequential scan, 800 million rows examined, 720 million of them have deleted=true. The database is spending 94% of its effort reading ghosts.

Think of it like a filing cabinet where 90% of the folders contain shredded paper that nobody took to the dumpster. Every time someone opens the drawer looking for an active file, they have to flip past hundreds of dead folders first. The drawer is full, the office paid for a bigger cabinet, then that one filled up too. Nobody noticed because the shredded paper accumulated one folder at a time over three years.

The story is depressingly common. A developer in 2021 implemented soft deletes the textbook way: UPDATE orders SET deleted = true, deleted_at = NOW() WHERE id = ?. Clean, reversible, audit-friendly. The PR got two approvals. Nobody asked what happens when the table hits a hundred million deleted rows. Or five hundred million. Or eight hundred million.

Three years later the orders table weighs 210GB on disk. Only 28GB of that is live data. The B-tree indexes span the full 800M rows because PostgreSQL’s standard indexes don’t discriminate by column value. Backups take 4.5 hours. Point-in-time recovery is a full-day operation. The monthly AWS bill for the RDS instance has quietly climbed from $1,200 to $4,800 because they had to upsize twice to keep the working set in RAM.

This is the data lifecycle management problem. Not a bug in the application logic, not a missing index, not a bad query plan. It’s the absence of a policy for what happens to data after it stops being useful to the application.

Why This Happens

Soft deletes are a reasonable default for any system that needs undo capability, audit trails, or regulatory compliance. The implementation is trivial and the immediate cost is zero. The problem is purely temporal: cost compounds invisibly until a threshold is crossed.

The failure chain looks like this:

Year 0: Developer adds deleted=true column
  → Table: 2M rows, queries fast, nobody notices
    → Year 1: 80M rows, 60M deleted
      → Index size doubles, still fits in RAM, P99 climbs from 4ms to 18ms
        → Year 2: 350M rows, 300M deleted
          → Buffer cache thrashing - working set exceeds shared_buffers
            → Sequential scans triggered - planner chooses seq over index
              → Year 3: 800M rows, 720M deleted
                → P99: 1.2s, VACUUM runs 6+ hours, backup window exceeded
                  → Emergency: storage 92% full, read replicas falling behind

The root cause is that soft deletes violate a core assumption of relational database design: that the majority of rows in a table are relevant to the majority of queries. When 90% of rows are irrelevant to 99% of queries, every optimization the database applies - buffer pool management, index traversal, statistics estimation, vacuum and bloat recovery - works against you.

PostgreSQL’s VACUUM process deserves special mention. VACUUM reclaims space from dead tuples - rows that have been updated or deleted at the transaction level. But soft-deleted rows aren’t dead tuples. They’re live rows with a flag set to true. VACUUM doesn’t touch them. The table bloats not because of transaction overhead but because of data that simply shouldn’t be there anymore.

The Naive Solution

The first instinct is to add a partial index and call it done:

CREATE INDEX idx_orders_active ON orders (created_at, customer_id)
WHERE deleted = false;

This helps read queries immediately. The index only covers the 80M active rows, so lookups are fast again. Engineers declare victory, close the ticket, move on.