PostgreSQL in Depth Module 7 – Partitioning large tables

Declarative partitioning: keys and pruning

Pick range, list or hash in PostgreSQL 18, design primary keys that include the partition key, read partition pruning in EXPLAIN, and use partitionwise joins.

Intermediate
15 minutes
Examples run with PostgreSQL 18.6 and Python 3.14.8
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answers to its 3 interview questions.

What you will learn

Decide whether a table should be partitioned and pick range, list or hash
Design primary keys and unique constraints that include every partition key column
Read partition pruning in EXPLAIN at planning time and at execution time
Use partitionwise joins and aggregates where they pay off

Before you start

Reading EXPLAIN ANALYZE in PostgreSQL 18

What you’ll learn · Before you start

What you will learn

Decide whether a table should be partitioned and pick range, list or hash
Design primary keys and unique constraints that include every partition key column
Read partition pruning in EXPLAIN at planning time and at execution time
Use partitionwise joins and aggregates where they pay off

Before you start

Reading EXPLAIN ANALYZE in PostgreSQL 18

On this page

A yearly events table of 60,000 rows is asked for the latest months of data, and the plan reads just one of its three partitions: Seq Scan on ev_2026, 16,550 rows kept, 3,245 removed by filter, no Append above it. Partitioning does not speed every query up on its own; what makes a partition plan short is pruning, which the planner proves from each partition’s bounds, and partitioning’s other win is the maintenance it makes cheap, like dropping a whole year as one DROP TABLE.

You will decide when partitioning pays off, pick between range, list and hash by workload, write a primary key that includes the partition key, read pruning at planning time and at execution time, and switch on partitionwise joins only for the queries that need them.

Why partition, and when the planning cost is not worth it

declarative partitioning: PostgreSQL's built-in way of splitting one logical table into child tables (partitions), each with its own files and indexes, by a declared partition key. The parent holds no data. turns one logical table into many physical ones, each with its own files, indexes and statistics. Two of the benefits the manual lists matter most: the planner skips partitions that cannot hold matching rows (partition pruning, section 4 below), and bulk removal becomes a DROP TABLE or ALTER TABLE ... DETACH PARTITION instead of a row-by-row DELETE and the VACUUM that follows. The partition-maintenance lesson of this module teaches the maintenance side; this one teaches the planning side.

The returns are not without cost. Every partition is a relation with its own catalog rows, index files and lock entry, and every query that touches the parent must open every partition that pruning did not remove. The manual says the planner handles up to a few thousand partitions well when typical queries prune all but a few. The release notes of PostgreSQL 18 list three improvements: planning is faster on tables with many partitions, a partitionwise join fits more queries and takes less memory, and VACUUM and ANALYZE take ONLY to process the parent without its children. Two rules changed too: an unlogged partitioned table is now refused outright, and a unique index of an access method other than B-tree may now cover the partition key.

Timeline · 5 events in order What PostgreSQL 18 changed for partitioned tables Walk through the five partitioning items in the release notes, in the order they help a running system.

Planner

Faster planning on partitioned tables lower cost

The planner opens fewer catalog entries and reuses more work when it builds plans that touch many partitions, so each query spends less time deciding where to look.
ANALYZE ONLY

VACUUM and ANALYZE take ONLY to skip children new keyword

Running ANALYZE ONLY events on a parent table processes just the parent, not every partition under it. Useful because autovacuum keeps the children up to date on its own.
Partitionwise

Partitionwise joins fit more plans fewer refusals

The planner now proves the pair-by-pair plan safe in cases it refused in earlier versions, and the memory a partitionwise plan takes grew smaller.
Unique keys

Unique indexes beyond B-tree on the key wider choice

A unique index of an access method other than B-tree, which only extensions provide, may now cover the partition key and serve a materialized view.
Unlogged

Unlogged partitioned tables are refused cleaner model

PostgreSQL once accepted CREATE UNLOGGED TABLE on a partitioned parent but kept its children logged, which crashed assumptions. The parent is now refused with an error.

Key idea

Partition for maintenance you can do by moving a whole file, and for pruning the planner can prove. A table too small to feel either return, or one whose queries never match the partition key, is better off as one relation with the right indexes.

Checkpoint

Check: When partitioning pays off

Question 1 of 2 Which of these are returns the PostgreSQL manual lists as reasons to partition a table?
Choose every answer that is right.

Every query gets faster even when its WHERE clause does not include the partition key A unique constraint can be enforced across partitions without naming the partition key Bulk removal becomes a DROP TABLE or ALTER TABLE DETACH PARTITION instead of a slow DELETE and VACUUM The planner can skip whole partitions that cannot hold matching rows
Show the answer: Question 1 of 2
Answer:
- Bulk removal becomes a DROP TABLE or ALTER TABLE DETACH PARTITION instead of a slow DELETE and VACUUM
- The planner can skip whole partitions that cannot hold matching rows
Partitioning helps queries only when pruning fires, and makes retention cheap because a whole file can be dropped. Everything else, PostgreSQL 18's planner wins included, reduces the price of partitioning rather than adding returns.
Question 2 of 2 Which of these is new in PostgreSQL 18 for partitioned tables?
Choose one answer.

Partition pruning now works without the partition key in the WHERE clause ANALYZE ONLY on the parent processes just the parent, not every partition under it Unlogged partitioned tables are allowed again A primary key no longer needs to include the partition key column

Show the answer: Question 2 of 2

Answer: ANALYZE ONLY on the parent processes just the parent, not every partition under it

PostgreSQL 18 also lets a unique index of another access method than B-tree cover the partition key, lowers partitionwise memory, and makes the planner faster on partitioned tables.

Range, list or hash: pick by the shape of the key

PostgreSQL supports three partitioning methods, and the manual’s advice is to pick the column that appears most often in WHERE clauses on the table. Each method answers a different shape of partition key: The column or expression list a partitioned table is split by. A primary key, unique constraint or exclusion constraint on the table must include every column of the partition key.:

Range divides a column with order into non-overlapping intervals, closed at the lower end and open at the upper (FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')). Natural for dates or ids that climb with time, because every interval ages out together.
List names the values each partition catches (FOR VALUES IN ('IN', 'LK', 'NP')). Fits a column with a small fixed vocabulary, like a tenant id, a country code or a region.
Hash splits by a modulus and remainder on the hash of the key (FOR VALUES WITH (MODULUS 8, REMAINDER 0)). Fits a column where every value is a point lookup, like a customer_id with heavy, uneven traffic, because the hash spreads it evenly across partitions.

Answer two questions about your workload, and the table rules out the methods that cannot help:

Interactive · answer and compare Range, list or hash? Answer two questions about your table's heavy query, and the table rules out methods that do not fit.

Answer the questions above: the table then marks what fits your case and says why the rest does not.

Three partitioning methods against the four things most heavy workloads care about.
Method	Ordered key, like a date	Equality lookup on a point value	Drop a slice in one step	Even spread across partitions
Range Reads first in planning: the partition bounds	Good natural fit: FROM lower TO upper per interval	Mixed works, but reads one partition only when the predicate matches a range	Good the whole year, month or week is one partition to detach	Poor the newest range always carries the heaviest writes
List Reads first in planning: the list of values	Poor no ordering between the listed values; range queries read every partition	Good one listed value lives in one named partition	Good a retired tenant or region is one partition to detach	Mixed depends on how often each listed value appears
Hash Reads first in planning: the modulus and remainder	Poor the hash hides every ordering, so a range predicate prunes nothing	Good the equality fixes the remainder, so one partition is read	Poor no slice is a hash of adjacent values; you cannot drop old data by hash	Good the hash spreads values across every partition on average

How the answers decide

“Which column drives the heaviest filters on this table?” A column with order, like a date: rules out List and Hash. Range matches the shape of the heavy filter; list and hash prune nothing on a range predicate.
“Which column drives the heaviest filters on this table?” A short fixed list of values, like region: rules out Range and Hash. List matches an equality on a short vocabulary; hash would spread the values across every partition.
“Which column drives the heaviest filters on this table?” Point lookups on many values, like customer_id: rules out Range. Hash spreads a many-valued equality column evenly; list would need every value named at build time.
“Do you need to drop or detach a whole slice (one year, one tenant) in one operation?” Yes, retention is a hard requirement: rules out Hash. Hash gives no slice to drop; range or list keeps a retention cutoff aligned with one partition.

Range is the method the maintenance lesson leans on, because dropping an old range by date is the usual retention rule. List pays off when the vocabulary is stable and small; a table partitioned by city on a hundred cities would be a hundred partitions whose planning cost outweighs their pruning. Hash is the right fit when a column drives most lookups by equality, as in the orders table of the hash example below:

Hash partitioning of orders into eight partitions, and a lookup by customer id

hash_session.py

"""Hash partitioning of orders by customer_id, and the plan of a single-customer lookup on PostgreSQL 18.6."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("hash_setup.sql", flags=("-q",))
    pg.psql("hash.sql")

Output

$ psql -X -q -f hash_setup.sql

$ psql -X -a -f hash.sql
-- A single-customer lookup prunes to one hash partition. The row counts match the eighth of the data
-- a hash partition holds on average, so the sum of orders_h0..orders_h7 is 100,000 and the lookup for
-- customer 1,234 reads only the partition whose MODULUS 8 remainder matches hash(1,234).
SELECT inhrelid::regclass::text AS partition_name,
       (SELECT count(*) FROM orders WHERE customer_id = 1234
          AND tableoid = inhrelid) AS rows_in_partition
FROM pg_inherits
WHERE inhparent = 'orders'::regclass
ORDER BY 1;
 partition_name | rows_in_partition 
----------------+-------------------
 orders_h0      |                 0
 orders_h1      |                 0
 orders_h2      |                50
 orders_h3      |                 0
 orders_h4      |                 0
 orders_h5      |                 0
 orders_h6      |                 0
 orders_h7      |                 0
(8 rows)

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, placed, total FROM orders WHERE customer_id = 1234;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders_h2 orders  (cost=4.67..85.30 rows=50 width=19) (actual rows=50.00 loops=1)
   Recheck Cond: (customer_id = 1234)
   Heap Blocks: exact=50
   ->  Bitmap Index Scan on orders_h2_customer_id_idx  (cost=0.00..4.66 rows=50 width=0) (actual rows=50.00 loops=1)
         Index Cond: (customer_id = 1234)
         Index Searches: 1
(6 rows)

SELECT count(*) AS lookups FROM orders WHERE customer_id = 1234;
 lookups 
---------
      50
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 hash_session.py

hash_setup.sql

-- Hash partitioning: a shop with no natural range or list in its lookup column. The 2,000 customers are
-- split by a hash of customer_id into eight partitions of about an eighth each. A lookup on one
-- customer_id value prunes to the one partition whose remainder matches, under a Bitmap or Index Scan.
CREATE TABLE orders (
  id          bigint      GENERATED ALWAYS AS IDENTITY,
  customer_id int         NOT NULL,
  placed      date        NOT NULL,
  total       numeric(10,2) NOT NULL,
  PRIMARY KEY (id, customer_id)
) PARTITION BY HASH (customer_id);
DO $$
BEGIN
  FOR r IN 0 .. 7 LOOP
    EXECUTE format(
      'CREATE TABLE orders_h%s PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER %s)',
      r, r);
  END LOOP;
END $$;
INSERT INTO orders (customer_id, placed, total)
SELECT 1 + (n % 2000),
       DATE '2026-01-01' + ((n - 1) % 180),
       (n % 900) + 99.5
FROM generate_series(1, 100000) AS n;
CREATE INDEX ON orders (customer_id);
VACUUM ANALYZE orders;

hash.sql

-- A single-customer lookup prunes to one hash partition. The row counts match the eighth of the data
-- a hash partition holds on average, so the sum of orders_h0..orders_h7 is 100,000 and the lookup for
-- customer 1,234 reads only the partition whose MODULUS 8 remainder matches hash(1,234).
SELECT inhrelid::regclass::text AS partition_name,
       (SELECT count(*) FROM orders WHERE customer_id = 1234
          AND tableoid = inhrelid) AS rows_in_partition
FROM pg_inherits
WHERE inhparent = 'orders'::regclass
ORDER BY 1;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, placed, total FROM orders WHERE customer_id = 1234;
SELECT count(*) AS lookups FROM orders WHERE customer_id = 1234;

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. A session may name more patterns (mask=) for numbers that change on
every run, such as the rates and times ANALYZE VERBOSE prints or the random key of pg_dump's \\restrict line; the
session's own file says which, and its record shows a fixed word in their place. The sessions never print the port.
A session stops with status 1 when a command fails that should have worked, or reports a different number of errors
than the session expects, so a run that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0, mask=()):
        code, out = self._call(args)
        out = _masked(out, mask)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None, mask=()):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        out = _masked(out, mask)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0, mask=()):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status, mask=mask)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _masked(text, mask):
    """Replaces what differs on every run (a rate, a time, a random key) with fixed text (see the docstring)."""
    for pattern, replacement in mask:
        text = re.sub(pattern, replacement, text, flags=re.M)
    return text


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

The 100,000 orders landed in orders_h0 through orders_h7 in roughly equal shares. Customer 1,234 lives in orders_h2, so a WHERE customer_id = 1234 pruned to that one partition: the plan ran a Bitmap Index Scan on orders_h2_customer_id_idx, kept 50 rows, and never touched the other seven. The equality on the partition key is what makes hash pruning fire. A query that filters on placed instead would read every partition.

Checkpoint

Check: Range, list or hash

Question 1 of 2 A table of events is written mostly to the current day and read mostly for the last 30 days. The retention rule drops data older than a year. Which partitioning method fits?
Choose one answer.

List on the day column Hash on the day column No partitioning: one big table with an index on the day column Range on the day column (one partition per week or month)

Show the answer: Question 1 of 2

Answer: Range on the day column (one partition per week or month)

Range is the usual answer when the heavy filter is ordered and the retention rule drops a whole slice at a time; one of the two returns the manual lists lines up with each.
Question 2 of 2 In the hash example, a lookup for customer_id = 1234 read orders_h2 and nothing else. Why did the other seven partitions stay out of the plan?
Choose one answer.

Autovacuum had cleaned only orders_h2 orders_h2 had an index on customer_id and the others did not PostgreSQL always scans the smallest partition first and stops when it finds a match The hash of 1,234, modulo 8, gives the remainder of orders_h2; the equality on the partition key proves the row is in that partition

Show the answer: Question 2 of 2

Answer: The hash of 1,234, modulo 8, gives the remainder of orders_h2; the equality on the partition key proves the row is in that partition

A WHERE customer_id = ANY (...) with values hashing to different partitions reads several of them. A WHERE on a column that is not the partition key reads every partition.

Primary keys and indexes carry the partition key

A unique constraint on a partitioned table is enforced by one unique index per partition, so uniqueness is proved within a partition and never across them. The manual’s rule follows: a primary key, a unique constraint or an exclusion constraint on a partitioned table must include every column of the partition key, and the operators used on those columns must be equality. The error, and the fix, both land in the next run on a sales table partitioned by tenant:

A primary key error on a partitioned table, the fix, and the lock-friendly way to attach an index

keys_session.py

"""Primary keys and unique constraints on a partitioned table: the error that names the rule, the fix,
and the lock-friendly way to add an index to every partition on PostgreSQL 18.6."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    # Two expected errors: the primary key on id, and the unique constraint on invoice.
    pg.psql("keys.sql", errors=2)

Output

$ psql -X -a -f keys.sql
-- Unique constraints on a partitioned table must include every partition key column, because each
-- partition's own index enforces uniqueness only within itself. First the error, then the fix, then the
-- lock-friendly way to add an index to every partition without a long AccessExclusiveLock on the parent:
-- CREATE INDEX ON ONLY the parent (invalid), build each partition's index with CREATE INDEX CONCURRENTLY,
-- and ATTACH them so the parent index becomes valid.
CREATE TABLE sales (
  id       bigint      GENERATED ALWAYS AS IDENTITY,
  tenant   int         NOT NULL,
  invoice  text        NOT NULL,
  total    numeric(10,2) NOT NULL,
  placed   date        NOT NULL
) PARTITION BY LIST (tenant);
CREATE TABLE
CREATE TABLE sales_t1 PARTITION OF sales FOR VALUES IN (1);
CREATE TABLE
CREATE TABLE sales_t2 PARTITION OF sales FOR VALUES IN (2);
CREATE TABLE
-- Attempt 1: a primary key on id alone. This fails because tenant, the partition key, is not in it.
ALTER TABLE sales ADD PRIMARY KEY (id);
psql:keys.sql:16: ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "sales" lacks column "tenant" which is part of the partition key.
-- Attempt 2: a primary key on (id, tenant). It works: the key includes every partition key column.
ALTER TABLE sales ADD PRIMARY KEY (id, tenant);
ALTER TABLE
-- Attempt 3: a unique constraint on invoice alone. Same error, same reason, for exactly the same rule.
ALTER TABLE sales ADD CONSTRAINT sales_invoice_key UNIQUE (invoice);
psql:keys.sql:20: ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "sales" lacks column "tenant" which is part of the partition key.
-- The lock-friendly pattern for a non-unique index on every partition.
CREATE INDEX sales_placed_idx ON ONLY sales (placed);
CREATE INDEX
CREATE INDEX CONCURRENTLY sales_t1_placed_idx ON sales_t1 (placed);
CREATE INDEX
CREATE INDEX CONCURRENTLY sales_t2_placed_idx ON sales_t2 (placed);
CREATE INDEX
ALTER INDEX sales_placed_idx ATTACH PARTITION sales_t1_placed_idx;
ALTER INDEX
ALTER INDEX sales_placed_idx ATTACH PARTITION sales_t2_placed_idx;
ALTER INDEX
SELECT indexrelid::regclass::text AS idx, indisvalid AS valid
FROM pg_index
WHERE indexrelid::regclass::text LIKE 'sales%placed_idx'
ORDER BY 1;
         idx         | valid 
---------------------+-------
 sales_placed_idx    | t
 sales_t1_placed_idx | t
 sales_t2_placed_idx | t
(3 rows)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 keys_session.py

keys.sql

-- Unique constraints on a partitioned table must include every partition key column, because each
-- partition's own index enforces uniqueness only within itself. First the error, then the fix, then the
-- lock-friendly way to add an index to every partition without a long AccessExclusiveLock on the parent:
-- CREATE INDEX ON ONLY the parent (invalid), build each partition's index with CREATE INDEX CONCURRENTLY,
-- and ATTACH them so the parent index becomes valid.
CREATE TABLE sales (
  id       bigint      GENERATED ALWAYS AS IDENTITY,
  tenant   int         NOT NULL,
  invoice  text        NOT NULL,
  total    numeric(10,2) NOT NULL,
  placed   date        NOT NULL
) PARTITION BY LIST (tenant);
CREATE TABLE sales_t1 PARTITION OF sales FOR VALUES IN (1);
CREATE TABLE sales_t2 PARTITION OF sales FOR VALUES IN (2);
-- Attempt 1: a primary key on id alone. This fails because tenant, the partition key, is not in it.
ALTER TABLE sales ADD PRIMARY KEY (id);
-- Attempt 2: a primary key on (id, tenant). It works: the key includes every partition key column.
ALTER TABLE sales ADD PRIMARY KEY (id, tenant);
-- Attempt 3: a unique constraint on invoice alone. Same error, same reason, for exactly the same rule.
ALTER TABLE sales ADD CONSTRAINT sales_invoice_key UNIQUE (invoice);
-- The lock-friendly pattern for a non-unique index on every partition.
CREATE INDEX sales_placed_idx ON ONLY sales (placed);
CREATE INDEX CONCURRENTLY sales_t1_placed_idx ON sales_t1 (placed);
CREATE INDEX CONCURRENTLY sales_t2_placed_idx ON sales_t2 (placed);
ALTER INDEX sales_placed_idx ATTACH PARTITION sales_t1_placed_idx;
ALTER INDEX sales_placed_idx ATTACH PARTITION sales_t2_placed_idx;
SELECT indexrelid::regclass::text AS idx, indisvalid AS valid
FROM pg_index
WHERE indexrelid::regclass::text LIKE 'sales%placed_idx'
ORDER BY 1;

Both attempts to omit tenant returned the same message: unique constraint on partitioned table must include all partitioning columns, with a DETAIL line naming the column that is missing. The fix is to add tenant to the primary key, which the second attempt did. PostgreSQL 18 relaxed one older restriction: a unique index of an access method other than B-tree may now cover the partition key. Core PostgreSQL builds unique indexes only with B-tree, so the change matters for access methods that extensions add.

The second half of the run shows the pattern for a non-unique index on every partition. CREATE INDEX ... ON ONLY <parent> builds an invalid parent index without touching the children. CREATE INDEX CONCURRENTLY on each partition builds its own index without blocking writers. ALTER INDEX ... ATTACH PARTITION attaches the per-child index to the parent, and once every child is attached the parent flips to valid. On the sales table the three index rows all show valid = t at the end. The alternative, CREATE INDEX ON <parent> alone, cascades to every partition automatically but cannot run with CONCURRENTLY, so it holds a lock for the whole build.

A business key that must be unique everywhere

On a table partitioned by tenant, the database can enforce UNIQUE (invoice, tenant) but not UNIQUE (invoice), because no index spans the partitions. If an invoice number must be unique across every tenant, give it a small table of its own, not partitioned, with a unique constraint, or issue the numbers from one sequence.

Checkpoint

Check: Keys include the partition key

Question 1 of 2 You try to add PRIMARY KEY (id) on a table partitioned by tenant. The server replies 'unique constraint on partitioned table must include all partitioning columns'. What is the shortest fix that keeps id unique?
Choose one answer.

Drop the partition key constraint check with ALTER TABLE Add tenant to the key, PRIMARY KEY (id, tenant) Create a unique index on id ONLY the parent, with CONCURRENTLY Make id GENERATED ALWAYS AS IDENTITY

Show the answer: Question 1 of 2

Answer: Add tenant to the key, PRIMARY KEY (id, tenant)

The alternative is to pick a different partition key, like partitioning by id itself. The sales example shows both halves: the error, and the PRIMARY KEY (id, tenant) that works.
Question 2 of 2 You add an index on a billion-row partitioned table and must not block writers. Which sequence does the job without a long AccessExclusiveLock on the parent?
Choose one answer.

CREATE INDEX ON ONLY <parent>; CREATE INDEX CONCURRENTLY per partition; ALTER INDEX ... ATTACH PARTITION for each CREATE INDEX CONCURRENTLY ON <parent> DROP every partition, create the index, then re-attach them CREATE INDEX ON <parent>

Show the answer: Question 2 of 2

Answer: CREATE INDEX ON ONLY <parent>; CREATE INDEX CONCURRENTLY per partition; ALTER INDEX ... ATTACH PARTITION for each

The sales example of the lesson runs the full sequence and ends with every index row showing valid = t.

Pruning at planning time, and at execution time

partition pruning: The optimisation that lets the planner, or the executor, skip partitions that cannot hold rows matching a WHERE clause. It is on by default through enable_partition_pruning. drops partitions the planner, or the executor, can prove hold no matching row. One setting, enable_partition_pruning, on by default, controls both phases. The run below plans a query with a constant date, which the planner can compare with the bounds before it chooses the plan:

Pruning at planning time: a Seq Scan of ev_2026 alone, then every partition when pruning is off

pruning_session.py

"""Partition pruning at planning time, on a yearly-partitioned events table of PostgreSQL 18.6."""

from pg_session import Server

# One plan on every run: autovacuum off, no parallel workers or JIT, statistics from every row.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("events_setup.sql", flags=("-q",))
    pg.psql("pruning.sql")

Output

$ psql -X -q -f events_setup.sql

$ psql -X -a -f pruning.sql
-- Partition pruning at planning time: the planner reads the partition bounds, proves that only the year-2026
-- partition could hold rows that satisfy created_at >= '2026-03-01', and plans a Seq Scan of ev_2026 alone.
-- First the layout of events, then the pruned plan, then the same plan with pruning switched off for
-- comparison, then the row count the query returns.
\d events
           Partitioned table "public.events"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | bigint  |           | not null | 
 created_at | date    |           | not null | 
 user_id    | integer |           | not null | 
 kind       | text    |           | not null | 
 payload    | jsonb   |           | not null | 
Partition key: RANGE (created_at)
Indexes:
    "events_pkey" PRIMARY KEY, btree (id, created_at)
Number of partitions: 3 (Use \d+ to list them.)

SELECT inhrelid::regclass::text AS partition_name
FROM pg_inherits
WHERE inhparent = 'events'::regclass
ORDER BY 1;
 partition_name 
----------------
 ev_2024
 ev_2025
 ev_2026
(3 rows)

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE created_at >= DATE '2026-03-01';
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=481.81..481.82 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Seq Scan on ev_2026 events  (cost=0.00..440.44 rows=16550 width=0) (actual rows=16550.00 loops=1)
         Filter: (created_at >= '2026-03-01'::date)
         Rows Removed by Filter: 3245
(4 rows)

SET enable_partition_pruning = off;
SET
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE created_at >= DATE '2026-03-01';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1459.14..1459.15 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Append  (cost=0.00..1417.76 rows=16552 width=0) (actual rows=16550.00 loops=1)
         ->  Seq Scan on ev_2024 events_1  (cost=0.00..447.62 rows=1 width=0) (actual rows=0.00 loops=1)
               Filter: (created_at >= '2026-03-01'::date)
               Rows Removed by Filter: 20130
         ->  Seq Scan on ev_2025 events_2  (cost=0.00..446.94 rows=1 width=0) (actual rows=0.00 loops=1)
               Filter: (created_at >= '2026-03-01'::date)
               Rows Removed by Filter: 20075
         ->  Seq Scan on ev_2026 events_3  (cost=0.00..440.44 rows=16550 width=0) (actual rows=16550.00 loops=1)
               Filter: (created_at >= '2026-03-01'::date)
               Rows Removed by Filter: 3245
(11 rows)

RESET enable_partition_pruning;
RESET
SELECT count(*) AS matched FROM events WHERE created_at >= DATE '2026-03-01';
 matched 
---------
   16550
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 pruning_session.py

events_setup.sql

-- The events table of this lesson: 60,000 events spread over three years, partitioned by range on
-- created_at into one partition per year. The partition key is in the primary key, as the manual requires
-- for a unique constraint on a partitioned table (partition-maintenance adds a default partition later).
CREATE TABLE events (
  id         bigint       NOT NULL,
  created_at date         NOT NULL,
  user_id    int          NOT NULL,
  kind       text         NOT NULL,
  payload    jsonb        NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE ev_2024 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE ev_2025 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE ev_2026 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
INSERT INTO events (id, created_at, user_id, kind, payload)
SELECT n,
       DATE '2024-01-01' + ((n - 1) % 1095),
       1 + (n % 200),
       (ARRAY['click', 'view', 'buy', 'signup'])[1 + (n % 4)],
       jsonb_build_object('n', n)
FROM generate_series(1, 60000) AS n;
VACUUM ANALYZE events;

pruning.sql

-- Partition pruning at planning time: the planner reads the partition bounds, proves that only the year-2026
-- partition could hold rows that satisfy created_at >= '2026-03-01', and plans a Seq Scan of ev_2026 alone.
-- First the layout of events, then the pruned plan, then the same plan with pruning switched off for
-- comparison, then the row count the query returns.
\d events
SELECT inhrelid::regclass::text AS partition_name
FROM pg_inherits
WHERE inhparent = 'events'::regclass
ORDER BY 1;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE created_at >= DATE '2026-03-01';
SET enable_partition_pruning = off;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE created_at >= DATE '2026-03-01';
RESET enable_partition_pruning;
SELECT count(*) AS matched FROM events WHERE created_at >= DATE '2026-03-01';

With pruning on, the plan has one Seq Scan of ev_2026: the planner proved from the bounds that ev_2024 and ev_2025 cannot hold rows at or after the 2026 cutoff date, and dropped them before the plan was built. The Append disappears too, because one child under it would be redundant. With pruning off, the plan returns to its unpruned shape: an Append over three sequential scans that keep 0 of 20,130, 0 of 20,075 and 16,550 of 19,795 rows. The query returns the same 16,550, so pruning cuts work without changing the answer.

Step through what the planner does with the yearly events table:

Interactive · step by step How the planner prunes an events query at plan time Walk through the WHERE arriving, the planner reading each partition's bounds, and the plan ending with one scan.

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text

The query arrives. The service sends a SELECT count on events with a WHERE on created_at at or after an early-March cutoff. The planner opens the partitioned events table.
ev_2024 checked. The planner reads ev_2024 bounds. Its upper bound is the start of year 2025, below the cutoff, so no row of ev_2024 can match the WHERE.
ev_2024 dropped. The partition is pruned before the plan is built. Pruning at plan time removes it from the plan tree entirely.
ev_2025 checked. ev_2025 holds dates up to the start of year 2026, also below the cutoff. The planner proves this from the bounds alone and drops the partition.
ev_2026 kept. ev_2026 could hold rows at or after the cutoff, so the planner must plan a scan of it. No proof lets it drop this one.
The plan has one Seq Scan. One child is left under the Append, so the planner removes the Append too. The plan is a Seq Scan of ev_2026 with the Filter the WHERE became.

A query with a parameter has no value for the planner to read, so the generic plan it builds keeps every partition under an Append. The executor prunes when it starts, with the value bound: the partitions it drops vanish from the output, and the Append reports how many with a line Subplans Removed: N:

Pruning at execution time: Subplans Removed under a prepared statement

prepared_session.py

"""Partition pruning at execution time, through a PREPAREd statement on PostgreSQL 18.6."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("events_setup.sql", flags=("-q",))
    pg.psql("prepared.sql")

Output

$ psql -X -q -f events_setup.sql

$ psql -X -a -f prepared.sql
-- Pruning at execution time: a PREPAREd statement with a parameter has no value when the planner runs,
-- so the generic plan keeps every partition under an Append. The executor prunes when it starts, with the
-- value bound: the pruned partitions vanish from the plan, and "Subplans Removed" says how many.
-- plan_cache_mode = force_generic_plan skips the first five custom plans PostgreSQL would try.
PREPARE events_from(date) AS
SELECT count(*) FROM events WHERE created_at >= $1;
PREPARE
SET plan_cache_mode = force_generic_plan;
SET
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF) EXECUTE events_from(DATE '2026-03-01');
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1485.00..1485.01 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Append  (cost=0.00..1435.00 rows=20000 width=0) (actual rows=16550.00 loops=1)
         Subplans Removed: 2
         ->  Seq Scan on ev_2026 events_1  (cost=0.00..440.44 rows=6598 width=0) (actual rows=16550.00 loops=1)
               Filter: (created_at >= $1)
               Rows Removed by Filter: 3245
(6 rows)

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF) EXECUTE events_from(DATE '2024-06-01');
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1485.00..1485.01 rows=1 width=8) (actual rows=1.00 loops=1)
   ->  Append  (cost=0.00..1435.00 rows=20000 width=0) (actual rows=51640.00 loops=1)
         ->  Seq Scan on ev_2024 events_1  (cost=0.00..447.62 rows=6710 width=0) (actual rows=11770.00 loops=1)
               Filter: (created_at >= $1)
               Rows Removed by Filter: 8360
         ->  Seq Scan on ev_2025 events_2  (cost=0.00..446.94 rows=6692 width=0) (actual rows=20075.00 loops=1)
               Filter: (created_at >= $1)
         ->  Seq Scan on ev_2026 events_3  (cost=0.00..440.44 rows=6598 width=0) (actual rows=19795.00 loops=1)
               Filter: (created_at >= $1)
(9 rows)

EXECUTE events_from(DATE '2026-03-01');
 count 
-------
 16550
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 prepared_session.py

prepared.sql

-- Pruning at execution time: a PREPAREd statement with a parameter has no value when the planner runs,
-- so the generic plan keeps every partition under an Append. The executor prunes when it starts, with the
-- value bound: the pruned partitions vanish from the plan, and "Subplans Removed" says how many.
-- plan_cache_mode = force_generic_plan skips the first five custom plans PostgreSQL would try.
PREPARE events_from(date) AS
SELECT count(*) FROM events WHERE created_at >= $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF) EXECUTE events_from(DATE '2026-03-01');
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF) EXECUTE events_from(DATE '2024-06-01');
EXECUTE events_from(DATE '2026-03-01');

With plan_cache_mode = force_generic_plan, PostgreSQL skipped the first five custom plans it would otherwise try and used the generic plan at once. For events_from(DATE '2026-03-01') the Append printed Subplans Removed: 2, and only ev_2026 reported real work. For events_from(DATE '2024-06-01'), the executor could prune nothing, so all three partition scans ran. That is the usual production shape: the service sends parameters, the plan is generic, and pruning depends on each call’s value, which EXPLAIN ANALYZE of an EXECUTE reveals.

Key idea

Partition pruning happens at planning time, from constants in the WHERE clause, and when the executor starts, from the values of parameters. Only the second shows up as Subplans Removed on the Append.

Checkpoint

Check: Pruning phases

Question 1 of 2 The recorded plan of pruning.sql has one Seq Scan of ev_2026 and no Append. The same query with enable_partition_pruning off had an Append over three Seq Scans. What changed between the two plans?
Choose one answer.

Pruning dropped the partitions at execution time, which EXPLAIN hides The planner proved from the partition bounds that ev_2024 and ev_2025 cannot hold matching rows and dropped them before building the plan The two years grew empty between the runs Autovacuum changed the row counts

Show the answer: Question 1 of 2

Answer: The planner proved from the partition bounds that ev_2024 and ev_2025 cannot hold matching rows and dropped them before building the plan

Pruning on a constant WHERE always happens at plan time. The Append disappears when only one child survives.
Question 2 of 2 A PREPAREd statement with a date parameter, run under plan_cache_mode = force_generic_plan, shows an Append over one Seq Scan, of ev_2026, with 'Subplans Removed: 2'. What does Subplans Removed count?
Choose one answer.

Rows the generic plan ignored because of plan_cache_mode Partitions the planner removed from the plan tree Rows removed from the result by the WHERE clause Partitions the executor dropped when it started, with the parameter's value; they no longer appear under the Append

Show the answer: Question 2 of 2

Answer: Partitions the executor dropped when it started, with the parameter's value; they no longer appear under the Append

A custom plan would have planned with the constant and pruned at plan time, so Subplans Removed appears only on a generic plan reused for a specific value.

Partitionwise joins and aggregates

A query that joins two tables partitioned the same way, on the partition key, can be run as one partitionwise join: A plan shape, enabled by enable_partitionwise_join, where a join of two tables partitioned the same way on the join key runs as one join per matching partition pair under an Append. instead of one join over every pair. The setting that lets the planner build such a plan, enable_partitionwise_join, is off by default, and so is its aggregate twin enable_partitionwise_aggregate, because both make the planner work harder on every statement, not only the ones they help. Compare the two shapes on a join of orders and shipments, both hashed into eight partitions by customer_id:

The default plan: one Hash Join over the Append of every partition

partitionwise_off_session.py

"""The default plan of a join on the partition key, with enable_partitionwise_join at its default (off),
on PostgreSQL 18.6, with orders and shipments both hashed into eight partitions by customer_id."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("hash_setup.sql", flags=("-q",))
    pg.psql("partitionwise_setup.sql", flags=("-q",))
    pg.psql("partitionwise_off.sql")

Output

$ psql -X -q -f hash_setup.sql

$ psql -X -q -f partitionwise_setup.sql

$ psql -X -a -f partitionwise_off.sql
-- The default plan: a single hash join covers all 16 partitions, with an Append of each table underneath.
-- enable_partitionwise_join is off by default; the switch raises planning cost, so the planner is asked
-- for it only on queries that join on the partition key.
SET enable_memoize = off;
SET
SET enable_nestloop = off;
SET
SELECT setting AS partitionwise_join FROM pg_settings WHERE name = 'enable_partitionwise_join';
 partitionwise_join 
--------------------
 off
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders o JOIN shipments s
  ON (s.customer_id = o.customer_id AND s.order_id = o.id);
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: ((o.customer_id = s.customer_id) AND (o.id = s.order_id))
         ->  Append
               ->  Seq Scan on orders_h0 o_1
               ->  Seq Scan on orders_h1 o_2
               ->  Seq Scan on orders_h2 o_3
               ->  Seq Scan on orders_h3 o_4
               ->  Seq Scan on orders_h4 o_5
               ->  Seq Scan on orders_h5 o_6
               ->  Seq Scan on orders_h6 o_7
               ->  Seq Scan on orders_h7 o_8
         ->  Hash
               ->  Append
                     ->  Seq Scan on shipments_h0 s_1
                     ->  Seq Scan on shipments_h1 s_2
                     ->  Seq Scan on shipments_h2 s_3
                     ->  Seq Scan on shipments_h3 s_4
                     ->  Seq Scan on shipments_h4 s_5
                     ->  Seq Scan on shipments_h5 s_6
                     ->  Seq Scan on shipments_h6 s_7
                     ->  Seq Scan on shipments_h7 s_8
(22 rows)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 partitionwise_off_session.py

partitionwise_setup.sql

-- Two tables partitioned the same way: orders by HASH (customer_id) and shipments by HASH (customer_id),
-- both into eight partitions. A GROUP BY customer_id on either, or a JOIN on customer_id, becomes a plan
-- per matching partition pair when enable_partitionwise_join or enable_partitionwise_aggregate is on.
CREATE TABLE shipments (
  order_id    bigint      NOT NULL,
  customer_id int         NOT NULL,
  shipped     date        NOT NULL,
  carrier     text        NOT NULL,
  PRIMARY KEY (order_id, customer_id)
) PARTITION BY HASH (customer_id);
DO $$
BEGIN
  FOR r IN 0 .. 7 LOOP
    EXECUTE format(
      'CREATE TABLE shipments_h%s PARTITION OF shipments FOR VALUES WITH (MODULUS 8, REMAINDER %s)',
      r, r);
  END LOOP;
END $$;
-- One shipment for every order placed on a Monday (an eighth, roughly), from orders.
INSERT INTO shipments (order_id, customer_id, shipped, carrier)
SELECT id, customer_id, placed + 2, (ARRAY['bluedart','dtdc','xpress'])[1 + (id % 3)]
FROM orders
WHERE extract(dow FROM placed) = 1;
VACUUM ANALYZE shipments;

partitionwise_off.sql

-- The default plan: a single hash join covers all 16 partitions, with an Append of each table underneath.
-- enable_partitionwise_join is off by default; the switch raises planning cost, so the planner is asked
-- for it only on queries that join on the partition key.
SET enable_memoize = off;
SET enable_nestloop = off;
SELECT setting AS partitionwise_join FROM pg_settings WHERE name = 'enable_partitionwise_join';
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders o JOIN shipments s
  ON (s.customer_id = o.customer_id AND s.order_id = o.id);

With enable_partitionwise_join on: an Append over eight per-pair Hash Joins

partitionwise_on_session.py

"""The same join with enable_partitionwise_join = on: an Append of eight per-partition hash joins
replaces the one hash join of every partition, on PostgreSQL 18.6."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("hash_setup.sql", flags=("-q",))
    pg.psql("partitionwise_setup.sql", flags=("-q",))
    pg.psql("partitionwise_on.sql")

Output

$ psql -X -q -f hash_setup.sql

$ psql -X -q -f partitionwise_setup.sql

$ psql -X -a -f partitionwise_on.sql
-- The plan with the switch on: the Append is at the top, over one hash join per matching partition pair
-- (orders_h0 with shipments_h0 and so on). Each pair fits a smaller hash than the join of every partition
-- would, so more queries stay in work_mem. Plain EXPLAIN keeps the output short; the OFF plan is run and
-- measured by its own session beside this one.
SET enable_memoize = off;
SET
SET enable_nestloop = off;
SET
SET enable_partitionwise_join = on;
SET
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders o JOIN shipments s
  ON (s.customer_id = o.customer_id AND s.order_id = o.id);
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Hash Join
               Hash Cond: ((o_1.customer_id = s_1.customer_id) AND (o_1.id = s_1.order_id))
               ->  Seq Scan on orders_h0 o_1
               ->  Hash
                     ->  Seq Scan on shipments_h0 s_1
         ->  Hash Join
               Hash Cond: ((o_2.customer_id = s_2.customer_id) AND (o_2.id = s_2.order_id))
               ->  Seq Scan on orders_h1 o_2
               ->  Hash
                     ->  Seq Scan on shipments_h1 s_2
         ->  Hash Join
               Hash Cond: ((o_3.customer_id = s_3.customer_id) AND (o_3.id = s_3.order_id))
               ->  Seq Scan on orders_h2 o_3
               ->  Hash
                     ->  Seq Scan on shipments_h2 s_3
         ->  Hash Join
               Hash Cond: ((o_4.customer_id = s_4.customer_id) AND (o_4.id = s_4.order_id))
               ->  Seq Scan on orders_h3 o_4
               ->  Hash
                     ->  Seq Scan on shipments_h3 s_4
         ->  Hash Join
               Hash Cond: ((o_5.customer_id = s_5.customer_id) AND (o_5.id = s_5.order_id))
               ->  Seq Scan on orders_h4 o_5
               ->  Hash
                     ->  Seq Scan on shipments_h4 s_5
         ->  Hash Join
               Hash Cond: ((o_6.customer_id = s_6.customer_id) AND (o_6.id = s_6.order_id))
               ->  Seq Scan on orders_h5 o_6
               ->  Hash
                     ->  Seq Scan on shipments_h5 s_6
         ->  Hash Join
               Hash Cond: ((o_7.customer_id = s_7.customer_id) AND (o_7.id = s_7.order_id))
               ->  Seq Scan on orders_h6 o_7
               ->  Hash
                     ->  Seq Scan on shipments_h6 s_7
         ->  Hash Join
               Hash Cond: ((o_8.customer_id = s_8.customer_id) AND (o_8.id = s_8.order_id))
               ->  Seq Scan on orders_h7 o_8
               ->  Hash
                     ->  Seq Scan on shipments_h7 s_8
(42 rows)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 partitionwise_on_session.py

partitionwise_on.sql

-- The plan with the switch on: the Append is at the top, over one hash join per matching partition pair
-- (orders_h0 with shipments_h0 and so on). Each pair fits a smaller hash than the join of every partition
-- would, so more queries stay in work_mem. Plain EXPLAIN keeps the output short; the OFF plan is run and
-- measured by its own session beside this one.
SET enable_memoize = off;
SET enable_nestloop = off;
SET enable_partitionwise_join = on;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders o JOIN shipments s
  ON (s.customer_id = o.customer_id AND s.order_id = o.id);

With the switch off, one Hash Join reads the Append of all eight orders partitions and the Append of all eight shipments partitions, and the hash it builds holds every shipment at once. With the switch on, the Append is at the top and each of its eight children is a hash join of one orders_hN with one shipments_hN, so each hash holds about an eighth of the shipments and each pair’s work is independent. The release notes of PostgreSQL 18 record two improvements here: partitionwise joins fit more plan shapes than before, and the planner’s memory for them is lower.

The manual gives the reasons the switches are off: planning gets markedly more expensive in memory and CPU, and each per-pair join or aggregate may use its own work_mem, so memory use can grow with the number of partitions. They pay off for a query whose per-pair hash fits in work_mem while the whole-table hash would spill. A session that runs one such report can SET enable_partitionwise_join = on for itself, so the extra planning does not reach every other statement.

Checkpoint

Check: Partitionwise joins

Question 1 of 2 In the recorded partitionwise plan, the Append is above eight Hash Joins. What drives that shape, and when cannot the planner build it?
Choose one answer.

The planner would build this plan for any join by setting the switch, even on tables with different partition keys The Append is on top because the switch cancels the Hash node Both tables partition the same way on the join key, so the Append sits above and one hash join covers each matching pair enable_partitionwise_join simply sorts the join inputs so they can be merged

Show the answer: Question 1 of 2

Answer: Both tables partition the same way on the join key, so the Append sits above and one hash join covers each matching pair

Partitionwise aggregate is the same idea for GROUP BY on the partition key: one aggregate per partition, then Append.
Question 2 of 2 You run a nightly reporting query that joins two tables on the partition key, both hashed into 32 partitions. Partitionwise join is off by default. What is the best place to turn it on?
Choose one answer.

Change the plan_cache_mode to force_generic_plan for the whole server ALTER SYSTEM SET enable_partitionwise_join = on, so every session gets it Rebuild the tables with half the partitions, which turns the switch on automatically SET enable_partitionwise_join = on in the session that runs the report, before the query

Show the answer: Question 2 of 2

Answer: SET enable_partitionwise_join = on in the session that runs the report, before the query

The reports role is a common place to pin such switches with ALTER ROLE ... SET, so every report session gets them and no OLTP session does.

Practise: read the plans and keys of this lesson

Each question below can be answered from the five recordings: the yearly events table, the prepared statement with Subplans Removed: 2, the hash lookup to orders_h2, the primary key error and its fix, and the two partitionwise plans. Before you start, open the pruning run and say, in one sentence, what the planner proved to drop ev_2024 and ev_2025:

On a test server of your own

Create an events table partitioned by created_at into one partition per month for the current year, insert a thousand rows into each, and run EXPLAIN ANALYZE SELECT count(*) FROM events WHERE created_at BETWEEN $1 AND $2 as a PREPARE. Try the same query with plan_cache_mode = force_generic_plan and read how many partitions the Subplans Removed line names.

Interview questions

Warm-up (fresher to 2–5 years): When should you partition a table, and when do the planning cost and extra work make it a bad trade?

Partition when at least one of two returns is worth the operational cost: the planner can prove partition pruning for the queries you run most often, or you can drop or detach whole slices of data instead of running a bulk DELETE and the VACUUM that follows. A yearly events table that queries filter by date gets both. A small lookup table whose queries filter by other columns gets neither; keep it as one relation with the right indexes. Three signs partitioning is the wrong choice: your heaviest WHERE clauses do not include the partition key, your partition count climbs into the thousands with pruning retaining many of them, or the retention policy keeps every row forever. In the first two cases the planner’s work grows faster than any pruning saves.

Warm-up (fresher to 2–5 years): Why must a primary key or unique constraint on a partitioned table include every partition key column?

Each partition carries its own unique index and enforces uniqueness only inside its own rows. PostgreSQL has no global unique index that spans partitions, so a key that leaves the partition key out could not be checked across them without reading every one. The manual’s rule turns that physical fact into a constraint of the SQL: a primary key, a unique constraint or an exclusion constraint must include every column of the partition key, and the operators used for those columns must be equality. The error names the missing column plainly, as in UNIQUE constraint on table "sales" lacks column "tenant" which is part of the partition key. The fix is either to add the partition key to the constraint or to pick a partition key the constraint can live without, like partitioning the table by the key column itself.

Three questions go further, into plans that do not prune, partitionwise joins and partition counts:

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Mid to senior

A service runs 'SELECT ... FROM events WHERE created_at >= $1' as a prepared statement, and EXPLAIN ANALYZE EXECUTE shows an Append over every yearly partition with no 'Subplans Removed' line. What is the plan cache doing, and how do you make the query prune?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

A nightly report joins orders and shipments on customer_id and runs 90 seconds. Both tables are hashed into 16 partitions by customer_id. enable_partitionwise_join is off. When and how should you turn it on, and what should you watch for after?

Model answer, Pro. First step and follow-ups
Mid to staff

You partitioned a 50-million-row orders table by HASH (customer_id) into 64 partitions, and most queries got slower, not faster. What are the two or three likely causes, and how do you decide what to change?

Model answer, Pro. First step and follow-ups

Flashcards · 6 cards

Recall it, then check

Say the answer to yourself before you look: recalling it is what makes it stick.

When is partitioning worth its cost?

When pruning or whole-partition maintenance pay for the per-partition catalog cost. Most heavy queries should match the partition key, and pruning should leave only a few partitions.
What do range, list and hash partitioning each suit?

Range suits a sortable key like date; list a stable short vocabulary like tenant id or region; hash a column that drives point lookups but has no natural ordering.
What must a primary key or unique constraint on a partitioned table include?

Every partition key column. PostgreSQL 18 also lets a unique index of an access method other than B-tree cover that key.
How do you build an index on a partitioned table without a long lock?

CREATE INDEX ... ON ONLY <parent>, then CREATE INDEX CONCURRENTLY per partition, then ALTER INDEX ... ATTACH PARTITION, so the parent index flips to valid without holding a lock for the whole build.
What are the two phases of partition pruning?

Planning-time pruning from constants (the plan shrinks to the matching children, Append and all) and execution-time pruning from parameters, where EXPLAIN ANALYZE prints "Subplans Removed: N" on the Append.
When should you turn on partitionwise join and aggregate?

enable_partitionwise_join and enable_partitionwise_aggregate are off by default. Set them for sessions whose joins and aggregates line up with the partition key, not server-wide, since they cost planning time.

Key takeaways

Partitioning is worth its weight when pruning or whole-partition maintenance pay for the per-partition catalog cost; most heavy queries should match the partition key, and pruning should leave only a few partitions.
Range suits a sortable key like date, list a stable short vocabulary like tenant id or region, and hash a column that drives point lookups but has no natural ordering.
A primary key, unique constraint or exclusion constraint on a partitioned table includes every partition key column; PostgreSQL 18 also lets a unique index of another access method than B-tree cover that key.
A long index build on a partitioned table is split with CREATE INDEX ... ON ONLY <parent>, CREATE INDEX CONCURRENTLY per partition, and ALTER INDEX ... ATTACH PARTITION, so the parent flips to valid without holding a lock for the whole build.
Pruning works in two phases: at planning time from constants (the plan shrinks to one child, Append and all), and at execution time from parameters, where EXPLAIN ANALYZE prints Subplans Removed: N on the Append.
enable_partitionwise_join and enable_partitionwise_aggregate are off by default; set them for sessions whose joins and aggregates line up with the partition key, not server-wide.

Terms in this lesson

declarative partitioning: PostgreSQL's built-in way of splitting one logical table into child tables (partitions), each with its own files and indexes, by a declared partition key. The parent holds no data.
partition key: The column or expression list a partitioned table is split by. A primary key, unique constraint or exclusion constraint on the table must include every column of the partition key.
partition pruning: The optimisation that lets the planner, or the executor, skip partitions that cannot hold rows matching a WHERE clause. It is on by default through enable_partition_pruning.
partitionwise join: A plan shape, enabled by enable_partitionwise_join, where a join of two tables partitioned the same way on the join key runs as one join per matching partition pair under an Append.

References

PostgreSQL 18 documentation: 5.12 Table Partitioning (The PostgreSQL Global Development Group)
PostgreSQL 18 documentation: CREATE TABLE (The PostgreSQL Global Development Group)
PostgreSQL 18 documentation: CREATE INDEX (The PostgreSQL Global Development Group)
PostgreSQL 18 documentation: ALTER TABLE (The PostgreSQL Global Development Group)
PostgreSQL 18 documentation: 19.7 Query Planning (enable_partition_pruning, enable_partitionwise_join, enable_partitionwise_aggregate, plan_cache_mode) (The PostgreSQL Global Development Group)
PostgreSQL 18 release notes (The PostgreSQL Global Development Group)

Report a problem with this lesson

What you will learn

Before you start

What you will learn

Before you start

Why partition, and when the planning cost is not worth it

Range, list or hash: pick by the shape of the key

Primary keys and indexes carry the partition key

Pruning at planning time, and at execution time

Partitionwise joins and aggregates

Practise: read the plans and keys of this lesson

Interview questions

A service runs 'SELECT ... FROM events WHERE created_at >= $1' as a prepared statement, and EXPLAIN ANALYZE EXECUTE shows an Append over every yearly partition with no 'Subplans Removed' line. What is the plan cache doing, and how do you make the query prune?

A nightly report joins orders and shipments on customer_id and runs 90 seconds. Both tables are hashed into 16 partitions by customer_id. enable_partitionwise_join is off. When and how should you turn it on, and what should you watch for after?

You partitioned a 50-million-row orders table by HASH (customer_id) into 64 partitions, and most queries got slower, not faster. What are the two or three likely causes, and how do you decide what to change?

Key takeaways

References