PostgreSQL Partition Table Generator

Describe the table you want to partition and this generator writes the full declarative partitioning script: the PARTITION BY parent, every child partition, a default catch-all, indexes that cascade to all partitions, and the ATTACH/DETACH statements you will need later. It also flags the mistakes PostgreSQL will reject, such as a primary key that does not contain the partition key. Everything runs in your browser.

TableStrategyPartition keyPrimary key

IntervalStart datePartitionsIndex columnsDEFAULT partition

Column definitions (one per line, without the partition clause)

Generated partitions: 13

⚠ A primary key on a partitioned table must contain the partition key, so "created_at" was added to the primary key. PostgreSQL rejects PRIMARY KEY (id) on a table partitioned by created_at.

⚠ A DEFAULT partition catches stray rows, but attaching a new partition whose range overlaps rows already sitting in the default requires a full scan of the default partition. Keep it empty in normal operation.

Partitioning DDL

CREATE TABLE events (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id   bigint NOT NULL,
  country     text NOT NULL,
  payload     jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE events_2026_04 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
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');
CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE events_2026_12 PARTITION OF events
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE INDEX ON events (tenant_id);  -- cascades to every existing and future partition

Maintenance SQL

-- Detach the oldest partition without locking out writers (PostgreSQL 14+)
ALTER TABLE events DETACH PARTITION events_2026_01 CONCURRENTLY;

-- Archive it, then drop it. Dropping a detached partition is instant:
-- it is a plain table by then, so there is no row-by-row DELETE.
DROP TABLE events_2026_01;

-- Attach a table that already holds data. Add a matching CHECK constraint
-- first so the ATTACH does not have to scan the whole table to validate it.
ALTER TABLE events_incoming
  ADD CONSTRAINT events_incoming_range
  CHECK (created_at >= '2026-01-01' AND created_at < '2026-02-01');

ALTER TABLE events ATTACH PARTITION events_incoming
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Confirm partition pruning is working: the plan should touch one partition.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

Do more than postgresql partition table generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table name and its column definitions, then pick the partition key column.
Choose RANGE for time-series data, LIST for a fixed set of values such as region or tenant, or HASH to spread rows evenly, and set the interval or bucket count.
Copy the generated DDL, review the warnings, and keep the maintenance script for rolling old partitions out later.

Frequently asked questions

When should I partition a PostgreSQL table?

Partitioning pays off when a table is large enough that maintenance becomes the problem — typically tens of millions of rows or more — and when queries filter on a column that can serve as the partition key. The biggest wins are bulk deletion, because dropping an old partition is instant while a DELETE of the same rows leaves bloat for autovacuum to clean up, and partition pruning, where the planner skips partitions that cannot match. If your queries do not filter on the partition key, partitioning adds planning overhead without giving you pruning.

Why does PostgreSQL reject my primary key on a partitioned table?

Every unique index on a partitioned table, including the primary key, must contain the partition key. PostgreSQL builds one index per partition rather than a single global index, so it can only guarantee uniqueness if the key itself tells it which partition a row belongs to. The usual fix is to make the primary key composite, for example PRIMARY KEY (id, created_at) when partitioning by created_at. This generator adds the partition key for you and warns when it does.

What is the difference between RANGE, LIST and HASH partitioning?

RANGE assigns rows to a partition by a lower and upper bound and is the standard choice for dates and timestamps, since it lets you drop an old month in one statement. LIST assigns rows by an exact value such as a country code or tenant ID, which suits a small, stable set of categories. HASH spreads rows evenly across a fixed number of buckets using a modulus and remainder; it gives no pruning for range queries and no cheap bulk delete, so it is mainly for balancing write contention across partitions.