PostgreSQL Native Partitioning: Declarative Syntax, Use‑Cases, and Gotchas

Why Partition?

When a table grows to millions or billions of rows, queries that filter on a single column can become slow and maintenance tasks—VACUUM, REINDEX, ANALYZE—become expensive. PostgreSQL’s native partitioning splits a logical table into several physical child tables (partitions). Each partition holds a subset of the data defined by a key. The database can then prune partitions that do not match a query’s predicate, reducing I/O and speeding up scans. Maintenance commands can be run on individual partitions, allowing cheap roll‑over of old data.

Declarative Syntax

Partitioning is declared at table creation with PARTITION BY. The partition key must be part of the table’s primary key or a unique constraint.

CREATE TABLE sales(
  sale_id BIGINT,
  sale_date DATE,
  amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

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

CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Each sales_YYYY table is a normal PostgreSQL table. The parent sales contains no data; it only holds the partitioning definition.

Key Constraints and Indexing

The partition key must be part of a PRIMARY KEY or UNIQUE constraint. If you omit it, PostgreSQL allows duplicate sale_id values across partitions, which can break referential integrity.

CREATE TABLE sales(
  sale_id BIGINT NOT NULL,
  sale_date DATE NOT NULL,
  amount NUMERIC(10,2),
  PRIMARY KEY (sale_id, sale_date)  -- key includes partition column
) PARTITION BY RANGE (sale_date);

Indexes defined on the parent table are automatically created on each partition. However, if you add an index after partitions exist, you must CREATE INDEX on each child table separately.

Query Pruning and Performance

When a query contains a predicate that references the partition key, PostgreSQL automatically scans only the matching partitions. Example:

EXPLAIN SELECT * FROM sales
WHERE sale_date BETWEEN '2023-06-01' AND '2023-06-30';

The plan will show a Range Scan on sales_2023 only. Predicates on other columns, such as amount, do not prune partitions and may still cause full scans of the relevant partition.

Maintenance and Roll‑Over

Dropping or detaching a partition is inexpensive. This makes it easy to roll over data, for example, moving a month’s logs to a new partition or deleting old data.

-- Detach a partition for migration
ALTER TABLE sales DETACH PARTITION sales_2023;
-- Drop the detached partition
DROP TABLE sales_2023;

Maintenance commands must be run on each partition. Running VACUUM ANALYZE on the parent table does not affect child tables.

Common Pitfalls

• Choosing a poor key: A high‑cardinality column with many distinct values can create many tiny partitions, negating benefits.
• Missing the key in a UNIQUE constraint: This allows duplicates across partitions and can break data integrity.
• Foreign keys to a partitioned table: PostgreSQL rejects foreign key references to a partitioned parent. Work‑arounds involve referencing the child tables or using triggers.
• Index alignment: Per‑partition indexes use the same columns as the parent, but if queries filter on non‑partitioned columns, consider adding a covering index on each partition.

Testing Your Setup

1. Create the partitioned table as shown above.

-- Create parent and two partitions
CREATE TABLE sales(
  sale_id BIGINT NOT NULL,
  sale_date DATE NOT NULL,
  amount NUMERIC(10,2),
  PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

2. Insert sample rows into the child tables.

INSERT INTO sales_2023 VALUES (1,'2023-06-15',100.00);
INSERT INTO sales_2024 VALUES (2,'2024-02-20',200.00);

3. Run EXPLAIN to confirm pruning.

EXPLAIN SELECT * FROM sales WHERE sale_date = '2023-06-15';

Expect the plan to reference sales_2023 only.

4. Verify foreign‑key restriction.

CREATE TABLE orders(order_id BIGINT PRIMARY KEY, sale_id BIGINT REFERENCES sales(sale_id));

PostgreSQL will return an error similar to: "cannot use a foreign key that references a partitioned table".

5. Run maintenance on a child partition.

VACUUM ANALYZE sales_2023;

Check the pg_stat_user_tables view to see that relname shows sales_2023 and that last_vacuum has been updated.

When to Use Partitioning

Partitioning shines when:

• Queries filter heavily on a single column (e.g., date, region).
• You need to delete or archive old data quickly.
• Maintenance windows are limited; you can vacuum a single partition instead of the whole table.

If your workload is read‑heavy with random access, or the key column is not selective, partitioning may add unnecessary complexity.

Conclusion

PostgreSQL’s declarative partitioning is a powerful tool for scaling large tables. By selecting an appropriate partition key, enforcing it in a primary key, and managing per‑partition indexes and maintenance, you can achieve significant query and maintenance performance gains. Be mindful of the constraints—especially the foreign‑key limitation—and verify your setup with EXPLAIN and system catalog checks before adopting it in production.