Is your database pain really a large-table problem?

Before restructuring anything, confirm the problem is the shape of one table rather than a missing index or a bad query. Partitioning will not fix an N+1 loop or a sequential scan caused by a missing index. It helps when a table grows without bound while the application mostly reads and writes a narrow, predictable slice of it, usually recent rows.

Look for these signals:

  • Retention DELETEs are slow and noisy. Purging old rows rewrites heap pages, generates WAL and leaves dead tuples behind. Dropping a partition avoids all of that.
  • Autovacuum runs long on one table. In pg_stat_user_tables, a high n_dead_tup relative to n_live_tup, a frequently climbing autovacuum_count, and a stale last_autovacuum point to vacuum struggling with sheer size.
  • Indexes are large but mostly cold. Compare pg_relation_size per index with idx_scan in pg_stat_user_indexes. Large B-trees on time-ordered data accumulate bloat that partitions would keep small.
  • Hot queries touch only recent data. Run EXPLAIN (ANALYZE, BUFFERS) on them. If the predicate filters on a time or tenant column, yet the Buffers: shared read counts are high, the planner is walking structures sized for the whole table. Partition pruning would let it skip irrelevant partitions entirely.

If most of these signals apply, partitioning is worth evaluating. This approach suits teams running a monolith on one Postgres cluster who feel scaling pressure. It is a schema change inside one database, far cheaper to try than splitting a service and its data ownership.

-- 1. Which tables are largest, and is vacuum keeping up on them?
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
       autovacuum_count,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- 2. Large indexes that are rarely scanned (counters are cumulative since the last stats reset)
SELECT relname AS table_name,
       indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'events'  -- placeholder: the suspect table
ORDER BY pg_relation_size(indexrelid) DESC;

-- 3. A typical hot query that only needs recent rows.
-- High 'shared read' counts here suggest the plan walks structures sized
-- for the whole table. Pruning would only help if the table were
-- partitioned on the column this predicate uses.
-- Note: ANALYZE executes the query, so run it on a replica or off-peak if it is heavy.
EXPLAIN (ANALYZE, BUFFERS)
SELECT account_id, count(*)
FROM events
WHERE created_at >= now() - interval '1 day'
GROUP BY account_id;

Diagnostic queries against pg_stat_user_tables and pg_stat_user_indexes, plus an EXPLAIN (ANALYZE, BUFFERS) run on a recent-data query, to check whether a table's pain comes from its size and access pattern rather than a missing index.

Who should use Postgres partitioning?

Partitioning fits backend and platform engineers who own a monolith's schema and migrations on a single Postgres cluster, and who are being asked whether the system needs to be split into services. It fits best when one or a few tables dominate the database's size and maintenance load:

  • Append-heavy, time-ordered tables such as events, audit logs, orders, metrics or notifications, where recent rows are hot and old rows are rarely read and eventually deleted.
  • Multi-tenant or multi-region SaaS tables, where most queries filter on a tenant or region and you need to remove or move one tenant's data cleanly.
  • Teams with a retention or compliance policy that today is enforced by batched DELETE jobs.

It is a poor fit for small tables that fit in memory, tables where every row stays equally hot (user accounts, product catalogues), and teams whose real problem is independent deployment or ownership across teams. The last section covers those cases.

When should you partition?

Partition when the diagnostics above point at table size and access pattern, and when cheaper fixes have been tried. In practice that means:

  • After you have ruled out missing indexes, bad plans and N+1 queries.
  • When hot queries consistently filter on one column, such as time or tenant, that could serve as the partition key.
  • When old data leaves the table by age or by tenant, so that dropping a partition can replace a DELETE.
  • Before you commit to extracting a service or a database for scaling reasons. Partitioning is reversible within one database and much cheaper to try.
  • Earlier rather than later for tables you can see growing without bound. Both migration approaches described below get slower and riskier as the table grows, and creating a new table as partitioned from day one avoids migration entirely.

What is Postgres declarative partitioning and how does it work?

Declarative partitioning splits one logical table into several physical ones. You create a parent table with PARTITION BY RANGE, LIST or HASH on one or more columns, then attach child partitions that each own a slice of the key space. Range partitions take a lower bound (inclusive) and an upper bound (exclusive), so monthly partitions line up without gaps or overlaps. List partitions take explicit values, such as a set of region codes. Hash partitions use a modulus and remainder to spread rows evenly.

The parent stores no rows. On insert, Postgres checks the partition key and routes the row to the matching child. If no child matches and there is no DEFAULT partition, the insert fails. This is useful because a missing partition shows up as an error instead of silently putting data in the wrong place.

                 events (parent, PARTITION BY RANGE (created_at))
                                   |
        +--------------------------+--------------------------+
        |                          |                          |
  events_2026_09             events_2026_10             events_2026_11
  [09-01, 10-01)             [10-01, 11-01)             [11-01, 12-01)
     PRUNED                     PRUNED                     SCANNED

  WHERE created_at >= '2026-11-05'  -> planner reads one partition

Pruning is where you get the speed. If the WHERE clause constrains the partition key with constants, the planner removes non-matching partitions at plan time. If the values arrive later, for example as prepared statement parameters or from the outer side of a nested loop join, Postgres can prune at execution time instead.

Partitioning organizes data. It does not make every query faster. A query that does not filter on the key has to visit every partition, and the extra planning work over many children can make it slower than the same query on a single table.

-- Parent table: declares the partition key and stores no rows itself.
CREATE TABLE events (
    id         bigint      NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb
) PARTITION BY RANGE (created_at);

-- Monthly children: lower bound inclusive, upper bound exclusive, so no gaps or overlaps.
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');

-- Routed to events_2026_11 based on created_at.
INSERT INTO events VALUES (1, '2026-11-05 10:00+00', '{"type":"signup"}');

-- No matching child and no DEFAULT partition, so this fails with
-- "no partition of relation \"events\" found for row" instead of misplacing data.
-- INSERT INTO events VALUES (2, '2026-12-02 09:00+00', '{}');

-- Plan-time pruning: the constant lets the planner drop September and October.
EXPLAIN SELECT * FROM events WHERE created_at >= '2026-11-05';

-- Execution-time pruning: with a generic plan the value is unknown at plan time,
-- so pruning happens at executor startup. Look for "Subplans Removed" in the output.
SET plan_cache_mode = force_generic_plan;
PREPARE recent(timestamptz) AS
    SELECT * FROM events WHERE created_at >= $1;
EXPLAIN EXECUTE recent('2026-11-05');

-- No filter on the partition key: every partition is scanned.
EXPLAIN SELECT * FROM events WHERE payload->>'type' = 'signup';

A range-partitioned events table with monthly children, showing insert routing, the error for an unmatched row, plan-time pruning with a constant, execution-time pruning with a prepared statement, and a query that has to scan every partition because it does not filter on the key.

How it works under the hood

Each partition is an ordinary table with its own heap, its own indexes and its own statistics. That has several practical consequences:

  • Vacuum and analyze work per partition. Autovacuum processes each partition separately, so a cold, old partition with no changes needs little attention while the current one gets vacuumed on its own schedule. Note that autovacuum does not analyze the partitioned parent itself. Run ANALYZE on the parent manually if queries depend on parent-level statistics.
  • Indexes are per partition. An index defined on the parent is a template. Postgres creates a matching index on every partition, and each one stays small enough to fit in memory for the hot partitions.
  • Queries become an Append over children. The planner builds a plan for each partition that survives pruning and combines the results. Pruning is controlled by enable_partition_pruning, which is on by default.
  • Joins and aggregates can work partition by partition. enable_partitionwise_join and enable_partitionwise_aggregate let the planner join or aggregate matching partitions separately. Both are off by default because they increase planning cost. Turn them on only after measuring your own workload.
  • Partitions can be detached and attached. Because a partition is a table, you can detach it and keep it as a standalone table, or load data into a standalone table and attach it. That is what makes retention and bulk loads cheap.

How do you choose the right partition key?

Start with your slowest and most frequent queries, not the table's schema. Pruning only happens when the planner can compare the partition key against a constant or parameter in the WHERE clause. If your hot queries filter on created_at, partitioning by customer_id gives you all the overhead and none of the benefit.

The second test is how data leaves the table. If old rows are deleted by age, a time-based key lets you drop a whole partition instead of running a large DELETE that bloats the table and generates heavy WAL.

Strategy Fits Pruning works when Retention
Range Events, logs, orders Query filters on a time or ID range Drop or detach old partitions
List Tenant, region Query filters on a specific tenant or region Remove one tenant or region cleanly
Hash Spreading writes evenly Query uses equality on the key None; old rows sit in every partition

You can check pruning with EXPLAIN. Partition an orders table by month on created_at. A query with WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01' produces a plan that touches only the June partition. The same query filtered only by customer_id = 42 produces an Append node with one scan per partition, every month included.

Choose carefully, because changing the key later means rewriting the whole table. Until then, every query that omits the key pays for visiting every partition.

-- Orders partitioned by month on created_at (range).
CREATE TABLE orders (
    id          bigint      NOT NULL,
    customer_id bigint      NOT NULL,
    total_cents integer     NOT NULL,
    created_at  timestamptz NOT NULL,
    -- A primary key on a partitioned table must include the partition key.
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Upper bound is exclusive, so months do not overlap.
CREATE TABLE orders_2024_05 PARTITION OF orders
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');
CREATE TABLE orders_2024_06 PARTITION OF orders
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');
CREATE TABLE orders_2024_07 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

-- 1) Filter on the partition key: the planner prunes.
--    Expect only orders_2024_06 in the plan.
EXPLAIN
SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

-- 2) No partition key in WHERE: nothing can be pruned.
--    Expect an Append node with one scan per partition.
EXPLAIN
SELECT * FROM orders
WHERE customer_id = 42;

-- Retention by age becomes a metadata operation instead of a big DELETE.
ALTER TABLE orders DETACH PARTITION orders_2024_05;
DROP TABLE orders_2024_05;

A month-partitioned orders table where filtering on created_at prunes down to one partition, filtering only on customer_id scans every partition, and old data is removed by detaching and dropping a partition.

What schema changes does partitioning force on primary keys, foreign keys and ORMs?

Partitioning changes how the database guarantees uniqueness. Postgres enforces a primary key or unique constraint on a partitioned table through a separate index on each partition, and no index spans the whole table. It can only guarantee uniqueness if every possible duplicate must land in the same partition, so the constraint has to include all partition key columns. A table partitioned on created_at therefore cannot keep PRIMARY KEY (id) and must use PRIMARY KEY (id, created_at) instead.

That change matters more than it looks. The database now enforces only that the pair is unique. Two rows with the same id and different timestamps are legal. If you generate ids from a sequence or UUIDs, collisions remain unlikely in practice, but nothing in the schema prevents one, and code that writes ids explicitly (imports, backfills, merges) can create duplicates.

Foreign keys follow the same rule. A table that references the partitioned table must point at the full composite key, so child tables need a copy of created_at, and that copy must be kept correct on every write. Older Postgres releases cannot reference a partitioned table with a foreign key at all.

ORMs usually assume a single-column identity. Lookups by id, identity maps, and association loading may need composite-key configuration, or they may not support composite keys well.

Logical replication, CDC pipelines, and schema-diff tools handle partitions differently depending on the Postgres version. Some treat the parent as one table, while others see each partition separately. Test all of this against your exact Postgres version, ORM, and tooling before you migrate.

-- Parent table partitioned by time. PRIMARY KEY (id) alone is rejected here:
-- a unique constraint on a partitioned table must include the partition key.
CREATE TABLE orders (
    id          bigint      NOT NULL,
    created_at  timestamptz NOT NULL,
    customer_id bigint      NOT NULL,
    PRIMARY KEY (id, created_at)  -- enforced per partition, not globally
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_10 PARTITION OF orders
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE orders_2026_11 PARTITION OF orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- An explicit-id write path (import, backfill, merge) can now create
-- duplicate ids. Both inserts succeed because the (id, created_at) pairs differ.
INSERT INTO orders (id, created_at, customer_id)
VALUES (1001, '2026-10-15', 42),
       (1001, '2026-11-02', 42);

-- A referencing table must carry the full composite key, including a copy of
-- created_at that the application has to keep correct on every write.
-- Foreign keys that reference a partitioned table require Postgres 12 or later.
CREATE TABLE order_items (
    order_id         bigint      NOT NULL,
    order_created_at timestamptz NOT NULL,
    sku              text        NOT NULL,
    quantity         int         NOT NULL,
    FOREIGN KEY (order_id, order_created_at)
        REFERENCES orders (id, created_at)
);

-- Detection query: the schema no longer prevents duplicate ids, so check for them.
SELECT id, count(*) AS copies
FROM orders
GROUP BY id
HAVING count(*) > 1;

Converting an id-only primary key to (id, created_at) on a range-partitioned table: duplicate ids become legal, and referencing tables must carry the partition key to satisfy the composite foreign key.

How do you migrate a live table to a partitioned table without a long outage?

Two approaches avoid rewriting the table under a heavy lock. In both, set a short lock_timeout before every DDL statement. A blocked ACCESS EXCLUSIVE request queues behind long-running queries and stalls everything behind it, so it is better to fail fast and retry.

Approach A: attach the existing table

  1. Create the partitioned parent and future partitions under a temporary name. These are new objects, so the live table takes no lock.
  2. Add a CHECK constraint on the live table that matches its future partition bounds, using NOT VALID. This takes a brief ACCESS EXCLUSIVE lock but no scan.
  3. Run VALIDATE CONSTRAINT. It scans the table under SHARE UPDATE EXCLUSIVE, so reads and writes continue. Only a validated constraint lets the attach skip its own scan.
  4. In one short transaction, rename the old table, rename the parent into its place, and run ATTACH PARTITION. Each step takes ACCESS EXCLUSIVE on the table it renames or attaches. The attach also takes SHARE UPDATE EXCLUSIVE on the parent.

Approach B: backfill into new partitions

  1. Create the parent and its partitions.
  2. Add a trigger on the old table that mirrors inserts, updates and deletes into the new table. CREATE TRIGGER takes a brief SHARE ROW EXCLUSIVE lock. Application-level dual writes also work.
  3. Backfill in primary-key batches with ON CONFLICT DO NOTHING, keeping each transaction small.
  4. Verify row counts, then swap names in a short transaction and drop the trigger.

Indexes

CREATE INDEX on the parent cascades to every partition and blocks writes on each one while it builds. CONCURRENTLY is rejected on a partitioned parent. Instead:

  1. Create the index ON ONLY the parent. It starts out invalid.
  2. Build a matching index on each partition with CREATE INDEX CONCURRENTLY.
  3. Attach each partition's index with ALTER INDEX ... ATTACH PARTITION.

The parent index becomes valid once every partition has an attached index.

Approach A: attach existing Approach B: backfill
Data copied None Entire table
Old data partitioned No, remains one large partition Yes
Main risk Constraint or column mismatch blocks the attach Trigger bugs, drift, extra write load
Effort Low High, and needs rehearsal

Running partitions in production: retention, creation and sizing

Retention becomes a metadata operation. Instead of a DELETE that touches millions of rows, bloats the table and waits on vacuum, you detach the oldest partition and drop it. DETACH PARTITION ... CONCURRENTLY avoids blocking queries on the parent, but check that your Postgres version supports it. It also has restrictions: it cannot run inside a transaction block, and it is not allowed when the table has a default partition.

Create future partitions ahead of time, either with a scheduled job or with an extension your team already runs and trusts. Use a DEFAULT partition only if you understand the cost. Rows that land in it hide routing mistakes, and they can block you from attaching a new partition whose range overlaps them. Alert when the newest partition is a few intervals from running out. Treat this job as a production dependency, with the same ownership and monitoring as a backup.

Illustrative estimate. Assumed inputs: 10 million rows per day, 13 months of retention (about 395 days).

Range Partitions kept Rows per partition
Daily ~395 ~10M
Weekly ~57 ~70M
Monthly ~13–14 ~300M

Daily partitions look tidy, but every partition has a cost. When pruning cannot happen at plan time, planning takes longer. Queries that touch many partitions take more locks. Each backend's relation cache uses more memory. For this workload, weekly or monthly ranges are usually enough. The engineering time comes back elsewhere: no batched-delete scripts, no vacuum firefighting and no bloat-driven incidents.

How can partitioning save engineering time?

The savings come from replacing recurring operational work with metadata operations, and from deferring a much larger project.

  • Retention stops being a project. A batched-delete job needs batch-size tuning, throttling to protect replicas from WAL bursts, and someone to investigate when it falls behind. Detaching and dropping a partition replaces that with a short scheduled job whose failure modes are simple to monitor.
  • Vacuum and bloat incidents become rarer. Old partitions stop changing, so vacuum has little to do on them, and the active partition stays small enough that autovacuum keeps up without per-table tuning. Less time goes into investigating bloat, rebuilding indexes and tuning thresholds.
  • Maintenance is scoped to one partition. REINDEX, VACUUM, ANALYZE or a data fix can target a single partition rather than the whole table, which shortens maintenance windows and makes them easier to schedule.
  • Bulk loads and archiving get simpler. You can load a backfill into a standalone table, index and validate it, then attach it. You can archive a whole period by detaching a partition and moving or dumping that table on its own.
  • A service split can wait until it is needed for the right reasons. Extracting a service for database scaling means new deployment pipelines, cross-database consistency work and rewriting joins. Partitioning is a schema change inside the existing database, done by the team that already owns it, and it can be reversed. If scaling pressure was the only reason for the split, that work may never be needed.

The time is not free. The migration, the composite-key changes in application code and the partition-creation job all cost engineering effort up front, and the creation job needs ongoing ownership. The trade pays off when the table's pain is recurring, not when it is a one-off.

When is partitioning not enough, and what are the alternatives?

Partitioning earns its cost only when the table has a recency or retention pattern that queries and deletes can exploit. A table of a few gigabytes that fits comfortably in memory gains little, and planning every query against dozens of children can make it slower. A table where every row stays hot forever, such as user accounts, gives pruning nothing to skip. Partitioning also does nothing for write throughput: every partition still lives on one primary, sharing its WAL, CPU and disk. Nor does it address organisational problems. If two teams need to change the schema and deploy independently, they still share one database, one migration pipeline and one failure domain.

Option What it solves Development cost Operational cost
Better indexes and query fixes Slow queries caused by bad plans or missing indexes Low Low
Archive to a separate table Hot table size when old rows are rarely read Low to medium Medium, because batch moves need scheduling
Read replicas Read load on the primary Medium, because of replication lag handling Medium
Extract to a separate database Write load and noisy neighbours for one domain High, because cross-database joins are lost High
Full service split Team ownership and independent deploys Very high Very high

Try the cheap rows first; most "large table" incidents are an index or query problem. If you do partition, choose a key that matches a domain boundary, such as tenant or region, where the workload allows it. Each partition then becomes a natural unit to move into its own database later. Partitioning postpones a service split. It does not replace one.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres table partitioning, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.