PostgreSQL Partitioned Tables and Table Inheritance

Q: Must a partitioned table's primary key include the partition key?

Yes. PostgreSQL requires that a partitioned table's unique constraint (including the primary key) include the partition-key column, otherwise cross-partition uniqueness cannot be guaranteed.

Q: Can an existing plain table be converted to a partitioned table?

Not directly. You must create a new partitioned parent table, then migrate data with INSERT INTO...SELECT or ATTACH.

Q: What is the risk of a DEFAULT partition?

A DEFAULT partition receives all data that matches nothing else; if a new partition's range overlaps data already in DEFAULT, attaching the new partition fails. Review the DEFAULT partition's contents periodically.

Q: Is there an upper limit on the number of partitions?

PostgreSQL has no hard limit, but each partition adds planner overhead. Keep a single table's partition count under a few hundred; beyond 1,000 partitions, planning time can rise noticeably.

Q: Can table inheritance replace declarative partitioning?

Generally not recommended. Inheritance lacks automatic routing, pruning, and cross-table constraints, and is costly to maintain. Use inheritance only when you need heterogeneous child tables (extra columns).

Q: Can HASH partitioning use partition pruning?

Yes. As long as the WHERE condition includes an equality comparison on the partition key, PG can compute which hash bucket the data lands in, and prune the other partitions.

Q: Does an UPDATE that moves a row across partitions lock the table?

PG 11+ supports cross-partition UPDATE; it is internally a DELETE + INSERT, which does not lock the whole table but takes row locks on each of the two partitions.

1. What You'll Learn

Understand the principle and use cases of declarative partitioning
Create RANGE / LIST / HASH / multi-level partitions
Use partition pruning to speed up queries
Perform partition maintenance: DETACH / ATTACH / DROP
Compare table inheritance (INHERITS) with declarative partitioning
Design an indexing strategy for partitioned tables

2. The Story

Alice's e-commerce platform orders table gains 100K new rows every day. After three years the single table exceeds 100 million rows; even with indexes, a monthly query still scans the whole table. The DBA suggests monthly RANGE partitioning — querying the last 30 days then scans only 1 partition instead of 3 years of the whole table. After partitioning went live, the monthly report query dropped from 12 seconds to 0.8 seconds, and disk I/O fell by 95%.

3. Concept: Declarative Partitioning Overview

(1) Why Partitioning Is Needed

Once a single table passes tens of millions of rows, the index B-Tree gets deeper, VACUUM takes much longer, and the query planner may pick a suboptimal plan. Partitioning splits a logical big table into several physical small tables, each with its own independent index and maintenance.

Metric	Unpartitioned (100M rows)	Monthly partitions (36 partitions)
Rows per partition	100M	~2.8M
Index depth	5-6 levels	3-4 levels
VACUUM time	30+ min	< 2 min/partition
Monthly-query I/O	Full table scan	Scan only 1 partition

(2) PostgreSQL Partitioning Evolution

Version	Feature
PG 9.x	Table inheritance + trigger-based manual partitioning
PG 10	Declarative RANGE / LIST partitioning
PG 11	HASH partitioning, enhanced partition pruning, cross-partition UPDATE migration
PG 12	ATTACH/DETACH partitions
PG 13	Multi-level partition pruning optimization
PG 14+	Further partition-pruning performance improvements

(3) Four Partitioning Strategies

Strategy	Use case	Partition-key requirement
RANGE	Time series, numeric ranges	Sortable type
LIST	Enumeration categories (region, status)	Discrete values
HASH	Even distribution, no clear range	Any hashable type
MULTI-LEVEL	RANGE + LIST/HASH combination	Multi-column combination

4. Operation: RANGE Partitioning

▶ Example: Create a Monthly RANGE-Partitioned Orders Table

SQL

-- Parent table: partition key only
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      TEXT DEFAULT 'pending'
) PARTITION BY RANGE (order_date);

-- Monthly partitions for 2024
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

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

CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Default partition catches out-of-range rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

TEXT

CREATE TABLE

▶ Example: Function to Auto-Generate Partitions

SQL

-- Generate monthly partitions for a given year
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    p_parent  REGCLASS,
    p_year    INT
) RETURNS VOID AS $$
DECLARE
    m      INT;
    p_name TEXT;
    s_date TEXT;
    e_date TEXT;
BEGIN
    FOR m IN 1..12 LOOP
        p_name := format('%s_%s_%02s', p_parent::text, p_year, m);
        s_date := format('%s-%02s-01', p_year, m);
        e_date := format('%s-%02s-01', p_year, m + 1);
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %s
             FOR VALUES FROM (%L) TO (%L)',
            p_name, p_parent::text, s_date, e_date
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT create_monthly_partitions('orders', 2025);

TEXT

CREATE TABLE

▶ Example: RANGE Partition Insert and Query

SQL

INSERT INTO orders (user_id, order_date, total_amount, status)
VALUES
    (1001, '2024-01-15', 299.99, 'completed'),
    (1002, '2024-02-20', 159.50, 'shipped'),
    (1003, '2024-03-10', 89.00,  'pending');

-- Query with partition pruning
SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

TEXT

  id  | user_id | order_date | total_amount | status
------+---------+------------+--------------+--------
 1002 |    1002 | 2024-02-20 |       159.50 | shipped
(1 row)

5. Operation: LIST and HASH Partitioning

▶ Example: LIST Partition — Split Users Table by Region

SQL

CREATE TABLE users_by_region (
    id       BIGINT GENERATED ALWAYS AS IDENTITY,
    name     TEXT NOT NULL,
    region   TEXT NOT NULL,
    email    TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_north_america PARTITION OF users_by_region
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE users_europe PARTITION OF users_by_region
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_asia PARTITION OF users_by_region
    FOR VALUES IN ('CN', 'JP', 'KR', 'SG');

CREATE TABLE users_other PARTITION OF users_by_region DEFAULT;

TEXT

CREATE TABLE

▶ Example: HASH Partitioning — Evenly Distribute a Log Table

SQL

CREATE TABLE access_logs (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id    BIGINT,
    action     TEXT,
    log_time   TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- Create 8 hash partitions
CREATE TABLE access_logs_p0 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE access_logs_p4 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE access_logs_p5 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE access_logs_p6 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE access_logs_p7 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

TEXT

CREATE TABLE

▶ Example: Multi-Level Partitioning (RANGE + LIST)

SQL

CREATE TABLE order_details (
    id          BIGINT,
    order_id    BIGINT,
    order_date  DATE NOT NULL,
    region      TEXT NOT NULL,
    product_id  INT,
    quantity    INT
) PARTITION BY RANGE (order_date);

-- First level: by month
CREATE TABLE order_details_2024q1 PARTITION OF order_details
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    PARTITION BY LIST (region);

-- Second level: by region within each month range
CREATE TABLE order_details_2024q1_na PARTITION OF order_details_2024q1
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE order_details_2024q1_eu PARTITION OF order_details_2024q1
    FOR VALUES IN ('UK', 'DE', 'FR');

TEXT

CREATE TABLE

6. Concept: Partition Pruning

(1) Pruning Principle

Partition pruning lets the query planner rule out irrelevant partitions at planning time, avoiding runtime scans.

flowchart TD
    A["SELECT * FROM orders<br/>WHERE order_date = '2024-02-15'"] --> B["Query Planner"]
    B --> C{"Partition Pruning"}
    C -->|"order_date in [2024-02-01, 2024-03-01)"| D["orders_2024_02 ✓"]
    C -->|"out of range"| E["orders_2024_01 ✗"]
    C -->|"out of range"| F["orders_2024_03 ✗"]
    C -->|"out of range"| G["orders_default ✗"]
    D --> H["Scan 1 partition only"]

(2) Verify Pruning Effect

SQL

-- Enable partition pruning (default ON)
SET enable_partition_pruning = on;

-- Check which partitions are scanned
EXPLAIN (COSTS OFF) SELECT * FROM orders
WHERE order_date = '2024-02-15';

TEXT

Append
  -> Seq Scan on orders_2024_02
       Filter: (order_date = '2024-02-15'::date)

(3) Common Causes of Pruning Failure

Scenario	Pruned?	Reason
`WHERE order_date = '2024-02-15'`	✅	Constant is prunable
`WHERE order_date = $1` (prepared stmt)	✅ PG 11+	Generic parameter pruning
`WHERE order_date = now()`	✅	Stable function is prunable
`WHERE order_date = random_func()`	❌	Volatile function is not prunable
`WHERE to_char(order_date, 'YYYY-MM') = '2024-02'`	❌	Function wraps the partition key

7. Operation: Partition Maintenance

▶ Example: DETACH Old Partition for Archiving

SQL

-- Detach January 2023 partition (no data loss)
ALTER TABLE orders DETACH PARTITION orders_2023_01;

-- Now it's a standalone table, can be moved to cheaper storage
ALTER TABLE orders_2023_01 SET TABLESPACE archive_tbs;

-- Or export and drop
COPY orders_2023_01 TO '/archive/orders_2023_01.csv';
DROP TABLE orders_2023_01;

TEXT

-- SQL statement executed successfully

▶ Example: ATTACH a New Partition

SQL

-- Create new table first
CREATE TABLE orders_2025_01 (LIKE orders INCLUDING DEFAULTS);

-- Add check constraint for validation (speeds up ATTACH)
ALTER TABLE orders_2025_01
    ADD CONSTRAINT orders_2025_01_check
    CHECK (order_date >= '2025-01-01' AND order_date < '2025-02-01');

-- Attach to parent (takes exclusive lock briefly)
ALTER TABLE orders ATTACH PARTITION orders_2025_01
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

TEXT

CREATE TABLE

▶ Example: Concurrent DETACH (PG 14+)

SQL

-- DETACH without blocking concurrent reads
ALTER TABLE orders DETACH PARTITION orders_2023_01 CONCURRENTLY;

TEXT

-- SQL statement executed successfully

▶ Example: Partition Indexing Strategy

SQL

-- Index on parent propagates to all partitions
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Each partition gets its own index
\d orders_2024_01

TEXT

Indexes:
    "orders_2024_01_user_id_idx" btree (user_id)

Indexing strategy	Description
Create index on parent	Automatically propagates to all existing and future partitions
Create index on a single partition	Affects only that partition; must sync manually on ATTACH
Unique index	Must include the partition key (cross-partition uniqueness is guaranteed by the partition key)

▶ Example: Unique Index Must Include the Partition Key

SQL

-- This FAILS: unique without partition key
CREATE UNIQUE INDEX idx_orders_id ON orders (id);
-- ERROR: unique constraint must contain partition key

-- This WORKS: unique with partition key
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);

TEXT

CREATE TABLE

8. Concept: Table Inheritance (INHERITS)

(1) Inheritance Syntax and Characteristics

Table inheritance is a PostgreSQL-only feature, more flexible than declarative partitioning — child tables can have extra columns and need not cover all of the parent's value domains.

SQL

-- Parent table
CREATE TABLE people (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

-- Child table inherits all columns + adds extras
CREATE TABLE employees (
    salary    NUMERIC(10,2),
    dept      TEXT,
    hire_date DATE
) INHERITS (people);

-- Add primary key to child
ALTER TABLE employees ADD PRIMARY KEY (id);

(2) Inheritance vs Declarative Partitioning

Feature	Table inheritance (INHERITS)	Declarative partitioning
Extra columns on child	✅ Allowed	❌ Must share the same structure
Parent stores data	✅ Yes	❌ Parent is an empty shell
Auto-routes INSERT	❌ Needs a trigger	✅ Automatic
Partition pruning	❌ Needs CHECK constraints	✅ Automatic
Cross-table unique constraint	❌ Single table only	✅ With partition key, cross-table
Foreign key to parent	❌ Not supported	✅ Supported
Flexibility	High	Medium
Recommended scenario	Heterogeneous subtypes	Large-table split optimization

(3) Inheritance Query: the ONLY Keyword

SQL

-- Query parent + all children
SELECT * FROM people;

-- Query parent ONLY (no children)
SELECT * FROM ONLY people;

-- Check which table each row comes from
SELECT tableoid::regclass, * FROM people;

9. Operation: Cross-Partition Query Optimization

▶ Example: Collect Partition Statistics

SQL

-- Analyze specific partition
ANALYZE orders_2024_02;

-- Analyze all partitions via parent
ANALYZE orders;

-- Check partition stats
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Cross-Partition JOIN Optimization

SQL

-- Partition-wise join (PG 12+)
SET enable_partitionwise_join = on;

SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Parameter	Default	Description
`enable_partition_pruning`	on	Partition pruning
`enable_partitionwise_join`	off	Partition-level JOIN
`enable_partitionwise_aggregate`	off	Partition-level aggregation
`constraint_exclusion`	partition	Constraint exclusion (for inheritance)

▶ Example: Partition + Inheritance Hybrid Scenario

SQL

-- Base audit table
CREATE TABLE audit_log (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    table_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (changed_at);

-- Monthly partitions, each inherited by type-specific tables
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Additional columns for specific audit type via inheritance
CREATE TABLE audit_log_user_changes (
    old_email TEXT,
    new_email TEXT
) INHERITS (audit_log_2024_01);

TEXT

CREATE TABLE

10. Comprehensive Example

SQL

-- Complete monthly partitioning setup for e-commerce orders
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending',
    created_at   TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (order_date);

-- Create partitions for 2024 Q1
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Unique index must include partition key
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- Insert test data
INSERT INTO orders (user_id, order_date, total_amount, status) VALUES
    (1001, '2024-01-05', 299.99, 'completed'),
    (1001, '2024-02-14', 159.50, 'shipped'),
    (1002, '2024-01-20', 450.00, 'completed'),
    (1002, '2024-03-01', 89.00,  'pending'),
    (1003, '2024-02-28', 1200.00,'completed');

-- Verify partition pruning
EXPLAIN (COSTS OFF) SELECT * FROM orders
    WHERE order_date BETWEEN '2024-02-01' AND '2024-02-28';

-- Detach old partition for archiving
ALTER TABLE orders DETACH PARTITION orders_2024_01;
COPY orders_2024_01 TO '/archive/orders_2024_01.csv';

-- Attach new partition for next quarter
CREATE TABLE orders_2024_04 (LIKE orders INCLUDING DEFAULTS);
ALTER TABLE orders_2024_04
    ADD CONSTRAINT chk_2024_04
    CHECK (order_date >= '2024-04-01' AND order_date < '2024-05-01');
ALTER TABLE orders ATTACH PARTITION orders_2024_04
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- Monitor partition sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS size,
       reltuples::bigint AS row_estimate
FROM pg_class
WHERE relname LIKE 'orders_2024%' ORDER BY relname;

❓ FAQ

Q Must a partitioned table's primary key include the partition key?

A Yes. PostgreSQL requires that a partitioned table's unique constraint (including the primary key) include the partition-key column, otherwise cross-partition uniqueness cannot be guaranteed.

Q Can an existing plain table be converted to a partitioned table?

A Not directly. You must create a new partitioned parent table, then migrate data with INSERT INTO...SELECT or ATTACH.

Q What is the risk of a DEFAULT partition?

A A DEFAULT partition receives all data that matches nothing else; if a new partition's range overlaps data already in DEFAULT, attaching the new partition fails. Review the DEFAULT partition's contents periodically.

Q Is there an upper limit on the number of partitions?

A PostgreSQL has no hard limit, but each partition adds planner overhead. Keep a single table's partition count under a few hundred; beyond 1,000 partitions, planning time can rise noticeably.

Q Can table inheritance replace declarative partitioning?

A Generally not recommended. Inheritance lacks automatic routing, pruning, and cross-table constraints, and is costly to maintain. Use inheritance only when you need heterogeneous child tables (extra columns).

Q Can HASH partitioning use partition pruning?

A Yes. As long as the WHERE condition includes an equality comparison on the partition key, PG can compute which hash bucket the data lands in, and prune the other partitions.

Q Does an UPDATE that moves a row across partitions lock the table?

A PG 11+ supports cross-partition UPDATE; it is internally a DELETE + INSERT, which does not lock the whole table but takes row locks on each of the two partitions.

📖 Summary

Declarative partitioning (RANGE/LIST/HASH) is the standard way to manage large tables in PG 10+
RANGE partitioning suits time series; LIST suits enumeration categories; HASH suits even distribution
Partition pruning makes a query scan only the relevant partitions; the condition must be directly on the partition key
DETACH/ATTACH enable online partition maintenance; CONCURRENTLY reduces lock blocking
A partitioned table's unique index must include the partition key
Table inheritance is more flexible but lacks automatic routing and pruning; suits heterogeneous-subtype scenarios
enable_partitionwise_join/aggregate can further improve cross-partition query performance

📝 Exercises

⭐ Create a payments table partitioned by quarter with RANGE, with partitions for the four quarters of 2024, and verify the pruning effect for WHERE payment_date BETWEEN '2024-Q2'.
⭐⭐ Design a LIST partitioning scheme for a SaaS multi-tenant system: hash tenant_id into 4 partitions, write the creation statements, and test data isolation across different tenants.
⭐⭐⭐ Write a stored procedure that takes a year as input, automatically creates 12 monthly partitions for the orders table, adds CHECK constraints, creates indexes, and DETACHes the prior January's partition to an archive tablespace. Test the full flow of INSERT, cross-partition query, and pruning verification.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Partitioned Tables and Table Inheritance

1. What You'll Learn

2. The Story

3. Concept: Declarative Partitioning Overview

(1) Why Partitioning Is Needed

(2) PostgreSQL Partitioning Evolution

(3) Four Partitioning Strategies

4. Operation: RANGE Partitioning

▶ Example: Create a Monthly RANGE-Partitioned Orders Table

▶ Example: Function to Auto-Generate Partitions

▶ Example: RANGE Partition Insert and Query

5. Operation: LIST and HASH Partitioning

▶ Example: LIST Partition — Split Users Table by Region

▶ Example: HASH Partitioning — Evenly Distribute a Log Table

▶ Example: Multi-Level Partitioning (RANGE + LIST)

6. Concept: Partition Pruning

(1) Pruning Principle

(2) Verify Pruning Effect

(3) Common Causes of Pruning Failure

7. Operation: Partition Maintenance

▶ Example: DETACH Old Partition for Archiving

▶ Example: ATTACH a New Partition

▶ Example: Concurrent DETACH (PG 14+)

▶ Example: Partition Indexing Strategy

▶ Example: Unique Index Must Include the Partition Key

8. Concept: Table Inheritance (INHERITS)

(1) Inheritance Syntax and Characteristics

(2) Inheritance vs Declarative Partitioning

(3) Inheritance Query: the ONLY Keyword

9. Operation: Cross-Partition Query Optimization

▶ Example: Collect Partition Statistics

▶ Example: Cross-Partition JOIN Optimization

▶ Example: Partition + Inheritance Hybrid Scenario

10. Comprehensive Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好