Hot/Cold Data Partitioning
debt(d7/e7/b7/t5)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints mark automated:no; pganalyze and pg_partman can surface bloat/missing partitions but the absence of partitioning on a growing table is typically caught via performance review or production latency degradation, not a linter.
Closest to 'cross-cutting refactor across the codebase' (e7). The quick_fix sounds like one DDL change, but converting a live monolithic table to RANGE partitioning requires migrating data, updating indexes/FKs, ensuring every query includes the partition key (per common_mistakes), and automating partition lifecycle — touches schema, app queries, and ops.
Closest to 'strong gravitational pull' (b7). applies_to spans web/cli/queue-worker; once partitioned, every query, migration, backup job, and retention script is shaped by the partition key, and changing the scheme later is painful.
Closest to 'notable trap most devs eventually learn' (t5). The misconception (that it's about disk cost rather than working-set size) plus the pruning gotcha (queries without the partition key scan all partitions) is a well-documented gotcha but not catastrophically counterintuitive.
Also Known As
TL;DR
Explanation
Hot/cold partitioning (a form of data tiering) separates rows by access patterns, typically along a time axis. Hot partitions contain the last day, week, or month of data - the rows queried by 95% of traffic. Cold partitions hold historical records that are rarely read, kept for compliance, analytics, or occasional lookups. The goal is to keep the hot working set small enough to fit in RAM and indexed cheaply, while cold data lives on slower or compressed storage.
Implementation varies by database. PostgreSQL supports declarative partitioning via PARTITION BY RANGE on a timestamp column, with each child table potentially living on a different tablespace. MySQL offers partitioning on InnoDB. Many teams use application-level sharding: active_orders vs archived_orders tables, or separate schemas. Cloud warehouses like BigQuery and Snowflake handle tiering automatically. Beyond RDBMSs, you might offload cold data to S3/Parquet accessed via a query federation layer, or move it to a cheaper read-only replica.
Benefits are substantial: smaller indexes mean faster INSERTs and lookups, VACUUM/ANALYZE runs in seconds instead of hours, backups shrink, and query planners get accurate statistics on the hot slice. Partition pruning lets the engine skip entire cold partitions when queries filter on the partition key.
Trade-offs include query complexity (cross-partition queries can be slower), the need for a migration/rotation job to move data between tiers, and the risk of users asking for 'all data' queries that scan everything. Choose a partition key the application actually filters on - usually created_at or an event date. Automate partition creation and old partition detachment so operations do not become manual firefighting.
Common Misconception
Why It Matters
Common Mistakes
- Choosing a partition key the application rarely filters on, so partition pruning never kicks in and every query scans all partitions.
- Forgetting to automate partition creation - the system silently fails when inserts target a non-existent future partition.
- Running ad-hoc 'SELECT * FROM orders WHERE user_id = ?' queries that scan every cold partition because they lack a date filter.
- Moving data between hot and cold tables with large transactional DELETEs that cause bloat and lock contention instead of using DETACH PARTITION.
- Treating cold storage as write-once then needing to update historical rows for GDPR/corrections - plan for occasional cold writes.
Avoid When
- Small tables under ~10M rows where a single index comfortably fits in memory and partitioning adds operational overhead with no measurable benefit.
- Workloads that frequently query across the full history without time filters - partition pruning will not help and cross-partition JOINs are slower.
- Early-stage products where access patterns are still shifting - picking the wrong partition key is painful to reverse.
- Transactional systems requiring unique constraints that span the full table on columns other than the partition key - PostgreSQL requires unique indexes to include the partition key.
When To Use
- Time-series or append-mostly tables (events, logs, orders, metrics) with growing historical volume and clear time-based access patterns.
- When VACUUM, backups, or index maintenance on a monolithic table are blocking operations or exceeding maintenance windows.
- Compliance scenarios where old data must be retained but can live on cheaper, slower storage separate from the hot operational set.
- When you need cheap bulk deletion of expired data - DETACH PARTITION is instant compared to DELETE on millions of rows.
Code Examples
-- Single monolithic table - 2 billion rows, 400GB index
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
event_type TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
payload JSONB
);
CREATE INDEX idx_events_user ON events(user_id);
CREATE INDEX idx_events_created ON events(created_at);
-- Every query hits the full 400GB index
SELECT * FROM events
WHERE user_id = 42
AND created_at > NOW() - INTERVAL '7 days';
-- Vacuum takes 6 hours, backups are 500GB, p99 degrades monthly
-- Partition by month on the query filter column
CREATE TABLE events (
id BIGSERIAL,
user_id BIGINT NOT NULL,
event_type TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
payload JSONB,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Hot partitions - current and recent months on fast SSD
CREATE TABLE events_2024_11 PARTITION OF events
FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE events_2024_12 PARTITION OF events
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
-- Cold partitions on cheaper tablespace, compressed
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
TABLESPACE cold_storage;
CREATE INDEX ON events_2024_12 (user_id);
-- Partition pruning: planner touches only events_2024_12
SELECT * FROM events
WHERE user_id = 42
AND created_at > NOW() - INTERVAL '7 days';
-- Rotate out old data cheaply - no DELETE bloat
ALTER TABLE events DETACH PARTITION events_2023_01;