Foreign Keys vs Performance (Part 3): The CASCADE DELETE Story
Published at Jan 19, 2026
Part 3 of 3: This article focuses on DELETE operations (CASCADE deletes and alternatives). See Part 1 for WRITE operations (INSERT) and Part 2 for READ operations (SELECT).
⚡ 100% Real Data: All numbers in this article come from actual BenchmarkDotNet measurements on PostgreSQL 16 with 1M transactions. Source code available on GitHub.
The Final Piece: DELETE Operations
This is the final chapter of my weekend deep-dive into Foreign Key performance. What started with shock at seeing a production database without FKs turned into a comprehensive benchmarking journey.
The story so far:
Part 1 (INSERT Operations):
- Batch inserts: 32.7% overhead
- Concurrent inserts: 0.16% overhead (lock contention dominates)
- Mixed operations: 6.4% overhead
Part 2 (SELECT Operations):
- Simple SELECT: 0.75ms vs 0.88ms (negligible difference)
- Multi-table JOIN: 1.62ms (identical performance)
- Complex analytics: 23.26ms vs 23.51ms (< 1% difference)
But there’s one operation everyone warns about with Foreign Keys: CASCADE deletes.
The CASCADE Delete Problem
CASCADE deletes are often cited as the “gotcha” of Foreign Keys. You’ve probably heard:
“CASCADE deletes are 20-25× slower than manual cleanup!”
Is this true? Let’s find out with real benchmarks.
The Scenario
CREATE TABLE transactions (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
-- ... other columns
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);What happens when you delete a user with 100, 1,000, or 10,000 transactions?
Benchmark: CASCADE Delete Performance
Using the same PostgreSQL 16 setup from Parts 1 and 2:
- 100,000 users
- 250,000 accounts
- 5,000 merchants
- 10 categories
- 1,000,000 transactions
We’ll test:
- CASCADE delete (WITH FK):
DELETE FROM users WHERE id = X - Manual cleanup (WITHOUT FK):
DELETE FROM transactions; DELETE FROM users - Soft delete:
UPDATE users SET deleted_at = NOW()
Test Setup
-- Scenario 1: Small volume (100 transactions per user)
DELETE FROM users WHERE id = 12345;
-- Scenario 2: Medium volume (1,000 transactions per user)
DELETE FROM users WHERE id = 23456;
-- Scenario 3: Large volume (10,000 transactions per user)
DELETE FROM users WHERE id = 34567;Real Benchmark Results
Real Benchmarks (PostgreSQL 16, BenchmarkDotNet with 20 iterations):
| Transaction Count | WITH CASCADE | WITHOUT FK (manual cleanup) | Difference |
| ----------------- | ------------ | --------------------------- | -------------------------------- |
| 100 | 44.69 ms | 47.66 ms | CASCADE 6% faster |
| 1,000 | 61.91 ms | 43.18 ms | Manual 30% faster |
| 10,000 | 326.73 ms | 249.79 ms | Manual 24% faster |The Surprising Truth
Myth BUSTED! CASCADE is NOT 20-25× slower. At 10,000 transactions:
- Expected (according to myths): 326.73ms CASCADE vs ~15ms manual = 21× slower
- Reality: 326.73ms CASCADE vs 249.79ms manual = only 24% slower
Even more surprising: CASCADE is actually 6% FASTER for small volumes (100 transactions)!
Why CASCADE Overhead Exists (But It’s Not That Bad)
When you execute DELETE FROM users WHERE id = X with CASCADE:
What Happens Under the Hood
Database must:
- Find all child transactions (index scan on user_id)
- Delete each one (triggers CASCADE rules)
- Update all indexes on transactions table
- Acquire exclusive locks on all affected rows
- Write to WAL (write-ahead log)
- Trigger any BEFORE/AFTER DELETE triggers
WITHOUT CASCADE (manual cleanup):
- Delete transactions explicitly
- Delete user
- Can be batched, throttled, or backgrounded
Why Small Volumes Are Faster with CASCADE
For 100 transactions:
- CASCADE: Single operation, PostgreSQL optimizes the entire chain
- Manual: Two separate operations (DELETE transactions, DELETE user)
- Result: CASCADE wins by 6% (44.69ms vs 47.66ms)
Why Large Volumes Are Slower with CASCADE
For 10,000 transactions:
- CASCADE: Must maintain referential integrity during deletion
- Manual: Can delete in batches, no FK validation overhead
- Result: Manual wins by 24% (249.79ms vs 326.73ms)
The Lock Contention Problem
The real issue with CASCADE isn’t speed—it’s lock contention.
During CASCADE, the database holds exclusive locks on:
- The parent row (user)
- All child rows (10K transactions)
- All related indexes
What Gets Blocked
-- These operations wait until CASCADE completes:
INSERT INTO transactions (user_id, amount)
VALUES (12345, 100); -- BLOCKED for 327ms
SELECT * FROM transactions
WHERE user_id = 12345; -- BLOCKED for 327ms
UPDATE transactions
SET amount = 200
WHERE id = 999; -- MAY BE BLOCKEDReal-World Impact
Case Study: SaaS application with user deletion
- Average user: 10,000 transactions
- CASCADE delete time: 326.73ms (from our real benchmark)
- Lock contention window: 327ms of blocked operations
Is this a problem?
- For 99% of applications: No (327ms is acceptable)
- For high-concurrency systems: Maybe (depends on delete frequency)
- For real-time trading/gaming: Yes (every millisecond matters)
The Soft Delete Alternative
Instead of CASCADE or manual cleanup, consider soft deletes:
-- Add deleted_at column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;
-- "Delete" a user (instant)
UPDATE users SET deleted_at = NOW() WHERE id = 12345;
-- Queries automatically filter
SELECT * FROM users WHERE id = 12345 AND deleted_at IS NULL;
-- Transactions remain intact (no orphans)
SELECT t.* FROM transactions t
JOIN users u ON t.user_id = u.id AND u.deleted_at IS NULL
WHERE u.id = 12345;Soft Delete Performance
Real Benchmark (measured with BenchmarkDotNet):
| Approach | Delete Time | Lock Duration | Orphaned Records | Reversible |
| ------------------ | ------------------ | ------------- | ---------------- | ---------- |
| CASCADE | 326.73 ms | 326.73 ms | 0 | ❌ No |
| Manual (async) | ~5 ms + background | ~5 ms | Temporarily | ❌ No |
| Soft Delete | 9.75 ms | 9.75 ms | 0 | ✅ Yes |Winner: Soft Delete (33× faster than CASCADE!) 🏆
Key Insight: Soft Delete is consistently ~10ms regardless of transaction count. CASCADE scales linearly from 44.69ms (100 txns) to 326.73ms (10K txns).
Why Soft Delete Wins
- Instant deletion: Just UPDATE one row
- No cascading: Child records untouched
- No lock contention: Minimal locking
- Reversible: Can restore if needed
- Audit trail: Data preserved for compliance
Trade-offs
Advantages:
- 33× faster than CASCADE (9.75ms vs 326.73ms)
- No orphaned records
- Audit trail preserved
- Can restore deleted data
- Minimal lock contention
Disadvantages:
- Storage cost (deleted data remains)
- All queries must filter
deleted_at - Indexes slightly larger
- Periodic cleanup needed (archive old data)
Implementation: Async Cleanup Pattern
If you can’t use soft deletes, implement async cleanup:
public async Task DeleteUserAsync(long userId)
{
// 1. Soft delete the user (instant)
await _db.ExecuteAsync(@"
UPDATE users
SET deleted_at = NOW()
WHERE id = @userId
", new { userId });
// 2. Schedule background cleanup
await _jobQueue.EnqueueAsync(new CleanupUserDataJob
{
UserId = userId,
Priority = JobPriority.Low,
BatchSize = 1000 // Delete 1000 at a time
});
}
public class CleanupUserDataJob
{
public async Task ExecuteAsync()
{
// Batched, throttled deletion
int deleted;
do
{
deleted = await _db.ExecuteAsync(@"
DELETE FROM transactions
WHERE id IN (
SELECT id FROM transactions
WHERE user_id = @userId
LIMIT @batchSize
)
", new { UserId, BatchSize });
// Throttle to avoid overwhelming database
if (deleted > 0)
await Task.Delay(100);
} while (deleted > 0);
// Finally, delete the user
await _db.ExecuteAsync(@"
DELETE FROM users WHERE id = @userId
", new { UserId });
}
}Benefits:
- User deletion appears instant (soft delete completes in ~10ms)
- Actual cleanup happens in background (batched, throttled)
- No locks on transactions table during user interaction
- System remains responsive
- Can cancel/retry if needed
When CASCADE is Actually Okay
CASCADE isn’t always evil. It’s perfectly fine for:
1. Low-Volume Tables
-- User preferences (1 row per user)
CREATE TABLE user_preferences (
user_id BIGINT PRIMARY KEY,
theme VARCHAR(20),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Deleting 1 preference row? No problem (< 1ms)2. Session/Temporary Data
-- Active sessions (auto-cleanup desired)
CREATE TABLE user_sessions (
session_id UUID PRIMARY KEY,
user_id BIGINT NOT NULL,
expires_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- User deleted = all sessions should die immediately3. Bounded Child Tables
-- Shopping cart items (max 100 items per cart)
CREATE TABLE cart_items (
cart_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT,
FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE
);
-- Guaranteed small CASCADE scope (< 5ms)Decision Matrix
| Your Situation | Use CASCADE? | Reasoning |
| ----------------------------------- | ------------ | ----------------------------------- |
| < 100 child rows per parent | ✅ YES | CASCADE is actually faster! |
| 100-1K child rows | ⚠️ MAYBE | 62ms is usually acceptable |
| > 1K child rows | ❌ NO | Use soft delete or async cleanup |
| High concurrency (> 1000 TPS) | ❌ NO | Lock contention matters |
| Low delete frequency (< 10/min) | ✅ YES | Even 327ms is negligible |
| Need audit trail | ❌ NO | Use soft delete instead |
| Temporary/session data | ✅ YES | CASCADE is perfect for auto-cleanup |
| Real-time system (< 100ms required) | ❌ NO | Use soft delete (9.75ms) |The Complete Decision Framework
Combining insights from all three parts:
┌─────────────────────────────────────────────────────────────┐
│ Should I use Foreign Keys? │
└───────────────────────┬─────────────────────────────────────┘
│
▼
┌───────────────────────────────┐
│ Do I need CASCADE on │
│ tables with > 1K rows? │
└───────┬───────────────────┬───┘
│ │
YES NO
│ │
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ Use SOFT DELETE │ │ What's my │
│ 9.75ms │ │ workload? │
│ (33× faster) │ └────┬────────┬────┘
└──────────────────┘ │ │
Write-heavy Read-heavy
│ │
▼ ▼
Part 1: 32.7% Part 2: ~0%
overhead overhead
│ │
▼ ▼
Consider Keep FKs
removing (no downside)Key Takeaways
CASCADE Performance (100% Real Data!)
- ✅ CASCADE is FASTER for small volumes (44.69ms vs 47.66ms for 100 txns - 6% faster!)
- ⚠️ CASCADE is 24% slower for large volumes (326.73ms vs 249.79ms for 10K txns - NOT 20-25×!)
- 🏆 Soft Delete is 33× faster (9.75ms vs 326.73ms - consistent regardless of volume)
- ❌ Lock contention is the real problem (exclusive locks for 327ms at 10K scale)
- ✅ CASCADE is perfectly fine for small tables (< 100 rows per parent)
When to Use Each Approach
Use CASCADE when:
- Child tables have < 100 rows per parent
- Delete frequency is low (< 10/min)
- Temporary/session data (auto-cleanup desired)
- Immediate consistency required
Use Soft Delete when:
- Need audit trail
- Want reversibility
- High concurrency system
- Large child tables (> 1K rows)
- Real-time requirements (< 100ms)
Use Async Cleanup when:
- Can’t use soft delete (storage constraints)
- Need actual deletion (compliance)
- Can tolerate temporary orphans
- Background processing available
Combined Insights: All 3 Parts
| Operation | FK Impact | When FK is OK | When to Skip FK |
| ---------------- | ----------------- | --------------------------------- | ------------------------------ |
| INSERT (P1) | 0.16% - 32.7% | Concurrent/mixed workloads | Batch inserts (32.7% overhead) |
| SELECT (P2) | < 1% | Always (negligible impact) | Never (indexes matter, not FK) |
| CASCADE (P3) | +6% to +31% | < 100 rows per parent | > 1K rows (use soft delete) |
| Soft Delete | ~0% (just UPDATE) | Audit trail, reversibility needed | Storage constraints |Conclusion: Context-Driven Decisions
After benchmarking writes (Part 1), reads (Part 2), and deletes (Part 3):
The CASCADE DELETE myth is FALSE:
- Not 20-25× slower (only 24% at 10K scale)
- Actually 6% FASTER for small volumes (< 100 rows)
- Lock contention is the real issue, not speed
The pragmatic approach:
- Keep FKs on low-volume tables (< 100 child rows)
- Use soft delete for large tables with DELETE requirements
- Remove FKs from high-volume write paths (batch inserts)
- Always maintain indexes (they matter more than FK constraints)
- Measure your workload before deciding
Most importantly: “Always CASCADE” and “Never CASCADE” are both wrong. The right answer is context-specific.
Reproduce the Benchmarks
All three parts’ benchmarks are available:
git clone https://github.com/Thyago-Oliveira-Perez/fk-benchmark.git
cd fk-benchmark
# Setup databases
./scripts/setup.sh
# Part 1: INSERT benchmarks
cd InsertBenchmarks
dotnet run -c Release
# Part 2: SELECT benchmarks
cd ../ReadBenchmarks
dotnet run -c Release
# Part 3: DELETE benchmarks (CASCADE + Soft Delete)
cd ../DeleteBenchmarks
dotnet run -c Release
# Cleanup databases
./scripts/cleanup.shAll benchmarks use REAL measured data (BenchmarkDotNet with 20-100 iterations) 🎉
🎯 Myths vs Reality: CASCADE Edition
Myth #1: “CASCADE DELETE is 20-25× Slower”
Reality: ❌ FALSE - Only 24% slower at 10K scale (326.73ms vs 249.79ms)
Myth #2: “CASCADE is Always Slow”
Reality: ❌ FALSE - For 100 transactions, CASCADE is 6% FASTER (44.69ms vs 47.66ms)!
Myth #3: “Manual Cleanup is Always Better”
Reality: ❌ FALSE - Soft Delete is 33× faster than both (9.75ms)!
Myth #4: “You Should Never Use CASCADE”
Reality: ❌ FALSE - CASCADE is perfect for small tables (< 100 rows per parent)
✅ What IS True:
- Soft Delete is the real winner (9.75ms vs 326.73ms - 33× faster)
- CASCADE scales linearly (44.69ms → 61.91ms → 326.73ms)
- Lock contention is the real problem (not speed itself)
- Small table CASCADE is perfectly fine (< 100 rows = < 45ms)
My Conclusion: Question Everything
This weekend journey taught me an important lesson: question the dogma.
When I first saw that production database without Foreign Keys, I thought it was wrong. Database courses teach FKs as mandatory. But my CTO’s simple explanation—“performance”—led me down a rabbit hole that changed how I think about database design.
What I learned:
-
Context matters more than rules: FK overhead ranges from 0.16% (concurrent) to 32.7% (batch). There’s no universal answer.
-
Measure, don’t assume: I spent a weekend building benchmarks because assumptions aren’t enough. Real data beats intuition.
-
Academic knowledge ≠ Production reality: What works in college projects doesn’t always scale to millions of transactions per day.
-
Every architectural decision is a trade-off: Foreign Keys give you referential integrity at the cost of performance. Neither choice is “wrong”—it depends on your constraints.
The real answer to “Should I use Foreign Keys?” isn’t yes or no. It’s: “What’s your workload? What are you optimizing for? Have you measured it?”
I hope this 3-part series helps you make informed decisions for your specific use case. All benchmarks are open source on GitHub—run them, modify them, share your results.
And most importantly: never stop questioning what you think you know.
Questions about your specific use case? Found different results in your environment? Let’s discuss! Reach out on LinkedIn.