Last updated August 13, 2026

As apps and their associated data volumes grow, a few tables in a Postgres database often grow faster than the rest. Query times rise, bulk loads slow down, indexes take longer to build, and routine maintenance like VACUUM becomes expensive. Table partitioning splits one logically large table into smaller physical pieces, so queries and maintenance operate on a fraction of the data.

This article covers how to use partitioning on Heroku Postgres, using as an example a time-series events table partitioned by month.

Overview

In Postgres, a partitioned table is a single logical table divided into smaller physical child tables called partitions. You define the partition strategy on the parent (range, list, or hash) and Postgres routes each inserted row to the correct partition automatically. Indexes created on the parent propagate to all partitions. The most common use case for partitioning is time-series data, where each partition covers a time window, but range and list partitioning work for any column with a natural grouping.

Table partitioning helps when:

• The table is large (100 GB is a common threshold).
• The workload is time-series or otherwise has a natural range key, and you drop old data on a schedule. Dropping a partition is instant, unlike bulk deleting old rows from a table with DELETE.
• Your queries filter on the partition key, so the Postgres planner prunes irrelevant partitions.

Create the Partitioned Table

Connect to your database with a heroku pg:psql interactive session:

$ heroku pg:psql -a example-app

Then, create a parent table partitioned by month on its created_at column. The parent table holds no data itself and every row lives in a child partition.

CREATE TABLE events (
    id          bigserial,
    user_id     bigint      NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

In this example, the primary key includes created_at, as unique constraints on a partitioned table must include the partition key.

Create Partitions

Create one partition per month. The upper bound is exclusive.

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');

With Postgres partitioning, insert operations route to the correct partition automatically:

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'signup', '2026-06-15 10:00:00+00');

Add Indexes

Create indexes on the parent table. Postgres propagates them to every existing partition and to any partition you create later.

CREATE INDEX ON events (user_id);
CREATE INDEX ON events (event_type, created_at);

Partitioned parent tables don’t support CREATE INDEX CONCURRENTLY. To add an index to a large table without a long lock, create an index placeholder on the parent first, build the index concurrently on each partition, and attach them:

CREATE INDEX events_user_id_idx ON ONLY events (user_id);

CREATE INDEX CONCURRENTLY events_2026_06_user_id_idx
    ON events_2026_06 (user_id);

ALTER INDEX events_user_id_idx
    ATTACH PARTITION events_2026_06_user_id_idx;
-- repeat the CONCURRENTLY + ATTACH steps for each partition.

The parent index becomes valid after you attach the index for every partition.

Maintain Partitions

Native partitioning doesn’t create future partitions for you. Create them ahead of time from a scheduled job or Heroku Scheduler.

To drop an old partition and prevent long lock contention on the table, detach the partition concurrently and then drop it.

ALTER TABLE events DETACH PARTITION events_2026_01 CONCURRENTLY;
DROP TABLE events_2026_01;

Dropping a partition reclaims database space immediately, unlike DELETE operations.

Query Performance

Queries that filter on created_at prune to the partitions they need:

EXPLAIN
SELECT count(*) FROM events
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';

The query plan touches only events_2026_06. Queries that don’t filter on the partition key scan every partition, which is usually slower than an equivalent single-table query. Design your access patterns around the partition key before you partition.

Limitations

• Unique indexes and primary keys must include the partition key column.
• Partitioned parent tables don’t support CREATE INDEX CONCURRENTLY.
• The partition key column must be NOT NULL, or you need a DEFAULT partition to catch nulls.
• Foreign keys that reference a partitioned table are enforced per partition, not against the parent.

Set Up Partitioning with pg_partman

The pg_partman extension is available on Heroku Postgres Standard-tier plans and higher. Using pg_partman is optional, because Postgres supports native partitioning. pg_partman version 5 is based on Postgres’ native partitioning, whereas older pg_partman versions are trigger-based.

pg_partman is an extension to create and manage both time-based and serial-based table partition sets. The extension manages creating child tables and allows partitioning tables with existing data in easily managed smaller tables. An optional retention policy can automatically drop partitions that you no longer need.

While pg_partman handles partition management, you must use scheduled tasks or jobs to invoke the management of the partitions on an appropriate interval with pg_partman’s run_maintenance() function.

pg_partman Partitioning Setup

1. Install the pg_partman extension:

   $ heroku pg:psql -a example-app
   DATABASE=> CREATE EXTENSION pg_partman;
   

2. Create a parent table.

3. Determine the time interval to partition over and create the initial partitions:

   $ heroku pg:psql -a example-app
   DATABASE=> SELECT create_parent(p_parent_table := 'public.events', p_control := 'created_at', p_interval := '1 month');
   

   After running this command, pg_partman creates its internal control tables and associated data to manage the child tables.

4. Add maintenance scripts to your app.

   Because pg_partman doesn’t automatically partition tables over time, you need an external scheduling service like Heroku Scheduler or a clock process to manage the partitions. You must run the extension’s run_maintenance() function regularly to drop old partitions and create new ones.

Further Reading

• PostgreSQL: Table Partitioning
• pg_partman on GitHub
• Heroku Postgres Database Tuning