SQL Partitioning in PostgreSQL - What It Is, When to Use It

Wed Oct 21 2026

⚠️ This post is generated by LLM, read with caution.

SQL Partitioning in PostgreSQL - What It Is, When to Use It

When your PostgreSQL tables grow into the millions of rows, queries start getting slower. You’ve probably seen a simple query that used to finish in milliseconds suddenly take seconds or minutes. One tool PostgreSQL provides to deal with this is partitioning - splitting a large table into smaller pieces while keeping it looking like one table to your application.

Here’s what it means in practice, when it’s worth using, and what the execution plans actually show.

Think of a partitioned table as a logical container made up of several smaller physical tables behind the scenes. You define a partition key - usually a date column - and PostgreSQL decides which physical table each row goes into. From your application’s point of view, nothing changes: you still SELECT from the same table name, insert rows the same way, and write joins without knowing that the data is physically stored across multiple tables.

PostgreSQL supports three partitioning strategies: range (rows go into partitions based on a value range), list (based on an explicit list of values), and hash (distributed via a hash function). We’ll work with range partitioning by date, since it’s the most common use case for event logs, time-series data, and financial records.

Partitioning isn’t a free lunch. It introduces maintenance complexity - you need to create new partitions as time goes on - and adds a tiny bit of overhead on writes because PostgreSQL must check which partition each row belongs to. You shouldn’t use it just because your table is “big”.

Good candidates have these properties:

• The table has hundreds of thousands or millions of rows (with fewer rows, the effect is usually negligible).
• Queries regularly filter on the partition key column - PostgreSQL can skip entire partitions without reading them.
• You need to drop old data quickly. Instead of a slow DELETE that generates massive write-ahead log and holds locks, you can simply DROP TABLE events_2022.

Don’t expect partitioning to help if your queries filter on columns other than the partition key or if you frequently scan the entire table anyway.

The Code

We’ll compare an unpartitioned table against a range-partitioned one. The demo creates both tables from 50,000 rows spanning two years and then runs EXPLAIN (ANALYZE, BUFFERS) to show what PostgreSQL does differently under the hood.

The key SQL patterns to watch for:

1. PARTITION BY RANGE (event_date) - tells PostgreSQL to split the table by date ranges.
2. FOR VALUES FROM (...) TO (...) - defines the boundary of each partition.
3. The PRIMARY KEY must include the partition key (id, event_date) because every row’s identity needs to be unique within its own partition.

Here is the full script:

"""
Demonstrate PostgreSQL partitioning benefits with 50k rows.

Compares an unpartitioned table vs a range-partitioned table
across 2-year data, showing EXPLAIN (ANALYZE, BUFFERS) plans.
"""
import psycopg2
import random
from datetime import date, timedelta

HOST = "is-sql-partition-postgresql-when-use-pg"
DB   = "postgres"
USR  = "postgres"


def connect():
    return psycopg2.connect(host=HOST, dbname=DB, user=USR)


conn = connect()
cur = conn.cursor()

# ---- Clean slate ----
cur.execute("DROP TABLE IF EXISTS events_flat, events_yearly CASCADE;")
conn.commit()

# ---- Unpartitioned table ----
cur.execute("""
    CREATE TABLE events_flat (
        id          bigserial PRIMARY KEY,
        event_date  date NOT NULL,
        event_type  text NOT NULL,
        payload     jsonb DEFAULT '{}'::jsonb
    );
""")

# ---- Partitioned table with two child partitions ----
cur.execute("""
    CREATE TABLE events_yearly (
        id          bigserial,
        event_date  date NOT NULL,
        event_type  text NOT NULL,
        payload     jsonb DEFAULT '{}'::jsonb,
        PRIMARY KEY (id, event_date)
    ) PARTITION BY RANGE (event_date);

    CREATE TABLE events_yearly_2023
        PARTITION OF events_yearly
        FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

    CREATE TABLE events_yearly_2024
        PARTITION OF events_yearly
        FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
""")

conn.commit()
print("Created two identical table structures.\n")

# ---- Seed ~50,000 rows spanning 2 years ----
def generate_rows(n):
    rows = []
    base = date(2023, 1, 1)
    for _ in range(n):
        day_offset = random.randint(0, 730)
        event_date = base + timedelta(days=day_offset)
        event_type = random.choice(["click", "view", "purchase", "login"])
        rows.append((event_date.isoformat(), event_type))
    return rows

rows = generate_rows(50000)

cur.executemany("""
    INSERT INTO events_flat (event_date, event_type, payload)
    VALUES (%s, %s, '{}'::jsonb)
""", rows)

cur.executemany("""
    INSERT INTO events_yearly (event_date, event_type, payload)
    VALUES (%s, %s, '{}'::jsonb)
""", rows)
conn.commit()

for tbl in ["events_flat", "events_yearly"]:
    cur.execute(f"SELECT COUNT(*) FROM {tbl}")
    print(f"  {tbl}: {cur.fetchone()[0]} rows")
cur.execute("SELECT COUNT(*) FROM events_yearly_2023")
print(f"  events_yearly_2023 (child partition): {cur.fetchone()[0]} rows")
cur.execute("SELECT COUNT(*) FROM events_yearly_2024")
print(f"  events_yearly_2024 (child partition): {cur.fetchone()[0]} rows\n")

# ---- ANALYZE so the planner has up-to-date statistics ----
for tbl in ["events_flat", "events_yearly"]:
    cur.execute(f"ANALYZE {tbl}")
conn.commit()


# ---- Run EXPLAIN (ANALYZE, BUFFERS) and pretty-print the plan ----
def explain(label, sql):
    cur.execute(
        f"EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) " + sql.rstrip().rstrip(";")
    )
    rows = cur.fetchall()
    print("=" * 64)
    print(f"  {label}")
    print("=" * 64)
    for row in rows:
        plan_str = str(row[0])
        # Each plan line comes wrapped; strip quotes and commas
        for line in plan_str.splitlines():
            print(line.strip("'").strip())
    print()


# Query 1 — filter on 2023 only (should find ~50% of rows)
sql_2023 = """
SELECT COUNT(*) FROM %s
WHERE event_type = 'click'
  AND event_date >= '2023-01-01' AND event_date < '2024-01-01'
"""

explain("Unpartitioned table — Seq Scan on ALL 50,000 rows",
        sql_2023 % "events_flat")

explain("Partitioned table — only scans events_yearly_2023\n"
        "(PostgreSQL pruned away the 2024 partition)",
        sql_2023 % "events_yearly")

# Query 2 — filter on 2024 only (symmetric)
sql_2024 = """
SELECT COUNT(*) FROM %s
WHERE event_type = 'click'
  AND event_date >= '2024-01-01' AND event_date < '2025-01-01'
"""

explain("Unpartitioned table — Seq Scan on ALL 50,000 rows",
        sql_2024 % "events_flat")

explain("Partitioned table — only scans events_yearly_2024\n"
        "(PostgreSQL pruned away the 2023 partition)",
        sql_2024 % "events_yearly")

# Query 3 — no date filter; pruning can't help
print("=" * 64)
print("  No date filter: partition pruning CANNOT skip anything")
print("=" * 64)
cur.execute("""
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT COUNT(*) FROM events_yearly WHERE event_type = 'click'
""")
for row in cur.fetchall():
    for line in str(row[0]).splitlines():
        print(line.strip("'").strip())

cur.close()
conn.close()
print("\nDone!")

The Python connects to PostgreSQL, creates both tables (one flat, one partitioned), seeds them with 50,000 rows each spanning 2023 and 2024, runs ANALYZE so the query planner has up-to-date statistics, then compares execution plans for a filtered query.

Running It

The script creates two identical table structures, populates both with 50,000 rows (roughly split 50/50 between 2023 and 2024), and then runs four queries - filtering on 2023 data and on 2024 data against each table - reporting EXPLAIN (ANALYZE, BUFFERS) output.

Watch what happens when the same query hits both tables. There are two things to look for in the EXPLAIN output:

1. Rows Removed by Filter - on the unpartitioned table, this number is large (the query reads all 50k rows and discards most of them to find the matching ~25k). On the partitioned table, this line only appears inside one child partition because PostgreSQL pruned away the other.
2. Buffers: shared hit=N - on the unpartitioned table, PostgreSQL reads buffers from the entire table (a higher number like 300+). On the partitioned table, it reads roughly half that many because it only touches one partition.

The execution plan for the partitioned table when querying for a specific year shows something special: the other child partition doesn’t appear at all. Where the flat table has Seq Scan on events_flat, the partitioned version has Seq Scan on events_yearly_2023 events_yearly (or events_yearly_2024). The pruned-away partition isn’t just skipped - it’s not in the plan.

Takeaway

Partitioning works by making queries that filter on the partition key skip entire partitions at planning time. If your queries always look at a subset of data defined by date, range, or list boundaries, partitioning can cut read work roughly in half when you have two partitions - or much more when you have dozens.

But it’s not magic: remove the filter on the partition key and PostgreSQL falls back to scanning everything anyway. Choose your partition key carefully - it should match how most queries actually access your data.