Column-Family Databases

Netflix sends you a personalised recommendation every time you open the app. Behind that recommendation is a system that has processed billions of watch history events — every play, pause, skip, and rating — across 238 million subscribers. That data lives in a column-family database. Processing it row by row like SQL would take hours. Column-family storage reads only the columns it needs, skipping terabytes of irrelevant data. The result: recommendations computed in seconds, not hours. This lesson explains exactly how that works.

Row Storage vs Column Storage — The Physical Difference

This is the most important concept in this lesson. It's not just a software difference — it's a physical difference in how bytes are laid out on disk. That physical layout determines everything: which queries are fast, which are slow, and why column-family databases exist at all.

What "Column-Family" Actually Means

The term "column-family database" is slightly misleading. These databases do not just store data by column — they store data in groups of related columns called column families. Each column family is stored separately on disk. Columns within a family are always read together.

The Write Path — LSM-Tree Architecture

Column-family databases use an LSM-tree (Log-Structured Merge-tree) write path. This is the architectural reason they achieve millions of writes per second. Here is how it works step by step:

The Read Path — Memtable + Bloom Filters + SSTables

Reads in column-family databases are more complex than writes. The database must check multiple places before returning a result:

Hands-on — Building a SaaS Analytics Table

The scenario: You are building a real-time analytics dashboard for a SaaS platform. Every user action generates an event. You need to query events by user for the last 30 days. Here is the full table definition and working queries:

-- Create keyspace and event table
CREATE KEYSPACE saas_analytics
  WITH replication = {'class':'SimpleStrategy','replication_factor':3};

USE saas_analytics;

CREATE TABLE user_events (
  user_id    TEXT,
  event_time TIMESTAMP,
  event_type TEXT,
  properties MAP,       -- flexible key-value for event metadata
  PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC)
  AND default_time_to_live = 2592000; -- auto-delete after 30 days

from cassandra.cluster import Cluster
from datetime import datetime, timedelta

session = Cluster(['node1', 'node2']).connect('saas_analytics')

# Insert a page view event
session.execute("""
  INSERT INTO user_events (user_id, event_time, event_type, properties)
  VALUES (%s, %s, %s, %s)
""", ('user_4421', datetime.now(), 'page_view',
      {'url': '/pricing', 'time_on_page': '42s'}))

# Query: all events for user_4421 in the last 7 days
seven_days_ago = datetime.now() - timedelta(days=7)

rows = session.execute("""
  SELECT event_time, event_type, properties
  FROM user_events
  WHERE user_id   = 'user_4421'
    AND event_time >= %s
""", (seven_days_ago,))

event_time                 | event_type  | properties
---------------------------+-------------+------------------------------------------
2024-01-15 16:04:22.000    | page_view   | {'url': '/pricing', 'time_on_page': '42s'}
2024-01-15 15:58:11.000    | page_view   | {'url': '/features', 'time_on_page': '18s'}
2024-01-14 09:22:03.000    | purchase    | {'product_id': 'p_44', 'amount': '49.99'}
2024-01-13 14:01:55.000    | signup      | {'plan': 'pro', 'referrer': 'google'}

(4 rows — sequential read of one partition, pre-sorted DESC)
Query time: 3.2ms

Where Column-Family Databases Shine

When NOT to Use Column-Family Databases

Practice Questions — You're the Engineer

Quiz — Column-Family in Production