Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 90 ยท Partitioning and Scaling

Managing Partitions in Production

Keep partitioned PostgreSQL tables healthy: automate partition creation, use pg_partman, sub-partition, migrate a big table online and avoid common pitfalls.

Creating a partitioned table is the easy part. The hard part is the third year, when someone has to make sure next month's partition exists before midnight on the 31st, old partitions are archived on schedule, and nobody has quietly filled the default partition with a million rows. This lesson is about running partitioned tables in production: automating the routine work, converting an existing huge table, and knowing the limits.

It builds on Declarative Partitioning. Everything PostgreSQL-specific here (DDL, extensions, scheduled jobs) needs a real server, so those blocks are read-only. The runnable examples generate the same names, bounds and DDL text that a maintenance script produces, using a recursive CTE as a calendar.

The partition maintenance loop

Create ahead Always have next months ready before data arrives.
Check Alert if the default partition is not empty.
Archive Detach and export partitions past retention.
Drop Remove them and free the space instantly.

Automate all four, then monitor the automation itself.

1 / 2

Creating partitions ahead of time

A partition must exist before a row for its range arrives. If it does not, the insert fails (or the row lands in the default partition). So the rule is simple: create partitions in advance, for example the next three to twelve months, every day or week. The job is idempotent, so running it twice does no harm.

The next query uses a recursive CTE to produce the names, bounds and DDL for the next six months. Real scripts do the same thing in a PL/pgSQL DO block or from a scheduler (see PL/pgSQL Basics and Time-Series, Queues and Caching in PostgreSQL).

SQL Generate the DDL for the next six months

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL the same idea is a small function that loops and runs the statements with dynamic SQL. Using IF NOT EXISTS makes the loop safe to repeat:

SQL
DO $$
DECLARE
  first_day date := date_trunc('month', now())::date;
  start_day date;
BEGIN
  FOR i IN 0..5 LOOP
    start_day := first_day + make_interval(months => i);
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF sensor_readings FOR VALUES FROM (%L) TO (%L)',
      'sensor_readings_' || to_char(start_day, 'YYYY_MM'),
      start_day,
      start_day + interval '1 month'
    );
  END LOOP;
END $$;

Note the %I (identifier) and %L (literal) placeholders of format(). Never build DDL by pasting strings together without them.

Keeping an eye on the default partition

If a default partition exists, treat any row in it as a bug report. A simple check belongs in your monitoring:

SQL
SELECT count(*) FROM sensor_readings_default;

Cleaning it up means creating the missing partition, but PostgreSQL will not let you attach a range that overlaps rows sitting in the default partition. The usual repair is: detach the default partition, create the new partition, copy the stray rows into the parent, then re-attach the default partition. That is one more reason to create partitions early.

SQL Find rows that would end up in the default partition

Press Run (or Ctrl + Enter) to see the result here.

The output tells you exactly which partitions the maintenance job should have created.

pg_partman

Writing and debugging your own maintenance code is optional. pg_partman is a widely used extension that creates partitions ahead of time, applies retention rules and can help migrate existing data. It is not part of core PostgreSQL, so check that your host offers it (many managed services do; see Extensions). You register a parent table once:

SQL
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

SELECT partman.create_parent(
  p_parent_table => 'public.sensor_readings',
  p_control      => 'recorded_at',
  p_interval     => '1 month',
  p_premake      => 4
);

UPDATE partman.part_config
SET retention = '24 months',
    retention_keep_table = false
WHERE parent_table = 'public.sensor_readings';

Argument names and the available options change between pg_partman versions, so follow the documentation for the version you install. Then partman.run_maintenance() has to run regularly. It creates the next partitions and removes expired ones. You can call it from pg_cron, from an external scheduler, or from pg_partman's background worker.

Whatever tool you choose, the lessons are the same: partition ahead, make the job idempotent, alert when it fails, and test retention on a copy before it deletes anything for real.

Sub-partitioning

A partition can itself be partitioned. A common pattern is range by month, then hash or list inside each month:

SQL
CREATE TABLE events (
  id          bigint      NOT NULL,
  tenant_id   integer     NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload     jsonb,
  PRIMARY KEY (id, tenant_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_04 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01')
  PARTITION BY HASH (tenant_id);

CREATE TABLE events_2026_04_h0 PARTITION OF events_2026_04
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... h1, h2, h3

The primary key has to include every partition key on the path. The cost is multiplication: 24 months times 4 hash partitions is already 96 tables. Only sub-partition when a single month is still too big or one tenant's queries need isolating.

SQL Counting partitions with sub-partitioning

Press Run (or Ctrl + Enter) to see the result here.

288 leaf tables for one logical table is manageable, but it is also 288 sets of indexes, 288 relations for autovacuum to visit and a lot more locks for any query that does not prune. Count first, then decide.

Converting an existing big table

PostgreSQL cannot turn a normal table into a partitioned one in place. There are two common routes.

Route 1: new table, copy, swap. Create the partitioned table beside the old one, copy the data in batches (or with COPY, see Bulk Loading and COPY), keep both in sync while you copy, then rename the tables inside one short transaction.

Route 2: attach the old table as a partition. This avoids copying history at all:

SQL
-- 1. The old table must have the partition key in its primary key and a CHECK that proves its range
ALTER TABLE sensor_readings_old
  ADD CONSTRAINT old_range CHECK (recorded_at < '2026-04-01') NOT VALID;
ALTER TABLE sensor_readings_old VALIDATE CONSTRAINT old_range;

-- 2. In one transaction: rename it out of the way and attach it
BEGIN;
ALTER TABLE sensor_readings RENAME TO sensor_readings_old;
CREATE TABLE sensor_readings (LIKE sensor_readings_old INCLUDING ALL)
  PARTITION BY RANGE (recorded_at);
ALTER TABLE sensor_readings ATTACH PARTITION sensor_readings_old
  FOR VALUES FROM (MINVALUE) TO ('2026-04-01');
CREATE TABLE sensor_readings_2026_04 PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
COMMIT;

NOT VALID then VALIDATE checks the existing rows without a long exclusive lock (see Schema Changes and Migrations). The old table then keeps all history in one big partition and new data flows into small ones. You can split the history later at a quiet time. In both routes, rehearse on a copy of production data and measure the lock durations.

Limits and pitfalls

  • Partition count. Hundreds are fine. Thousands slow planning and use memory, and queries that cannot prune take a lock on every partition, which can exceed max_locks_per_transaction.
  • Pruning blind spots. WHERE recorded_at::date = '2026-02-10' or a filter that wraps the key in a function may stop pruning. Compare the key directly with constants or parameters.
  • Unique constraints across partitions are only possible if they include the key. If you need a globally unique ID, use a sequence, a UUID or an identity column and accept that the database cannot enforce it alone.
  • Per-partition work. Each partition has its own indexes, statistics and vacuum schedule. Autovacuum processes the partitions but never analyzes the partitioned parent itself, so run ANALYZE on the parent after big loads to keep its statistics current.
  • Missing partitions after a failure. If the maintenance job stopped two months ago, you find out at midnight. Alert on "fewer than N future partitions exist".
  • Schema changes to the parent propagate to all partitions, which can mean hundreds of table rewrites. Test them first.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should partitions be created ahead of time?

    Without a partition for the row's key, the insert fails or the row goes to the default partition, where it makes later maintenance harder. Create the next ranges before data arrives.
  2. 2. Which statement about converting an existing normal table into a partitioned one is true?

    PostgreSQL cannot change a table into a partitioned table in place, but a table with a suitable CHECK constraint can be attached as a partition, which avoids copying its history.
  3. 3. What does pg_partman mainly do?

    pg_partman builds on declarative partitioning. Its maintenance function creates upcoming partitions and drops or detaches expired ones.
  4. 4. What is the main risk of sub-partitioning a table by month and then by hash into 16 pieces for 5 years of data?

    Sub-partitioning multiplies the table count (60 months times 16 is 960). Each leaf has its own indexes and vacuum work, so use it only when a single level is not enough.

Summary

  • Create partitions ahead of time with an idempotent job, and alert when future partitions are missing.
  • Treat the default partition as a smoke detector: it should stay empty.
  • pg_partman automates creation and retention; the pattern matters more than the tool.
  • Sub-partitioning multiplies the number of tables, so count before you commit.
  • Existing tables are converted by copy-and-swap or by attaching the old table as a partition with a CHECK constraint.
  • Keep the partition count moderate and filter on the key directly so pruning works.

Next: Streaming and Logical Replication.