Section 8: Data Operations

Master DELETE Operations

Complete guide to deleting data in Cassandra - tombstones, range deletes, TTL cleanup, conditional deletes, and performance optimization!

Home › Section 8: Data Operations › DELETE Data

🎯 DELETE in Cassandra

For Beginners: Understanding DELETE

Imagine you have a physical notebook:

  • Traditional databases (like MySQL): When you delete, it's like using an eraser to completely remove the text. The data is GONE instantly.
  • Cassandra: When you delete, it's like drawing a line through the text with a pen and writing "DELETED" next to it. The original text is still there underneath!

Why does Cassandra do this?

Cassandra spreads your data across many computers (nodes). When you delete data, all those computers need to know about it. Instead of immediately erasing the data from all computers (which is complicated), Cassandra writes a special marker called a "tombstone" that says "this data is deleted." This marker spreads to all computers, and then later (after 10 days by default), the actual data gets cleaned up.

💡 Simple Summary: DELETE in Cassandra = Writing "DELETED" marker, not erasing immediately. This is good for distributed systems, but means queries have to read these "DELETED" markers, which can slow things down if you have too many.

CRITICAL: DELETE Creates Tombstones!

In Cassandra, DELETE doesn't actually remove data immediately! It writes a special marker called a TOMBSTONE that tells Cassandra the data is deleted.

⚠️ The Tombstone Problem:

Too many tombstones = Performance disaster!
Cassandra must read tombstones during queries → Slow reads → Potential timeouts

Real-world analogy: Imagine searching through a filing cabinet where 50% of the files have big red "DELETED" stickers on them. You still have to flip through all those deleted files to find the active ones. The more "DELETED" stickers, the slower your search. That's exactly what happens in Cassandra!

✅ What DELETE Does

  • ✓ Delete entire row
  • ✓ Delete specific columns
  • ✓ Delete range of rows
  • ✓ Conditional deletes (IF)
  • ✓ Delete collection elements
  • ✓ Works with TTL

❌ Dangers of DELETE

  • ✗ Creates tombstones (not instant)
  • ✗ Tombstones slow reads
  • ✗ Must wait for gc_grace_seconds
  • ✗ Bulk deletes can crash cluster
  • ✗ No recovery after DELETE
  • ✗ Can cause read timeouts

📖 Basic DELETE Syntax

Complete DELETE Syntax

Full Syntax
DELETE [column1, column2, ...]
FROM table_name
[USING TIMESTAMP microseconds]
WHERE primary_key_column = value
  AND clustering_column = value
[IF condition];

-- Delete entire row
DELETE FROM table_name
WHERE partition_key = value;

-- Delete specific columns
DELETE column1, column2
FROM table_name
WHERE partition_key = value;

-- Delete with condition
DELETE FROM table_name
WHERE partition_key = value
IF column = value;

Simple Examples

-- Delete entire row
DELETE FROM users
WHERE user_id = ?;

-- Delete specific columns
DELETE email, phone
FROM users
WHERE user_id = ?;

-- Delete specific row in partition
DELETE FROM user_posts
WHERE user_id = ?
  AND post_id = ?;

-- Delete with condition
DELETE FROM sessions
WHERE session_id = ?
IF expired = true;

💀 Tombstones: The Hidden Cost

Beginner's Guide: What are Tombstones?

Think of a tombstone like a sticky note that says "IGNORE THIS DATA"

📚 Step-by-Step Explanation:

Step 1: You have data stored

Cassandra stores your data in files on disk (called SSTables). Let's say you have a user: Alice, email=alice@example.com

Step 2: You delete that data

When you run DELETE FROM users WHERE user_id='Alice', Cassandra doesn't open those disk files and erase Alice's data. Why? Because:

  • Those files are immutable (can't be changed once written)
  • Your data is copied across multiple servers
  • Erasing from all servers immediately would be too slow and complex

Step 3: Cassandra writes a tombstone

Instead, Cassandra creates a NEW file with a special marker: "💀 Alice is DELETED (timestamp: 2025-01-15 10:30:00)"

Step 4: Queries read both

When someone searches for Alice, Cassandra finds:

  • Old file: "Alice, email=alice@example.com (timestamp: 2025-01-10)"
  • New file: "💀 Alice DELETED (timestamp: 2025-01-15)"

Cassandra sees the tombstone has a NEWER timestamp, so it knows to ignore the old data. Result: "Alice not found"

Step 5: Eventually cleaned up

After 10 days (by default), during a process called "compaction," Cassandra merges files together and removes both the old data AND the tombstone. NOW it's truly gone!

⚠️ The Problem with Too Many Tombstones:

Imagine you're looking for a book in a library. If the library has:

  • 10 books + 5 "REMOVED" cards: Easy! You flip through quickly.
  • 10 books + 10,000 "REMOVED" cards: Nightmare! You're spending all your time reading "REMOVED" cards.

That's why too many DELETEs in Cassandra can slow down your entire system - every query has to read through thousands of "DELETED" markers!

⚠️ What Happens When You DELETE BEFORE DELETE Data on Disk (SSTable) Row 1: user_id=001, name="Alice", email="[email protected]" Row 2: user_id=002, name="Bob", email="[email protected]" ... ⚠️ DELETE FROM users WHERE user_id = 001 AFTER DELETE (Tombstone Written!) Newer SSTable (Memtable flushed) 💀 TOMBSTONE: user_id=001 DELETED (Timestamp: newer than data below) Original SSTable Still Exists: Row 1: user_id=001, name="Alice" (shadowed by tombstone) Row 2: user_id=002, name="Bob" (still active) 💀 Tombstone Facts What is a Tombstone? • Marker that says "data deleted" • Has timestamp (newer than data) • Written to new SSTable Why Not Delete Immediately? • Distributed system: replicas need notice • SSTables immutable (can't edit files) • Need to propagate DELETE to all nodes When Are Tombstones Removed? • During compaction (merges SSTables) • After gc_grace_seconds (default: 10 days) • If tombstone older than gc_grace • AND all replicas have seen it ⚠️ THE PROBLEM Queries must read tombstones! • SELECT scans tombstones to know what's deleted • 1000s of tombstones = SLOW queries • Can cause read timeouts • Even crash nodes (out of memory) → Avoid mass deletes if possible!

Tombstone Warning Threshold

Default warning: 1,000 tombstones in a query
Default abort: 100,000 tombstones in a query
What happens: Query aborted with TombstoneOverwhelmingException

🔄 DELETE Execution Flow

DELETE Execution Flow 1️⃣ Client Sends DELETE DELETE FROM users WHERE user_id = ? 2️⃣ Create Tombstone Marker Tombstone = {key: user_id, timestamp: NOW, deleted: true} 3️⃣ Write Tombstone to Memtable Tombstone stored in memory (just like regular write) 4️⃣ Append to Commit Log Durability ensured (tombstone survives crashes) 5️⃣ Acknowledge to Client ✅ DELETE successful (tombstone written) ⚡ Fast! • DELETE = Write operation • No read required • 1-5ms typically • Same speed as INSERT ⏰ Later... • Memtable → SSTable flush • Tombstone in SSTable • Replicas sync • Wait gc_grace_seconds • Compaction runs • Tombstone purged (10+ days typically)

DELETE Performance

DELETE is FAST to execute (1-5ms) because it's just a write operation.
DELETE is SLOW for reads because queries must scan tombstones.

🗑️ Types of DELETE Operations

Beginner's Guide: Different Ways to Delete

Just like you can delete things differently in real life, Cassandra has different types of deletes. Let's understand each one with simple examples!

📝 Think of it Like Editing a Document:

1. Delete Entire Page (Row DELETE):

"Delete everything about User Alice" - removes the entire row
Example: User deletes their account

2. Delete Specific Words (Column DELETE):

"Delete Alice's email and phone, but keep her name" - removes only specific columns
Example: User wants to remove contact info but keep account

3. Delete List Items (Collection DELETE):

"Delete Alice's list of hobbies" - removes a whole collection
Example: User clears their interest list

4. Delete Date Range (Range DELETE - Dangerous!):

"Delete all posts from January" - removes multiple rows at once
⚠️ Warning: This creates performance problems!

5. Auto-Delete (TTL - Best Choice!):

"This session expires in 1 hour" - data deletes itself automatically
✅ Best: No manual deletion needed!

💡 Which Type Should You Use?
  • Deleting one user/item: Use Row DELETE (safe)
  • Removing personal info: Use Column DELETE (safe)
  • Clearing a list: Use Collection DELETE (safe)
  • Data expires after time: Use TTL (BEST!)
  • Deleting many items at once: Avoid if possible! If you must, be very careful

1. Row DELETE (Entire Row)

Delete Entire Row
-- Delete entire partition (all clustering rows)
DELETE FROM user_posts
WHERE user_id = ?;
// Deletes ALL posts for this user

-- Delete specific clustering row
DELETE FROM user_posts
WHERE user_id = ?
  AND post_id = ?;
// Deletes ONE specific post

2. Column DELETE (Specific Columns)

-- Delete specific columns from row
DELETE email, phone
FROM users
WHERE user_id = ?;
// Deletes email and phone, keeps other columns

-- Delete single column
DELETE temporary_token
FROM users
WHERE user_id = ?;

3. Collection DELETE

-- Delete entire collection
DELETE tags
FROM posts
WHERE post_id = ?;

-- Delete map key
DELETE preferences['theme']
FROM users
WHERE user_id = ?;

-- Delete list element (NOT recommended - full scan)
UPDATE posts
SET tags = tags - ['old-tag']
WHERE post_id = ?;
// Use UPDATE for list/set element removal
DELETE Type What It Does Tombstone Created
Row DELETE Deletes entire row Partition or Row tombstone
Column DELETE Deletes specific columns Cell tombstone (per column)
Range DELETE Deletes range of clustering rows Range tombstone
Collection DELETE Deletes entire collection Collection tombstone
TTL Expiration Auto-delete after time TTL tombstone (auto-created)

📊 Range DELETE (Dangerous!)

Understanding Range DELETE for Beginners

What is a Range DELETE?

Instead of deleting one specific row, you delete MANY rows at once using conditions like "greater than" or "between dates."

📖 Real-World Example:

Imagine you're a teacher with a gradebook:

  • Individual Delete: "Cross out John's grade, cross out Mary's grade, cross out Bob's grade..."
    → Creates 3 separate "DELETED" marks
  • Range Delete: "Cross out ALL grades from January 1st to January 31st"
    → Creates ONE big "DELETED FROM JAN 1-31" sticky note that covers many rows
🚨 Why Range DELETE is Dangerous:

The "sticky note" stays there forever (well, 10 days), and EVERY time someone looks for ANY grade in that section, they have to check:

"Does this fall under the January 1-31 deletion range?"

So even if you're looking for a February grade (which wasn't deleted), you still waste time checking against that January range!

Result: ALL queries in that partition become slower, not just queries for deleted data!

Range DELETE Warning

Range deletes create a SINGLE range tombstone that covers many rows. This can be extremely dangerous for performance!

Range DELETE Examples

-- Delete range of clustering rows
DELETE FROM sensor_data
WHERE sensor_id = ?
  AND timestamp >= '2025-01-01'
  AND timestamp <= '2025-01-31';
// ⚠️ Deletes all January data with ONE range tombstone

-- Delete everything after a point
DELETE FROM user_timeline
WHERE user_id = ?
  AND post_date > '2024-12-01';
// ⚠️ Range tombstone covers all future posts
⚠️ Range Tombstone Problem Individual Row Deletes (Better) 💀 Tombstone: Row 1 💀 Tombstone: Row 2 💀 Tombstone: Row 3 ✓ Active Row 4 ✓ Active Row 5 3 tombstones to scan Range DELETE (Worse!) 💀 RANGE TOMBSTONE Covers Rows 1-3 (Queries MUST check every row in range to see if it falls under this tombstone) ✓ Active Row 4 (checked against range) ALL rows checked! ⚠️ Range tombstones affect EVERY query in partition Even queries NOT touching deleted rows must check against range tombstone!

When Range DELETE is Acceptable

  • ✓ Deleting entire partition (going away anyway)
  • ✓ Small ranges (< 100 rows)
  • ✓ Time-series data with TTL (auto-cleanup)
  • ✗ Large ranges (1000s of rows)
  • ✗ Frequently queried partitions
  • ✗ Without understanding the performance impact

✅ Conditional DELETE (IF)

IF EXISTS / IF Conditions

Conditional DELETE
-- Only delete if row exists
DELETE FROM sessions
WHERE session_id = ?
IF EXISTS;

-- Delete if column matches value
DELETE FROM users
WHERE user_id = ?
IF status = 'inactive';

-- Multiple conditions
DELETE FROM orders
WHERE order_id = ?
IF status = 'pending'
  AND created_at < '2025-01-01';

Performance Cost

Conditional DELETE uses lightweight transactions (Paxos):
Normal DELETE: 1-5ms
Conditional DELETE: 10-50ms (10x slower)

⏰ TTL vs DELETE

Beginner's Guide: TTL - The Smart Alternative

What is TTL?

TTL stands for "Time To Live." It's like setting a self-destruct timer on your data - after X seconds, it automatically disappears!

🍪 Cookie Analogy:

DELETE approach: You bake cookies, eat some, then manually throw away each remaining cookie one by one. Each time you throw one away, you leave a note "Cookie #5 thrown away." Those notes pile up!

TTL approach: You bake cookies with an expiration date printed on them. After 3 days, they automatically vanish - no manual throwing away, no notes piling up, completely automatic cleanup!

✅ Why TTL is MUCH Better:
  1. Automatic: You set it once when inserting data, then forget about it. No code needed to delete later!
  2. No tombstone problem: TTL creates special "TTL tombstones" that Cassandra handles more efficiently
  3. Cleaner: Data expires naturally, like milk going bad after a week
  4. Faster: No accumulation of regular tombstones that slow down queries
💡 When to Use Each:
Use TTL for: Use DELETE for:
• Session data (expires in hours)
• Cache data (expires in minutes)
• Temporary codes (expire in 5 mins)
• Anything with predictable lifetime 		• User deletes their account
• Admin removes inappropriate content
• User deletes a post/comment
• Anything user-initiated

💡 Golden Rule: If you know data will expire after a certain time, ALWAYS use TTL instead of DELETE!

✅ Use TTL When:

  • ✓ Data expires after known time
  • ✓ Session data (hours/days)
  • ✓ Cache entries
  • ✓ Temporary tokens
  • ✓ Recent activity logs
  • ✓ Auto-cleanup preferred

⚠️ Use DELETE When:

  • • Immediate deletion needed
  • • User-triggered removal
  • • Data violation (GDPR)
  • • Explicit deletion required
  • • No predictable expiration
  • • But be aware of tombstones!

TTL Example (Preferred!)

-- Insert with TTL (auto-deletes after 1 hour)
INSERT INTO sessions (session_id, data)
VALUES (?, ?)
USING TTL 3600;
// No tombstone accumulation!
// Data expires cleanly

-- vs DELETE (creates tombstones)
DELETE FROM sessions
WHERE session_id = ?;
// Tombstone remains for gc_grace_seconds

Why TTL is Better

  • No manual DELETE needed: Automatic cleanup
  • Less tombstone pressure: Compaction handles expired data efficiently
  • Better performance: No accumulation of delete tombstones
  • Simpler code: Set once, forget it

💡 Real-World DELETE Examples

Example 1: User Account Deletion

Scenario: User deletes account - must remove all related data

-- 1. Delete main user record
DELETE FROM users
WHERE user_id = ?;

-- 2. Delete all user posts
DELETE FROM user_posts
WHERE user_id = ?;

-- 3. Delete user preferences
DELETE FROM user_preferences
WHERE user_id = ?;

-- 4. Delete sessions
DELETE FROM user_sessions
WHERE user_id = ?;

// ⚠️ Problem: Multiple partition deletes
// Better: Design with user_id as partition key in all tables

Example 2: Cleanup Old Data

Scenario: Delete data older than 90 days

-- ❌ BAD: Range delete (huge range tombstone)
DELETE FROM logs
WHERE app_id = ?
  AND log_date < '2024-10-01';

-- ✅ BETTER: Use TTL from the start
INSERT INTO logs (app_id, log_date, message)
VALUES (?, ?, ?)
USING TTL 7776000; -- 90 days in seconds

-- ✅ ALTERNATIVE: Delete by day (if must DELETE)
DELETE FROM logs
WHERE app_id = ?
  AND log_date = '2024-09-30';
// Delete one day at a time, not 90-day range

Example 3: Cancel Pending Order

Scenario: User cancels order - only delete if still pending

-- Conditional delete (only if pending)
DELETE FROM orders
WHERE order_id = ?
IF status = 'pending';

-- Check result
// [applied]=true → Successfully deleted
// [applied]=false → Order already processing/shipped

Example 4: Clear Shopping Cart

Scenario: User clears cart after purchase

-- Delete entire cart partition
DELETE FROM shopping_carts
WHERE user_id = ?;

-- OR just delete items collection
DELETE items
FROM shopping_carts
WHERE user_id = ?;

⚡ Performance Issues & Solutions

Understanding DELETE Performance Problems

Why does DELETE cause performance problems?

🎬 Movie Theater Analogy:

Imagine you're in a movie theater looking for empty seats:

Scenario 1: Few Deleted Seats

Theater has 100 seats, 10 have "RESERVED - DO NOT SIT" signs.
Finding an empty seat is EASY - you only skip past 10 reserved ones.

Scenario 2: Many Deleted Seats (THE PROBLEM!)

Theater has 100 seats, 80 have "RESERVED - DO NOT SIT" signs.
Finding an empty seat is NIGHTMARE - you're checking 80 "RESERVED" signs to find 20 actual seats!

This is EXACTLY what happens in Cassandra when you delete too much data. Queries become slow because they're reading through thousands of "DELETED" markers!

🚨 The 4 Main Problems with DELETE:

Problem 1: Queries Get Slower Over Time

What you see: Your app was fast, now it's slow
What's happening: Tombstones piling up like trash
Solution: Use TTL instead, or redesign your table

Problem 2: Queries Start Timing Out

What you see: "TombstoneOverwhelmingException" error
What's happening: Query tried to read 100,000+ tombstones and gave up
Solution: Stop mass deleting! Use smaller batches or TTL

Problem 3: Everything Slows Down (Range Tombstone)

What you see: Even queries for non-deleted data are slow
What's happening: Range DELETE created a massive tombstone covering everything
Solution: Delete individual rows, or accept 10 days of slow queries

Problem 4: Disk Space Doesn't Free Up

What you see: Deleted millions of rows, but disk still 90% full
What's happening: Tombstones + old data still on disk (waiting 10 days)
Solution: Wait for compaction (automatic cleanup) or run manual cleanup

✅ How to Avoid These Problems:
  1. Use TTL whenever possible - Let data expire automatically
  2. Design tables to be dropped entirely - Create one table per day/month, drop old tables
  3. Monitor tombstone warnings - Check logs for warnings before it becomes critical
  4. Delete in small batches - If you must DELETE, do 100-1000 rows at a time, not millions
  5. Test delete patterns before production - What works with 1000 rows might fail with 1 million

Common DELETE Problems

Problem 1: Tombstone Accumulation

Symptom: Queries getting slower over time

Cause: Too many deletes creating too many tombstones

Solution: Use TTL instead, or redesign table

Problem 2: Read Timeouts

Symptom: TombstoneOverwhelmingException

Cause: Query reading 100,000+ tombstones

Solution: Avoid mass deletes, increase tombstone thresholds (temporary)

Problem 3: Range Tombstone Overhead

Symptom: All queries in partition slow

Cause: Range DELETE created range tombstone

Solution: Delete individual rows, or accept performance hit

Problem 4: Disk Space Not Freed

Symptom: Disk usage stays high after DELETE

Cause: Tombstones waiting for gc_grace_seconds + compaction

Solution: Wait for compaction, or run manual nodetool compact

Tombstone Monitoring

-- Check tombstone warnings in logs:
// WARN ... Read 5000 live rows and 10000 tombstone cells
// for query SELECT * FROM ... (see tombstone_warn_threshold)

-- Monitor with nodetool:
$ nodetool cfstats keyspace.table | grep Tombstone

-- Check table metrics:
$ nodetool tablestats keyspace.table

-- Set custom thresholds (cassandra.yaml):
tombstone_warn_threshold: 1000
tombstone_failure_threshold: 100000
Issue Impact Solution
Many deletes Slow reads Use TTL instead
Range deletes Very slow reads Delete individual rows
Mass deletes Cluster instability Batch in small chunks
Old tombstones Disk space wasted Run compaction
Conditional DELETE 10x slower Use sparingly

✅ DELETE Best Practices

✅ DO These Things

  • ✓ Prefer TTL over DELETE
  • ✓ Design for TTL from start
  • ✓ Monitor tombstone warnings
  • ✓ Delete individual rows (not ranges)
  • ✓ Batch small delete operations
  • ✓ Run regular compaction
  • ✓ Test delete workloads
  • ✓ Consider table redesign

❌ DON'T Do These

  • ✗ Mass delete operations
  • ✗ Range deletes on hot data
  • ✗ Delete entire partitions frequently
  • ✗ Ignore tombstone warnings
  • ✗ Overuse conditional DELETE
  • ✗ Expect instant disk recovery
  • ✗ Delete without monitoring
  • ✗ Use DELETE as primary workflow

Golden Rules for DELETE

  1. TTL is king: Always prefer TTL for time-based expiration
  2. Tombstones are expensive: They slow reads for gc_grace_seconds (default 10 days)
  3. Range deletes are dangerous: Create range tombstones that affect all queries
  4. Monitor actively: Watch for tombstone warnings in logs
  5. Design for deletes: If heavy deletes expected, consider time-bucketed tables
  6. Compaction is crucial: Tombstones only removed during compaction
  7. Batch carefully: Delete in small batches (100-1000 rows at a time)
  8. Test at scale: Delete patterns that work small may fail at scale

Recommended Delete Patterns

Use Case Recommended Approach Reason
Session data TTL (1-24 hours) Auto-cleanup, no tombstones
Cache entries TTL (minutes-hours) Perfect for TTL
Time-series logs Time-bucketed tables + TTL Drop entire tables
User deletion Individual row DELETE Infrequent, acceptable
GDPR compliance DELETE + manual compaction Legal requirement
Bulk cleanup Redesign or truncate table DELETE too expensive

When to Consider Table Redesign

If you find yourself frequently deleting data, consider these alternatives:

  • Time-bucketed tables: Create table per day/month, drop entire tables
  • TTL everywhere: Set TTL on all writes, let data expire
  • Separate hot/cold tables: Active data in one table, archive in another
  • Status columns: Mark as deleted, filter in queries (if acceptable)
Previous
Update Data
Advertisement

Responsive Ad