Skip to content

Partitioning

In one line

Partitioning divides a table into smaller pieces by range/list/hash — improves query speed and maintenance.

In simple words

Partitioning splits a large table into smaller, more manageable pieces called partitions. The table still appears as one logical table to applications, but the data is physically stored in separate partitions.

Types:

  1. Range partitioning — by value ranges (e.g., orders by date: 2023, 202, 2025)
  2. List partitioning — by discrete values (e.g., by region: US, EU, Asia)
  3. Hash partitioning — by hash of a column (even distribution)

Benefits:

  1. Query pruning — only scan relevant partitions (e.g., query 2024 data only scans 2024 partition)
  2. Maintenance — archive/drop old partitions without affecting others
  3. Parallelism — queries can scan partitions in parallel
  4. Storage — each partition can be on different storage tiers

Example

sql
-- PostgreSQL range partitioning by date
CREATE TABLE orders (
  id SERIAL,
  created_at DATE NOT NULL,
  total DECIMAL
) PARTITION BY RANGE (created_at);

-- Create partitions by year
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Query only scans the 2024 partition
SELECT * FROM orders WHERE created_at >= '2024-06-01';
-- Plan: Seq Scan on orders_2024 (not orders_2023!)

Partitioning splits orders by year. A query for 2024 data only scans the orders_2024 partition — partition pruning skips 2023 automatically.

Common confusions

  • Confused with: sharding

    The difference: Partitioning splits a table within a single database into smaller pieces. Sharding distributes data across multiple database servers. Partitioning is single-node; sharding is multi-node. Partitioning improves query speed; sharding improves scale-out capacity.

Related terms

Related Resources

Related Lessons

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary