Adding a Soft Delete Flag to High Volume Tables

The Hidden Risks of Boolean Flags

High-volume database tables often face performance degradation when soft delete flags are introduced without careful planning. When billions of rows exist, adding a simple boolean column like `is_deleted` can result in massive write amplification, lock escalation, and index bloat. Simply executing an `ALTER TABLE` to add a column with a default value of `false` on a live production table with high transactional volume can lock the table for hours, causing downstream application timeouts and service outages.

Designing a Zero-Downtime Migration

To safely introduce the soft delete flag, we must avoid default values that rewrite the entire table. Instead, we add a nullable timestamp or use a boolean without a default value, backfilling historical data in small, controlled batches. This approach prevents transaction log exhaustion and keeps replication lag minimal. We also need to configure partial indexes so that queries searching for active records do not scan millions of soft-deleted rows.

-- Step 1: Add the column as nullable without a default value
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL;

-- Step 2: Create a partial index for active records
CREATE INDEX CONCURRENTLY idx_orders_active 
ON orders (user_id, created_at) 
WHERE deleted_at IS NULL;

-- Step 3: Backfill historical deletes in batches
-- Execute in a loop with a sleep interval
UPDATE orders 
SET deleted_at = NOW() 
WHERE id IN (
    SELECT id FROM orders 
    WHERE status = 'archived' AND deleted_at IS NULL 
    LIMIT 1000
);

Optimizing Query Execution and Indexes

After applying the column, existing queries must be updated to filter by the new soft delete flag. Database optimizers fall back to full table scans and destroy application performance when queries fail to utilize the partial index. Developers must audit all application queries and update the ORM configurations to automatically append the deletion filter.

• Avoid Default Values: Adding a column with a default value on older database engines forces a full table rewrite.
• Use Partial Indexes: Create indexes with a `WHERE deleted_at IS NULL` clause to keep index size small and search performance high.
• Batch Backfilling: Never update millions of rows in a single transaction; use small chunked updates to protect replication pipelines.