Advanced PostgreSQL: Indexing, Partitioning, Replication · Pelajaran

Melampirkan dan Melepaskan Partisi

Pelajari cara memindahkan data secara efisien ke dalam dan ke luar tabel berpartisi menggunakan ATTACH PARTITION dan DETACH PARTITION.

Melampirkan dan Melepaskan Partisi adalah pelajaran Advanced PostgreSQL: Indexing, Partitioning, Replication gratis di CoddyKit. Ini adalah pelajaran 2 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar Advanced PostgreSQL: Indexing, Partitioning, Replication, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus Advanced PostgreSQL: Indexing, Partitioning, Replication mencakup 4 pelajaran total.

Bagian dari pelajaran ini belum diterjemahkan dan ditampilkan dalam bahasa Inggris.

Move Data Efficiently

Partitioned tables in PostgreSQL are powerful for managing large datasets. But how do you handle data that needs to be added or removed from these partitions?

This lesson introduces ATTACH PARTITION and DETACH PARTITION, two critical commands that allow you to efficiently move data in and out of your partitioned tables with minimal disruption.

The DETACH PARTITION Command

DETACH PARTITION is used to separate an existing partition from its parent partitioned table. When a partition is detached, it becomes a standalone, regular table.

Syntax: ALTER TABLE parent_table DETACH PARTITION child_table;
Why use it? For archiving old data, performing maintenance on a specific partition, or moving data to a different storage tier.

The main partitioned table remains available for queries during this operation.

Detach for Archiving

Imagine you have a sales table partitioned by month. After a year, you might want to archive the oldest months to reduce the active dataset size.

Instead of deleting data, which can be slow and resource-intensive, you can detach the old partition. This converts it into a separate table, which you can then move, compress, or delete without affecting the main sales table.

Detach in Action

Let's see DETACH PARTITION in action. We'll create a partitioned table, add a partition, insert some data, and then detach it.

After running, try SELECT * FROM sales; and SELECT * FROM sales_2023_q1; in your console to see the effect.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023_q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

INSERT INTO sales (sale_date, amount) VALUES ('2023-02-15', 100.00);

ALTER TABLE sales DETACH PARTITION sales_2023_q1;

The ATTACH PARTITION Command

ATTACH PARTITION is the opposite: it adds an existing, independent table as a new partition to a partitioned table.

Syntax: ALTER TABLE parent_table ATTACH PARTITION child_table FOR VALUES FROM (...) TO (...);
Why use it? For bulk loading new data, adding new time ranges, or integrating data from external sources.

The attached table must match the parent's schema (columns, types, and NOT NULL constraints).

Attach for New Data

A common scenario for ATTACH PARTITION is to load large amounts of new data.

Instead of inserting millions of rows directly into a partitioned table (which can be slow due to locking and index updates), you can:

Create a new, empty table with the same schema.
Load all your new data into this temporary table.
Once data is loaded and validated, attach this table as a new partition to your main partitioned table.

This minimizes downtime on the main table.

Attach in Action

Let's demonstrate ATTACH PARTITION. We'll create a partitioned table, then create a separate table, load data into it, and finally attach it as a new partition.

After running, try SELECT * FROM sales_main; in your console to confirm the data is now part of the partitioned table.

CREATE TABLE sales_main (
    sale_id SERIAL,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_q1_temp (
    sale_id SERIAL,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales_2024_q1_temp (sale_date, amount) VALUES ('2024-03-10', 250.50);

ALTER TABLE sales_main ATTACH PARTITION sales_2024_q1_temp
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Benefits of ATTACH/DETACH

These commands offer significant advantages for managing large partitioned tables:

Zero-Downtime Operations: Detaching/attaching usually involves metadata changes, not full table rewrites, keeping the main table online.
Faster Data Loading: Pre-loading data into a separate table and then attaching is much faster than direct inserts for large volumes.
Efficient Archiving/Deletion: Quickly remove old data from active queries by detaching, then process the standalone table separately.
Simplified Maintenance: You can rebuild indexes or run VACUUM on a detached partition without impacting the performance of the main table.

Important Considerations

While powerful, there are a few things to keep in mind:

Schema Match: The child table's schema must exactly match the parent's, including column order, types, and NOT NULL constraints.
Indexes: Indexes on the parent table are NOT automatically applied to an attached child. You must create them manually on the new partition.
Foreign Keys: Foreign key constraints referencing the partitioned table can complicate detachment.
Data Validation: When attaching, PostgreSQL will verify that all data in the child table falls within the specified partition range. This scan can be time-consuming for very large tables, but can be skipped with ATTACH PARTITION ... CONCURRENTLY (PostgreSQL 13+) if you've already ensured data integrity.

Quick Check: Partition Management

You've learned about the flexibility and efficiency offered by ATTACH PARTITION and DETACH PARTITION.

Which of the following is a key advantage of using ALTER TABLE ... ATTACH PARTITION for loading new data into a partitioned table?

Recap: Partition Flexibility

In this lesson, you explored how ATTACH PARTITION and DETACH PARTITION provide powerful mechanisms for managing data in PostgreSQL partitioned tables.

DETACH PARTITION allows you to remove a partition, converting it into a standalone table for archiving or maintenance.
ATTACH PARTITION enables you to add an existing table as a new partition, ideal for efficient bulk data loading.

Mastering these commands is crucial for maintaining high performance and flexibility in large-scale partitioned environments.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Melampirkan dan Melepaskan Partisi” gratis?

Ya — teks lengkap “Melampirkan dan Melepaskan Partisi” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus Advanced PostgreSQL: Indexing, Partitioning, Replication, upgrade ke CoddyKit PRO. Kursus Advanced PostgreSQL: Indexing, Partitioning, Replication mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Melampirkan dan Melepaskan Partisi”?

Pelajari cara memindahkan data secara efisien ke dalam dan ke luar tabel berpartisi menggunakan ATTACH PARTITION dan DETACH PARTITION. Kamu berlatih Advanced PostgreSQL: Indexing, Partitioning, Replication dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai Advanced PostgreSQL: Indexing, Partitioning, Replication?

Tidak diperlukan pengalaman sebelumnya. Advanced PostgreSQL: Indexing, Partitioning, Replication di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 2 dari 4.

Berapa lama pelajaran “Melampirkan dan Melepaskan Partisi” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran Advanced PostgreSQL: Indexing, Partitioning, Replication ini?

Ya. Setiap pelajaran Advanced PostgreSQL: Indexing, Partitioning, Replication menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

← Kembali ke Advanced PostgreSQL: Indexing, Partitioning, Replication