SQL Academy · Pelajaran

Pemeliharaan Indeks dan Bloat

Diagnosis bloat indeks, bangun ulang dengan REINDEX CONCURRENTLY, dan hapus indeks yang tidak digunakan dengan aman

Pemeliharaan Indeks dan Bloat adalah pelajaran SQL Academy gratis di CoddyKit. Ini adalah pelajaran 4 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 SQL Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus SQL Academy mencakup 4 pelajaran total.

Mengapa Indeks Membengkak

PostgreSQL menggunakan MVCC: UPDATE menulis versi baris baru dan membiarkan versi lama tetap terlihat oleh transaksi yang sedang berjalan. Entri indeks untuk kedua versi tersebut ada. Seiring waktu:

Tabel yang sering diperbarui mengakumulasi entri indeks yang sudah tidak aktif
Indeks menjadi lebih besar dari yang diperlukan
Pencarian melambat karena B-tree menjadi lebih dalam

Mendiagnosis Pembengkakan

Kueri untuk memeriksa pembengkakan tidak sederhana. Alat yang umum digunakan:

Ekstensi pgstattuple
Laporan pg_repack
Kueri pembengkakan dari check_postgres / alat pemantauan

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

Membangun ulang indeks. Bentuk klasiknya mengambil kunci ACCESS EXCLUSIVE — buruk untuk lingkungan produksi:

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12+)

Varian tanpa pemblokiran — pembacaan dan penulisan terus berlangsung selama pembangunan ulang:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Indeks yang Tidak Digunakan

Indeks yang tidak digunakan memperlambat setiap operasi tulis tetapi tidak mempercepat satu pun operasi baca. Temukan indeks tersebut:

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Hapus Indeks yang Tidak Digunakan

Hapus indeks tersebut — tetapi verifikasi terlebih dahulu di semua lingkungan dan rentang waktu. Indeks yang digunakan untuk laporan sesekali akan tampak tidak digunakan hampir sepanjang waktu.

DROP INDEX CONCURRENTLY old_unused_idx;

Indeks Duplikat

Terkadang indeks yang sama dibuat oleh kendala dan CREATE INDEX manual. Periksa pg_indexes untuk menemukan duplikat, lalu hapus indeks yang berlebihan:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Penguatan Operasi Tulis oleh Indeks

Setiap INSERT/UPDATE/DELETE memperbarui semua indeks yang relevan. Tiga indeks pada tabel yang sering ditulis = biaya penulisan 3×. Tambahkan hanya indeks yang memberikan manfaat.

Daftar Tertunda GIN

Indeks GIN mengelompokkan pembaruan dalam daftar tertunda. Kosongkan secara manual atau andalkan autovacuum:

SELECT gin_clean_pending_list('events_data_gin');

VACUUM Membersihkan Entri Indeks

VACUUM (dibahas dalam pelajaran MVCC) menghapus entri indeks yang sudah tidak aktif dari halaman heap. Tanpa autovacuum, indeks akan terus membesar.

Memantau Ukuran Indeks

Lacak ukuran indeks dari waktu ke waktu:

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: Menulis Ulang Tabel Secara Daring

Untuk pembengkakan parah, pg_repack menulis ulang tabel dan indeks secara daring — tanpa mengunci seluruh tabel. Instal sebagai paket OS dan ekstensi PostgreSQL.

Ringkasan

Indeks memerlukan perawatan.

Pembengkakan akibat MVCC adalah hal yang normal — kelola dengan VACUUM dan REINDEX CONCURRENTLY
Hapus indeks yang tidak digunakan
Hindari duplikat
Setiap indeks baru memperlambat penulisan — tentukan dengan cermat

Pemeriksaan Singkat

Perintah PostgreSQL mana yang membangun ulang indeks TANPA memblokir penulisan?

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Pemeliharaan Indeks dan Bloat” gratis?

Ya — teks lengkap “Pemeliharaan Indeks dan Bloat” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus SQL Academy, upgrade ke CoddyKit PRO. Kursus SQL Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Pemeliharaan Indeks dan Bloat”?

Diagnosis bloat indeks, bangun ulang dengan REINDEX CONCURRENTLY, dan hapus indeks yang tidak digunakan dengan aman Kamu berlatih SQL Academy 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 SQL Academy?

Tidak diperlukan pengalaman sebelumnya. SQL Academy 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 4 dari 4.

Berapa lama pelajaran “Pemeliharaan Indeks dan Bloat” 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 SQL Academy ini?

Ya. Setiap pelajaran SQL Academy 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 SQL Academy