cara menggunakan alter table…drop partition untuk menghapus partisi dan data - PolarDB

Menghapus partisi tertentu beserta seluruh datanya dari tabel partisi. Tindakan ini bersifat permanen dan tidak dapat dikembalikan. Pastikan partisi yang akan dihapus tidak lagi diperlukan atau datanya telah di-backup sebelum menjalankan perintah ini.

Syntax

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

Parameter

Parameter	Wajib	Deskripsi	Contoh
`table_name`	Ya	Nama tabel partisi target dimana partisi akan dihapus.	`sales_records`
`partition_name`	Ya	Nama partisi yang akan dihapus.	`p_2023_q1`

Catatan

Hanya pemilik tabel atau user dengan hak akses yang sesuai yang dapat menjalankan perintah ini.
Anda tidak dapat menghapus partisi terakhir dari tabel terpartisi, karena tabel terpartisi harus memiliki setidaknya satu partisi.
Pernyataan DROP PARTITION memperoleh kunci eksklusif tingkat tabel AccessExclusiveLock. Kunci ini memblokir semua operasi Data Manipulation Language (DML) dan sebagian besar operasi Data Definition Language (DDL) pada tabel. Jalankan pernyataan ini di luar jam sibuk dan alokasikan waktu yang cukup agar proses dapat selesai tanpa mengganggu operasi penting lainnya pada tabel.

Contoh

Persiapan

-- Buat tabel yang dipartisi berdasarkan rentang tanggal penjualan.
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION P_2023_Q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION P_2023_Q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- Buat indeks unik global.
CREATE UNIQUE INDEX idx_sale_id_global ON sales_records(sale_id) GLOBAL;

-- Masukkan data.
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD')); -- q1
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD')); -- q2

Lakukan pemeriksaan awal

Lakukan langkah-langkah berikut sebelum menghapus partisi untuk mencegah kehilangan data atau gangguan layanan.

Verifikasi keberadaan partisi.

-- Periksa informasi partisi.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- Konfirmasikan bahwa p_2023_q1 ada dalam daftar.

(Opsional) Backup data.
Untuk mencegah penghapusan yang tidak disengaja, backup tabel atau database secara keseluruhan sebelum menghapus partisi. Lihat Metode backup.
Evaluasi dampak operasional.
Periksa transaksi yang berjalan lama atau workload penting yang sedang mengakses tabel. Operasi penghapusan ini memperoleh kunci eksklusif level tabel, yang memblokir semua permintaan baca dan tulis.
```
-- Periksa kunci aktif.
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;
```

Jalankan perintah

Setelah menyelesaikan pemeriksaan awal, jalankan perintah ALTER TABLE ... DROP PARTITION di luar jam sibuk.

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

Verifikasi hasil

Setelah operasi selesai, verifikasi bahwa partisi dan datanya telah dihapus sesuai harapan.

Periksa bahwa partisi telah dihapus.

Kueri view metadata untuk memastikan partisi yang dihapus tidak lagi terdapat dalam daftar partisi.

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' and partition_name = 'P_2023_Q1';
-- Hasil yang diharapkan: Perintah mengembalikan set hasil kosong.

Konfirmasikan bahwa data telah dihapus.
Kueri data dari partisi yang dihapus untuk mengonfirmasi bahwa data tersebut telah dihapus.
```
SELECT * 
FROM sales_records 
WHERE sale_date < '2023-04-01';
-- Hasil yang diharapkan: Perintah mengembalikan set hasil kosong.
```
Perbarui statistik tabel.
Setelah menghapus partisi, segera perbarui statistik tabel agar query optimizer dapat menghasilkan rencana eksekusi yang akurat.
```
ANALYZE SALES_RECORDS;
```

FAQ

Q1: Mengapa saya menerima error partition "..." of relation "..." does not exist setelah menjalankan perintah?

Nama partisi tidak ada atau terjadi ketidakcocokan huruf besar/kecil. Nama partisi bersifat case-sensitive. Kueri view USER_TAB_PARTITIONS untuk mengonfirmasi nama partisi yang tepat, atau gunakan tanda kutip ganda ("") untuk melakukan pencocokan.

Q2: Mengapa saya menerima error permission denied for table ... atau must be owner of table ...?

Pengguna saat ini tidak memiliki izin yang cukup. Harap beralih ke pemilik tabel atau ke pengguna yang memiliki hak akses yang diperlukan untuk menjalankan perintah tersebut.

Q3: Mengapa saya menerima error cannot drop partition due to dependent objects?

Objek database lain, seperti foreign key constraint atau view, bergantung pada partisi ini. Perilaku RESTRICT default mencegah penghapusan agar tidak dilakukan. Anda harus terlebih dahulu mengidentifikasi dan menghapus objek-objek dependen ini.

Q4: Mengapa operasi berjalan lama atau tampak macet?

Pernyataan DROP PARTITION memperoleh AccessExclusiveLock. Jika kunci ini diblokir oleh transaksi aktif lain yang berjalan lama, operasi tersebut harus menunggu hingga kunci dilepaskan, yang dapat menyebabkan penundaan atau bahkan timeout. Untuk mengidentifikasi sesi yang memblokir, periksa kunci aktif di sistem dengan memeriksa kunci aktif. Kemudian, gunakan blocking_pid dari kueri tersebut untuk menemukan detail sesi dengan pernyataan berikut:

-- Kueri informasi pengguna dari thread yang memblokir berdasarkan PID.
SELECT 
    pid,
    usename AS username,           -- Nama pengguna database
    application_name,              -- Nama aplikasi (seperti JDBC, psql)
    client_addr,                   -- Alamat IP klien
    client_hostname,               -- Hostname klien
    client_port,                   -- Port klien
    backend_start,                 -- Waktu mulai koneksi
    xact_start,                    -- Waktu mulai transaksi
    query_start,                   -- Waktu mulai kueri saat ini
    state_change,                  -- Waktu terakhir status berubah
    state,                         -- Status (aktif, idle, idle dalam transaksi)
    wait_event_type,               -- Jenis event tunggu (seperti Lock, IO)
    wait_event,                    -- Event tunggu spesifik
    query                          -- Kueri SQL yang sedang berjalan atau yang terakhir dijalankan
FROM 
    pg_stat_activity
WHERE 
    pid in 'blocking_pid';

SQL Terkait

ALTER TABLE ADD PARTITION: Menambahkan partisi baru ke tabel terpartisi.
ALTER TABLE TRUNCATE PARTITION: Menghapus semua data dari partisi tanpa menghapus partisi itu sendiri.
ALTER TABLE SPLIT PARTITION: Membagi satu partisi menjadi dua partisi.