Postgres Partitioning at Scale

Declarative partitioning shipped in Postgres 10 (2017). By 2024 the rough edges were smoothed; in 2026 it's the standard answer for tables that have grown past 100M rows. The most common application: time-series data that ages out, where dropping a partition is cheaper than running a DELETE.

When you actually need it#

Three signals that a table wants to be partitioned:

• The table is past 100M rows and queries that touch a small slice scan more pages than they should because the heap is too big.
• You have a retention policy: rows older than X months get deleted or archived. DELETE on hot tables is expensive; dropping a partition is instant.
• Maintenance operations are blocking: a VACUUM or REINDEX on the whole table is too slow. Partitions let you operate per-segment.

If none of these apply, you almost certainly don't need partitioning. Add indexes first.

Range partitioning (the common case)#

Best for time-series: events, audit logs, metrics.

-- Parent table is partitioned
CREATE TABLE events (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at timestamptz NOT NULL,
  tenant_id   uuid NOT NULL,
  payload     jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

-- Create a partition per month
CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- Indexes go on the parent; Postgres creates them per partition
CREATE INDEX events_tenant_time_idx ON events (tenant_id, occurred_at DESC);

Queries that filter on occurred_at get partition pruning automatically: only the relevant partitions are scanned.

List partitioning#

Use when you partition by a categorical column with a small, bounded set of values. Multi-region apps partitioning byregion is the canonical case.

CREATE TABLE invoices (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  region    text NOT NULL,
  ...
) PARTITION BY LIST (region);

CREATE TABLE invoices_us PARTITION OF invoices FOR VALUES IN ('us', 'us-east', 'us-west');
CREATE TABLE invoices_eu PARTITION OF invoices FOR VALUES IN ('eu', 'eu-west', 'eu-central');

Hash partitioning#

Splits data evenly across N partitions by hashing a column. Useful when you want to spread write load across many physical tables but don't have a natural range or list to partition by.

CREATE TABLE user_events (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id   uuid NOT NULL,
  ...
) PARTITION BY HASH (user_id);

CREATE TABLE user_events_p0 PARTITION OF user_events FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE user_events_p1 PARTITION OF user_events FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE user_events_p2 PARTITION OF user_events FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE user_events_p3 PARTITION OF user_events FOR VALUES WITH (modulus 4, remainder 3);

pg_partman for time-series#

Manually creating monthly partitions ahead of time is tedious and error-prone. pg_partman automates the creation, retention, and maintenance.

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'occurred_at',
  p_type         => 'native',
  p_interval     => 'monthly',
  p_premake      => 4              -- keep 4 months of future partitions ready
);

-- Then a cron / pg_cron job runs:
SELECT partman.run_maintenance();
-- Creates future partitions, drops old ones based on retention setting.

On Supabase, partman is available as an extension; enable it in Studio. Add a pg_cron job that runs run_maintenance() daily.

Three gotchas#

1. Primary keys must include the partition key

Postgres requires this. If your existing table has a primary key that doesn't include the partition column, you'll need to widen it.

2. Foreign keys to partitioned tables are limited

Pre-Postgres-15, you couldn't create a foreign key referencing a partitioned table. 15+ fixes this; older versions need application-level enforcement.

3. Migration of an existing table is non-trivial

You can't turn an existing table into a partitioned one in-place. The migration: create a new partitioned table; copy data; rename. Plan for downtime or use the rename-the-old-table / cutover trick.