10 Tahun Peningkatan pada Optimizer PostgreSQL

Peningkatan pada optimizer PostgreSQL selama 10 tahun

• Sebagai peneliti optimisasi kueri, selama 10 tahun terakhir telah memanfaatkan optimizer kueri open source PostgreSQL yang canggih untuk penelitian
• Setelah berkecimpung dalam pekerjaan basis data, muncul rasa ingin tahu seberapa besar PostgreSQL telah meningkat selama 10 tahun terakhir
• Ada banyak catatan perubahan dan opini, tetapi sulit menemukan perbandingan empiris yang kuat, sehingga diputuskan untuk langsung menjalankan Join Order Benchmark (JOB) pada PostgreSQL 8 hingga 16
• Untuk setiap versi basis data, dicatat waktu latensi kueri persentil ke-90

Konfigurasi lingkungan pengujian

• Setiap versi PostgreSQL dibangun menggunakan GCC 13.2 di dalam kontainer Docker pada Arch Linux
• Untuk mengukur kualitas optimizer kueri, shared_buffers diatur ke 8GB (cukup besar untuk menampung seluruh basis data)
• work_mem diatur ke 8MB untuk semua versi
• Setiap kueri dijalankan sekali untuk memanaskan cache, lalu dijalankan 5 kali lagi dan dicatat latensi median-nya

Peningkatan performa secara keseluruhan

• Performa tail PostgreSQL meningkat secara signifikan, tetapi versi 13 hingga 16 secara umum relatif stabil
• Dibandingkan antara versi 8 dan 16, optimizer PostgreSQL selama 10 tahun terakhir hampir memangkas setengah latensi tail
• Distribusi seluruh kueri juga dapat diteliti (lihat skala log)

Mengukur peningkatan melalui analisis regresi

• Dengan menggunakan analisis regresi, dapat diperiksa apakah kemiringan penurunan latensi signifikan, dan dapat dikuantifikasi seberapa besar peningkatan yang dibawa oleh tiap versi PostgreSQL
• Jika nomor versi mayor PostgreSQL diregresikan terhadap latensi kueri, maka setiap versi mayor baru PostgreSQL rata-rata memberikan peningkatan performa 15% pada Join Order Benchmark
• Namun, model linear bisa dibilang merupakan ukuran yang kurang baik untuk mengukur perubahan

Pertimbangan tambahan

• Tentu saja, tidak semua peningkatan ini berasal dari optimizer kueri. Peningkatan pada execution engine, mulai dari parallel worker hingga kompilasi just-in-time (JIT), juga berperan
• Meneliti bagaimana rencana kueri untuk setiap JOB berubah dari tahun ke tahun juga akan menarik

Poin utama

• Lakukan upgrade basis data! Berpindah dari PostgreSQL 8 ke 16 dapat secara signifikan memperbaiki latensi tail pada workload Anda
• Para peneliti perlu menyadari bahwa PostgreSQL adalah target yang terus bergerak
  • Riset learned query optimization selama ini membandingkan dengan berbagai versi PostgreSQL dari waktu ke waktu
  • Jika teknik lama meningkatkan PostgreSQL sebesar 30% dan teknik terbaru meningkatkan PostgreSQL sebesar 25%, bisa jadi teknik terbaru sedang dibandingkan dengan PostgreSQL yang sudah lebih kuat

Opini GN⁺

• PostgreSQL terus meningkatkan performa, tetapi pada versi-versi terbaru besarnya peningkatan mulai mengecil. Ini mungkin karena optimisasi yang signifikan sudah banyak dilakukan. Perbaikan berikutnya tampaknya akan lebih berfokus pada area-area yang lebih rinci

• Bukan hanya optimizer kueri, peningkatan pada execution engine juga berkontribusi pada kenaikan performa. Optimisasi dilakukan dari berbagai sisi seperti pemrosesan paralel maupun kompilasi JIT

• Eksperimen ini terbatas pada Join Order Benchmark, sehingga dampak peningkatan performa dalam pekerjaan nyata bisa berbeda tergantung workload. Akan baik untuk menjalankan benchmark yang sesuai dengan karakteristik pekerjaan Anda sendiri

• Para peneliti perlu mempertimbangkan perubahan versi PostgreSQL. Bahkan dengan algoritme yang sama, besarnya peningkatan performa relatif dapat berubah tergantung versi PostgreSQL yang dijadikan pembanding

• Jika Anda masih menggunakan PostgreSQL versi lama, upgrade layak dipertimbangkan secara serius. Dibandingkan versi 10 tahun lalu, versi terbaru telah mengalami peningkatan performa yang mencolok. Tentu saja, isu seperti kompatibilitas akibat upgrade tetap perlu diperhitungkan

[GN⁺]

Komentar Hacker News

Ringkasan:

• Untuk menyelesaikan masalah optimisasi dengan baik, data tentang biaya sangat penting. PostgreSQL masih punya banyak ruang untuk perbaikan. Khususnya, data seperti latensi syscall dan statistik foreign key masih kurang.
• Untuk kueri berskala besar, perlu diperkenalkan teknik seperti deferred planning yang memungkinkan rencana diubah saat eksekusi berlangsung.
• Machine learning tepat digunakan untuk meningkatkan model prediksi biaya. Menggunakan machine learning untuk langsung menyusun rencana kueri bukan pendekatan yang tepat.
• Menetapkan shared buffer sangat besar lalu menaikkan seluruh data ke memori untuk benchmarking membuat kinerja nyata optimizer sulit dievaluasi dengan tepat.
• Kompilator JIT masih sering justru menyebabkan penurunan performa.
• Karena penomoran versi PostgreSQL berubah mulai versi 10, akan menarik juga jika tren performa dianalisis dengan menganggap versi 8.x dan 9.x sebagai versi mayor.
• Hanya dari grafik yang disajikan, sulit memastikan tren peningkatan performa secara jelas. Tail latency tampak membaik, tetapi metrik lainnya bisa berbeda-beda.
• Membuat optimizer yang hebat adalah tugas yang sangat menantang.
• Muncul pertanyaan apakah optimisasi kueri berada pada level SQL atau level algoritma.