Membuat kueri Postgres 1.000 kali lebih cepat

• Mattermost menggunakan Elasticsearch untuk mengurangi beban database dan memberikan hasil pencarian yang jauh lebih cepat
  • Agar Elasticsearch bekerja dengan baik, semua data yang menjadi target pencarian harus diindeks
  • Untuk data yang sudah diindeks, proses pengindeksan untuk postingan dan file baru setelahnya cukup cepat
  • Namun, melakukan pengindeksan penuh dari awal pada database yang sangat besar (100 juta post) sangat lambat (bahkan setelah 18 jam belum mencapai setengah, dan makin lama makin lambat)
• Melalui grafik waktu yang dibutuhkan untuk tiap pemanggilan database, diketahui bahwa kueri SQL pada metode PostStore.GetPostsBatchForIndexing adalah sumber masalah
  • Kueri tersebut pada dasarnya mengurutkan post berdasarkan creation timestamp dan mengembalikan N post yang lebih baru dari timestamp yang diberikan
  • Job indexing menjalankan kueri ini berulang kali hingga semua post selesai diindeks
• Rencana eksekusi kueri dianalisis menggunakan EXPLAIN (ANALYZE, BUFFERS):
  • Saat melakukan index scan pada tabel Posts, sistem memproses 40 juta blok untuk menerapkan kondisi Filter (309GB)
  • JOIN dengan tabel Channels bukanlah masalah
  • Jika hanya bagian Posts.CreateAt > ?1 dari klausa OR pada kondisi WHERE yang diterapkan, hasilnya jauh lebih cepat (30ms)
  • Lalu jika kondisi Posts.CreateAt = ?1 AND Posts.Id > ?2 diterapkan, hasilnya menjadi sangat cepat (0,047ms)
• Penyebabnya:
  • Kueri asli memindai semua baris di Posts lalu menyaringnya dengan Filter, sedangkan kueri yang diperbaiki hanya memeriksa indeks dan mengambil baris yang diperlukan
  • Alasan kueri makin lambat seiring waktu adalah karena semakin banyak baris yang harus disaring
• Solusi:
  • Memanfaatkan fitur row constructor comparison di PostgreSQL dengan mengubah kondisi menjadi (Posts.CreateAt, Posts.Id) > (?1, ?2)
  • Setelah perubahan ini, waktu eksekusi kueri turun drastis menjadi 34 milidetik
  • Namun di MySQL, kueri yang diubah justru berjalan lebih lambat. Karena kueri asli lebih cepat di MySQL, kode akhirnya dibagi agar memakai kueri yang berbeda untuk tiap DB
• Hal-hal yang dipelajari:
  • Selalu gunakan opsi BUFFERS saat menjalankan EXPLAIN
  • Usahakan menggunakan Index Cond alih-alih Filter
  • Anggap bahwa PostgreSQL dan MySQL hampir selalu berperilaku berbeda
• Kesimpulan
  • Melalui optimasi ini, waktu eksekusi kueri berhasil dikurangi lebih dari 1000 kali lipat
  • Optimasi ini telah diterapkan ke Mattermost v9.7.0 dan versi v9.5 ESR
  • Proses optimasi ini memberikan banyak pelajaran

[dontcryme]

Seperti juga disebutkan di tulisan paling akhir, judul artikel ini terasa agak clickbait... kalau diubah jadi yang lebih substantif, mungkin jadi

'contoh pemanfaatan PostgreSQL yang dipelajari dari kesalahan'

barangkali..

[vwjdalsgkv]

Hmm... secara pribadi, kalau tulisan dengan kualitas seperti ini ditulis sambil mengaitkannya dengan perusahaan/produk tertentu, saya malah merasa tingkat kepercayaan terhadap produk itu akan jauh menurun.
Penyusunannya memang rapi dan mudah diikuti, tetapi sayang nilai teknis yang terkandung di dalamnya terasa agak kurang.

[savvykang]

Saya juga justru jadi kurang percaya setelah membaca tulisan ini. Karena artinya produk yang dijual berbayar itu merilis fitur tanpa pengujian pemrosesan skala besar. Menurut saya, indeks sesederhana itu seharusnya sudah diatur pada tahap pengembangan fitur. Terlihat seperti banyak prosedur pengembangan perangkat lunak yang dilewatkan.