Range partitioning by date: the 80% case

Capsule description

In capsule 02 you decided when to partition and chose the type. If your table has time-range queries — and that's 80% of real cases: events, logs, metrics, sessions, audit trails — range partitioning by date is the answer. This capsule teaches you PostgreSQL 16's declarative syntax for creating a table partitioned by month, connecting it with async SQLAlchemy 2.0 without touching the ORM code, and verifying with EXPLAIN ANALYZE that the queries only scan the partitions of the requested range.

You're going to work on the Blog API's events table (the same one you took as a case in module 1). You're going to start from a non-partitioned version with 50M rows where a last-month query takes 3.2 seconds. By the end of the capsule, that same query runs in 40 milliseconds on the partitioned version. That delta is the demonstration of the value.

By the close you'll be able to write the CREATE TABLE ... PARTITION BY RANGE for any time-series table, configure a default partition, propagate indexes to the children, and read an EXPLAIN to confirm partition pruning. Capsule 04 does the equivalent for list partitioning; 06 goes deeper into how the planner decides pruning; 08 applies everything to the real migration.

Mental model: the parent table as a router, the children as storage

Declarative range partitioning in PostgreSQL 10+ has clear mechanics. The parent table stores no rows: it only holds metadata (the partition column, the RANGE type, each child's ranges). The child partitions are real tables, each with its file on disk, its indexes, its independent autovacuum.

When you insert a row, PostgreSQL evaluates the partition key's value, decides which child it goes to, and writes there. When you query, the planner looks at the WHERE: if it filters by the partition key, it identifies the partitions of the requested range and scans only those (partition pruning). If it doesn't filter by the partition key, it scans all of them.

┌──────────────────────────────────────────────────────────────┐
│                    INSERT INTO events ...                    │
│                    VALUES (..., '2026-04-15')                │
│                                                              │
│                            │                                 │
│                            ▼                                 │
│              ┌─────────────────────────┐                     │
│              │ events (parent, router) │                     │
│              │ PARTITION BY RANGE      │                     │
│              │   (created_at)          │                     │
│              └────────────┬────────────┘                     │
│                           │                                  │
│           ┌───────────────┼───────────────┐                  │
│           ▼               ▼               ▼                  │
│   ┌──────────────┐ ┌──────────────┐ ┌──────────────┐         │
│   │events_2026_03│ │events_2026_04│ │events_2026_05│         │
│   │ FOR VALUES   │ │ FOR VALUES   │ │ FOR VALUES   │         │
│   │ FROM '2026-  │ │ FROM '2026-  │ │ FROM '2026-  │         │
│   │ 03-01' TO    │ │ 04-01' TO    │ │ 05-01' TO    │         │
│   │ '2026-04-01' │ │ '2026-05-01' │ │ '2026-06-01' │         │
│   └──────────────┘ └──────▲───────┘ └──────────────┘         │
│                           │                                  │
│              The row goes to events_2026_04                  │
│              (the range contains '2026-04-15')               │
└──────────────────────────────────────────────────────────────┘

Three important details of these mechanics:

1. The ranges are half-open: [FROM, TO). The upper bound is not included. A partition FROM '2026-04-01' TO '2026-05-01' covers all of April (2026-04-30 23:59:59.999) but not May 1st at 00:00:00. This avoids ambiguity at the edges but requires care when defining them.

2. Each child can have its indexes. When you create an index on the parent table (PG 11+), PostgreSQL automatically creates it on every existing and future partition. The "global" index doesn't exist physically — they're N local indexes, one per partition.

3. The default partition is the safety net. If you insert a row whose partition key doesn't fall into any defined range, it goes to the DEFAULT partition (if one exists) or the insert fails. The default shouldn't be used as permanent storage — it's only for detecting bugs (data with strange dates, future partitions not created in time).

This architecture explains why range queries win: if you ask for April's data, the planner opens only events_2026_04. The other 23 partitions never get touched. That's why the non-partitioned events table at 50M rows takes 3.2s and the partitioned one takes 40ms.

The complete SQL: creating events partitioned by month

You're going to build the setup step by step. Assume PostgreSQL 16 running locally and a schema with the posts and users tables already existing (from guide #8).

Step 1: create the parent table

CREATE TABLE events (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    post_id     BIGINT,
    event_type  TEXT NOT NULL,
    metadata    JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Three key points in the DDL:

• PARTITION BY RANGE (created_at) declares that this table is partitioned by range over created_at. The declarative syntax replaces the old inheritance method (deprecated). It only applies from PG 10 on.

• PRIMARY KEY (id, created_at) includes the partition key. This is the limitation you saw in capsule 02: any UNIQUE/PRIMARY KEY on a partitioned table must include all the partition key's columns. If you declared PRIMARY KEY (id) alone, PostgreSQL would fail with unique constraint on partitioned table must include all partitioning columns.

• There are no foreign keys going out of events here. If you need an FK from events.user_id to users.id, add it afterwards with ALTER TABLE events ADD CONSTRAINT .... PG 12+ supports FKs from partitioned tables with caveats — it works but verify your specific version.

Step 2: create the child partitions

-- March 2026 partition
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- April 2026 partition
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- May 2026 partition
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

Each partition is a real table. You can query it directly:

SELECT count(*) FROM events_2026_04;
-- Only looks at that partition.

Or query the parent and let the planner route:

SELECT count(*) FROM events
WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01';
-- The planner identifies that only events_2026_04 applies.

Step 3: the default partition (the safety net)

CREATE TABLE events_default PARTITION OF events DEFAULT;

Any insert with a created_at outside the defined ranges (before March 2026 or after May 2026) lands here instead of failing. It's useful while you configure pg_partman (capsule 07) or while you discover data with unexpected dates. But it shouldn't be a permanent store.

Monitor it with an alert:

-- Alert if the default partition accumulates rows
SELECT count(*) FROM events_default;
-- If > 1000, investigate: a future partition not created in time? a bug in the data?

Step 4: indexes that propagate

-- Index on the parent table
CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_post_id_created_at_idx ON events (post_id, created_at DESC);

PostgreSQL 11+ propagates these indexes automatically to all the existing partitions and creates them on future partitions when they show up. If you list indexes with \di+ events* in psql, you're going to see:

events_user_id_idx                          (on the parent, logical)
events_2026_03_user_id_idx                  (on the child, real)
events_2026_04_user_id_idx                  (on the child, real)
events_2026_05_user_id_idx                  (on the child, real)
events_default_user_id_idx                  (on the child, real)

The "parent's index" doesn't store anything physical — it's metadata. The real ones are the children's, each small (because its partition is small) and it fits in RAM.

Worked example: 50M rows, before and after

Let's simulate the complete operation with real data. Assume you have a non-partitioned events_old table with 50 million rows distributed across 12 months (~4M rows per month on average).

Setup: populate the non-partitioned table

-- Create the non-partitioned table for comparison
CREATE TABLE events_old (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    post_id BIGINT,
    event_type TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX events_old_user_id_idx ON events_old (user_id);
CREATE INDEX events_old_created_at_idx ON events_old (created_at);

-- Generate synthetic data: 50M rows spread over 12 months
-- (This takes several minutes. Reduce to 5M rows if you're testing locally.)
INSERT INTO events_old (user_id, post_id, event_type, created_at)
SELECT
    (random() * 100000)::bigint AS user_id,
    (random() * 10000)::bigint AS post_id,
    (ARRAY['view', 'like', 'comment', 'share'])[1 + (random() * 3)::int] AS event_type,
    NOW() - (random() * INTERVAL '365 days') AS created_at
FROM generate_series(1, 50000000);

ANALYZE events_old;

The last-month query on the NON-partitioned table

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), event_type
FROM events_old
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY event_type;

Typical output:

GroupAggregate  (cost=842310.45..842311.95 rows=4 width=16)
                (actual time=3215.892..3215.893 rows=4 loops=1)
  Group Key: event_type
  Buffers: shared hit=42 read=421688
  ->  Sort  (cost=842310.45..842320.45 rows=4000 width=8)
            (actual time=3215.812..3215.817 rows=4173291 loops=1)
        ->  Bitmap Heap Scan on events_old
              (cost=12842.10..842231.32 rows=4000 width=8)
              (actual time=124.421..2891.221 rows=4173291 loops=1)
            Recheck Cond: (created_at >= (now() - '30 days'::interval))
            Buffers: shared hit=42 read=421688
            ->  Bitmap Index Scan on events_old_created_at_idx
                  (cost=0.00..12841.10 rows=4000 width=0)
                  (actual time=89.213..89.213 rows=4173291 loops=1)

Planning Time: 0.412 ms
Execution Time: 3216.231 ms

Reading this plan:

• Total: 3.2 seconds.
• The Bitmap Heap Scan reads 421k blocks from disk (read=421688) — that is, ~3.4 GB of data.
• The index helps identify the rows (Bitmap Index Scan) but you still have to read them all from the heap.

This is typical of large tables with range queries: the index points at the rows, but the cost is in reading the massive heap.

Setup: populate the partitioned table

Now replicate the data into the partitioned table (events):

-- Create the 12 monthly partitions (a range from a year back until today)
DO $$
DECLARE
    start_month DATE := DATE_TRUNC('month', NOW() - INTERVAL '12 months');
    i INT;
    partition_name TEXT;
    partition_start DATE;
    partition_end DATE;
BEGIN
    FOR i IN 0..12 LOOP
        partition_start := start_month + (i || ' months')::INTERVAL;
        partition_end := start_month + ((i + 1) || ' months')::INTERVAL;
        partition_name := 'events_' || TO_CHAR(partition_start, 'YYYY_MM');
        EXECUTE FORMAT(
            'CREATE TABLE %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
            partition_name, partition_start, partition_end
        );
    END LOOP;
END $$;

-- Migrate the data (using INSERT ... SELECT, simple — in production you use zero-downtime, capsule 08)
INSERT INTO events (user_id, post_id, event_type, metadata, created_at)
SELECT user_id, post_id, event_type, metadata, created_at
FROM events_old;

ANALYZE events;

The last-month query on the partitioned table

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), event_type
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY event_type;

Typical output (PG 16):

GroupAggregate  (cost=82310.45..82311.95 rows=4 width=16)
                (actual time=39.812..39.814 rows=4 loops=1)
  Group Key: event_type
  Buffers: shared hit=128 read=34201
  ->  Sort
        ->  Append  (cost=0.42..78231.32 rows=4173291 width=8)
                   (actual time=0.052..32.421 rows=4173291 loops=1)
            ->  Seq Scan on events_2026_04 events_1
                  Filter: (created_at >= (now() - '30 days'::interval))
                  Rows Removed by Filter: 0
            ->  Seq Scan on events_2026_05 events_2
                  Filter: (created_at >= (now() - '30 days'::interval))
                  Rows Removed by Filter: 0

Planning Time: 0.812 ms
Execution Time: 39.921 ms

Reading this plan:

• Total: 40 milliseconds. ~80× faster.
• Append lists the partitions the planner decided to scan: only events_2026_04 and events_2026_05 (the ones covering the last 30 days). The other 11 partitions don't even appear in the plan — that's partition pruning.
• Buffers: read=34201 (~280 MB) instead of 3.4 GB. The difference is that it only reads the relevant partitions.

This is what justifies the whole module. Without partitioning, the query scans 50M rows. With partitioning, it scans ~8M (the ones in the requested range). Same SQL, same schema, same index. The only thing that changed is that the table is physically divided.

Working with partitioned tables from async SQLAlchemy 2.0

The good news: the ORM doesn't notice. SQLAlchemy interacts with the parent table like any normal table. The partitioning happens at the PostgreSQL level.

The model

# models/event.py
from datetime import datetime
from sqlalchemy import BigInteger, String
from sqlalchemy.dialects.postgresql import JSONB, TIMESTAMP
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Event(Base):
    __tablename__ = "events"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    post_id: Mapped[int | None] = mapped_column(BigInteger)
    event_type: Mapped[str] = mapped_column(String, nullable=False)
    # Careful: the attribute CANNOT be called `metadata` (see below).
    metadata_: Mapped[dict] = mapped_column("metadata", JSONB, default=dict)
    created_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True),
        primary_key=True,  # Part of the compound PK
        nullable=False,
    )

Three differences vs a non-partitioned model:

1. created_at is part of the primary key. It reflects the schema's reality in the ORM. Without this, SQLAlchemy can generate UPDATEs that assume id is the unique key and behave badly.

2. The __tablename__ points at the parent table. Never interact with the child partitions from the ORM. If you need something specific from a child (rare), use plain text() SQL.

3. The attribute is called metadata_, not metadata. This has nothing to do with partitioning, but it's going to bite you anyway: metadata is a reserved name on DeclarativeBase (it's where SQLAlchemy stores the MetaData object). If you declare metadata: Mapped[dict], the model blows up on import:

   sqlalchemy.exc.InvalidRequestError: Attribute name 'metadata' is reserved
   for the MetaData instance when using a declarative base class.
   

   The way out is the pattern you already used in module 2: name the attribute metadata_ and pass the real column name ("metadata") as the first argument to mapped_column. The column in PostgreSQL is still called metadata; only the Python name changes.

Insert

# services/event_service.py
from sqlalchemy.ext.asyncio import AsyncSession
from datetime import datetime, timezone

async def record_event(
    session: AsyncSession,
    user_id: int,
    event_type: str,
    post_id: int | None = None,
    metadata: dict | None = None,
) -> Event:
    event = Event(
        user_id=user_id,
        event_type=event_type,
        post_id=post_id,
        metadata_=metadata or {},
        created_at=datetime.now(timezone.utc),
    )
    session.add(event)
    await session.flush()
    return event

PostgreSQL receives the insert on events, evaluates created_at, and routes to the right partition. To SQLAlchemy, it's a single table.

A query with a range filter (taking advantage of pruning)

# services/event_service.py
from sqlalchemy import select, func
from datetime import datetime, timedelta, timezone

async def count_events_last_30_days(session: AsyncSession) -> dict[str, int]:
    cutoff = datetime.now(timezone.utc) - timedelta(days=30)

    stmt = (
        select(Event.event_type, func.count().label("total"))
        .where(Event.created_at >= cutoff)
        .group_by(Event.event_type)
    )
    result = await session.execute(stmt)
    return {row.event_type: row.total for row in result.all()}

PostgreSQL applies partition pruning because the WHERE filters by created_at (the partition key). The plan is the same one you saw above (~40ms on the 50M-row table).

A query WITHOUT a partition key filter (doesn't take advantage of pruning)

async def get_events_by_user(session: AsyncSession, user_id: int) -> list[Event]:
    stmt = (
        select(Event)
        .where(Event.user_id == user_id)
        .order_by(Event.created_at.desc())
        .limit(100)
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

This query doesn't filter by created_at. PostgreSQL scans all the partitions (12 in this case). The plan looks like this:

Limit
  ->  Sort
        ->  Append
              ->  Index Scan on events_user_id_idx (events_2025_05)
              ->  Index Scan on events_user_id_idx (events_2025_06)
              ->  Index Scan on events_user_id_idx (events_2025_07)
              ...
              ->  Index Scan on events_user_id_idx (events_2026_05)

Each per-partition index is small (4M rows), but that's 12 lookups. If this query is very frequent, consider:

• Adding an implicit date filter in the app: WHERE user_id = ? AND created_at > NOW() - INTERVAL '90 days' (assuming that for a "user feed" you don't need events from a year ago).
• If you really do need all the user's historical events: the random-access pattern isn't ideal for a table partitioned by date. Consider whether that query justifies a specific materialized view (module 5).

Why does this matter in real work?

1. It's the default tool for time-series tables. In any app with events, logs, metrics, or growing temporal data, range partitioning by date is the answer 80% of the time. Knowing how to write it from memory, without googling, sets you apart. What you type during the ticket is what you learned today.

2. The 3.2s → 40ms difference is one users feel. Any dashboard, paginated listing, or report that takes >1s degrades perceived UX. Partitioning the events table drops that latency 80×. It's the kind of change that shows up in your product's analytics, not just in DB metrics.

3. DROP PARTITION replaces a massive DELETE. When the ticket "delete events older than 18 months" arrives, DROP TABLE events_2024_10 runs in ~10ms and frees the disk. Without partitioning, the equivalent DELETE can take 30-60 minutes and hold locks that block inserts. That operational difference is weeks of work avoided per quarter.

4. The pattern replicates directly to audit_logs, metrics, sessions, notifications. Once you internalize range by date, you apply it identically to any other time-series table. The steps are the same: a compound PRIMARY KEY with the partition key, monthly partitions (or daily if the volume is >100M/month), a default partition with monitoring, indexes that propagate.

5. The connection with guide #13 (audit logs). Guide #13 introduced audit_logs and mentioned partitioning as a technique for keeping the table fast. What you learn here is exactly how it's done. If you already have a non-partitioned audit_log in production, you can apply this pattern with the zero-downtime migration from capsule 08.

Traps and common mistakes

Mistake 1 (conceptual): confusing the half-open range [FROM, TO)

Symptom: you define FOR VALUES FROM ('2026-04-01') TO ('2026-04-30') thinking it covers all of April. Inserts with created_at = '2026-04-30 12:00:00' go to the default partition (or fail).

Why it happens: PostgreSQL uses half-open ranges. TO ('2026-04-30') means "strictly less than 2026-04-30 00:00:00". The whole 30th is left out.

How to tell: the correct convention for partitioning by month is FROM ('2026-04-01') TO ('2026-05-01'). The first day of the next month as the upper bound. This is what pg_partman (capsule 07) does by default and it's the industry convention.

How to fix it:

-- INCORRECT: loses the 30th
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-04-30');

-- CORRECT: covers all of April
CREATE TABLE events_2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

Mistake 2 (practical): forgetting to include the partition key in the primary key

Symptom: you try CREATE TABLE events (id BIGSERIAL PRIMARY KEY, ...) PARTITION BY RANGE (created_at) and you get the error unique constraint on partitioned table must include all partitioning columns.

Why it happens: PostgreSQL can't guarantee global uniqueness without scanning all the partitions. The solution: include the partition key in any PK/UNIQUE.

How to fix it:

-- INCORRECT
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL,
    ...
) PARTITION BY RANGE (created_at);  -- ERROR

-- CORRECT
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    ...,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Implication for SQLAlchemy: declare created_at as primary_key=True in the model too, so the ORM reflects reality.

Mistake 3 (conceptual): assuming the default partition is safe as a store

Symptom: you create events_default, you leave it running. Six months later it has 80M rows because there was a bug where some inserts came in with created_at = NULL or dates from 1970.

Why it happens: the default is a safety net for detecting bugs, not a legitimate store. Rows in the default usually indicate: (a) future partitions not created in time, (b) data with invalid dates, (c) old partitions dropped but old data still arriving.

How to prevent it:

-- Daily alert
SELECT count(*) FROM events_default;
-- If > 1000, investigate BEFORE taking action.

-- If you decide to move them (after investigating), use:
INSERT INTO events SELECT * FROM events_default WHERE created_at IS NOT NULL;
TRUNCATE events_default;

Mistake 4 (practical): the query doesn't use the partition key, there's no pruning

Symptom: after partitioning, a query like SELECT * FROM events WHERE user_id = 42 LIMIT 100 doesn't benefit (it can be slower than before).

Why it happens: the query doesn't filter by created_at. The planner opens all 12 partitions, does an Index Scan on each one, merges the results, and applies the LIMIT. Before it was 1 lookup; now it's 12.

How to tell: run EXPLAIN. If you see an Append with all the partitions listed, there's no pruning.

How to mitigate:

1. Add an implicit date filter in the app when it makes sense:

   stmt = select(Event).where(
       Event.user_id == user_id,
       Event.created_at > datetime.now() - timedelta(days=90)
   ).limit(100)

2. If you really need the user's complete history, evaluate a specific materialized view (module 5) or an auxiliary table indexed by user_id.

Mistake 5 (conceptual): thinking future partitions create themselves

Symptom: you configure 12 partitions for the next 12 months. The year passes. The new partitions don't show up. Inserts with created_at = '2027-06-01' start going to the default partition (or failing if there's no default).

Why it happens: native PostgreSQL does not create future partitions automatically. Creating the new partitions in time is an operational decision. Capsule 07 (pg_partman) automates this. Without pg_partman, someone has to run scripts or cron jobs manually.

How to prevent it:

• Short term (while you aren't using pg_partman): a monthly cron job that creates the next month's partition at least 7 days in advance.
• Medium term: install pg_partman (capsule 07), which automates creation + dropping of old ones + retention.

Exercises

Exercise 1: create a metrics table partitioned by day

You have to design a metrics table for storing application metrics: each metric has a metric_name, value (float), tags (JSONB), recorded_at. Expected volume: 5M records/day. Retention: 30 days.

Write the complete DDL: parent table, today's and tomorrow's partitions, default partition, and an index over metric_name.

-- Parent table
CREATE TABLE metrics (
    id          BIGSERIAL,
    metric_name TEXT NOT NULL,
    value       DOUBLE PRECISION NOT NULL,
    tags        JSONB DEFAULT '{}'::jsonb,
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- Today's partition
CREATE TABLE metrics_2026_05_02 PARTITION OF metrics
    FOR VALUES FROM ('2026-05-02') TO ('2026-05-03');

-- Tomorrow's partition
CREATE TABLE metrics_2026_05_03 PARTITION OF metrics
    FOR VALUES FROM ('2026-05-03') TO ('2026-05-04');

-- Default
CREATE TABLE metrics_default PARTITION OF metrics DEFAULT;

-- Index on the parent (it propagates to the children)
CREATE INDEX metrics_metric_name_recorded_at_idx
    ON metrics (metric_name, recorded_at DESC);

Exercise 2: read an EXPLAIN to detect pruning

Given this plan, decide: is there partition pruning? How many partitions get scanned? What would you change in the query to take advantage of pruning?

Aggregate
  ->  Append
        ->  Seq Scan on events_2025_05 events_1
        ->  Seq Scan on events_2025_06 events_2
        ->  Seq Scan on events_2025_07 events_3
        ->  Seq Scan on events_2025_08 events_4
        ->  Seq Scan on events_2025_09 events_5
        ->  Seq Scan on events_2025_10 events_6
        ->  Seq Scan on events_2025_11 events_7
        ->  Seq Scan on events_2025_12 events_8
        ->  Seq Scan on events_2026_01 events_9
        ->  Seq Scan on events_2026_02 events_10
        ->  Seq Scan on events_2026_03 events_11
        ->  Seq Scan on events_2026_04 events_12
        ->  Seq Scan on events_2026_05 events_13

The original query was:

SELECT count(*) FROM events WHERE event_type = 'view';

SELECT count(*) FROM events
WHERE event_type = 'view'
  AND created_at >= DATE_TRUNC('month', NOW());

Aggregate
  ->  Append
        ->  Seq Scan on events_2026_05 events_1
              Filter: (event_type = 'view')

Exercise 3: SQLAlchemy 2.0 async — a query with partition pruning

Write an async function that, given a user_id and a range (start_date, end_date), returns all of that user's events in that range. Make sure the query takes advantage of partition pruning.

from datetime import datetime
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

async def get_user_events_in_range(
    session: AsyncSession,
    user_id: int,
    start_date: datetime,
    end_date: datetime,
) -> list[Event]:
    stmt = (
        select(Event)
        .where(
            Event.user_id == user_id,
            Event.created_at >= start_date,
            Event.created_at < end_date,
        )
        .order_by(Event.created_at.desc())
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

Exercise 4: detect and fix a half-open boundary

Your teammate created this partition and now complains that some March 31st inserts go to the default partition:

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-03-31');

What's the bug and how do you fix it?

-- Option 1: drop the partition and recreate it correctly
DROP TABLE events_2026_03;
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- But first you move the default's data that belongs to March:
INSERT INTO events SELECT * FROM events_default
WHERE created_at >= '2026-03-31' AND created_at < '2026-04-01';
DELETE FROM events_default
WHERE created_at >= '2026-03-31' AND created_at < '2026-04-01';

Exercise 5: add a composite index that propagates

You need to optimize two queries over events:

1. WHERE post_id = ? ORDER BY created_at DESC LIMIT 50 (a post's activity feed)
2. WHERE user_id = ? AND event_type = 'like' AND created_at > NOW() - INTERVAL '7 days' (a user's recent likes)

Write the two indexes, making sure they propagate to all the existing and future partitions.

-- Index for query 1: a post's feed
CREATE INDEX events_post_id_created_at_idx
    ON events (post_id, created_at DESC);

-- Index for query 2: a user's recent likes
CREATE INDEX events_user_id_event_type_created_at_idx
    ON events (user_id, event_type, created_at DESC);

\di+ events*

CREATE INDEX events_user_id_likes_recent_idx
    ON events (user_id, created_at DESC)
    WHERE event_type = 'like';

Summary and next step

In this capsule you built the 80% case: range partitioning by date over events. Specifically:

• The complete declarative syntax: a parent table with PARTITION BY RANGE (created_at), child partitions with FOR VALUES FROM ... TO ..., a default partition as a safety net, indexes that propagate automatically.

• The half-open range convention: [FROM, TO) with the first day of the next month as the upper bound. The cause of the most common bug if it isn't respected.

• A compound primary key with the partition key: PRIMARY KEY (id, created_at) because PostgreSQL requires it. Reflect it in SQLAlchemy with primary_key=True on created_at.

• A demonstration with real numbers: the same query, 3.2s unpartitioned vs 40ms partitioned (over 50M rows). The delta justifies the surgery.

• Transparent async SQLAlchemy 2.0 integration: the ORM doesn't notice. Queries with a partition key filter take advantage of pruning automatically.

Before moving on, you should be able to:

• Write the complete DDL for partitioning a time-series table (parent, children, default, indexes) without copying.
• Identify bugs in the range definition (the half-open boundary).
• Read an EXPLAIN and recognize whether there's partition pruning (by looking at the Append).
• Write SQLAlchemy queries that take advantage of pruning.

Next capsule — List partitioning by category/tenant. Range covers 80% (time-series). When the partition column is discrete (a fixed set of tenants, regions, statuses), the right type is LIST. Capsule 04 teaches you the syntax, the typical multi-tenant SaaS cases, and the combination with RLS for security isolation. You're going to see why "RLS or partitioning" is a false dichotomy and how they complement each other in production.

Resources

1. PostgreSQL 16 — CREATE TABLE PARTITION BY RANGE — the official reference. The "PARTITION BY" section covers the complete syntax.
2. PostgreSQL 16 — Table Partitioning, "Declarative Partitioning" — section 5.11.2 explains the declarative model in depth.
3. Crunchy Data — Postgres Partitioning Best Practices — recommended patterns for production, including naming and range conventions.
4. depesz — Partition pruning details — the history and details of how the planner prunes.
5. SQLAlchemy 2.0 docs — Composite Primary Keys — how to declare composite PKs in the ORM, necessary for partitioned tables.
6. Hironobu Suzuki — Internals of PostgreSQL: Table Partitioning — how PostgreSQL stores partitions on disk. Useful for understanding the physical model.
7. pgDash — Postgres Partition Manager — a deep dive into partitioning performance with real benchmarks.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: List partitioning by category/tenant — multi-tenant SaaS and the combination with RLS.