SAFETY GUIDE: DROP TABLE

DROP TABLE Safety Guide

Learn from costly mistakes! Master table deletion safety, recovery strategies, and production best practices. One wrong DROP = hours of downtime + thousands lost!

🚀 Discord's 5 Billion Messages/Day

The Challenge

5 BILLION messages every single day from 150M+ active users

57,870 messages/second average
120,000+ messages/second at peak
~2 TB of new data daily
<50ms response time required

How They Do It

1. Smart Table Design

CREATE TABLE messages (
    channel_id BIGINT,
    message_id BIGINT,
    user_id BIGINT,
    content TEXT,
    created_at TIMESTAMP,
    PRIMARY KEY ((channel_id), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);
                

2. Batch Writes - Reduces network round trips by 90%, improves latency from 30ms → 5ms

3. TTL for Efficiency - Auto-expire temporary messages after 30 days, saves 40% storage

4. Async Inserts - Message appears instantly (<5ms), database write happens in background

The Results

✅ 120,000 INSERTs/second
✅ <5ms latency (user-facing)
✅ 40% storage savings (TTL cleanup)
✅ 99.99% uptime

📝 INSERT Basics

Basic Syntax

-- Standard INSERT
INSERT INTO users (user_id, name, email, created_at)
VALUES ('U001', 'Alice', 'alice@email.com', '2024-01-15 10:30:00');

-- INSERT with explicit column order
INSERT INTO users (email, user_id, name)  -- Different order is OK!
VALUES ('bob@email.com', 'U002', 'Bob');

-- INSERT with partial columns (other columns will be NULL)
INSERT INTO users (user_id, name)
VALUES ('U003', 'Charlie');  -- email is NULL

-- INSERT with JSON format (Cassandra 4.0+)
INSERT INTO users JSON '{"user_id": "U004", "name": "Diana", "email": "diana@email.com"}';
            

Key Points

Column order doesn't matter - As long as values match column names
All PRIMARY KEY columns required - Cannot be NULL
Non-key columns optional - Will be NULL if omitted
No RETURNING clause - INSERT doesn't return inserted data

Data Type Examples

CREATE TABLE examples (
    id UUID PRIMARY KEY,
    text_col TEXT,
    int_col INT,
    decimal_col DECIMAL,
    timestamp_col TIMESTAMP,
    boolean_col BOOLEAN,
    list_col LIST<TEXT>,
    set_col SET<INT>,
    map_col MAP<TEXT, INT>
);

INSERT INTO examples (
    id, text_col, int_col, decimal_col, timestamp_col, 
    boolean_col, list_col, set_col, map_col
) VALUES (
    uuid(),                              -- UUID: auto-generated
    'sample text',                     -- TEXT: quoted
    42,                                  -- INT: no quotes
    19.99,                               -- DECIMAL: decimal point
    '2024-01-15 10:30:00',              -- TIMESTAMP: quoted
    true,                               -- BOOLEAN: true/false
    ['item1', 'item2', 'item3'],        -- LIST: ordered, allows duplicates
    {1, 2, 3, 3},                       -- SET: unique values only (3 appears once)
    {'key1': 100, 'key2': 200}          -- MAP: key-value pairs
);
            

⚖️ INSERT vs UPDATE: They're the SAME!

🤯 Mind-Blowing Fact

In Cassandra, INSERT and UPDATE are IDENTICAL operations!

Both perform an "UPSERT" - if the row exists, it's updated; if not, it's inserted.

📝

INSERT

INSERT INTO users (user_id, name)
VALUES ('U001', 'Alice');
                    

What happens:

If U001 doesn't exist → creates new row
If U001 exists → overwrites name column

🔄

UPDATE

UPDATE users
SET name = 'Alice'
WHERE user_id = 'U001';
                    

What happens:

If U001 doesn't exist → creates new row
If U001 exists → overwrites name column

Exactly the same result! This is called "UPSERT" behavior.

Why This Matters

-- In SQL (traditional databases):
-- INSERT → Creates new row (fails if exists)
-- UPDATE → Modifies existing row (does nothing if doesn't exist)

-- In Cassandra:
-- INSERT → UPSERT (insert or overwrite)
-- UPDATE → UPSERT (insert or overwrite)

-- Example: Running this twice creates ONE row, not two!
INSERT INTO users (user_id, name) VALUES ('U001', 'Alice');
INSERT INTO users (user_id, name) VALUES ('U001', 'Alice Updated');
-- Result: One row with name='Alice Updated'
            

When to Use Each

Use INSERT: When inserting ALL or MOST columns
Use UPDATE: When updating SPECIFIC columns (more readable)
Performance: Identical! Choose based on readability

⚙️ How INSERT Works Internally

The Write Path (4 Steps)

Step 1: Commit Log - Write to append-only log on disk (crash recovery)
Step 2: Memtable - Write to in-memory structure (fast!)
Step 3: Respond to Client - "Success!" (before hitting disk!)
Step 4: Flush to SSTable - Eventually written to disk (async)

Detailed Write Path

-- You execute:
INSERT INTO users (user_id, name) VALUES ('U001', 'Alice');

-- What happens (in order):

-- [0ms] Client sends INSERT to coordinator node
-- [0.5ms] Coordinator determines replica nodes (based on partition key hash)

-- [1ms] Write to Commit Log (sequential disk write - FAST)
--       Purpose: Crash recovery (if node crashes, replay from here)

-- [1.5ms] Write to Memtable (in-memory sorted tree - VERY FAST)
--         Memtable: RAM-based structure, holds recent writes

-- [2ms] ✓ Respond to client: "Success!" 
--       Data is durable (commit log) and queryable (memtable)

-- [Later...] When memtable fills up (64MB typical):
--   → Flush memtable to SSTable on disk
--   → SSTable: Immutable sorted file
--   → Compaction: Merge SSTables periodically
            

Why So Fast?

No disk seeks - Sequential append-only writes (commit log)
No indexes to update - Unlike SQL databases
No locks - No transaction coordination needed
In-memory writes - Memtable in RAM
Async disk writes - SSTables written later

Performance Numbers

Single INSERT latency: 1-5ms average
Throughput: 10,000-50,000 writes/sec per node
Batch INSERT: 50,000-150,000 writes/sec per node
Cluster throughput: Scales linearly (10 nodes = 10x throughput)

📦 Batch Operations

LOGGED vs UNLOGGED Batch

🔒

LOGGED (Default)

BEGIN BATCH
  INSERT INTO users (...) VALUES (...);
  INSERT INTO users (...) VALUES (...);
APPLY BATCH;
                    

Guarantees:

✅ All-or-nothing (atomic)
✅ Writes to batchlog first
⚠️ Slower (extra overhead)

Use when: Writes to SAME partition, atomicity critical

⚡

UNLOGGED (Fast)

BEGIN UNLOGGED BATCH
  INSERT INTO users (...) VALUES (...);
  INSERT INTO users (...) VALUES (...);
APPLY BATCH;
                    

Guarantees:

✅ Much faster (no batchlog)
⚠️ NOT atomic
✅ Fewer network round trips

Use when: Performance matters, atomicity not needed

Batch Example: Insert Multiple Users

BEGIN UNLOGGED BATCH
  INSERT INTO users (user_id, name, email) 
  VALUES ('U001', 'Alice', 'alice@email.com');
  
  INSERT INTO users (user_id, name, email) 
  VALUES ('U002', 'Bob', 'bob@email.com');
  
  INSERT INTO users (user_id, name, email) 
  VALUES ('U003', 'Charlie', 'charlie@email.com');
APPLY BATCH;

-- Benefits:
-- ✓ One network round trip instead of 3
-- ✓ Reduces latency by ~70%
-- ✓ Higher throughput
            

⚠️ Batch Anti-Patterns

DON'T batch writes to different partitions - Actually SLOWER!
DON'T batch 100+ statements - Coordinator bottleneck
DON'T use LOGGED for performance - Use UNLOGGED instead

Rule of thumb: Batch 5-50 statements to SAME partition

⏰ TTL (Time To Live)

What is TTL?

TTL: Automatic data expiration. Cassandra deletes data after N seconds.

-- Insert with 30-day TTL
INSERT INTO sessions (session_id, user_id, data)
VALUES ('S001', 'U001', 'session data')
USING TTL 2592000;  -- 30 days = 2,592,000 seconds

-- Common TTL values:
-- 1 hour    = 3600
-- 1 day     = 86400
-- 7 days    = 604800
-- 30 days   = 2592000
-- 90 days   = 7776000

-- Check remaining TTL
SELECT session_id, TTL(data) FROM sessions WHERE session_id = 'S001';
-- Returns seconds remaining until expiration
            

Real-World TTL Examples

Session Storage

-- User sessions expire after 24 hours
INSERT INTO user_sessions (...)
VALUES (...)
USING TTL 86400;
                    

Benefit: No manual cleanup needed, saves storage

Cache Data

-- API responses cached for 1 hour
INSERT INTO api_cache (...)
VALUES (...)
USING TTL 3600;
                    

Benefit: Auto-invalidate stale cache

Temporary Messages

-- Temp messages deleted after 7 days
INSERT INTO temp_messages (...)
VALUES (...)
USING TTL 604800;
                    

Benefit: Compliance, storage savings

Discord's TTL Strategy

Discord uses TTL for temporary channels and DM message previews:

Temporary channels: 30-day TTL saves 40% storage
Message previews: 7-day TTL for quick access
Result: $2M+ annual savings on storage costs

🕐 Timestamps & Conflict Resolution

Default Behavior: Last Write Wins

-- Time: 10:00:00 - Alice's write
INSERT INTO users (user_id, name) VALUES ('U001', 'Alice');

-- Time: 10:00:01 - Bob's write (1 second later)
INSERT INTO users (user_id, name) VALUES ('U001', 'Bob');

-- Result: name = 'Bob' (last write wins!)
-- Cassandra uses microsecond timestamps to resolve conflicts
            

USING TIMESTAMP (Custom Timestamps)

-- Scenario: Synchronizing offline writes

-- Write 1: Happened at 10:00:00, but sent at 10:05:00
INSERT INTO users (user_id, name) VALUES ('U001', 'Alice')
USING TIMESTAMP 1705316400000000;  -- Microseconds: 10:00:00

-- Write 2: Happened at 09:59:00, but sent at 10:05:01
INSERT INTO users (user_id, name) VALUES ('U001', 'Bob')
USING TIMESTAMP 1705316340000000;  -- Microseconds: 09:59:00

-- Result: name = 'Alice' (happened later, even though sent first!)
            

When to Use Custom Timestamps

Offline sync: Mobile apps syncing old changes
Batch imports: Preserving original creation time
Event sourcing: Replay events with original timestamps
Testing: Deterministic test scenarios

Check Timestamp (Writetime)

-- Get the timestamp of when data was written
SELECT user_id, name, WRITETIME(name) FROM users 
WHERE user_id = 'U001';

-- Output:
-- user_id | name  | writetime(name)
-- U001    | Alice | 1705316400000000  (microseconds since epoch)

-- Convert to readable format:
SELECT user_id, name, 
       toTimestamp(WRITETIME(name)) AS written_at
FROM users WHERE user_id = 'U001';
            

🔒 LWT: IF NOT EXISTS

What are Lightweight Transactions?

LWT: Atomic compare-and-set operations. Ensures uniqueness or conditional writes.

-- Problem: Two users try to register same username simultaneously

-- Regular INSERT (BAD - both succeed!)
INSERT INTO users (username, user_id) VALUES ('alice', 'U001');
INSERT INTO users (username, user_id) VALUES ('alice', 'U002');
-- Result: U002 overwrites U001 (last write wins)

-- LWT INSERT (GOOD - only first succeeds!)
INSERT INTO users (username, user_id) 
VALUES ('alice', 'U001') 
IF NOT EXISTS;
-- Result: [applied]: true

INSERT INTO users (username, user_id) 
VALUES ('alice', 'U002') 
IF NOT EXISTS;
-- Result: [applied]: false (username already taken!)
            

Performance Cost

⚡

Regular INSERT

Latency: 1-5ms
Throughput: 50K writes/sec
Consistency: Eventually consistent

🐢

LWT INSERT

Latency: 20-50ms (4-10x slower!)
Throughput: 5K writes/sec (10x slower!)
Consistency: Linearizable (Paxos)

⚠️ Use LWT Sparingly!

LWT uses Paxos consensus: Requires 4 round trips instead of 1

✅ Use for: Unique constraints, critical atomicity (user registration)
❌ Don't use for: Regular writes, high-throughput operations
💡 Better alternative: Application-level uniqueness checks

⚡ Performance Optimization

1. Async Writes (Application Level)

// Bad: Synchronous writes (blocks user)
result = cassandra.execute("INSERT INTO users ...")
sendResponse(result)  // User waits 5ms

// Good: Async writes (non-blocking)
cassandra.executeAsync("INSERT INTO users ...")
sendResponse("Success!")  // User gets response immediately
// Database write happens in background
            

2. Prepared Statements

// Bad: Parse query every time (slow!)
for (i = 0; i < 1000; i++) {
    cassandra.execute("INSERT INTO users (id, name) VALUES (?, ?)", [i, "User"+i])
}

// Good: Prepare once, execute many (fast!)
prepared = cassandra.prepare("INSERT INTO users (id, name) VALUES (?, ?)")
for (i = 0; i < 1000; i++) {
    cassandra.execute(prepared, [i, "User"+i])
}
// 50-70% faster! Query parsing done once
            

3. Batch Size Tuning

-- ❌ Too small: Many network round trips
BEGIN UNLOGGED BATCH
  INSERT ...
APPLY BATCH;
-- 1 statement = not worth batching

-- ❌ Too large: Coordinator bottleneck
BEGIN UNLOGGED BATCH
  INSERT ...  -- 500 statements
APPLY BATCH;
-- Coordinator struggles, causes timeouts

-- ✅ Just right: Sweet spot
BEGIN UNLOGGED BATCH
  INSERT ...  -- 20-50 statements
APPLY BATCH;
-- Optimal balance
            

Performance Metrics

Production Benchmarks

Operation	Latency	Throughput (per node)
Single INSERT	1-5ms	10K-50K writes/sec
Batch INSERT (UNLOGGED)	3-10ms	50K-150K writes/sec
Prepared statement	0.5-3ms	100K+ writes/sec
LWT (IF NOT EXISTS)	20-50ms	5K-10K writes/sec

🖥️ Interactive INSERT Console

INSERT Simulator

🎮 Interactive INSERT Simulator Ready!

Try the examples or write your own INSERT statements.
This simulator shows you what happens internally!

🏢 Real-World Production Examples

📺

Netflix: Viewing History

CREATE TABLE viewing_history (
    user_id UUID,
    watched_at TIMESTAMP,
    content_id TEXT,
    duration_seconds INT,
    device_type TEXT,
    PRIMARY KEY ((user_id), watched_at)
) WITH CLUSTERING ORDER BY 
  (watched_at DESC);

-- Insert viewing record
INSERT INTO viewing_history (
    user_id, watched_at, content_id,
    duration_seconds, device_type
) VALUES (
    'U123', 
    '2024-01-15 20:30:00',
    'stranger-things-s4e1',
    3420, 
    'Smart TV'
);
                    

Scale: 200M users, 500M inserts/day

Challenge: Personalized recommendations based on watch history

🚗

Uber: Driver Locations

CREATE TABLE driver_locations (
    city TEXT,
    geohash TEXT,
    updated_at TIMESTAMP,
    driver_id UUID,
    latitude DOUBLE,
    longitude DOUBLE,
    status TEXT,
    PRIMARY KEY ((city, geohash), 
        updated_at, driver_id)
) WITH CLUSTERING ORDER BY 
  (updated_at DESC)
AND default_time_to_live = 300;

-- Update location (every 4 seconds)
INSERT INTO driver_locations (
    city, geohash, updated_at, driver_id,
    latitude, longitude, status
) VALUES (
    'SF', '9q8yy', toTimestamp(now()),
    'D456', 37.7749, -122.4194, 
    'available'
);
                    

Scale: 5M drivers, 15M updates/minute

TTL: 5-minute auto-expiration keeps data fresh

🍎

Apple: Health Data

CREATE TABLE health_metrics (
    user_id UUID,
    metric_date DATE,
    recorded_at TIMESTAMP,
    metric_type TEXT,
    value DECIMAL,
    unit TEXT,
    source_device TEXT,
    PRIMARY KEY ((user_id, metric_date),
        recorded_at, metric_type)
);

-- Insert step count
INSERT INTO health_metrics (
    user_id, metric_date, recorded_at,
    metric_type, value, unit, source_device
) VALUES (
    'U789', '2024-01-15', 
    '2024-01-15 14:30:00',
    'steps', 8543, 'count', 
    'Apple Watch Series 9'
);
                    

Scale: 1B+ devices, 100B datapoints/day

Partitioning: By user_id + date prevents hotspots

⭐ Best Practices

✅

DO's

Use prepared statements for repeated inserts (50-70% faster)
Batch 20-50 statements to same partition
Use UNLOGGED batches for better performance
Add TTL for temporary/cache data
Use async writes in applications
Monitor write latency and throughput
Test with production load before deploying
Use correct data types (UUID not TEXT for IDs)
Partition by time for time-series data
Set realistic TTL values based on use case

❌

DON'Ts

Don't batch to different partitions (slower!)
Don't use LWT unnecessarily (10x slower)
Don't batch >100 statements (coordinator bottleneck)
Don't reparse queries (use prepared statements)
Don't synchronously wait (use async)
Don't insert NULL values (waste space)
Don't use sequential IDs (use UUID/TIMEUUID)
Don't forget timestamps (add created_at/updated_at)
Don't ignore errors (handle write failures)
Don't skip monitoring (track write metrics)

Production Checklist

✅ Schema design validated with expected query patterns
✅ Prepared statements implemented for all repeated INSERTs
✅ Batch size optimized (20-50 statements to same partition)
✅ TTL configured for temporary data (sessions, cache)
✅ Async writes implemented in application layer
✅ Error handling with retry logic and circuit breakers
✅ Monitoring dashboards for write latency and throughput
✅ Load testing completed at 2x expected peak traffic
✅ Alerts configured for high write latency (>100ms)
✅ Runbook documented for common write issues

❌ Common Mistakes & Fixes

Mistake #1: Batching to Different Partitions

-- ❌ BAD: Different user_ids = different partitions
BEGIN UNLOGGED BATCH
  INSERT INTO users (user_id, name) VALUES ('U001', 'Alice');
  INSERT INTO users (user_id, name) VALUES ('U002', 'Bob');
  INSERT INTO users (user_id, name) VALUES ('U003', 'Charlie');
APPLY BATCH;
-- Coordinator must send to 3 different nodes - SLOWER than individual inserts!

-- ✅ GOOD: Same partition (e.g., user's activity feed)
BEGIN UNLOGGED BATCH
  INSERT INTO activity_feed (user_id, activity_id, ...) 
  VALUES ('U001', 'A1', ...);
  INSERT INTO activity_feed (user_id, activity_id, ...) 
  VALUES ('U001', 'A2', ...);
APPLY BATCH;
-- All go to same node - MUCH faster!
                

Mistake #2: Using LWT for Everything

-- ❌ BAD: LWT for regular user updates
INSERT INTO user_profiles (user_id, bio) 
VALUES ('U001', 'Updated bio') 
IF NOT EXISTS;
-- 20-50ms latency, 10x slower, unnecessary!

-- ✅ GOOD: Regular INSERT (upsert is fine)
INSERT INTO user_profiles (user_id, bio) 
VALUES ('U001', 'Updated bio');
-- 1-5ms latency, overwriting is OK here

-- ✅ GOOD: LWT only when truly needed
INSERT INTO usernames (username, user_id) 
VALUES ('alice', 'U001') 
IF NOT EXISTS;
-- Username uniqueness is critical!
                

Mistake #3: Not Using Prepared Statements

// ❌ BAD: Parse every time (Python example)
for i in range(10000):
    session.execute(f"INSERT INTO users (id, name) VALUES ({i}, 'User{i}')")
# Parses 10,000 times! Wastes CPU + increases latency

// ✅ GOOD: Prepare once, execute many
prepared = session.prepare("INSERT INTO users (id, name) VALUES (?, ?)")
for i in range(10000):
    session.execute(prepared, (i, f'User{i}'))
# 50-70% faster! Parses only once
                

Mistake #4: Synchronous Writes in Web Apps

// ❌ BAD: User waits for database
app.post('/api/events', async (req, res) => {
    await cassandra.execute("INSERT INTO events ...")  // 5ms
    res.json({ success: true })
})
// Total response time: 5ms (user waits)

// ✅ GOOD: Fire-and-forget async
app.post('/api/events', async (req, res) => {
    cassandra.executeAsync("INSERT INTO events ...")  // Don't await!
    res.json({ success: true })
})
// Total response time: <1ms (user happy!)
                

💼 Interview Questions & Answers

1 Explain why INSERT and UPDATE are the same in Cassandra. What are the implications? ▼

Answer:

Why They're the Same:

Both INSERT and UPDATE perform an "upsert" operation. Cassandra doesn't check if a row exists before writing—it simply writes the new data with a timestamp. This is because:

No read-before-write: Checking existence would require reading from disk (slow)
Last-write-wins: Conflicts resolved by timestamp, not existence
Performance: Avoids the overhead of existence checks

Implications:

1. No INSERT errors: Running INSERT twice won't fail—it just overwrites
2. Unintentional overwrites: Must use IF NOT EXISTS for uniqueness
3. Tombstones: UPDATE can create rows even if they didn't exist
4. Application logic: Can't rely on INSERT vs UPDATE distinction

Example:

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');  
-- Result: ONE row with name='Bob' (not an error!)

UPDATE users SET name = 'Charlie' WHERE id = 999;  
-- Result: Creates NEW row with id=999 (even if didn't exist!)
                        

2 When should you use LOGGED vs UNLOGGED batches? What's the performance difference? ▼

Answer:

LOGGED Batch:

Guarantee: All-or-nothing atomicity (all succeed or all fail)
Mechanism: Writes to batchlog first, then applies statements
Cost: ~30% performance overhead
Use when: Atomicity critical (e.g., debit + credit operations)

UNLOGGED Batch:

Guarantee: No atomicity (some might succeed, others fail)
Mechanism: Directly applies statements, no batchlog
Cost: Minimal overhead
Use when: Performance matters, partial failures acceptable

Performance Comparison:

LOGGED: ~15-25ms for 20 statements
UNLOGGED: ~5-10ms for 20 statements (2-3x faster)
Individual INSERTs: ~100-200ms for 20 statements (10-20x slower)

Best Practice:

Use UNLOGGED by default. Only use LOGGED when atomicity is truly required (e.g., financial transactions where partial success would cause data inconsistency).

3 Explain the Cassandra write path. Why is it so fast compared to traditional databases? ▼

Answer:

The Write Path (4 Steps):

Commit Log: Sequential append to log file on disk (~0.5ms)
Memtable: Write to in-memory sorted structure (~0.5ms)
Respond: Send "success" to client (~1ms total)
Flush (async): Eventually flush memtable to SSTable on disk

Why It's Fast:

1. Sequential writes: Commit log is append-only (no disk seeks)
2. In-memory writes: Memtable operations are RAM-speed
3. No indexes to update: Unlike SQL (B-tree updates)
4. No locks: No transaction coordination
5. Async disk writes: SSTables written later, not blocking
6. No read-before-write: Doesn't check if row exists

Comparison with SQL:

Operation	SQL	Cassandra
Write latency	5-20ms	1-5ms
Index updates	Yes (slow)	No
Disk seeks	Random I/O	Sequential only
Locks	Row/page locks	None

4 How would you optimize INSERT performance for bulk data loading (1M+ rows)? ▼

Answer (Comprehensive Strategy):

1. Use Prepared Statements

prepared = session.prepare("INSERT INTO table (...) VALUES (?, ?, ?)")
for row in data:
    session.execute(prepared, row)
# 50-70% faster than parsing each time
                        

2. Parallel Execution

# Use concurrent futures for parallel inserts
from concurrent.futures import ThreadPoolExecutor

with ThreadPoolExecutor(max_workers=50) as executor:
    futures = [executor.submit(insert_row, row) for row in data]
# 10-20x faster than sequential
                        

3. Batch Wisely (20-50 statements)

# Group by partition key, batch same-partition writes
for partition_batch in group_by_partition(data, size=30):
    BEGIN UNLOGGED BATCH
        ... 30 inserts to same partition ...
    APPLY BATCH
                        

4. Tune Write Settings

Consistency Level: Use ONE instead of QUORUM (3x faster)
Connection pool: Increase to 10-20 connections per node
Async execution: Don't wait for each write

5. Optimize Cassandra Settings

Disable commitlog_sync: batch mode during bulk load
Increase memtable_flush_writers: 4-8 writers
Disable compaction: nodetool disableautocompaction (re-enable after)

Expected Performance:

Sequential: 1,000-5,000 inserts/sec
Optimized parallel: 50,000-150,000 inserts/sec per node
Cluster (10 nodes): 500K-1.5M inserts/sec

5 Explain TTL in Cassandra. How would you use it for a session storage system? ▼

Answer:

What is TTL:

Time To Live - automatic data expiration. Cassandra marks data with a timestamp and deletes it after N seconds. No manual cleanup needed.

Session Storage Design:

CREATE TABLE user_sessions (
    session_id UUID PRIMARY KEY,
    user_id UUID,
    created_at TIMESTAMP,
    last_activity TIMESTAMP,
    session_data TEXT,
    ip_address TEXT,
    user_agent TEXT
);

-- Insert with 24-hour TTL
INSERT INTO user_sessions (
    session_id, user_id, created_at, session_data
) VALUES (
    uuid(), 'U001', toTimestamp(now()), '{...}'
) USING TTL 86400;  -- 24 hours

-- Update last_activity (extends TTL)
UPDATE user_sessions USING TTL 86400
SET last_activity = toTimestamp(now())
WHERE session_id = ?;
                        

Benefits:

1. No manual cleanup: Sessions auto-expire
2. Storage savings: Prevents unbounded growth
3. Performance: No background cleanup jobs needed
4. Simple logic: Set and forget

Advanced Pattern - Sliding Window:

-- Every user activity resets 24-hour timer
app.on('user_activity', (session_id) => {
    UPDATE user_sessions USING TTL 86400
    SET last_activity = now()
    WHERE session_id = session_id;
})
// Session expires only after 24 hours of INACTIVITY
                        

Production Considerations:

Check remaining TTL: SELECT TTL(session_data) to warn user
Grace period: Use 25 hours, warn at 24 hours
Monitoring: Track tombstone creation rate

Responsive Ad