Attach/Detach, Rolling Windows, Retention, Bulk Load, and Archiving Workflows

Learning outcomes

ServiceHub's strongest reason to partition by month is not raw query speed—it is lifecycle operations. A monthly partition can be bulk-loaded before exposure, attached with a short metadata operation, detached from the active hierarchy, backed up, and dropped without generating millions of row-by-row DELETEs and subsequent VACUUM work.

1. Rolling-window preflight

SELECT c.oid::regclass AS relation,       pg_get_expr(c.relpartbound, c.oid) AS boundFROM pg_class AS cWHERE c.oid IN (  SELECT relid FROM pg_partition_tree('app.ch16_work_orders'::regclass))ORDER BY relation::text;SELECT min(opened_on), max(opened_on), count(*)FROM app.ch16_work_orders_default;

Lesson 1 deliberately inserted one September row into DEFAULT. Before attaching a September partition, that row must be moved out; otherwise PostgreSQL correctly rejects the new bound because DEFAULT currently owns all values not covered by explicit partitions.

2. Stage September data outside the hierarchy

CREATE TABLE app.ch16_stage_2026_09(LIKE app.ch16_work_orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS);ALTER TABLE app.ch16_stage_2026_09ADD CONSTRAINT ch16_stage_2026_09_boundCHECK (  opened_on >= DATE '2026-09-01'  AND opened_on < DATE '2026-10-01');INSERT INTO app.ch16_stage_2026_09SELECT 170000 + g,       6000 + (g % 200),       (ARRAY['north','south','east','west'])[(g % 4) + 1],       'queued',       DATE '2026-09-01' + (g % 30),       g % 180,       repeat('s',30)FROM generate_series(1,5000) AS g;ANALYZE app.ch16_stage_2026_09;SELECT count(*), min(opened_on), max(opened_on)FROM app.ch16_stage_2026_09;

The matching CHECK lets PostgreSQL prove the staged table already satisfies the future partition bound, avoiding a full validation scan of the table while it is locked for attach.

3. Drain values from DEFAULT before narrowing its implicit bound

BEGIN;DELETE FROM app.ch16_work_orders_defaultWHERE opened_on >= DATE '2026-09-01'  AND opened_on < DATE '2026-10-01'RETURNING work_order_id, customer_id, region, status,          opened_on, labor_minutes, payload;-- In production, transfer returned rows safely.-- For this deterministic lab, reinsert the one known row:INSERT INTO app.ch16_stage_2026_09VALUES (169001, 5999, 'north', 'queued', DATE '2026-09-12', 0, 'future');COMMIT;

A real migration should use an atomic application-specific move pattern or a controlled maintenance window; do not copy a row and later delete it without a consistency plan.

Because a DEFAULT partition exists, PostgreSQL may scan and lock it when attaching a new explicit partition unless a constraint proves DEFAULT contains no rows for the new range. Add that exclusion constraint first.

ALTER TABLE app.ch16_work_orders_defaultADD CONSTRAINT ch16_default_excludes_2026_09CHECK (  opened_on < DATE '2026-09-01'  OR opened_on >= DATE '2026-10-01');

4. ATTACH the staged table

ALTER TABLE app.ch16_work_ordersATTACH PARTITION app.ch16_stage_2026_09FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');ALTER TABLE app.ch16_stage_2026_09RENAME TO ch16_work_orders_2026_09;SELECT tableoid::regclass AS partition, count(*)FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-09-01'  AND opened_on <  DATE '2026-10-01'GROUP BY tableoid;

ATTACH PARTITION takes a SHARE UPDATE EXCLUSIVE lock on the parent. Validation can still require stronger locks/scans on the candidate partition and, when applicable, the DEFAULT partition. The explicit CHECK constraints are therefore operational tools, not decorative metadata.

5. Index readiness before exposure

If the parent already has valid partitioned indexes, attaching a compatible table requires corresponding child indexes to be created/attached as part of the hierarchy contract. For very large staging tables, build needed leaf indexes before attach—concurrently where appropriate and possible—or budget the attach/index work explicitly.

SELECT indexname, indexdefFROM pg_indexesWHERE schemaname = 'app'  AND tablename = 'ch16_work_orders_2026_09'ORDER BY indexname;

6. Retire June: DETACH instead of deleting row by row

Ordinary detach requires an ACCESS EXCLUSIVE lock on the parent but completes in one transaction. PostgreSQL also provides DETACH PARTITION ... CONCURRENTLY, which uses lower parent-lock levels across two internal transactions—but it cannot run inside a transaction block and is not allowed when the partitioned table has a DEFAULT partition.

ALTER TABLE app.ch16_work_ordersDETACH PARTITION app.ch16_work_orders_2026_06 CONCURRENTLY;-- This hierarchy contains a DEFAULT partition, so use ordinary DETACH-- or redesign the retention hierarchy if concurrent detach is required.

ALTER TABLE app.ch16_work_ordersDETACH PARTITION app.ch16_work_orders_2026_06;SELECT count(*) AS parent_rows_after_detachFROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-06-01'  AND opened_on <  DATE '2026-07-01';SELECT count(*) AS detached_rowsFROM app.ch16_work_orders_2026_06;

The detached table remains queryable as a normal table. Parent queries no longer include it, which is exactly the retention boundary we wanted.

7. Archive before drop

pg_dump   --dbname="service=servicehub-lab-admin"   --format=custom   --table=app.ch16_work_orders_2026_06   --file=ch16_work_orders_2026_06.dumppg_restore --list ch16_work_orders_2026_06.dump | head

Use the Chapter 13 restore-first discipline: archive existence is not recovery proof. Restore the detached table into a disposable database/schema, validate row count and business invariants, record checksum/location/retention metadata, then authorize destruction.

-- Guardrail: run only after the archive restore drill passed.SELECT count(*) AS rows_pending_dropFROM app.ch16_work_orders_2026_06;DROP TABLE app.ch16_work_orders_2026_06;

8. Lock observation during maintenance

SELECT a.pid, a.application_name, a.state,       l.locktype, l.mode, l.granted,       l.relation::regclass AS relationFROM pg_locks AS lJOIN pg_stat_activity AS a USING (pid)WHERE l.relation IN (  'app.ch16_work_orders'::regclass,  'app.ch16_work_orders_default'::regclass)ORDER BY a.pid, l.granted, l.mode;

Authoritative references

Partitioning behavior is planner-, lock-, constraint-, and version-sensitive. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Partition Maintenance
• PostgreSQL 18 — ALTER TABLE ATTACH/DETACH PARTITION
• PostgreSQL 18 — pg_dump