Cara Menghindari Fragmentasi Tabel Pada PosgtreSQL Untuk Kinerja Query

Fragmentasi pada tabel PostgreSQL terjadi ketika tabel sering diperbarui (update) atau dihapus (delete). PostgreSQL menggunakan MVCC (Multiversion Concurrency Control), yang membuat salinan baru dari baris yang diubah dan menandai baris lama sebagai "mati". Baris mati ini menyebabkan tabel menjadi terfragmentasi, mengurangi kinerja query dan penggunaan penyimpanan.

Cara mengidentifikasi dan menangani fragmentasi pada tabel PostgreSQL.

1. Memahami Fragmentasi pada PostgreSQL

Fragmentasi terjadi karena:

• UPDATE: PostgreSQL tidak mengganti baris secara langsung tetapi membuat salinan baru.
• DELETE: Data dihapus hanya secara logis; ruangnya tidak langsung dikembalikan.
• VACUUM: Proses otomatis untuk membersihkan data mati mungkin tidak memadai pada tabel besar atau sering diperbarui.

Tabel yang terfragmentasi akan:

• Membutuhkan lebih banyak ruang penyimpanan.
• Memperlambat kinerja kueri.

2. Mengidentifikasi Fragmentasi

Gunakan kueri berikut untuk memeriksa jumlah ruang yang digunakan oleh baris mati:

SELECT relname AS table_name,

       n_live_tup AS live_rows,

       n_dead_tup AS dead_rows,

       pg_size_pretty(pg_total_relation_size(relid)) AS total_size

FROM pg_stat_all_tables

WHERE schemaname = 'public'

ORDER BY n_dead_tup DESC;

live_rows: Jumlah baris yang aktif.

dead_rows: Jumlah baris mati (fragmentasi).

total_size: Total ruang yang digunakan oleh tabel.

3. Mengatasi Fragmentasi

a. VACUUM: Membersihkan Baris Mati

VACUUM ANALYZE Membersihkan baris mati dan memperbarui statistik tabel:

VACUUM ANALYZE table_name;

VACUUM FULL Menghapus baris mati dan mengecilkan ukuran tabel. Proses ini memerlukan downtime karena mengunci tabel.

VACUUM FULL table_name;

Catatan: Gunakan VACUUM FULL hanya jika fragmentasi sangat parah.

b. AUTOVACUUM: Pengaturan Otomatis

Pastikan fitur autovacuum diaktifkan untuk menjaga fragmentasi tetap terkendali:

Periksa status autovacuum:

SHOW autovacuum;

Jika off, aktifkan dengan perintah:

ALTER SYSTEM SET autovacuum = 'on';

Konfigurasi parameter autovacuum di postgresql.conf:

autovacuum_naptime = 1min         # Interval antar autovacuum

autovacuum_vacuum_threshold = 50 # Minimum jumlah baris mati

autovacuum_vacuum_scale_factor = 0.2 # Persentase baris mati

Reload PostgreSQL:

sudo systemctl reload postgresql

4. Menggunakan REINDEX untuk Indeks yang Terfragmentasi

Indeks juga dapat terfragmentasi jika tabel sering diperbarui. Jalankan perintah berikut untuk menghapus fragmentasi indeks:

REINDEX TABLE table_name;

5. Mengurangi Fragmentasi di Masa Depan

a. Partisi Tabel

Pecah tabel besar menjadi beberapa partisi untuk mengurangi fragmentasi:

CREATE TABLE orders_partitioned (

    order_id SERIAL,

    order_date DATE NOT NULL,

    customer_id INT NOT NULL

) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024 PARTITION OF orders_partitioned

FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

b. Strategi Desain

Gunakan INSERT daripada UPDATE jika memungkinkan.

Hapus data secara batch untuk mengurangi beban autovacuum:

DELETE FROM table_name WHERE created_at < '2023-01-01';

c. Pemantauan Rutin

Buat pemantauan otomatis untuk fragmentasi:

CREATE OR REPLACE FUNCTION check_fragmentation()

RETURNS void AS $$

DECLARE

    r RECORD;

BEGIN

    FOR r IN

        SELECT relname, n_dead_tup

        FROM pg_stat_all_tables

        WHERE schemaname = 'public' AND n_dead_tup > 1000

    LOOP

        RAISE NOTICE 'Table % has % dead rows', r.relname, r.n_dead_tup;

    END LOOP;

END;

$$ LANGUAGE plpgsql;

SELECT check_fragmentation();

6. Studi Kasus: Tabel Transaksi

Masalah

Tabel transactions memiliki 1 juta baris aktif dan 500 ribu baris mati. Kinerja kueri mulai melambat.

Langkah Penyelesaian

Identifikasi fragmentasi:

SELECT relname, n_dead_tup

FROM pg_stat_all_tables

WHERE relname = 'transactions';

Jalankan VACUUM FULL untuk membersihkan:

VACUUM FULL transactions;

Reindex tabel:

REINDEX TABLE transactions;

Aktifkan autovacuum jika belum diaktifkan:

ALTER SYSTEM SET autovacuum = 'on';

Hasil

Fragmentasi berkurang, ukuran tabel menyusut, dan kinerja kueri meningkat.

Dengan langkah-langkah ini, Anda dapat mengidentifikasi dan mengatasi fragmentasi pada tabel PostgreSQL yang sering diperbarui, menjaga kinerja database tetap optimal. Semoga bermanfaat mohon maaf jika ada kesalahan informasi yang ditampilkan.