How does table partitioning work in PostgreSQL?

Question

Accepted Answer

**Partitioning** splits a large table into smaller physical pieces (**partitions**) based on a column's value, while it remains one logical table. PostgreSQL's **declarative partitioning** (Postgres 10+) makes this clean. It improves performance and manageability for very large tables.

## Declarative partitioning (the modern way)

```sql
-- create a partitioned table, partitioned by RANGE on a date
CREATE TABLE orders (id INT, order_date DATE, amount NUMERIC)
PARTITION BY RANGE (order_date);

-- create partitions for each range
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

`orders` is one logical table, physically stored in per-year partitions. Inserts and queries are routed to the right partition automatically.

## Partition pruning — the key performance benefit

```sql
-- a query filtered on the partition key scans ONLY relevant partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → Postgres skips orders_2023 entirely ("partition pruning") — scans only orders_2024
```

**Partition pruning** is the main win: queries filtering on the partition key only touch relevant partitions, dramatically reducing data scanned on huge tables.

## Partition strategies

```text
PARTITION BY RANGE → value ranges (dates, numbers) — common for time-series
PARTITION BY LIST  → discrete values (region, status)
PARTITION BY HASH  → hash of the key (even distribution across N partitions)
```

## Maintenance benefits

```sql
-- drop old data instantly by dropping a whole partition (vs slow DELETE)
DROP TABLE orders_2020;              -- removes all 2020 data instantly
-- vs DELETE FROM orders WHERE year = 2020 → slow, creates bloat

-- detach/attach partitions; automate with pg_partman extension
```

Dropping a whole partition to archive/remove old data is instant — a major operational advantage over `DELETE` for large datasets.

## Why it matters

Table partitioning is an important PostgreSQL technique for managing and querying **very large tables**, and understanding it is valuable for working with Postgres at scale.

As tables grow to hundreds of millions or billions of rows, partitioning addresses performance and manageability by splitting the table into smaller physical pieces.

Postgres's **declarative partitioning** (Postgres 10+) makes this clean and is the modern standard to know.

The key performance benefit is **partition pruning** — queries filtering on the partition key scan only the relevant partitions (skipping the rest), dramatically reducing data scanned for huge tables (especially valuable for time-series data filtered by date, a very common case).

Beyond performance, partitioning provides major **maintenance benefits**: archiving or deleting old data becomes instant by dropping a whole partition (versus a slow, bloat-creating `DELETE` of millions of rows — a significant operational advantage for data with retention policies, like logs and events).

Understanding the **strategies** (RANGE for date/numeric ranges — common for time-series; LIST for discrete values; HASH for even distribution) and **when to use partitioning** (very large tables with a natural partition key, especially time-series with regular archiving — not small tables where indexing suffices) reflects sound judgment.

Since managing very large tables is a real challenge in scaled PostgreSQL systems, and since declarative partitioning (with partition pruning and efficient partition-drop maintenance) is the standard tool — complementing indexing — understanding PostgreSQL table partitioning is valuable, practically-relevant knowledge for database scalability, particularly important for large-scale, high-volume, or time-series applications and a common topic demonstrating ability to manage Postgres at scale.