Create partitioned tables by range

You have a events table with 10 million rows. SELECT queries keep scanning the whole table, maintenance windows stretch into the night, and old partitions are a pain to drop. The fix is range partitioning — split data into manageable pieces based on a date or numeric range. In this lesson you'll learn how to create partitioned tables by range in PostgreSQL, walk through a hands-on example, and know exactly when to choose range partitioning over other strategies.

The problem this lesson solves

Unbounded growth is the silent killer of database performance. Without partitioning, every query that filters on a date column like created_at forces PostgreSQL to scan the entire table — even if only the last month's rows are relevant. Worse, as your table grows:

• Indexes get bigger and less cache-friendly.
• Dirty pages accumulate, and VACUUM takes longer.
• Deleting old data (e.g., legal retention) means a massive DELETE that locks the table and generates huge WAL traffic.

Partitioning doesn't solve every problem — it's not a magic VACUUM or a replacement for proper indexing — but it directly addresses the "big table" pain. With range partitioning, you:

• Reduce the amount of data scanned for typical queries (partition pruning).
• Drop old partitions instantly instead of deleting rows one by one.
• Keep each partition small enough to fit in memory and be maintained quickly.

This lesson is part of your PostgreSQL journey — previous lessons covered CREATE TABLE, constraints, and indexing; this one builds on those ideas to help you design tables that scale.

Core concept / mental model

Think of a partitioned table as a filing cabinet with labeled drawers, not one massive pile of papers. The parent table is the cabinet itself — a logical view over the data. Each partition is a physical drawer that holds only the papers (rows) that belong to a specific range (e.g., January, February, March).

When you query the parent table with a filter such as WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01', PostgreSQL can look only at the January drawer — this is partition pruning. You write queries exactly as you would against a normal table, but the optimizer eliminates partitions that can't contain matching rows.

Key terms

• Parent table (or partitioned table): the logical table you query. It has no storage of its own.
• Partition: a child table that stores rows for a specific range. Each partition inherits the parent's columns and constraints.
• Partition key: the column(s) used to decide which partition a row belongs to (e.g., created_at).
• Range: a contiguous interval defined by FROM and TO bounds.

PostgreSQL uses declarative partitioning (built-in, robust) rather than the old-school inheritance-based partitioning. Declarative partitioning handles routing, constraints, and pruning automatically — you don't have to write triggers or triggers.

How it works step by step

Creating a range-partitioned table involves three layers, each with a specific role:

1. Define the parent table with PARTITION BY RANGE (...).
2. Create child partitions with CREATE TABLE ... PARTITION OF ....
3. Insert data — PostgreSQL routes each row to the correct partition automatically.

Here's the high-level flow:

• Step 1: Declare the partition key. Choose a column that is natural for your queries (usually a date/timestamp or a numeric ID). The column type must match the range bounds.

• Step 2: Guarantee uniqueness. If you need a primary key or unique constraint, the partition key must be included in it. This is a non-negotiable rule — see Troubleshooting for what happens if you forget.

• Step 3: Create partitions with non-overlapping ranges. Use FROM and TO bounds. The lower bound is inclusive, the upper bound is exclusive — so '2025-01-01' TO '2025-02-01' includes January 1st but not February 1st.

• Step 4: Let PostgreSQL route rows. When you insert a row, the system compares the partition key value to the bounds and sends it to the right partition. If no partition matches, you get an error — that's where DEFAULT partitions come in (next section).

Range bounds and dates

Range bounds can be any type that supports comparison operators: date, timestamp, integer, numeric, text (but text ordering often surprises). For monthly partitions, use DATE '2025-01-01' style literals. For timestamps, be careful about time-of-day — '2025-01-01 00:00:00' becomes the lower bound, and you need to specify an upper bound like '2025-02-01 00:00:00' to cover the whole month.

Indexes on partitions

Create indexes on each partition (or create them after partition creation). PostgreSQL does not automatically index partitions just because you indexed the parent. A common mistake is to skip this — queries will still be correct, but they'll do sequential scans on each partition. Always add indexes on the partition key and any columns used in frequent WHERE clauses.

Hands-on walkthrough

Let's build a realistic example: an events table that we'll partition by month. We'll create the parent, two partitions, and insert data to see how automatic routing works.

1. Create the parent table

CREATE TABLE events (
    event_id bigserial,
    event_date date NOT NULL,
    payload jsonb
) PARTITION BY RANGE (event_date);

Note we did not set event_id as a primary key — we'll handle that later, because a unique constraint must include the partition key.

2. Create partitions for January and February

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

CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Now any row with event_date from January 1st (inclusive) to February 1st (exclusive) goes to events_2025_01, and February goes to events_2025_02.

3. Insert and verify routing

INSERT INTO events (event_date, payload) VALUES
    ('2025-01-15', '{"type":"click"}'),
    ('2025-02-10', '{"type":"purchase"}');

SELECT tableoid::regclass AS partition, event_id, event_date, payload FROM events;

Expected output:

     partition      | event_id | event_date |        payload        
--------------------+----------+------------+-----------------------
 events_2025_01     |        1 | 2025-01-15 | {"type": "click"}
 events_2025_02     |        2 | 2025-02-10 | {"type": "purchase"}
(2 rows)

The tableoid::regclass trick shows which physical partition each row landed in — proof that routing works.

4. Query with partition pruning

EXPLAIN (ANALYZE) SELECT * FROM events
WHERE event_date >= '2025-01-01' AND event_date < '2025-02-01';

You'll see in the plan that only events_2025_01 is scanned — that's partition pruning in action. Even with thousands of partitions, the planner trims the list early.

5. Add a DEFAULT partition (optional but wise)

PostgreSQL 12+ allows a DEFAULT partition to catch out-of-range rows. Without it, inserting a row for March 2025 would fail with:

ERROR: no partition of relation "events" found for row

Create a catch-all:

CREATE TABLE events_default PARTITION OF events DEFAULT;

Now March rows go there. This is handy for incremental loading but can hide mistakes — always validate data quality before relying on it.

Compare options / when to choose what

Why range over hash or list? Here's a quick comparison:

When to choose range:

• You have a date/timestamp column and query it often.
• You need data retention (e.g., delete data older than 12 months by dropping partitions).
• Your queries typically filter on a range (e.g., "last 30 days").

When to skip range:

• Your queries filter on a non-range column (like customer_id) — range partitioning won't prune; you'd be better with hash or list.
• You have a tiny table — partitioning adds complexity without benefit.
• You need a global primary key that doesn't include the partition key — not allowed.

Troubleshooting & edge cases

"No partition of relation found for row"

When a row's partition key value falls outside all defined ranges and there is no DEFAULT partition, PostgreSQL rejects the insert. Fix: add a DEFAULT partition or extend the range. A DEFAULT partition can mask logic bugs, so use it deliberately.

Unique constraints / primary keys must include the partition key

If you try CREATE TABLE events (id bigserial PRIMARY KEY, ...) PARTITION BY RANGE (created_at), you'll get:

ERROR: unique constraint on partitioned table must include all partitioning columns

Solution: include the partition key in the constraint, e.g., PRIMARY KEY (event_id, event_date). This is a common stumbling block for beginners.

Indexes on partitions are separate

Creating an index on the parent table with CREATE INDEX will propagate to existing and future partitions (PostgreSQL 11+), but if you create partitions after the index, you must re-run the CREATE INDEX on the new partition — or rely on the automatic propagation if you create the index after all partitions. Test with EXPLAIN to confirm indexes are being used.

Range bounds and floating point

For numeric ranges, be careful with floating-point bounds — comparisons can be surprising. Use numeric or integer for range keys. For timestamp, use explicit timestamp literals to avoid implicit casts.

Queries that don't filter on the partition key

If a query has no WHERE on the partition key, PostgreSQL will scan all partitions. This can be slower than a single table if you have many partitions. Use sensible partition counts (e.g., monthly, not daily) to balance.

Border values

Remember: lower bound inclusive, upper bound exclusive. A row with event_date = '2025-02-01' goes to events_2025_02, not events_2025_01. Double-check your ranges if data seems to go to the wrong partition.

What you learned & what's next

You now understand how to create partitioned tables by range in PostgreSQL. You can:

• Explain the core idea: parent table + partition children + automatic routing.
• Create a range-partitioned table with monthly or custom ranges.
• Use DEFAULT partitions and know when they help or hurt.
• Compare range, list, and hash partitioning and pick the right strategy.

The next lesson in this track will cover partition management — adding new partitions dynamically, detaching and dropping old ones, and monitoring partition sizes. That's where you'll turn your static partitioned table into a living, auto-maintained system.

Until then, practice by creating a partitioned orders table with quarterly ranges, inserting rows, and verifying pruning with EXPLAIN.