Partitioning

Introduction

Partitioning splits a giant table into smaller physical pieces — by range (date), list (region) or hash (user_id). Each query touches only relevant partitions, speeding scans dramatically.

Understanding the topic

Core concepts to understand:

• Range: by date — common for events.
• List: by enum (region, tenant).
• Hash: by ID for even distribution.
• Partition key must be in WHERE for pruning.
• Adds maintenance (creating new partitions).

Syntax reference

Visual workflow / architecture:

events (2023..2025) — 5B rows
   ▼ partitioned by month
events_2024_01
events_2024_02
events_2024_03
...
WHERE created_at = '2024-02-15'   → only events_2024_02 scanned

Real-world use

Cloudflare logs, Stripe events, IoT sensor data — all partitioned by time. Trillion-row tables only work with partitioning.

Best practices

• Partition only when single tables exceed ~50–100GB.
• Always include partition key in WHERE.
• Automate creation of new partitions.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Three partitioning strategies.
• Q2. When to start partitioning.
• Q3. What is partition pruning?
• Q4. Maintenance burden of partitioning.
• Q5. Partition key choice.