PostgreSQL Partitioning Practical Guide

• 0. Scope of This Post and Reference Version
• 1. What Is Partitioning?
  • Advantages of Partitioning
• 2. Range Partitioning
• 3. List Partitioning
• 4. Hash Partitioning
• 5. Verifying Partition Pruning
• 6. Automatic Partition Creation (pg_partman)
  • cron Configuration
  • Manual Automation Script
• 7. Deleting Old Partitions
• 8. Operational Tips
  • Monitoring Partition Status
  • Caveats
• Partition DDL and the Lock Each One Takes
  • The safe order: create separately, validate, then attach
  • A DEFAULT partition adds one more step
  • DETACH does not delete the table
• Building an Index on a Partitioned Table Without Downtime
• Converting an Existing Table Into a Partitioned Table
• Failure Cases and the Order to Diagnose Them
  • Symptom: we only added one partition and the whole service stopped
  • Symptom: ATTACH PARTITION has been running for 30 minutes
  • Symptom: I added one index and all writes stopped
  • Symptom: partitions are created fine but the query plan looks wrong
  • Symptom: I dropped a partition but disk usage did not fall
• When Not to Go This Far
• References
• 9. Quiz
• Quiz

0. Scope of This Post and Reference Version

What this post covers is the DDL that creates, attaches and detaches partitions, and the locks that DDL takes. Deciding between Range, List and Hash, and choosing which column becomes the partition key, are handled separately in PostgreSQL Partitioning Complete Guide. The syntax examples in sections 2 through 4 below exist only to show how each strategy is expressed as DDL; the centre of gravity of this post is the operational DDL that follows section 8.

The reference engine is PostgreSQL 18. Every lock level and default value in this post was read from the PostgreSQL 18 documentation, and the document URLs are collected in the references section at the end. Partitioning is an area whose behaviour has changed across major versions, so if your server is not on 18 you must not assume the same sentences still hold. In particular, check whether a later addition such as DETACH PARTITION CONCURRENTLY is supported in the documentation for the version you are actually running.

The reason locks come first is simple. What hurts people in partitioning is not syntax, it is locks. Get the syntax wrong and you get an error immediately. Get the lock wrong and there is no error. The service just stops for the duration.

1. What Is Partitioning?

Partitioning is a technique that divides a single large table into multiple physical partitions. It can significantly improve query performance when the table contains hundreds of millions of rows or more.

Advantages of Partitioning

• Query performance improvement: Partition pruning scans only the necessary partitions
• Bulk data deletion: DROP PARTITION for instant deletion (hundreds of times faster than DELETE)
• Parallel processing: Parallel scan per partition is possible
• Easier management: Per-partition indexing, VACUUM, and backup

2. Range Partitioning

The most commonly used method, dividing data by date or numeric ranges.

-- Create parent table
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (order_date);

-- Create monthly partitions
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE orders_2026_03 PARTITION OF orders
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Per-partition indexes (automatically inherited)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

-- Default partition (catches data that does not fit any range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

3. List Partitioning

Divides data by specific value lists. Suitable for regions, categories, etc.

CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (event_type);

CREATE TABLE events_user PARTITION OF events
    FOR VALUES IN ('user_signup', 'user_login', 'user_logout');

CREATE TABLE events_order PARTITION OF events
    FOR VALUES IN ('order_created', 'order_paid', 'order_cancelled');

CREATE TABLE events_system PARTITION OF events
    FOR VALUES IN ('health_check', 'deploy', 'config_change');

CREATE TABLE events_default PARTITION OF events DEFAULT;

4. Hash Partitioning

Distributes data evenly using a hash function. Suitable when the distribution of a specific key is uniform.

CREATE TABLE user_sessions (
    id         BIGSERIAL,
    user_id    INTEGER NOT NULL,
    session_id UUID NOT NULL,
    data       JSONB,
    expires_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- Even distribution across 4 partitions
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

5. Verifying Partition Pruning

-- Check if partition pruning is enabled
SHOW enable_partition_pruning;  -- on

-- Verify pruning with EXPLAIN
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2026-03-01'
  AND order_date < '2026-04-01';

-- Example result:
-- Append (actual rows=50000)
--   -> Seq Scan on orders_2026_03 (actual rows=50000)
--        Filter: (order_date >= '2026-03-01' AND order_date < '2026-04-01')
-- orders_2026_01 and orders_2026_02 are NOT scanned!

6. Automatic Partition Creation (pg_partman)

-- Install pg_partman
CREATE EXTENSION pg_partman;

-- Configure automatic partition management
SELECT partman.create_parent(
    p_parent_table   => 'public.orders',
    p_control        => 'order_date',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3       -- Pre-create 3 months ahead
);

-- Maintenance function (run daily via cron)
SELECT partman.run_maintenance();

cron Configuration

# pg_partman maintenance (daily at 2 AM)
0 2 * * * psql -U postgres -d mydb \
  -c "SELECT partman.run_maintenance();" \
  >> /var/log/pg_partman.log 2>&1

Manual Automation Script

-- Manual automation without pg_partman
CREATE OR REPLACE FUNCTION create_monthly_partition(
    p_table TEXT,
    p_year INTEGER,
    p_month INTEGER
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('%s_%s_%s',
        p_table,
        p_year,
        LPAD(p_month::TEXT, 2, '0')
    );
    start_date := make_date(p_year, p_month, 1);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, p_table, start_date, end_date
    );

    RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT create_monthly_partition('orders', 2026, 4);
SELECT create_monthly_partition('orders', 2026, 5);

7. Deleting Old Partitions

-- Detach partition (preserve data, exclude from queries)
ALTER TABLE orders DETACH PARTITION orders_2025_01;

-- Keep the detached partition as a separate table or delete it
DROP TABLE orders_2025_01;  -- Instant deletion (even hundreds of millions of rows)

-- For comparison: DELETE is very slow
-- DELETE FROM orders WHERE order_date < '2025-02-01';  -- Do not do this!

8. Operational Tips

Monitoring Partition Status

-- Check row count per partition
SELECT
    schemaname || '.' || relname AS partition,
    n_live_tup AS row_count,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

-- List partitions
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS bounds
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 = 'orders'
ORDER BY child.relname;

Caveats

1. Partition key MUST be included in PRIMARY KEY
   CREATE TABLE orders (...) PARTITION BY RANGE (order_date);
   -> PK must be in the form (id, order_date)

2. UNIQUE constraints also need to include the partition key

3. Too many partitions increase planning overhead
   -> Recommended to keep under 1000

4. Cross-partition UPDATE is only supported in PostgreSQL 11+

Partition DDL and the Lock Each One Takes

There are really only four partition DDL statements you use in production. The problem is that all four take different locks.

The PostgreSQL documentation states this difference very directly: creating a partition with PARTITION OF requires an ACCESS EXCLUSIVE lock on the parent partitioned table, dropping a partition with DROP TABLE requires the same, and doing the equivalent work with ATTACH and DETACH performs those operations with a weaker lock, reducing interference with concurrent operations.

ACCESS EXCLUSIVE is the strongest lock there is. Even a SELECT that only reads the table has to wait. So a single line of CREATE TABLE orders_2026_09 PARTITION OF orders ... stalls every session touching orders the moment it runs. Creating an empty table finishes in milliseconds, but the time spent waiting to acquire the lock is not measured in milliseconds. If one long-running transaction ahead of you holds even a weak lock on orders, your DDL queues behind it — and the moment an ACCESS EXCLUSIVE request sits at the head of that queue, every SELECT arriving afterwards is blocked too. That is the precise mechanism behind "we only added one partition and it took the site down".

The safe order: create separately, validate, then attach

-- 1) Create it as a standalone table unrelated to the parent.
--    No lock is taken on the parent at all.
CREATE TABLE orders_2026_09 (
    LIKE orders INCLUDING DEFAULTS INCLUDING STORAGE
);

-- 2) Load data here if you need to. It has no relationship to the parent yet,
--    so however long this takes, the service is unaffected.

-- 3) Add a CHECK constraint identical to the partition bound, as NOT VALID.
--    Because it is NOT VALID, no existing rows are scanned at this point.
ALTER TABLE orders_2026_09
    ADD CONSTRAINT orders_2026_09_bound
    CHECK (order_date >= DATE '2026-09-01' AND order_date < DATE '2026-10-01')
    NOT VALID;

-- 4) Run the validation on its own. This step takes only SHARE UPDATE
--    EXCLUSIVE, so it does not block concurrent INSERT/UPDATE.
ALTER TABLE orders_2026_09 VALIDATE CONSTRAINT orders_2026_09_bound;

-- 5) Attach. A valid CHECK already exists, so the full scan is skipped.
ALTER TABLE orders ATTACH PARTITION orders_2026_09
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 6) Drop the now-redundant CHECK constraint.
ALTER TABLE orders_2026_09 DROP CONSTRAINT orders_2026_09_bound;

Why each step is needed is spelled out in the documentation.

ATTACH PARTITION performs a full table scan to check that existing rows in the table do not violate the partition constraint, and that scan runs while holding an ACCESS EXCLUSIVE lock on that partition. The documentation names the way out: add a valid CHECK constraint to the table that allows only rows satisfying the desired partition constraint before running the command, and it will be used to determine that the table need not be scanned. Steps 3 and 4 are exactly that.

And VALIDATE CONSTRAINT only has to check pre-existing rows, since the constraint is already being enforced for new ones — so it does not need to lock out concurrent updates. In the documentation's words, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. In other words, this whole recipe is just reordering the work so that the slow scan happens under a weak lock and the ATTACH, which needs the strong lock, finishes instantly with no scan at all.

A DEFAULT partition adds one more step

The documentation gives a separate warning for the case where a DEFAULT partition exists. When you attach a new partition, PostgreSQL must verify that the DEFAULT partition contains no rows that properly belong in the new partition, and that check is performed while holding an ACCESS EXCLUSIVE lock on the DEFAULT partition. If the DEFAULT partition has grown large, that one scan can consume tens of minutes — during which the DEFAULT partition cannot even be read.

The way out is the same. Add a CHECK constraint to the DEFAULT partition that excludes the range you are about to attach.

ALTER TABLE orders_default
    ADD CONSTRAINT orders_default_excl_2026_09
    CHECK (order_date < DATE '2026-09-01' OR order_date >= DATE '2026-10-01')
    NOT VALID;

ALTER TABLE orders_default VALIDATE CONSTRAINT orders_default_excl_2026_09;
-- Now ATTACH will not scan the DEFAULT partition.

Not having a DEFAULT partition at all is also a legitimate choice. Many teams decide that having an out-of-range INSERT fail loudly and immediately is better than having it accumulate quietly in DEFAULT and block a partition attach months later. Either way it is a trade-off, not a right answer. A DEFAULT partition is a safety net and a debt whose invoice arrives later.

DETACH does not delete the table

DETACH PARTITION only severs the link to the parent. The table itself remains and keeps occupying disk. Not knowing this produces "we cleaned up the old partitions but disk usage did not drop".

-- Detach in a way that does not block concurrent access
ALTER TABLE orders DETACH PARTITION orders_2025_01 CONCURRENTLY;

-- Confirm: no longer a child of the parent, but the table is still alive
SELECT c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname = 'orders_2025_01';

-- If you really mean to delete it, delete it explicitly here
DROP TABLE orders_2025_01;

With CONCURRENTLY, PostgreSQL splits the work into two internal transactions. The first takes a SHARE UPDATE EXCLUSIVE lock on both the parent and the partition, marks the partition as undergoing detach, commits, and then waits for all other transactions using the partitioned table to finish. The second transaction then acquires SHARE UPDATE EXCLUSIVE on the partitioned table and ACCESS EXCLUSIVE on the partition and completes the detach. The point is that a strong lock is never taken on the parent table.

There is a cost. Because it commits twice internally, this form cannot be used inside a transaction block. If your migration tool wraps every DDL statement in a single transaction, you cannot use it as-is. You need a way to run that one migration outside a transaction, and how you do that differs per tool.

Building an Index on a Partitioned Table Without Downtime

On an ordinary table the answer is simple. As the documentation puts it, a standard index build locks out writes (but not reads) on the table until it is done, while CONCURRENTLY builds the index without taking any locks that prevent concurrent inserts, updates or deletes — at the cost of scanning the table twice and waiting for existing transactions to terminate.

The problem is that you cannot use CONCURRENTLY on a partitioned table. The documentation states the restriction explicitly: concurrent builds for indexes on partitioned tables are not supported. So if you run a plain CREATE INDEX against the partitioned parent, writes to the whole table are blocked until the index has been built on every partition. If there are 36 partitions, that means until all 36 are done.

The workaround the documentation recommends has three steps.

-- 1) Create the index definition on the parent only. ONLY is the key word.
--    At this point the parent index is invalid and no data is touched.
CREATE INDEX idx_orders_customer ON ONLY orders (customer_id);

-- 2) Build it per partition with CONCURRENTLY. Writes are not blocked here.
CREATE INDEX CONCURRENTLY idx_orders_2026_09_customer
    ON orders_2026_09 (customer_id);
CREATE INDEX CONCURRENTLY idx_orders_2026_10_customer
    ON orders_2026_10 (customer_id);

-- 3) Attach the built indexes to the parent index.
ALTER INDEX idx_orders_customer
    ATTACH PARTITION idx_orders_2026_09_customer;
ALTER INDEX idx_orders_customer
    ATTACH PARTITION idx_orders_2026_10_customer;

The moment every partition's index is attached, the parent index is marked valid automatically. Miss even one and the parent index stays invalid — and an invalid index is not used for queries while still paying the full update cost. That is the worst of both worlds.

So this work must always be followed by a verification query.

SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

According to the catalog documentation, indisvalid being false means the index is possibly incomplete and cannot safely be used for queries, but must still be modified by INSERT and UPDATE operations. No rows returned means you are fine. Rows returned means one of two things: you skipped an ATTACH in step 3, or a CREATE INDEX CONCURRENTLY failed partway.

The latter is common. The documentation notes that if a concurrent build hits a deadlock or a uniqueness violation the command fails but leaves behind an invalid index, and gives the recommended recovery as dropping that index and running CREATE INDEX CONCURRENTLY again, or rebuilding it with REINDEX INDEX CONCURRENTLY. Because an abandoned invalid index quietly consumes write overhead forever, the query above is worth running periodically, not only right after index work.

Converting an Existing Table Into a Partitioned Table

There is no command that converts a populated ordinary table into a partitioned one in place. You have to create a new partitioned table and move the existing one into it. Two approaches are used in practice.

Approach A is to attach the existing table wholesale as the first partition. It copies not a single row, so it is overwhelmingly faster. The catch is that every row in the existing table must fall within one partition bound. If you are happy to lump all historical data into a single "before 2026-09-01" partition and split monthly from there, that condition is satisfied.

-- 1) Create the new partitioned parent. The partition key must be in the PK,
--    so the old PK often cannot be copied as-is. That is why INCLUDING ALL
--    is avoided here.
CREATE TABLE orders_new (
    LIKE orders INCLUDING DEFAULTS INCLUDING STORAGE
) PARTITION BY RANGE (order_date);

-- 2) Add a bound-matching CHECK to the existing table and validate separately
ALTER TABLE orders
    ADD CONSTRAINT orders_bound CHECK (order_date < DATE '2026-09-01') NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_bound;

-- 3) Attach the existing table as the first partition. The CHECK skips the scan.
ALTER TABLE orders_new ATTACH PARTITION orders
    FOR VALUES FROM (MINVALUE) TO ('2026-09-01');

-- 4) Create the partition that will receive incoming data
CREATE TABLE orders_2026_09 PARTITION OF orders_new
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 5) Rename. Only this step needs a brief ACCESS EXCLUSIVE lock.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders RENAME TO orders_legacy;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

MINVALUE is used in step 3 for the reason the documentation gives: MINVALUE and MAXVALUE are not real stored values but representations of "no lower bound" and "no upper bound". Also, in a RANGE partition the FROM bound is inclusive and the TO bound is exclusive. TO ('2026-09-01') therefore does not include midnight on September 1, which makes it exactly the same boundary as the CHECK in step 2. Get that inequality wrong and either the ATTACH fails or a day's worth of rows on the boundary goes missing.

The lock_timeout line in step 5 is the single most important line in this recipe. The documentation gives its default as zero, which disables the timeout — meaning an unbounded wait. The rename itself is instantaneous, but if it cannot acquire ACCESS EXCLUSIVE it waits forever, and that wait blocks every query behind it. With a short lock_timeout, failing to get the lock kills your DDL rather than your service. Failing and retrying a few minutes later is better than holding the site down until you succeed.

Approach B is to batch-copy into a new partitioned table and then switch over. Use it when existing rows must scatter across several partitions, or when you are changing the schema at the same time. You move historical data period by period with INSERT ... SELECT, catch up the changes arriving meanwhile using a trigger or logical replication, then rename under a short lock at the end. It takes far longer than approach A and needs catch-up logic, but it puts no constraint on the boundaries.

Whichever you choose, do not run it in production without a rehearsal first. Approach A in particular tends to sail through step 3 and then stall at step 5 because it cannot get the lock. Remember that at that moment orders is already a partition of orders_new. Your rollback plan has to be written down as far as "undo step 3 with DETACH".

Failure Cases and the Order to Diagnose Them

Symptom: we only added one partition and the whole service stopped

Lock waits. Check in this order.

-- 1) How many sessions are waiting on a lock right now
SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- 2) What are they waiting for, and who is blocking them
SELECT pid,
       now() - query_start AS waiting_for,
       left(query, 60)     AS query,
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Example output

  pid  | waiting_for |                query                 | blocked_by
-------+-------------+--------------------------------------+------------
 24815 | 00:04:12    | CREATE TABLE orders_2026_09 PARTITI  | {24102}
 24903 | 00:04:07    | SELECT id, amount FROM orders WHERE  | {24815}
 24911 | 00:04:06    | SELECT count(*) FROM orders WHERE o  | {24815}

Reading it correctly is what matters. pg_blocking_pids returns, as the documentation puts it, an array of the process IDs of the sessions blocking the specified server process from acquiring a lock. In the output above the two SELECT statements are blocked by the DDL (24815), and the DDL is in turn blocked by 24102. So the real cause is not the DDL — it is the old transaction that 24102 is holding open, with the DDL sitting behind it holding an ACCESS EXCLUSIVE request that drags every subsequent read to a halt alongside it.

That fixes the order of response too. Killing the DDL while 24102 is still alive just reproduces the same thing on the next attempt. Deal with the old transaction in front first, and next time set a lock_timeout and replace CREATE TABLE ... PARTITION OF with the CREATE → CHECK → VALIDATE → ATTACH sequence.

Symptom: ATTACH PARTITION has been running for 30 minutes

Almost always a scan. Two things to check.

1. Does the table being attached have a valid CHECK constraint matching the partition bound? Run \d+ orders_2026_09 and confirm the constraint is there and no longer marked NOT VALID. A constraint left in the NOT VALID state is not used by ATTACH as grounds to skip the scan. Forgetting VALIDATE CONSTRAINT is the most common cause.
2. Is there a DEFAULT partition? If so, what is being scanned right now may not be the table you are attaching but the DEFAULT partition. You need the exclusion CHECK from the previous section.

Symptom: I added one index and all writes stopped

You ran CREATE INDEX without CONCURRENTLY against a partitioned parent. Since CONCURRENTLY cannot be used on partitioned tables, this happens by accident. Cancel it and redo the work with the three-step ON ONLY recipe above — but after cancelling, always check with the pg_index query that no invalid index was left behind.

Symptom: partitions are created fine but the query plan looks wrong

Most likely the parent has no statistics. The documentation says it plainly: partitioned tables do not directly store tuples and consequently are not processed by autovacuum, which means autovacuum does not run ANALYZE on partitioned tables, and this can cause suboptimal plans for queries that reference partitioned table statistics. Individual partitions are processed by autovacuum just like ordinary tables; the parent is the one nobody looks after.

The fix is in the documentation too: manually run ANALYZE on partitioned tables when they are first populated, and again whenever the distribution of data in their partitions changes significantly.

-- Run it explicitly against the parent
ANALYZE orders;

If your system adds a partition every month, appending this one line to the end of the partition-creation script is the most reliable way to make it happen.

Symptom: I dropped a partition but disk usage did not fall

You ran DETACH and never ran DROP TABLE. Find the orphaned tables like this.

-- Partitions currently attached to the parent
SELECT c.relname
FROM pg_inherits i
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_class c ON c.oid = i.inhrelid
WHERE p.relname = 'orders';

-- Tables whose name starts with orders_ but which do not appear above
-- are the ones detached long ago and forgotten

When Not to Go This Far

By this point it should be clear that operating partitions takes real effort. It does, and that means there are plenty of cases where not doing it is the right call.

If the table is small, partitioning is a pure loss. Planning gets more expensive because the planner has to reason about every partition, you take on the operational burden of creating and dropping partitions every month, and you gain nothing. The documentation is blunt about this: never just assume that more partitions are better than fewer partitions, nor vice versa.

If your queries do not put the partition key in the WHERE clause, partitioning is close to a net negative. Without pruning, scanning one large table simply becomes scanning N smaller ones, plus the added planning cost. Choosing a partition key is really the question "what do our queries filter on", and that is the subject of the strategy post.

If a table has no retention policy — that is, if you never delete data — you will never use DROP TABLE, which is partitioning's biggest single win. In that case partitioning's value shrinks to pruning alone, and pruning is usually obtainable with an index.

Finally, to be honest about it: look at your indexes and your queries before you consider partitioning. Partitioning changes your schema in a direction that is hard to reverse. A bad index can simply be dropped; reversing a partitioned table means running this post's migration once more in the opposite direction.

References

• PostgreSQL 18 — ALTER TABLE — ATTACH/DETACH lock levels, the two-phase behaviour of DETACH CONCURRENTLY, SHARE UPDATE EXCLUSIVE for VALIDATE CONSTRAINT. Checked 2026-08-16
• PostgreSQL 18 — CREATE TABLE — ACCESS EXCLUSIVE for PARTITION OF and DROP TABLE, inclusive/exclusive RANGE bounds, MINVALUE and MAXVALUE, DEFAULT partition. Checked 2026-08-16
• PostgreSQL 18 — Table Partitioning — the ON ONLY plus ALTER INDEX ATTACH PARTITION workaround, the DEFAULT partition scan warning, guidance on partition counts. Checked 2026-08-16
• PostgreSQL 18 — CREATE INDEX — locking behaviour of CONCURRENTLY, lack of support on partitioned tables, invalid indexes after failure and how to recover. Checked 2026-08-16
• PostgreSQL 18 — Routine Vacuuming — partitioned tables are not processed by autovacuum, and manual ANALYZE is recommended. Checked 2026-08-16
• PostgreSQL 18 — Client Connection Defaults — definition and default of lock_timeout. Checked 2026-08-16
• PostgreSQL 18 — pg_index — meaning of indisvalid and indisready. Checked 2026-08-16
• PostgreSQL 18 — System Information Functions — behaviour of pg_blocking_pids. Checked 2026-08-16
• pg_partman documentation — create_parent signature and changes since 5.0. Checked 2026-08-16

One correction to section 6 above: the pg_partman example uses p_type => 'native', but as of the pg_partman 5.0 documentation the values p_type accepts are range and list, with range as the default. In 5.x, trigger-based partitioning was removed and all partitioning is done with built-in declarative partitioning. The documented default for p_premake is 4. Check the version of the extension you have installed first, and write the arguments to match that version's documentation.

9. Quiz

Quiz