Stop Using DELETE in Production: Hard Delete vs Soft Delete in High-Concurrency Systems

The Scene: A Simple DELETE That Broke Everything

Friday evening. A backend engineer deploys a cleanup job to production:

-- "Just cleaning up cancelled orders, what could go wrong?"
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

Within minutes:

• API response times jump from 45ms to 3,200ms
• Connection pool saturates — new requests start queuing
• Replica lag spikes to 38 seconds
• Alerts fire across the dashboard

The query deleted 120,000 rows. On a small table, this would take milliseconds. But the orders table had 22M rows, 5 secondary indexes, and 400 concurrent transactions. That simple DELETE turned into a system-wide incident.

Let's understand exactly why.

What DELETE Actually Does Inside InnoDB

When you run DELETE FROM orders WHERE id = 42, here's what the database engine (InnoDB) actually does:

That's one row. Now multiply by 120,000 rows. And each secondary index removal involves a full B-tree traversal — seek to the leaf page, remove the entry, and potentially merge underfilled pages.

The Index Tax: Why DELETE Is So Expensive

This is the part most engineers miss. The row itself is cheap to remove. The indexes are where the real cost lives.

B-tree Page Merges: The Hidden Killer

When you delete entries from a B-tree index, leaf pages become underfilled. When a page drops below ~50% capacity, InnoDB merges it with a neighbor. This merge operation:

• Locks both pages
• Copies entries from one page to another
• Updates parent pointers
• May cascade upward if the parent becomes underfilled

With bulk deletes, these merges cascade across the entire index tree. This is why your I/O spikes during mass deletions.

Why findOne + UPDATE (Soft Delete) Is Dramatically Cheaper

Now let's look at what happens with a soft delete:

-- Instead of DELETE:
UPDATE orders
SET deleted_at = NOW()
WHERE id = 42;

Here's what the engine does:

The key insight: UPDATE on a non-indexed column doesn't touch any secondary index. The deleted_at column exists only in the clustered index row. InnoDB modifies it in-place — same page, same slot. No B-tree traversals on secondary indexes. No page merges. No cascading rebalancing.

Side-by-Side: DELETE vs Soft Delete Cost

The Concurrency Impact: Locks Under Load

This is where the difference becomes critical in production.

DELETE holds the exclusive row lock for the entire duration of all index operations. With 5 secondary indexes, InnoDB holds that lock 5-6x longer than a simple UPDATE needs. Under high concurrency, this means:

• Lock wait chains — transactions accumulate waiting for the row lock
• Connection pool saturation — waiting transactions hold connections idle
• Replica lag — the replication thread must replay all index operations sequentially

Benchmark: Real Numbers

Test setup: MySQL 8.0, orders table with 10M rows, 5 secondary indexes, 100 concurrent connections, deleting/soft-deleting 50,000 rows.

The numbers speak for themselves. DELETE is not just slower — it creates cascading effects (replica lag, deadlocks, page merges) that impact every other query running on the system.

The Implementation Pattern

Here's how to implement soft delete correctly:

-- Step 1: Add the soft delete column
ALTER TABLE orders ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;

-- Step 2: Add a partial index (PostgreSQL) or generated column index (MySQL)
-- PostgreSQL:
CREATE INDEX idx_orders_active ON orders (id) WHERE deleted_at IS NULL;

-- MySQL (no partial indexes — use a generated column):
ALTER TABLE orders
  ADD COLUMN is_active TINYINT GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) STORED;
CREATE INDEX idx_orders_active ON orders (is_active);

-- Step 3: Your queries now filter on deleted_at
-- Before (hard delete world — no filter needed):
SELECT * FROM orders WHERE customer_id = 1087;

-- After (soft delete — add the filter):
SELECT * FROM orders WHERE customer_id = 1087 AND deleted_at IS NULL;

-- Step 4: Application-level helper (Node.js / Prisma example)
-- In your ORM or query builder, add a default scope:

-- Prisma middleware example:
-- prisma.$use(async (params, next) => {
--   if (params.action === 'findMany' || params.action === 'findFirst') {
--     params.args.where = { ...params.args.where, deleted_at: null };
--   }
--   return next(params);
-- });

-- Step 5: Periodic hard-delete purge (off-peak hours)
-- Run during low-traffic windows (e.g., 3 AM Sunday)
DELETE FROM orders
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL 90 DAY
LIMIT 1000;
-- Use LIMIT to batch — never bulk-delete in one shot
-- Run in a loop with 500ms sleep between batches

When Hard DELETE IS the Right Choice

Soft delete isn't always the answer. Use hard DELETE when:

For GDPR, the pattern is: soft delete immediately (fast), then run a compliance purge job that does the hard DELETE in batches during off-peak hours. You get fast user-facing response AND true erasure within your SLA.

Common Mistakes Engineers Make

-- MISTAKE 1: Indexing the deleted_at column unnecessarily
CREATE INDEX idx_deleted ON orders (deleted_at);
-- ❌ This defeats the purpose — now UPDATE touches this index too

-- CORRECT: Use partial index or filter in composite indexes
-- PostgreSQL:
CREATE INDEX idx_orders_customer ON orders (customer_id) WHERE deleted_at IS NULL;

-- MISTAKE 2: Bulk DELETE without LIMIT
DELETE FROM orders WHERE status = 'cancelled';
-- ❌ Locks millions of rows in one transaction

-- CORRECT: Batch with LIMIT
DELETE FROM orders WHERE status = 'cancelled' LIMIT 1000;
-- Run in a loop with delay between batches

-- MISTAKE 3: Forgetting to filter soft-deleted rows
SELECT COUNT(*) FROM orders WHERE customer_id = 1087;
-- ❌ Includes soft-deleted rows — wrong count!

-- CORRECT: Always include the filter
SELECT COUNT(*) FROM orders
WHERE customer_id = 1087 AND deleted_at IS NULL;

The Decision Framework

The bottom line

DELETE is the most expensive DML operation in a relational database. It is expensive not because removing one row is hard, but because the index maintenance cost scales with the number of secondary indexes. Every index doubles the work. In a high-concurrency system, that extra work translates directly into longer lock hold times, more contention, and cascading failures. Soft delete with UPDATE SET deleted_at = NOW() sidesteps the entire index tax. Use it as your default. Batch your real deletes for off-peak hours. Your production database will thank you.