SQL Academy · Lesson

Detaching and Attaching Partitions

Use ATTACH PARTITION and DETACH PARTITION to roll partitions in and out without blocking reads.

Detaching and Attaching Partitions is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Detach?

You can swap an old partition out of the live table without dropping data. Common uses:

Archive to a separate database
Move to cheaper storage
Backup-then-drop pattern

DETACH PARTITION

Removes the partition from the parent but keeps the data:

ALTER TABLE events DETACH PARTITION events_2022;
-- events_2022 is now a standalone table.
-- Queries on events no longer see those rows.

DETACH CONCURRENTLY (PG 14+)

Avoids the ACCESS EXCLUSIVE lock:

ALTER TABLE events DETACH PARTITION events_2022 CONCURRENTLY;
-- Two-step: detach pending, then finalize. App writes continue.

Archiving Detached Partitions

After detaching:

-- Backup with pg_dump:
pg_dump --table=events_2022 mydb > events_2022.sql

-- Move to a cold-storage Postgres or S3:
psql cold_db < events_2022.sql

-- Then drop from primary:
DROP TABLE events_2022;

ATTACH PARTITION

Add an existing table as a new partition:

CREATE TABLE events_2025 (LIKE events INCLUDING ALL);
-- Load data with COPY or INSERT ... SELECT

ALTER TABLE events ATTACH PARTITION events_2025
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Validation During ATTACH

ATTACH scans the new partition to confirm every row fits the FOR VALUES bounds. Avoid the scan with a matching CHECK constraint pre-attach:

ALTER TABLE events_2025
  ADD CONSTRAINT events_2025_ts_check
  CHECK (ts >= '2025-01-01' AND ts < '2026-01-01');

ALTER TABLE events ATTACH PARTITION events_2025
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- No scan needed; PG trusts the CHECK.

Rolling Time Windows

Roll forward weekly/monthly: pre-create the next partition, drop the oldest:

BEGIN;
  CREATE TABLE events_2024_q4 PARTITION OF events FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
  DROP TABLE events_2023_q4;
COMMIT;

Switching Storage Tiers

Detach a partition, ALTER TABLE … SET TABLESPACE to slower storage, ATTACH back:

ALTER TABLE events DETACH PARTITION events_2022;
ALTER TABLE events_2022 SET TABLESPACE archive_disk;
ALTER TABLE events ATTACH PARTITION events_2022 FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Indexes on Attached Partitions

For a partition to be attached, its indexes must match the parent's. PG can auto-create missing indexes during ATTACH on PG 14+.

Atomic Partition Swap

Wrap DETACH + ATTACH in a transaction to swap partition contents atomically. Common pattern for "build the new partition offline, then replace":

BEGIN;
  ALTER TABLE events DETACH PARTITION events_2024_q1;
  ALTER TABLE events ATTACH PARTITION events_2024_q1_new
    FOR VALUES FROM ...;
COMMIT;

Performance

DETACH and ATTACH are catalog operations — instant when constraints are pre-set. The CHECK constraint trick is essential at scale.

Recap

Detach/attach is partitioning's secret weapon for online maintenance.

DETACH PARTITION CONCURRENTLY for online removal
Pre-attach CHECK constraint skips validation scan
Swap, archive, tier-move
Atomic with BEGIN/COMMIT

Quick Check

How do you avoid the full-table scan when running ATTACH PARTITION?

Frequently asked questions

Is the “Detaching and Attaching Partitions” lesson free?

Yes — the full text of “Detaching and Attaching Partitions” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Detaching and Attaching Partitions”?

Use ATTACH PARTITION and DETACH PARTITION to roll partitions in and out without blocking reads. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Detaching and Attaching Partitions” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy