0Pricing
Advanced PostgreSQL: Indexing, Partitioning, Replication · Lesson

Range Partitioning by Time

Learn how to partition large tables by time ranges to keep recent data fast and archive old data efficiently.

Range Partitioning by Time is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Range Partitioning

Range partitioning splits a table into chunks based on a continuous value, most often a date or timestamp.

It is the most common strategy for time-series data such as logs, events, and orders, because old data can be dropped or archived as a whole partition.

Declaring a Range-Partitioned Table

You declare partitioning with PARTITION BY RANGE on the parent table.

CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

Creating Monthly Partitions

Each child partition covers a half-open interval: the lower bound is inclusive and the upper bound is exclusive.

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

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

Half-Open Intervals

The exclusive upper bound prevents gaps and overlaps. A row at exactly 2024-02-01 00:00:00 lands in events_2024_02, never in January.

This makes consecutive partitions perfectly contiguous.

Inserting Routes Automatically

You insert into the parent table. PostgreSQL routes each row to the correct partition based on the range key.

INSERT INTO events (created_at, payload)
VALUES ('2024-01-15', '{"type":"login"}');
-- lands in events_2024_01

A DEFAULT Partition

A DEFAULT partition catches any row that does not match a defined range, avoiding insert errors.

CREATE TABLE events_default PARTITION OF events DEFAULT;

Indexes on Partitions

An index created on the parent is automatically propagated to all current and future partitions.

CREATE INDEX ON events (created_at);

Dropping Old Data Instantly

The killer feature: deleting old data is just DROP TABLE on a partition. No row-by-row DELETE, no bloat, no vacuum pressure.

DROP TABLE events_2024_01;

Querying with Pruning

When the planner sees a range predicate on the partition key it can skip entire partitions. This is called partition pruning.

SELECT count(*) FROM events
WHERE created_at >= '2024-02-10'
  AND created_at <  '2024-02-20';
-- only events_2024_02 is scanned

Automating Partition Creation

Tools like pg_partman create future partitions ahead of time on a schedule, so you never insert into the default partition by accident.

  • Define a retention window
  • Pre-create N future partitions
  • Detach or drop expired ones

Choosing the Range Width

Pick a width that keeps each partition in the tens of millions of rows. Too many tiny partitions hurt planning time; too few huge ones lose pruning benefits.

Quick Check

Why is dropping an old partition better than a bulk DELETE?

Recap

You learned range partitioning by time: declare with PARTITION BY RANGE, create half-open child intervals, rely on automatic routing and pruning, and archive by dropping whole partitions. Automation tools like pg_partman keep the partition set healthy.

Frequently asked questions

Is the “Range Partitioning by Time” lesson free?

Yes — the full text of “Range Partitioning by Time” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.

What will I learn in “Range Partitioning by Time”?

Learn how to partition large tables by time ranges to keep recent data fast and archive old data efficiently. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Advanced PostgreSQL: Indexing, Partitioning, Replication?

No prior experience is required. Advanced PostgreSQL: Indexing, Partitioning, Replication on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Range Partitioning by Time” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?

Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Hash Partitioning for Distribution
  2. Sub-Partitioning Techniques
  3. Managing Partitioned Tables
  4. Range Partitioning by Time
← Back to Advanced PostgreSQL: Indexing, Partitioning, Replication