Table Partition Methods RANGE · half-open · DEFAULT

RANGE Partition

Split a table by contiguous ranges of a column. Best for time-series and tiered numeric data; pair with DEFAULT for catch-all and DETACH for fast archival.

What range partitioning is

Range partitioning splits data into partitions by contiguous ranges of a column's values. Each partition owns a half-open interval — [from, to) — and a row goes to the partition whose range contains its value.

This is the most common partitioning method, especially for time-series data. "All sales from January 2024," "all events in Q3," "all orders below $1,000" — anything where the natural way to slice data is along a continuous axis.

When to choose RANGE

Data has a natural ordering — timestamps, dates, monotonic IDs, numeric amounts.
Queries frequently filter on ranges of that column (WHERE created_at >= ...).
You'll archive or drop old partitions on a schedule.

Syntax

-- Parent
CREATE TABLE parent_table (...)
    PARTITION BY RANGE (partition_column);

-- Each child
CREATE TABLE child_table
    PARTITION OF parent_table
    FOR VALUES FROM (start_value) TO (end_value);

📌 Half-open intervals. FROM (a) TO (b) means a is included, b is not. So three partitions FROM (0) TO (1001), FROM (1001) TO (2001), FROM (2001) TO (10001) tile the range without gaps or overlaps.

Example 1 — partition sales by amount

SQL — parent

CREATE TABLE sales_data (
    sales_id    INT,
    sales_date  DATE,
    amount      NUMERIC
) PARTITION BY RANGE (amount);

SQL — children

CREATE TABLE sales_amount_low
    PARTITION OF sales_data
    FOR VALUES FROM (0)    TO (1001);

CREATE TABLE sales_amount_medium
    PARTITION OF sales_data
    FOR VALUES FROM (1001) TO (2001);

CREATE TABLE sales_amount_high
    PARTITION OF sales_data
    FOR VALUES FROM (2001) TO (10001);

SQL — insert & query

INSERT INTO sales_data VALUES
(1, '2024-01-15',  500),    -- → sales_amount_low
(2, '2024-02-08', 1500),    -- → sales_amount_medium
(3, '2024-03-21', 5500),    -- → sales_amount_high
(4, '2024-04-30',  900);    -- → sales_amount_low

-- Query the parent — partition pruning kicks in
SELECT * FROM sales_data WHERE amount > 2000;
-- Only sales_amount_high is scanned

Example 2 — partition by date (the canonical case)

SQL

CREATE TABLE events (
    event_id    BIGSERIAL,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE events_2024_q3 PARTITION OF events
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE events_2024_q4 PARTITION OF events
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

One partition per quarter. When Q1 ages out, drop events_2024_q1 in a single instant.

The DEFAULT partition

If a row's value falls outside every defined range, the insert errors — unless you've created a DEFAULT partition that catches stragglers.

SQL

CREATE TABLE sales_amount_other
    PARTITION OF sales_data
    DEFAULT;

-- Now this insert succeeds even though 50000 is outside the explicit ranges
INSERT INTO sales_data VALUES (5, '2024-05-01', 50000);

⚠ Default partitions block creating new ranges that would overlap. Once a row sits in DEFAULT, you can't add a new partition whose range covers that row's value. Either move the row first or attach a new partition without DEFAULT in place.

Adding and removing partitions

SQL — add a new month

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

SQL — archive an old quarter

-- Detach without deleting the data
ALTER TABLE events DETACH PARTITION events_2024_q1;
-- Now events_2024_q1 is a standalone table — back it up, drop it, move it

-- Or just drop the partition outright
DROP TABLE events_2024_q1;

Partition pruning in action

Run a query with EXPLAIN and you'll see the planner skip non-matching partitions:

SQL

EXPLAIN SELECT * FROM events
WHERE occurred_at >= '2024-07-01' AND occurred_at < '2024-09-01';

-- Append
--   ->  Seq Scan on events_2024_q3 events_1
-- (Only Q3 is scanned — Q1, Q2, Q4 pruned)

Recap

Range partitions own half-open intervals — FROM (a) TO (b) includes a but not b.
Best for time-series and other naturally ordered data.
Add a DEFAULT partition to catch values outside the defined ranges.
DETACH PARTITION + DROP TABLE archives a slice in seconds, no row-by-row delete needed.
Partition pruning makes range queries scan only the relevant partitions.