- Berawal dari peringatan kekurangan ruang penyimpanan, dilakukan pembersihan PostgreSQL dan berhasil memperoleh kembali lebih dari 70GB ruang yang tidak optimal atau tidak terpakai tanpa menghapus indeks maupun data
- Awalnya, teknik reklamasi umum seperti indeks yang tidak digunakan, bloat tabel dan indeks,
REINDEX CONCURRENTLY, pg_repack, serta B-Tree deduplication di PostgreSQL 13 diperiksa
- Perbedaan penentunya adalah, tidak seperti Oracle, PostgreSQL juga mengindeks nilai NULL; ketika indeks penuh pada
cancelled_by_user_id yang sebagian besar berisi NULL diubah menjadi partial index, ukurannya turun dari 769MB menjadi kurang dari 5MB
- Indeks kolom tunggal dengan
pg_stats.null_frac tinggi dicari untuk menyeleksi kandidat partial index dengan kondisi WHERE column IS NOT NULL, lalu setelah penghitung statistik direset, penggunaan aktualnya dikonfirmasi melalui pg_stat_all_indexes
- Di Django, nullable
ForeignKey dapat membuat indeks B-Tree default, sehingga perlu menetapkan db_index=False secara eksplisit dan mendefinisikan indeks parsial sendiri dengan models.Index(..., condition=Q(...__isnull=False))
Pembersihan yang dimulai dari peringatan kekurangan ruang penyimpanan
- Setiap beberapa bulan, pemantauan database memunculkan peringatan kekurangan ruang penyimpanan, dan biasanya ditangani dengan menambahkan storage
- Saat itu, karena masa isolasi membuat beban sistem rendah, situasinya cocok untuk mencoba pekerjaan pembersihan yang biasanya lebih membebani
- Hasilnya, tanpa menghapus indeks atau data, berhasil diperoleh kembali lebih dari 70GB ruang yang tidak optimal atau tidak terpakai
- Rekonstruksi indeks dan tabel secara umum saja sudah mengurangi banyak ruang, lalu setelah itu sebuah temuan tak terduga menghapus tambahan sekitar 20GB nilai indeks yang tidak digunakan
Penyebab yang pertama kali dicurigai
-
Indeks yang tidak digunakan
- Indeks yang tidak digunakan dibuat untuk performa query, tetapi bisa memakan ruang serta memperlambat
INSERT dan UPDATE
- Di
pg_stat_all_indexes, indeks dengan idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 dicari untuk memeriksa indeks yang belum pernah dipindai atau di-fetch sejak reset statistik terakhir
- Ada kasus ketika indeks yang tampak tidak digunakan dari luar tetap tidak boleh dihapus
- Ketika optimizer PostgreSQL menggunakan metadata, bukan indeks itu sendiri
- Indeks yang menjamin constraint unique atau primary key pada tabel yang sudah lama tidak diperbarui
- Apakah sebuah indeks benar-benar bisa dihapus harus ditinjau satu per satu dari daftar, dan penghitung statistik dikelola dengan cara direset setelah pemeriksaan
- Dalam kasus ini, karena pekerjaan yang sama sudah dilakukan secara berkala, tidak ada indeks yang tidak digunakan untuk dihapus
-
Bloat indeks dan tabel
- Saat PostgreSQL memperbarui baris, tuple lama ditandai dead dan tuple baru ditambahkan di ruang lain, sehingga bloat dapat terjadi
- Bloat tidak hanya memengaruhi tabel, tetapi juga indeks, dan membuat storage yang dipakai lebih besar daripada ukuran yang sebenarnya dibutuhkan
- Estimasi bloat tidak sederhana, sehingga bloat tabel dan indeks B-Tree diperkirakan memakai query dari wiki PostgreSQL dan
pgsql-bloat-estimation
Cara mengurangi bloat indeks
-
Membuat ulang dan merekonstruksi indeks
- Untuk menghilangkan bloat indeks, indeks harus dibuat ulang
- Jika langsung di-drop lalu dibuat kembali, indeks baru dapat dibuat dalam bentuk yang sudah dioptimalkan
REINDEX PostgreSQL merekonstruksi indeks yang ada di tempat- Cara pembuatan ulang biasa mengunci tabel dan mencegah perubahan, sehingga di lingkungan produksi, pendekatan
REINDEX INDEX CONCURRENTLY index_name lebih sesuai
REINDEX CONCURRENTLY membuat indeks baru dengan sufiks _ccnew, menyinkronkan perubahan, lalu saat selesai mengganti indeks lama dan menghapus indeks lama tersebut- Jika rekonstruksi terhenti di tengah jalan, indeks baru bisa tertinggal dalam status invalid dan memakan ruang, sehingga indeks yang bernama
_ccnew dan bukan indisvalid perlu dicari dan dihapus
-
B-Tree deduplication di PostgreSQL 13
- PostgreSQL 13 memperkenalkan B-Tree Deduplication untuk menyimpan nilai duplikat secara lebih efisien di indeks B-Tree
- Hingga PostgreSQL 12, meskipun ada banyak nilai duplikat, setiap nilai duplikat disimpan berulang di leaf indeks sehingga bisa memakai banyak ruang
- Jika deduplication diaktifkan di PostgreSQL 13, nilai duplikat hanya disimpan sekali, sehingga dapat berdampak besar pada ukuran indeks yang memiliki banyak nilai duplikat
- Di PostgreSQL 13,
deduplicate_items = ON adalah nilai default, dan jika upgrade dari versi sebelumnya, indeks harus direkonstruksi dengan REINDEX untuk memperoleh efek penuh
- Dalam contoh 1 juta baris, indeks kolom non-unique dengan banyak duplikat turun dari 21MB menjadi 6840kB ketika deduplication diterapkan, sedangkan indeks kolom unique tetap 21MB tanpa perubahan
- Dalam kasus ini, PostgreSQL 13 saat itu masih versi baru dan penyedia cloud belum mendukungnya, sehingga ruang tidak dapat dikurangi melalui deduplication
Bloat tabel dan pg_repack
-
Keterbatasan penghapusan bloat tabel
- Tabel juga bisa mengalami bloat dan fragmentasi akibat dead tuple
- Tidak seperti indeks, tabel sulit dibuat ulang begitu saja; diperlukan pembuatan tabel baru, pemindahan data, sinkronisasi, serta pembuatan ulang indeks, constraint, dan referential constraint
VACUUM FULL table_name dapat merebut kembali ruang yang ditempati bloat dan dead tuple, tetapi membutuhkan lock tabel- Pembuatan ulang tabel dan
VACUUM FULL masing-masing dapat berujung pada pekerjaan pengembangan besar atau downtime
-
Menggunakan pg_repack
- pg_repack adalah ekstensi PostgreSQL yang digunakan untuk merekonstruksi tabel dan indeks tanpa downtime
- Untuk menggunakannya, buat ekstensi dengan
CREATE EXTENSION pg_repack;, lalu jalankan dari konsol dalam bentuk pg_repack -k --table table_name db_name
pg_repack membuat tabel baru, memuat data asal sambil menyinkronkan data baru, lalu juga merekonstruksi indeks dan pada akhirnya menukar kedua tabel- Saat diterapkan di produksi, ada beberapa keterbatasan
- Diperlukan kira-kira ruang penyimpanan tambahan sebesar ukuran tabel dan indeks yang direkonstruksi
- Jika repack gagal atau dihentikan secara manual, objek perantara dapat tertinggal dan membutuhkan pembersihan manual
- Jika storage sudah dalam kondisi kurang, kebutuhan ruang tambahan bisa membuatnya tidak cocok, sehingga ruang kosong harus dipantau dan direncanakan sebelumnya
Temuan tak terduga: PostgreSQL juga mengindeks NULL
- Setelah menghapus indeks yang tidak digunakan dan membersihkan bloat tabel serta indeks dengan teknik umum, masih ada ruang yang bisa dikurangi
- Salah satu tabel terbesar menyimpan data transaksi, dan juga mencatat kasus ketika pengguna membatalkan setelah pembayaran lalu menerima refund
- Tabel transaksi memiliki foreign key untuk pengguna pembeli maupun pengguna pembatal, dan indeks B-Tree didefinisikan pada masing-masing field
- Pengguna pembeli memiliki constraint
NOT NULL, sehingga semua baris memiliki nilai
- Pengguna pembatal bersifat nullable, dan karena pembatalan jarang terjadi, sebagian besar nilainya adalah NULL
- Indeks pengguna pembatal diperkirakan jauh lebih kecil daripada indeks pengguna pembeli, tetapi ukuran aktualnya sama
- Di Oracle dipelajari bahwa NULLs are not indexed, tetapi di PostgreSQL, nilai NULL juga diindeks
- Indeks penuh yang ada adalah sebagai berikut
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- Hipotesis diperiksa dengan menggantinya menjadi partial index yang mengecualikan nilai NULL
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Setelah reindexing, ukuran indeks penuh adalah 769MB dan lebih dari 99% nilainya NULL
- Partial index yang mengecualikan NULL berukuran kurang dari 5MB, sehingga hasilnya mengurangi lebih dari 99% isi indeks
- Setelah statistik tabel direset dan diamati selama beberapa waktu, indeks baru digunakan seperti indeks lama, dan lebih dari 760MB indexed tuple yang tidak digunakan dihapus tanpa penurunan performa
Menemukan kandidat partial index
- Setelah mendapat hasil baik dari satu partial index, indeks serupa dicari dengan mencari indeks pada kolom yang memiliki
pg_stats.null_frac tinggi
null_frac adalah rasio NULL pada nilai kolom tersebut yang diperkirakan PostgreSQL- Query pencarian kandidat menerapkan kondisi berikut
- Primary key dikecualikan karena tidak bisa menjadi partial
- Indeks yang sudah berupa partial index dikecualikan
- Composite index dikecualikan
- Hanya indeks berukuran lebih dari 10MB yang disertakan
- Dalam contoh hasil,
tx_cancelled_by_ix muncul sebagai kandidat berukuran 1418MB dengan null_frac 96,15%, sehingga berpotensi menghemat sekitar 1363MB
- Mengecualikan semua NULL dari indeks tidak selalu menguntungkan
- Query yang mencari NULL dengan kondisi
IS NULL bisa memperoleh manfaat indeks untuk nilai NULL
- Selain nilai yang bukan NULL, nilai lain yang hampir tidak pernah di-query juga dapat dikecualikan dengan partial index
- Angka 20GB pada judul dimungkinkan karena struktur di mana pengurangan sekitar 10GB pada primary database juga mengurangi storage serupa pada replica
Migrasi ke partial index di Django ORM
-
Nullable ForeignKey dan indeks implisit
- Django membuat indeks B-Tree secara implisit pada field
models.ForeignKey jika db_index=False tidak ditetapkan secara eksplisit
- Jika tidak ada pengaturan apa pun pada nullable
ForeignKey yang menyimpan pengguna pembatal, Django akan membuat indeks penuh
- Untuk membuat partial index, tetapkan
db_index=False pada field FK dan tambahkan models.Index bersyarat di Meta.indexes
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Nullable foreign key cocok menjadi kandidat partial index
- Agar fitur implisit tidak membuat indeks tanpa disadari, dibuat Django check yang memaksa
db_index selalu dinyatakan secara eksplisit pada foreign key
-
Mengubah indeks penuh menjadi partial index tanpa downtime
- Saat mengganti indeks penuh dengan partial index, proses harus dilakukan bertahap untuk menghindari downtime atau penurunan performa
- Ubah model Django terkait agar indeks penuh diganti menjadi partial index, tetapi jangan jalankan migrasi yang dihasilkan Django apa adanya
- Migrasi tersebut bisa menonaktifkan constraint FK, men-drop indeks penuh yang ada, lalu membuat partial index baru, sehingga dapat menyebabkan downtime dan penurunan performa
- Buat SQL dengan
./manage.py sqlmigrate, ambil hanya CREATE INDEX, ubah menjadi CONCURRENTLY, lalu jalankan manual di database
- Karena indeks penuh lama masih ada, query tetap dapat memakai indeks lama selama partial index dibuat
- Untuk memastikan indeks parsial baru digunakan, reset penghitung statistik indeks penuh dengan
pg_stat_reset_single_table_counters(<full index oid>)
- Setelah itu, amati
idx_scan, idx_tup_read, idx_tup_fetch di pg_stat_all_indexes dan bandingkan penggunaan indeks penuh dengan partial index
- Jika partial index dinilai sudah digunakan, drop indeks penuh dan periksa ruang penyimpanan yang benar-benar diperoleh kembali
- Setelah status database sesuai dengan status model, daftarkan migrasi Django sebagai sudah dijalankan dengan
./manage.py migrate --fake
- Di lingkungan seperti dev, QA, dan staging yang tidak terlalu mempertimbangkan downtime, migrasi Django berjalan normal dan indeks penuh diganti menjadi partial index
Ringkasan akhir
- Disk, parameter storage, dan optimasi konfigurasi saja memiliki batas dalam meningkatkan performa dan ruang
- Ruang perbaikan terakhir ada pada objek database itu sendiri, dalam kasus ini pada perubahan definisi indeks
- Alur yang diterapkan dapat diringkas menjadi tiga tahap
- Menghapus indeks yang tidak digunakan
- Melakukan repack tabel dan indeks, serta mengaktifkan B-Tree deduplication jika memungkinkan
- Memanfaatkan partial index agar hanya nilai yang diperlukan yang diindeks
- Pembersihan semacam ini dapat menjadi opsi praktis untuk memberi kelonggaran beberapa hari lagi sebelum perlu memprovisikan storage tambahan
1 komentar
Komentar Hacker News
Meski ruang penyimpanan melimpah, membuat indeks menjadi kecil tetap bagus. Dengan begitu, lebih banyak data bisa masuk ke hot set.
Namun, dari sudut pandang mengoperasikan database berskala TB, “tinggal tambah saja ruang penyimpanan” juga selalu merupakan pilihan yang valid. Terutama jika berada di luar cloud; pada hardware sendiri, SSD NVMe enterprise sekitar $80/TB, sedangkan RAM DDR4 sekitar $1,20/GB. Jika biaya 4 jam engineering diperkirakan sekitar $1000, itu bisa membeli RAM 800GB atau penyimpanan 12TB
Backup mingguan selama 1 tahun saja membuat 1TB menjadi 50TB, dan itu tetap berlaku meski penyimpanannya offline. Di perusahaan kami, menambah ruang penyimpanan ke DB live memang mudah sehingga kami melakukannya selama beberapa tahun, tetapi memindahkan backup multi-TB makin lama makin merepotkan, sehingga kami berada dalam situasi harus mengurangi data produksi meski perlu mengeluarkan biaya engineering
Jika hanya sekali, infrastruktur yang ada mungkin bisa menyerap biaya seperti ruang rack kosong atau slot PCIe cadangan, tetapi amortisasi semacam ini hanya bekerja baik pada skala kecil. Selain itu, semakin besar sistem, performa per unit perangkat pasti turun karena biaya pengelolaan dan peningkatan latensi. Jika terus menyelesaikan masalah dengan memperbesar sistem, keseluruhan sistem bisa menjadi makin lamban hingga tidak bisa dioperasikan. Sebaliknya, solusi yang mengurangi sumber daya sistem yang dibutuhkan akan meningkatkan performa per unit sumber daya, dan dengan sendirinya menciptakan aset yang lebih berkualitas
Jika sebuah optimisasi menghemat 1TB hari ini, beberapa tahun kemudian bisa saja menghemat 2TB, dan hanya beberapa keputusan seperti ini bisa menghasilkan perbedaan lebih dari satu digit. Terutama pada skala yang lebih besar, dan pada akhirnya selalu harus dicari keseimbangannya
Pilihan “melempar hardware” kadang bisa bijak, tetapi dalam kebanyakan kasus, menurut saya sulit mengubah server dengan RAM 128GB hari ini menjadi RAM 1TB besok seharga $1K
Namun karena ini bukan pengeluaran sekali, melainkan biaya bulanan, perhitungannya sedikit lebih rumit. Tentu saja, jika database tumbuh dengan laju tetap, provisioning sekali di on-premises pun pada praktiknya mendekati biaya bulanan
Artikel ini menyebut deduplikasi B-tree yang ada di PostgreSQL 13 tetapi tidak ada di versi 12 yang digunakan, dan mengatakan bahwa mayoritas nilai pada sebagian indeks foreign key adalah NULL.
Saya penasaran apakah deduplikasi B-tree akan membantu dalam kasus ini. Melihat bagian 63.4.2 dokumentasi PostgreSQL 13, sepertinya iya: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” Tentu tidak akan seefektif partial index yang diterapkan di artikel, tetapi tetap menarik.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Artikel ini berguna bukan hanya karena “penemuan” partial index yang menjadi inti, tetapi juga karena merangkum secara umum teknik-teknik bagus yang perlu diketahui saat khawatir PostgreSQL memakai ruang secara tidak efisien.
Namun, jika Anda startup atau perusahaan tahap awal, patut diingat bahwa untuk masalah penyimpanan seperti ini, hampir selalu lebih baik memilih menambah ruang disk daripada memikirkan optimisasi ukuran. Developer mahal, disk murah
Dulu ketika bekerja di startup yang tumbuh cepat, salah satu founder bersikeras untuk terus meng-upgrade satu server saja; redundansi dan backup tetap ada, tetapi arsitekturnya sederhana sehingga mudah dikelola, di-debug, dan dipulihkan. Selama 5 tahun pertama perusahaan, seluruh sistem berjalan di satu server sambil tetap tumbuh eksponensial dan melayani jutaan pengguna di seluruh dunia. Setelah melihat itu, saya yakin upgrade harus dilakukan hanya saat dibutuhkan, dengan cara yang paling sederhana dan langsung
Menggunakan partial index yang jelas cocok dengan use case, seperti ketika sebagian besar nilai adalah NULL, lebih dekat ke pemodelan yang benar dan tidak seharusnya dianggap sebagai optimisasi prematur atau pemborosan waktu developer
Dalam konteks serupa, skrip-skrip ini cukup membantu.
https://github.com/NikolayS/postgres_dba
Dengan menyusun ulang urutan kolom pada tabel besar, kami bisa mendapatkan kembali sekitar 10% ruang penyimpanan, yaitu sekitar 100GB
Minggu lalu, hanya dengan
reindexdanpg_repack, kami mendapatkan kembali 200GB dari DB 600GB. Tabel terparah datanya 17GB tetapi indeksnya 142GB, dan setelah reindexing turun menjadi 21GB.Tabel ini memiliki indexing berlebihan, dengan beberapa indeks untuk berbagai kombinasi kolom. Salah satu penyebab indeks raksasa itu tampaknya adalah distribusi data. Sejak 2015, insert, update, dan delete terus terjadi; data yang lebih lama lebih mungkin dihapus sehingga data tahun-tahun terbaru lebih banyak, tetapi masih sekitar 0,1% adalah data dari 2015. Menurut saya, distribusi miring dengan ekor panjang seperti ini mungkin membuat vacuum lebih sulit menangani pembengkakan indeks
Saat itu juga sudah dibahas: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - Februari 2021, 78 komentar
Sangat merekomendasikan pganalyze.com untuk menemukan indeks yang tidak digunakan, peluang optimasi, dan kueri dengan latensi tinggi
Ternyata ungkapan “menghemat 20GB dengan satu trik aneh” benar-benar bukan berlebihan. Di lingkungan Oracle / SQL Server lama, sering ada permintaan untuk memakai bentuk indeks khas PostgreSQL yang hampir tidak pernah terdengar, tetapi saya tidak tahu bahwa nilai NULL juga ikut diindeks
Pada proyek besar pertama yang saya ikuti, seiring dataset membesar, tim tidak bisa memahami mengapa pekerjaan menjadi lambat
Untuk indeks, waktu penyisipan per record adalah log(n). Jika DB pengujian awalnya punya 1000 record lalu mendekati 65k, waktu penyisipan naik 60% (2^10 vs 2^16). Keberhasilan membuat semuanya melambat, dan upgrade server pun ada batasnya. Jika menambahkan beberapa indeks lagi karena fitur samar yang diinginkan bisnis, sekarang menjadi dua kali lipat
Saya juga mengelola banyak DB dengan ratusan juta record dan lebih dari 40 indeks per tabel/koleksi
Pencarian biner di dalam halaman B-tree kurang dari 100 siklus. Traversal B-tree untuk 100 juta record pun seharusnya masih dalam orde mikrodetik, dan pencarian biner di atasnya juga akan berada di orde mikrodetik, kalaupun bukan ratusan nanodetik
Apakah prinsip ini juga berlaku ketika satu atau beberapa nilai sangat umum? Misalnya 90% dari kolom integer bernilai 0
where val = 0menyisakan 90% baris, ia bisa memilih scan tabel alih-alih indeks