Melepas dan Memasang Partisi

Mengapa DETACH?

DETACH PARTITION

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+)

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

Mengarsipkan Partisi yang Dilepas

-- 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

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');

Validasi Selama 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.

Jendela Waktu Bergulir

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;

Mengganti Tingkat Penyimpanan

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');

Indeks pada Partisi yang Di-attach

Pertukaran Partisi Atomik

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

Kinerja

Rangkuman

Pemeriksaan Singkat