← Home ← Codex ← DEBT ← Engine
Browse by Category
+ added · ✎ updated 7d
← Back to glossary

Hot/Cold Data Partitioning

Database Advanced
debt(d7/e7/b7/t5)
d7 Detectability Operational debt — how invisible misuse is to your safety net

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.

e7 Effort Remediation debt — work required to fix once spotted

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.

b7 Burden Structural debt — long-term weight of choosing wrong

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.

t5 Trap Cognitive debt — how counter-intuitive correct behaviour is

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.

About DEBT scoring →

Also Known As

data tiering temporal partitioning hot-cold storage time-based partitioning

TL;DR

Splitting tables by access frequency - hot partitions hold recent, frequently-queried rows while cold partitions archive old data to shrink the working set.

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

✗ Hot/cold partitioning is only about saving disk cost - the primary benefit is shrinking the active working set and indexes so hot queries stay fast as total data grows unbounded.

Why It Matters

A single orders table with 5 years of history slows every query, bloats indexes, and makes backups painful - partitioning by month lets 99% of queries touch only the current partition, keeping p99 latency flat as the dataset grows.

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

✗ Vulnerable
-- 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
✓ Fixed
-- 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;

Added 7 Oct 2026
Views 42
Rate this term
No ratings yet
🤖 AI Guestbook educational data only
| |
Last 30 days
1 ping S 3 pings S 0 pings M 1 ping T 1 ping W 0 pings T 0 pings F 0 pings S 0 pings S 0 pings M 1 ping T 0 pings W 0 pings T 0 pings F 0 pings S 0 pings S 0 pings M 0 pings T 0 pings W 1 ping T 0 pings F 0 pings S 0 pings S 0 pings M 0 pings T 0 pings W 0 pings T 1 ping F 0 pings S 0 pings S
No pings yet today
No pings yesterday
PetalBot 2 Ahrefs 2 Unknown AI 2 Applebot 2 Google 1 ChatGPT 1 Perplexity 1 Majestic 1 Bing 1
crawler 13
🧱 FUNDAMENTALS — new to this? Start with the ground floor.
Database database A database is an organized collection of data stored electronically, designed so programs can efficiently retrieve, add, update, and delete information.

Nearly every application needs to remember information between sessions. Databases provide the reliable, fast, and organized storage that makes persistent data possible at any scale.

💡 Always use prepared statements with placeholders—never concatenate user input directly into database queries.

Ask Codex about Database →
⚡ DEV INTEL Tools & Severity
🟡 Medium ⚙ Fix effort: High
⚡ Quick Fix
Identify your largest time-series table, add RANGE partitioning on created_at by month, and schedule a job to create next month's partition and detach partitions older than your retention policy.
📦 Applies To
web cli queue-worker postgresql mysql timescaledb bigquery
🔗 Prerequisites
🔍 Detection Hints
CREATE\s+TABLE\s+\w+\s*\((?!.*PARTITION\s+BY).*created_at\s+TIMESTAMP
Auto-detectable: ✗ No pg_partman pt-online-schema-change pganalyze
⚠ Related Problems
🤖 AI Agent
Confidence: Medium False Positives: Medium ✗ Manual fix Fix: High Context: File Tests: Regenerate


✓ schema.org compliant