SQL Partitioning: Range, Hash, List, and Composite Strategies Explained

Scenario context

SQL partitioning divides one logical table into smaller physical child tables, all accessed through the parent table name. The query optimizer skips irrelevant child tables entirely — a process called partition pruning — turning a 30-second full-table scan into a 200ms single-partition read. Range partitioning is the right choice for time-series data with rolling archival. Hash partitioning prevents write hotspots.…

This walkthrough focuses on The 2-Billion-Row Problem: Why Indexes Stop Scaling Without Partitioning.

Before making a design choice, clarify:

• An index on a 2-billion-row table is still a 2-billion-entry B-Tree.
• Every lookup navigates that tree — hundreds of levels deep — and the leaf pages are scattered across the entire tablespace.
• Even with a selective index, large tables exhaust buffer pool capacity, force expensive disk reads, and make VACUUM operations that span the full table increasingly slow and disruptive.

Concepts in play: database-sharding, database-indexing, llm-agents-and-langgraph, system-design-hld-architectures

Stage 1 — The Four SQL Partitioning Strategies at a Glance

• Before going deep on each strategy, here is the orientation table.
• Every major SQL database — PostgreSQL, MySQL, CockroachDB, Spanner — supports some subset of these.
• PostgreSQL supports all four natively as of version 10.
• Each strategy makes the same fundamental trade-off in a different direction: range partitioning is optimal for queries that filter by a time window but concentrates writes on the current period; hash partitioning is opt…

Observable outcome: Each strategy makes the same fundamental trade-off in a different direction: range partitioning is optimal for queries that filter by a time window but concentrates writes on the current period; hash partitioning is opt…

Stage 2 — Range Partitioning: Month-by-Month Child Tables for Time-Series Data

• Range partitioning assigns each row to a child table based on whether the partition key value falls within a defined range boundary.
• The classic application is a time-series table partitioned by month — each month's data living in its own child table, with older months archived or dropped via a single DDL statement.
• Pseudo-schema — range-partitioned orders table: Partition layout: What partition pruning delivers here: A query filtering on created at BETWEEN 2024-01-10 AND 2024-01-20 causes the planner to evaluate each child table's…
• In a 48-partition table spanning four years, this prunes 47 of 48 child tables — a 47× reduction in I/O before even consulting a local index on customer id .

Observable outcome: In a 48-partition table spanning four years, this prunes 47 of 48 child tables — a 47× reduction in I/O before even consulting a local index on customer id .

Stage 3 — List Partitioning: Categorical Splits for Geo-Compliance Boundaries

• List partitioning assigns rows to child tables based on membership in an explicit set of values.
• When those values are geographic identifiers, the partition boundary becomes a compliance boundary — EU data physically lives in the EU partition, which can be storage-pinned to EU-region infrastructure.
• Pseudo-schema — list-partitioned orders table: Partition layout: The DEFAULT partition is mandatory.
• Without it, an insert for a region code not listed in any explicit partition fails with a constraint error.

Observable outcome: Without it, an insert for a region code not listed in any explicit partition fails with a constraint error.

Stage 4 — Hash Partitioning: Even Write Distribution Across N Buckets

• Hash partitioning applies hash(partition key) mod N to assign each row to one of N child tables.
• The distribution is deterministic but appears random — which is exactly what prevents write hotspots.
• Pseudo-schema — hash-partitioned events table: Partition layout — 4 partitions: Pruning for point lookups vs.
• range queries: Hash partitioning optimizes for point-lookup performance and write balance.

Observable outcome: range queries: Hash partitioning optimizes for point-lookup performance and write balance.

Stage 5 — Composite Partitioning: Two-Level Strategies for Tables That Outgrow a Single Approach

• Composite sub-partitioning applies two levels.
• The most common pattern: range by month at the first level, then hash by customer ID within each monthly partition.
• This delivers both date-range pruning for reporting queries and write balance within each month.
• Composite partition structure: The resulting partition count: 48 months × 4 hash buckets = 192 child tables for a 4-year dataset.

Observable outcome: Composite partition structure: The resulting partition count: 48 months × 4 hash buckets = 192 child tables for a 4-year dataset.

Stage 6 — How the Query Optimizer Decides Which Partitions to Skip

• The diagram below traces the full pruning decision path from query arrival through the PostgreSQL planner to partition elimination.
• Follow it from top to bottom to see exactly where the pruning decision is made and what determines which child tables are eliminated.
• The critical fork is the diamond node: is the partition key in the WHERE clause?
• When it is, the planner eliminates dozens of child tables before opening a single data file.

flowchart TD
    Q["Query received: WHERE created_at BETWEEN Jan and Feb 2024"] --> OPT["Query optimizer enters partition pruning phase"]
    OPT --> CHECK{"Partition key present in WHERE clause?"}
    CHECK -->|"Yes — pruning active"| BOUNDS["Evaluate each child table boundary against predicate"]
    CHECK -->|"No — no pruning possible"| FULL["Scan all child tables (avoid this pattern)"]
    BOUNDS --> SKIP1["Skip 2021 partitions — bounds outside predicate range"]
    BOUNDS --> SKIP2["Skip 2022 partitions — bounds outside predicate range"]
    BOUNDS --> SKIP3["Skip 2023 partitions — bounds outside predicate range"]
    BOUNDS --> SCAN["Scan Jan 2024 child table — bounds match predicate"]
    SKIP1 --> MERGE["Merge results and return to client"]
    SKIP2 --> MERGE
    SKIP3 --> MERGE
    SCAN --> MERGE
    FULL --> MERGE

Observable outcome: When it is, the planner eliminates dozens of child tables before opening a single data file.

End-to-end outcome

After working through the stages, you should be able to explain:

• The partition key is a permanent architectural decision.
• Re-partitioning a 500 GB live table requires building a new partitioned table schema, migrating data with minimal downtime via logical replication or bulk INSERT/ATTACH, and cutting over at a maintenance window.

Alternatives considered

PostgreSQL's pruning mechanism has two modes. Understanding the distinction matters for parameterized queries — the most common form in production application code.

PostgreSQL's pruning mechanism has two modes.

Partition Pruning Internals: How PostgreSQL Plans Around Boundaries

Static pruning happens at parse time when the partition key predicate contains a literal value.

Performance Analysis: Measuring Pruning Effectiveness

The most direct way to measure pruning impact is EXPLAIN (ANALYZE, BUFFERS) .

Choosing the Right Partition Strategy: Decision Guide

Choosing the Right Partition Strategy: Decision Guide

Failure and degraded modes

Hot-Partition Anti-Patterns and When NOT to Partition

• Detection: Five anti-patterns that destroy partition performance: When NOT to partition: The system design interview answer: partition when tables are large and queries are slow on a single node.
• User or operator impact: Five anti-patterns that destroy partition performance: When NOT to partition: The system design interview answer: partition when tables are large and queries are slow on a single node.