PostgreSQL Monthly Partitioning: Step-by-Step Setup

PostgreSQL monthly partitioning: step-by-step partitioning setup

1. Diagnosis

You’ve got a table with 40 million rows. VACUUM takes three hours. A query for last month scans the entire history back to 2019. Sound familiar?

This is a classic case. PostgreSQL monthly partitioning solves exactly this problem: the database gets cut into physical chunks by time range, and the planner stops dragging megabytes of irrelevant data off disk.

What you’ll get at the end:

• A parent table with automatically created child partitions by month
• Indexes and constraints inherited by every partition
• Automation through pg_partman: new partitions get created on their own, old ones get archived or dropped on their own
• A migration plan for existing data without blocking production

Time to set up from scratch: an hour to ninety minutes. Time to migrate existing data depends on volume, figure 15-20 minutes per 5-10 million rows on mid-range hardware.

You’ll need: PostgreSQL 13+ (16+ preferred, I’ll explain why below), superuser rights or CREATE on the database, and a window to test on staging. Running the migration straight on production without a rehearsal isn’t something I’d recommend to anyone who wants to sleep at night.

What’s in this article

• Why a large table without partitioning eventually slows down
• The recipe: creating a partitioned table + pg_partman + data migration
• Verification through EXPLAIN and partition pruning
• Complications: 7 real production errors with fixes
• Alternatives to monthly partitioning
• Prevention: monitoring, backup, security, updates

2. Causes

Let’s break down why a monolithic table with historical data eventually breaks production.

And now your queries are crawling, the database just sits there. A familiar picture if the table’s been growing for three years without any plan.

3. Recipe

Preparation

At the time of writing, PostgreSQL 18 is the current branch (latest minor release: 18.4), and branch 17 is still supported and getting patches (17.10). Check the latest releases at postgresql.org/docs before installing.

Check the version and your privileges:

SELECT version();
SELECT current_user, has_database_privilege(current_user, current_database(), 'CREATE');

Expected result: PostgreSQL version 13+, CREATE privilege true. If it’s false, you’ll need the cluster admin.

Step 1. Design the partition key

Partitioning by month means the key is an event date: created_at, event_date, order_date. Important: the partition key must be part of the PRIMARY KEY, otherwise PostgreSQL won’t let you create a unique index.

-- Example: event log table
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_date date NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

Result: the table is created, but without a single partition it won’t accept any rows. An insert will immediately fail with “no partition found for row”.

Step 2. Create partitions manually (to understand the mechanics)

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

Result: two partitions show up in the list of child tables. Check it:

SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'events'::regclass;

Creating partitions by hand for every month into the future is no fun at all. Next, we’ll automate this with pg_partman, you won’t be doing this manually.

Step 3. Install pg_partman

sudo apt update
sudo apt install postgresql-17-partman

If the package isn’t in your distro’s repository, build from source:

git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
make
sudo make install

Result: the extension files show up in your PostgreSQL’s share/extension.

Step 4. Create the extension and set up automation

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'event_date',
    p_interval => '1 month',
    p_premake => 3
);

p_premake => 3 means pg_partman will create partitions 3 months ahead of time. Result: partman created the current partition plus three future ones, and recorded the config in partman.part_config.

Check the configuration:

SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

Step 5. Set up automatic maintenance (background worker or cron)

pg_partman 5.x ships with a background worker (BGW). Enable it in postgresql.conf:

shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'partman_maintainer'
pg_partman_bgw.dbname = 'your_database'

Restart PostgreSQL. The shared_preload_libraries parameter requires a full restart, a config reload won’t cut it:

sudo systemctl restart postgresql

An alternative without BGW is a regular cron job calling run_maintenance_proc:

# /etc/cron.d/pg_partman_maintenance
0 * * * * postgres psql -d your_database -c "CALL partman.run_maintenance_proc();"

Result: once an hour, partman checks all tables under its management and creates or removes partitions according to the config rules.

Step 6. Migrating existing data into the partitioned structure

Now the real work begins. If you’ve got an existing table instead of starting from scratch, there’s no direct ALTER TABLE … PARTITION BY in PostgreSQL. You’ll have to migrate the data.

Migration flow:

%%{init: {
  'theme': 'base',
  'themeVariables': {
    'primaryColor': '#f8fafc',
    'primaryTextColor': '#1e293b',
    'primaryBorderColor': '#94a3b8',
    'lineColor': '#64748b',
    'fontSize': '15px',
    'fontFamily': 'ui-sans-serif, system-ui, sans-serif'
  },
  'flowchart': {'curve': 'linear', 'nodeSpacing': 50, 'rankSpacing': 50}
}}%%
flowchart TD
    A["Old table events_old"] --> B["New partitioned events"]
    B --> C["Data migration in batches via partition_data_proc"]
    C --> D["Row count check"]
    D --> E["Delete or archive events_old"]
    style A fill:#f8fafc,stroke:#f97316,stroke-width:2px,color:#9a3412
    style B fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style C fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style D fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style E fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d

Rename the old table, create a new one with the same name as the partitioned table:

ALTER TABLE events RENAME TO events_old;

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_date date NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'event_date',
    p_interval => '1 month',
    p_premake => 3
);

Migrate the data in batches so you don’t hold one giant transaction and flood the WAL:

CALL partman.partition_data_proc(
    p_parent_table => 'public.events',
    p_source_table => 'public.events_old',
    p_batch_count => 50,
    p_batch_interval => '1 month'
);

Result: data moves over in batches by month, the old table gradually empties out. Check the balance:

SELECT
    (SELECT count(*) FROM events_old) AS old_count,
    (SELECT count(*) FROM events) AS new_count;

Once the numbers match, you can drop the old table or keep it as an archive for a week just to be safe.

Step 7. Indexes and constraints

Indexes on a partitioned table are created on the parent. PostgreSQL automatically propagates them to all partitions, including future ones:

CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_date ON events (event_date);

Result: every partition gets its own physical index, but you manage it with one command on the parent.

Solution architecture

%%{init: {
  'theme': 'base',
  'themeVariables': {
    'primaryColor': '#f8fafc',
    'primaryTextColor': '#1e293b',
    'primaryBorderColor': '#94a3b8',
    'lineColor': '#64748b',
    'fontSize': '15px',
    'fontFamily': 'ui-sans-serif, system-ui, sans-serif'
  },
  'flowchart': {'curve': 'linear', 'nodeSpacing': 50, 'rankSpacing': 50}
}}%%
flowchart TD
    A["Client INSERT"] --> B["Parent table events"]
    B --> C["Partition events_2026_06"]
    B --> D["Partition events_2026_07"]
    B --> E["Partition events_2026_08"]
    F["pg_partman BGW once an hour"] --> B
    F --> G["Create new partitions"]
    F --> H["Drop stale partitions"]
    style A fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style B fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style C fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style D fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style E fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style F fill:#f8fafc,stroke:#f97316,stroke-width:2px,color:#9a3412
    style G fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style H fill:#f8fafc,stroke:#ef4444,stroke-width:2px,color:#b91c1c

4. Verification

Check that partitioning is actually working, not just sitting there for show. Three things: partition pruning in the query plan, the list of active partitions, and maintenance status.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE event_date >= '2026-07-01' AND event_date < '2026-08-01';

Result: the plan should show an “Append” covering only one partition (events_2026_07), the rest should be absent from the output. That’s partition pruning at work. If the plan shows all partitions in a row, constraint exclusion didn’t kick in, check the complications section below.

-- list of all partitions and their size
SELECT
    child.relname AS partition_name,
    pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'events'
ORDER BY child.relname;

-- log of the last pg_partman maintenance run
SELECT parent_table, last_updated, undo_in_progress
FROM partman.part_config
WHERE parent_table = 'public.events';

Check that the background worker is alive:

sudo -u postgres psql -c "SELECT * FROM pg_stat_activity WHERE backend_type = 'pg_partman_bgw';"

5. Complications

CALL partman.run_maintenance_proc(p_parent_table => 'public.events');

SHOW constraint_exclusion;
-- should be: partition

It’s not as bad as you think. It’s much worse, if you dive into the migration with no index on the source table and no disk headroom. Read the preparation section again before starting on production.

6. Alternatives

Monthly partitioning isn’t the only option, but for logs, events, metrics, and orders with a clear time axis, it’s usually the right choice.

• Weekly or daily partitioning: gives you more granular control, but spawns partitions faster. Over a year you’ll end up with 52 or 365 tables instead of 12. Fits very high-load systems with telemetry.
• HASH partitioning: use it when the key axis isn’t time but, say, tenant_id in a multi-tenant system. Doesn’t solve the problem of archiving old data, but spreads load evenly across tenants.
• LIST partitioning: when data naturally splits by category (region, status) rather than by date range.
• TimescaleDB: an extension on top of PostgreSQL specialized for time-series data, with automatic compression of old chunks. Overkill if all you need is to slice a table by month without metric-specific features.
• Archiving to a separate database or Parquet files: an option for data that’s almost never read but legally must be retained. Partitioning inside PostgreSQL is easier to maintain if the data is occasionally still needed in queries.

For the typical case, a logs/events/orders table with constant growth and queries mostly against the last few weeks, monthly partitioning with pg_partman remains the best balance between simplicity and control.

7. Prevention

Monitoring

-- partitions without fresh data for more than 40 days, a sign maintenance didn't run
SELECT parent_table, last_updated
FROM partman.part_config
WHERE last_updated < now() - interval '2 days';

Set up an alert in Zabbix or Prometheus (postgres_exporter) for the size of the events_old table and the age of the newest partition.

Backup

• What to back up: the partman.part_config configuration separately from the data. When restoring to a new server, the config needs to be restored first
• How often: scheduled full pg_dump/pg_basebackup; for large partitioned databases, WAL archiving through pgBackRest or Barman for point-in-time recovery
• Where to store: at least two locations, locally and in object storage (S3-compatible). The 3-2-1 rule still applies
• How to restore: pg_restore for a logical dump, or deploy from a base backup plus replay WAL up to the needed point. Test the restore on staging at least once a quarter

Security

• pg_partman from version 5.5 doesn’t require superuser. Create a separate partman_maintainer role with targeted privileges instead of a superuser
• Restrict access to port 5432 via firewall (ufw allow from specific IPs, not 0.0.0.0/0)
• fail2ban against password-guessing attempts in pg_hba.conf with md5/scram methods
• A separate database user for the application with rights only to the needed tables, not the whole cluster
• Row Level Security, if several roles work with the same partman configuration table: relevant in multi-tenant scenarios

Updates

• How to update safely: ALTER EXTENSION pg_partman UPDATE TO ‘x.y.z’, first on staging. Check the CHANGELOG for breaking changes between major versions (4.x → 5.x changed the background maintenance mechanics)
• What to check before updating: the current extension version (SELECT extversion FROM pg_extension WHERE extname = ‘pg_partman’), compatibility with your PostgreSQL version
• How to roll back: keep a backup of the part_config configuration before updating. pg_partman has no automatic downgrade, rolling back means restoring from backup

8. FAQ

Why doesn’t PostgreSQL partitioning work after setup?

Most often it’s constraint_exclusion, or the query applies a function on top of the partition column instead of a direct comparison. Check EXPLAIN. If the planner scans all partitions instead of one, rewrite the WHERE condition as a direct comparison with the date, without wrappers like date_trunc().

How do I verify that partitioning is working correctly?

Run EXPLAIN (ANALYZE, BUFFERS) on a typical query for a specific month. The plan should show only one partition in the Append, not all of them. Also compare the size of each partition with pg_relation_size, they should grow gradually, not stay empty.

What do I do if partition_data_proc hangs during data migration?

Check whether there’s an index on the partition column in the source table. Without one, every batch runs a full scan. Reduce p_batch_count if the server is under load, and watch pg_stat_activity for locks.

What’s the difference between pg_partman and built-in declarative partitioning?

Declarative partitioning is PostgreSQL’s own mechanism for creating and storing partitions. pg_partman is a layer on top of it that automates the routine work: creating new partitions on a schedule, dropping stale ones, migrating data out of a monolithic table. Without pg_partman, you’d have to do all of this manually or write your own cron scripts.

How many partitions can you create in PostgreSQL without losing performance?

There’s no formal limit, but in practice once you get past a few thousand partitions, the planner starts spending noticeable time just planning the query. For monthly partitioning this isn’t an issue: over 10 years you’ll end up with 120 partitions, a comfortable number. For long-term daily partitioning, plan a retention policy and dropping of old partitions ahead of time.

9. Outlook

You’ve set up monthly partitioning, rolled out pg_partman on autopilot, and migrated history without blocking production. Now queries for the current month hit a single partition instead of scanning the whole history, VACUUM processes partitions separately and finishes in a reasonable time, and archiving old data is a DETACH PARTITION instead of an hour-long DELETE with locks.

What’s left is routine: hook up monitoring for partition age, test a restore from backup at least once, and check the pg_partman CHANGELOG for new releases every quarter. If something didn’t work right after setup, drop a comment, we’ll sort it out.

Useful links:
Official PostgreSQL documentation on partitioning
pg_partman on GitHub