Wide-Column Stores (Cassandra)

-- Keyspace (like a database in PostgreSQL)
CREATE KEYSPACE IF NOT EXISTS app_data
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'us-east': 3,
    'eu-west': 3
};

-- Table with partition and clustering keys
CREATE TABLE app_data.user_events (
    user_id UUID,
    event_date DATE,
    event_time TIMESTAMP,
    event_type TEXT,
    payload MAP<TEXT, TEXT>,
    PRIMARY KEY ((user_id), event_date, event_time)
) WITH CLUSTERING ORDER BY (event_date DESC, event_time DESC);

-- Good: partition key distributes data evenly
PRIMARY KEY ((user_id), event_date)
-- Each user's events are on one node, users spread across nodes

-- Good: composite partition key for high-cardinality
PRIMARY KEY ((user_id, event_type), event_date)
-- Events partitioned by user+type, queried by date range

-- Bad: low cardinality partition key
PRIMARY KEY ((region), event_date)
-- Only 3-5 regions means uneven distribution (hot nodes)

-- Bad: too many partitions queried at once
SELECT * FROM user_events WHERE user_id IN (uuid1, uuid2, ...uuid1000);
-- Each IN value is a different partition on potentially different nodes

-- Range queries on clustering key (efficient within partition)
SELECT * FROM user_events
WHERE user_id = ? AND event_date >= '2024-01-01' AND event_date <= '2024-06-30';

-- Insert (upsert semantics — same PK overwrites)
INSERT INTO user_events (user_id, event_date, event_time, event_type)
VALUES (uuid(), '2024-06-15', toTimestamp(now()), 'login');

-- Select (must include partition key)
SELECT * FROM user_events WHERE user_id = ?;
SELECT * FROM user_events WHERE user_id = ? AND event_date = '2024-06-15';

-- Select with LIMIT
SELECT * FROM user_events WHERE user_id = ? LIMIT 10;

-- Update (same as upsert)
UPDATE user_events SET payload = {'key': 'value'}
WHERE user_id = ? AND event_date = '2024-06-15' AND event_time = ?;

-- Delete (creates tombstone)
DELETE FROM user_events WHERE user_id = ? AND event_date = '2024-06-15';

-- Batch (atomic across partitions — use sparingly)
BEGIN BATCH
    INSERT INTO user_events (user_id, event_date, event_time, event_type)
    VALUES (?, '2024-06-15', now(), 'purchase');
    UPDATE user_inventory SET quantity = quantity - 1 WHERE user_id = ?;
APPLY BATCH;

-- Delete creates a tombstone
DELETE FROM user_events WHERE user_id = ? AND event_date = '2024-01-01';

-- Tombstones affect read performance
-- If a query scans many tombstones, it becomes slow
-- tombstone_warn_threshold (default 1000)
-- tombstone_failure_threshold (default 100000)

-- Check tombstone status per table
nodetool tablestats app_data.user_events

-- TTL automatically creates future tombstones
INSERT INTO user_events (user_id, event_date, event_time, event_type)
VALUES (?, '2024-06-15', now(), 'session')
USING TTL 86400;  -- auto-delete after 24 hours

-- Avoid large range deletes
DELETE FROM user_events WHERE user_id = ? AND event_date >= '2024-01-01';
-- This creates millions of tombstones — very slow!

-- Size-tiered (default) — good for write-heavy
ALTER TABLE user_events WITH compaction = {
    'class': 'SizeTieredCompactionStrategy',
    'min_threshold': 4,
    'max_threshold': 32
};

-- Leveled — good for read-heavy
ALTER TABLE user_events WITH compaction = {
    'class': 'LeveledCompactionStrategy',
    'sstable_size_in_mb': 160
};

-- TimeWindow — best for time-series with TTL
ALTER TABLE user_events WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_size': 1,
    'compaction_window_unit': 'DAYS'
};

-- Check compaction status
nodetool compactionstats