PostgreSQL Solusi Terlalu Banyak Bloat dalam Tabel atau Indeks

Bloat dalam tabel atau indeks terjadi ketika terdapat ruang yang tidak efisien di database akibat penghapusan, pembaruan, atau penyisipan data yang berulang. Masalah ini dapat memperlambat kinerja query karena ukuran tabel atau indeks yang membengkak tanpa alasan yang jelas. Tutorial ini akan membahas cara mengidentifikasi dan mengatasi bloat dengan contoh pada PostgreSQL.

Langkah-Langkah Mengatasi Bloat

Identifikasi Bloat Untuk memeriksa apakah tabel atau indeks mengalami bloat, gunakan skrip berikut pada PostgreSQL:

SELECT 

    schemaname, 

    tablename, 

    reltuples::BIGINT AS live_tuples, 

    relpages::BIGINT AS pages, 

    pg_size_pretty(pg_table_size(oid)) AS table_size 

FROM pg_class c

JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema');

Skrip ini akan menampilkan informasi ukuran tabel. Rasio antara jumlah halaman (relpages) dengan jumlah tuple (reltuples) dapat memberi indikasi bloat.

Analisis Tingkat Fragmentasi Indeks Gunakan pg_stat_user_indexes untuk melihat indeks yang mungkin bloat:

SELECT 

    indexrelname AS index_name, 

    relname AS table_name, 

    idx_scan AS index_scans, 

    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE idx_scan < 50; -- Misalnya, indeks jarang dipakai

Jika ukuran indeks terlalu besar dan jarang digunakan, Anda mungkin perlu mempertimbangkan untuk menghapus atau memperbarui indeks tersebut.

Melakukan VACUUM PostgreSQL memiliki fungsi VACUUM untuk membersihkan ruang kosong. Ada dua jenis VACUUM:

• VACUUM: Membersihkan ruang kosong tanpa mengunci tabel.
• VACUUM FULL: Membersihkan ruang kosong secara menyeluruh, tetapi membutuhkan penguncian tabel.

Contoh:

VACUUM ANALYZE; -- Membersihkan ruang kosong dan memperbarui statistik

VACUUM FULL my_table; -- Mengurangi ukuran tabel secara langsung

Indeks Rebuild Untuk mengatasi bloat pada indeks, Anda bisa menggunakan REINDEX.

REINDEX TABLE my_table; -- Memperbarui indeks tabel

REINDEX INDEX my_index; -- Memperbarui indeks tertentu

Penghapusan Indeks yang Tidak Digunakan Jika ada indeks yang tidak lagi digunakan, pertimbangkan untuk menghapusnya:

DROP INDEX IF EXISTS my_index;

Pemetaan Autovacuum Pastikan fitur autovacuum diaktifkan agar PostgreSQL dapat menangani bloat secara otomatis:

SHOW autovacuum;

Jika autovacuum dinonaktifkan, aktifkan melalui konfigurasi:

ALTER SYSTEM SET autovacuum = 'on';

Contoh Studi Kasus

Misalkan Anda memiliki tabel orders yang sering mengalami pembaruan:

Identifikasi ukuran tabel:

SELECT pg_size_pretty(pg_table_size('orders'));

Lakukan VACUUM untuk membersihkan ruang kosong:

VACUUM FULL orders;

Periksa indeks pada tabel orders:

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))

FROM pg_stat_user_indexes

WHERE relname = 'orders';

Rebuild indeks jika ukurannya terlalu besar:

REINDEX TABLE orders;

Mengelola bloat di tabel atau indeks adalah bagian penting dari optimasi database. Dengan langkah-langkah di atas,semoga dapat membuat performa database tetap optimal. Pastikan juga untuk melakukan pemeliharaan rutin seperti VACUUM dan ANALYZE secara otomatis menggunakan autovacuum. Semoga bermanfaat dan mohon maaf jika ada informasi yang tidak tepat atau salah.