Fitur Baru Query Planner Postgres 16

• PostgreSQL 16 menambahkan 10 peningkatan pada query planner/optimizer, memperluas pilihan rencana eksekusi untuk kueri DISTINCT, agregasi, join, window function, dan kueri tabel partisi
• Pada SELECT DISTINCT, agregasi dengan ORDER BY/DISTINCT, serta pemrosesan setelah Merge Join, PostgreSQL 16 memanfaatkan input yang sudah terurut sebagian dengan lebih agresif sehingga dapat menghasilkan output dengan memori lebih sedikit dibanding full sort
• Dukungan Memoize di dalam UNION ALL, Right Anti Join, serta parallel hash join untuk join FULL/RIGHT berfokus pada pengurangan biaya lookup berulang dan pembuatan hash table besar
• Window function mengurangi pemrosesan RANGE yang tidak perlu dan WindowAgg yang harus berjalan sampai akhir; sebagian fungsi kini dapat berhenti lebih awal dalam kondisi tertentu
• Semua peningkatan aktif secara default, sehingga sebelum dan sesudah upgrade ke PostgreSQL 16, layak membandingkan EXPLAIN dan waktu eksekusi workload nyata

---

Cakupan peningkatan planner PostgreSQL 16

• PostgreSQL 16 memperkenalkan berbagai peningkatan pada query planner sehingga banyak kueri SQL dapat dijalankan lebih cepat dibanding versi PostgreSQL sebelumnya
• Peningkatan planner yang tercantum dalam catatan rilis PG16 dijelaskan lebih rinci, disertai perbandingan output EXPLAIN antara PG15 dan PG16 serta contoh pengujian yang dapat direproduksi
• Di sini, planner adalah komponen yang di database relasional lain umumnya disebut optimizer

Optimasi pengurutan dan DISTINCT

• Penggunaan Incremental Sort pada SELECT DISTINCT

  • Incremental Sort pertama kali ditambahkan di PostgreSQL 13, dan mengurangi biaya dengan hanya mengurutkan kolom sisanya ketika hasil sudah terurut berdasarkan kolom awal
  • Planner PostgreSQL 16 juga mempertimbangkan Incremental Sort untuk kueri SELECT DISTINCT
  • Misalnya, jika ada indeks btree pada kolom a dan urutan a, b diperlukan, hasil yang sudah terurut berdasarkan a dapat diperoleh dari indeks, lalu hanya b yang diurutkan setiap kali nilai a berubah
  • Pada quicksort PostgreSQL, mengurutkan beberapa grup kecil bisa lebih efisien daripada mengurutkan satu grup besar
  • Pada contoh kueri, PG15 menggunakan HashAggregate dan sequential scan, sedangkan PG16 memilih indeks distinct_test_a_idx dan Incremental Sort
  • Presorted Key: a pada output PG16 berarti input yang sudah terurut berdasarkan a dimanfaatkan
  • Metode hash PG15 melakukan spill sekitar 30MB ke disk, sedangkan memori maksimum Incremental Sort di PG16 adalah 26KB
  • Waktu eksekusi turun dari 414,226ms di PG15 menjadi 263,167ms di PG16

• Optimasi agregasi dengan ORDER BY atau DISTINCT

  • Di PostgreSQL 15 dan versi lebih lama, fungsi agregasi yang memiliki klausa ORDER BY atau DISTINCT selalu melakukan pengurutan di dalam node Aggregate
  • Planner PostgreSQL 16 dapat membuat rencana eksekusi yang memasok baris dalam urutan yang benar ke node Aggregate, dan executor melewati pengurutan internal jika input sudah terurut
  • Pada contoh COUNT(DISTINCT b), PG15 dan PG16 sama-sama menggunakan GroupAggregate dan Index Only Scan, tetapi output PG15 menampilkan temp read=4540 written=4560
  • I/O file sementara ini adalah hasil dari pengurutan implisit PG15 yang spill ke disk
  • Output PG16 tidak memiliki I/O sementara tersebut, dan waktu eksekusi menjadi lebih dari 2 kali lebih cepat, dari 302,693ms di PG15 menjadi 115,534ms di PG16

Peningkatan lookup berulang dan rencana join

• Penerapan Memoize di dalam UNION ALL

  • Node plan Memoize pertama kali diperkenalkan di PostgreSQL 14, dan bertindak seperti lapisan cache antara Nested Loop berparameter dan input bagian dalam
  • Planner PostgreSQL 16 juga mempertimbangkan penggunaan Memoize ketika ada kueri UNION ALL di bagian dalam Nested Loop berparameter
  • Pada contoh, PG15 menjalankan Append 1 juta kali, tetapi PG16 menempatkan Memoize di atas Append
  • Memoize di PG16 mencatat Hits: 999990, Misses: 10, Memory Usage: 2kB
  • Jumlah eksekusi Append turun dari 1 juta kali di PG15 menjadi 10 kali di PG16
  • Waktu eksekusi menjadi sekitar 6 kali lebih cepat, dari 1926,151ms di PG15 menjadi 282,120ms di PG16

• Dukungan Right Anti Join

  • Pada Hash Join untuk INNER JOIN, biasanya lebih menguntungkan membuat hash table pada tabel yang lebih kecil
  • Hash table kecil membutuhkan lebih sedikit pekerjaan pembuatan, lebih ramah terhadap cache CPU, dan menurunkan kemungkinan CPU stall saat menunggu data dari memori utama
  • Sebelum PostgreSQL 16, Anti Join selalu menempatkan tabel yang disebut dalam NOT EXISTS di sisi dalam join, sehingga hash table mungkin harus dibuat pada tabel yang lebih besar
  • PostgreSQL 16 mendukung Right Anti Join, sehingga sisi yang lebih kecil dari dua tabel dapat di-hash
  • Pada contoh, PG15 melakukan hash pada tabel large berisi 1 juta baris dan menggunakan memori 6446KB, sedangkan PG16 melakukan hash pada tabel small berisi 100 baris dan hanya menggunakan 12KB
  • Waktu eksekusi hampir turun setengah, dari 139,023ms di PG15 menjadi 77,076ms di PG16

• Parallel hash join untuk join FULL/RIGHT

  • PostgreSQL 11 memperkenalkan Parallel Hash Join, yang memungkinkan beberapa parallel worker ikut membuat satu hash table
  • Parallel Hash Join di PostgreSQL 16 mendukung tipe join FULL dan RIGHT
  • Rencana FULL OUTER JOIN dan Right Join juga dapat dijalankan secara paralel
  • Pada contoh FULL JOIN, PG15 menggunakan satu Hash Full Join, sedangkan PG16 menggunakan Parallel Hash Full Join dan Gather
  • Output PG16 menampilkan Workers Planned: 1, Workers Launched: 1
  • Waktu eksekusi turun signifikan, dari 220,677ms di PG15 menjadi 129,769ms di PG16

Optimasi window function

• Melewati pemrosesan RANGE yang tidak perlu

  • Pada window function seperti row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), dan ntile(), jika klausa window tidak memiliki opsi ROWS, PostgreSQL menggunakan opsi default RANGE
  • Opsi RANGE harus memeriksa baris sebelumnya untuk menemukan peer row dengan nilai pengurutan yang sama, dan biayanya bisa besar jika ada banyak nilai yang sama berdasarkan ORDER BY
  • Fungsi-fungsi di atas tidak berubah perilakunya berdasarkan apakah ROWS atau RANGE ditentukan, tetapi executor sebelum PostgreSQL 16 tidak dapat membedakannya sehingga harus memeriksa peer row dalam semua kasus
  • Planner PostgreSQL 16 mengetahui window function mana yang dipengaruhi oleh opsi ROWS/RANGE, lalu meneruskan informasi agar executor melewati pemrosesan yang tidak perlu
  • Pada contoh row_number() <= 10, PG15 membaca 50.410 baris dari indeks sebelum berhenti, sedangkan PG16 hanya membaca 11 baris
  • PG16 memanfaatkan fakta bahwa ketika row_number mencapai 11, tidak ada lagi baris yang memenuhi kondisi <= 10
  • Waktu eksekusi menjadi lebih dari 500 kali lebih cepat, dari 29,775ms di PG15 menjadi 0,058ms di PG16

• Perluasan penghentian dini untuk window function yang meningkat monoton

  • PostgreSQL 15 memungkinkan eksekusi WindowAgg berhenti lebih awal ketika kondisi klausa WHERE untuk window function tertentu, setelah menjadi false, tidak mungkin menjadi true lagi
  • PostgreSQL 16 memperluas target optimasi ini hingga ntile(), cume_dist(), dan percent_rank()
  • Di PostgreSQL 15, optimasi ini hanya berlaku untuk row_number(), rank(), dense_rank(), count(), dan count(*)
  • Pada contoh percent_rank() <= 0.01, PG15 menangani kondisi sebagai Filter pada subkueri, dan WindowAgg memproses seluruh 50.000 baris
  • PG16 menggunakan kondisi yang sama sebagai Run Condition sehingga eksekusi WindowAgg berhenti lebih awal
  • Waktu eksekusi menjadi lebih dari 4 kali lebih cepat, dari 84,358ms di PG15 menjadi 19,454ms di PG16

Tabel partisi dan penanganan DISTINCT yang trivial

• Penghapusan LEFT JOIN pada tabel partisi

  • PostgreSQL sejak lama dapat menghapus LEFT JOIN yang tidak diperlukan oleh kueri dan tidak memiliki kemungkinan menduplikasi baris
  • Sebelum PostgreSQL 16, penghapusan LEFT JOIN untuk tabel partisi belum didukung
  • Penyebabnya adalah tidak adanya bukti yang diperlukan pada tabel partisi untuk menentukan apakah baris bagian dalam tidak mungkin menduplikasi baris bagian luar
  • Planner PostgreSQL 16 menerapkan optimasi penghapusan LEFT JOIN juga pada tabel partisi
  • Optimasi ini bisa sangat berguna pada view
    • Karena meskipun view memiliki banyak kolom, kueri nyata tidak selalu mengambil semua kolom
  • Pada contoh, rencana PG15 menyertakan join ke part_tab, tetapi rencana PG16 hanya melakukan sequential scan pada normal_table

• Menangani DISTINCT yang hasilnya sudah pasti satu sebagai Limit

  • Planner PostgreSQL dapat melewati node plan untuk menghapus duplikasi hasil jika dapat mendeteksi bahwa semua baris memiliki nilai yang sama
  • PostgreSQL 16 memanfaatkan fakta bahwa hasil hanya berisi nilai yang sama ketika semua kolom target DISTINCT dikunci oleh kondisi kesetaraan di klausa WHERE, dan menanganinya dengan LIMIT 1
  • Pada contoh kueri SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5, setiap kolom DISTINCT dibatasi ke nilai yang sama
  • PG15 membaca seluruh hasil lalu menguranginya menjadi 1 baris dengan operator Unique
  • PG16 menggunakan Limit dan sequential scan untuk mengembalikan hanya 1 baris
  • Waktu eksekusi menjadi lebih dari 1200 kali lebih cepat, dari 30,381ms di PG15 menjadi 0,025ms di PG16

Perluasan pemanfaatan Incremental Sort setelah Merge Join

• Sebelum PostgreSQL 16, ketika mempertimbangkan Merge Join, planner hanya menggunakan urutan join jika urutan pengurutan join cocok persis dengan kebutuhan operasi tingkat atas seperti DISTINCT, GROUP BY, atau ORDER BY
• Aturan ini tidak cukup mencerminkan bahwa Incremental Sort dapat memanfaatkan input yang terurut sebagian pada operasi tingkat atas
• PostgreSQL 16 melonggarkan aturan dalam mempertimbangkan urutan Merge Join, dari “harus cocok persis” menjadi “minimal 1 kolom awal harus terurut dengan benar”
• Dengan perubahan ini, planner dapat lebih sering menggunakan Incremental Sort untuk menyesuaikan hasil Merge Join dengan operasi tingkat atas
  • Incremental Sort memanfaatkan input yang terurut sebagian dan mengurutkan dalam batch kecil, sehingga dapat mengurangi penggunaan memori dan jumlah perbandingan dibanding full sort
• Pada contoh, PG15 menggunakan Sort penuh setelah Merge Join, sedangkan PG16 menggunakan Incremental Sort
  • Memori maksimum Incremental Sort di PG16 adalah 26KB
  • Waktu eksekusi sedikit turun dari 1010,738ms di PG15 menjadi 915,589ms di PG16, dan memori yang digunakan untuk pengurutan berkurang besar

Cara penerapan dan verifikasi praktis

• Kesepuluh peningkatan planner PostgreSQL 16 semuanya aktif secara default
• Setiap optimasi diterapkan pada semua kasus yang memungkinkan, atau dipilih secara selektif ketika planner menilai optimasi tersebut membantu
• Jika sedang menggunakan versi PostgreSQL lama, Anda dapat menjalankan workload nyata di PostgreSQL 16 untuk melihat kueri mana yang menjadi lebih cepat
• Umpan balik penggunaan nyata dapat dibagikan ke milis [e-mail]

[GN⁺]

Opini Hacker News

• Akan sangat bagus jika query planner PostgreSQL bisa merencanakan ulang kueri di tengah eksekusi
  Kueri yang sangat lambat secara patologis sering terjadi karena planner tidak mengetahui informasi yang diperlukan tentang distribusi data, sehingga salah memperkirakan biaya; perbedaannya bisa mudah mencapai 1000 kali, misalnya waktu eksekusi menjadi 1 detik alih-alih 1 ms
  Statistik tabel tidak mungkin 100% akurat, jadi setelah kueri dimulai, jika progresnya lebih lambat dari perkiraan, akan bagus jika informasi progres saat ini seperti jumlah halaman yang sudah dipindai dan tuple yang cocok dimasukkan kembali ke planner untuk membuat rencana baru
  Namun PostgreSQL tidak membuat seluruh hasil sampai selesai lalu mengirimkannya, melainkan mengirim secara streaming; jadi untuk mengubah rencana di tengah jalan, perlu melacak hasil yang sudah dikirim ke klien, yang berarti perubahan infrastruktur besar
  Selain itu, klien juga bisa membalik arah di tengah kueri dan meminta kembali hasil sebelumnya dalam urutan terbalik, sehingga kompleksitasnya makin besar

  • Sebagai penulis blog sekaligus committer PostgreSQL, saya pikir akan bagus jika fitur ini ada. Namun masalah mengirim tuple ke klien lebih rumit daripada yang disebutkan di atas
    Sebab tidak ada jaminan bahwa rencana baru akan mengembalikan tuple yang sama. Misalnya, jika tidak ada ORDER BY seperti SELECT * FROM table LIMIT 10, tuple mana yang keluar bersifat nondeterministik
    Mungkin lebih mudah jika X tuple dimasukkan dulu ke antrean, lalu mulai dikirim saat antrean penuh. Setelah antrean penuh, dianggap sudah terlambat untuk replan dan tetap memakai rencana saat ini
    Pengguna bisa menyesuaikan X untuk memperpanjang waktu yang tersedia untuk mengubah rencana, dengan konsekuensi memakai lebih banyak memori dan menambah latensi tuple pertama
  • Dari sudut pandang lain, bisa juga ada kueri yang mengizinkan perencanaan yang lebih lama. Izinkan planner memakai 1 detik atau beberapa detik untuk memilih rencana optimal, dan selama proses itu mengumpulkan statistik tambahan atau menjalankan kueri sebentar
  • Saya penasaran di mana fitur klien yang membalik arah di tengah kueri untuk menerima kembali hasil sebelumnya dalam urutan terbalik itu berguna
  • Jika kueri tidak sepenuhnya menentukan urutan pengurutan, saya penasaran apakah rencana kueri bisa memengaruhi urutan hasil. Jika ya, pendekatan yang diusulkan mungkin hampir mustahil
    Kueri baru tidak bisa sekadar melewati N hasil pertama; ia harus mencocokkan setiap baris yang sudah dikirim dengan daftar
  • Makalah ini dan makalah-makalah yang dikutip mungkin menarik: https://arxiv.org/pdf/1902.08291

• Untuk visualisasi kueri, saya memakai alat ini: https://explain.dalibo.com/
  Ada juga https://www.pgexplain.dev/; dulu keluarannya kurang bagus, tetapi sekarang keduanya tampak mirip

  • Alatnya bagus dan saya memakainya, tetapi saya belum memahami cukup dalam untuk melihat bagian yang tampak buruk dalam rencana lalu tahu bagaimana harus memperbaiki pendekatan saya
  • Dari profilnya terlihat Anda CTO fintech; saya penasaran bagaimana Anda menangani peringatan alat itu yang mengatakan “disarankan untuk tidak mengirim informasi penting atau sensitif”
    Saya juga penasaran apakah ada alat sanitasi execution plan yang membantu dalam situasi seperti ini

• Perbaikan query planner selalu disambut baik, dan ini bagian yang sangat penting dalam database. Tentu saja, biasanya paling terasa ketika hasilnya tidak seperti yang saya inginkan
  Secara pribadi, bagian yang cukup membuat frustrasi adalah JIT di PostgreSQL modern. Heuristik untuk menentukan kapan menggunakannya sama sekali tidak tampak kokoh
  Saya melihatnya pada kueri tipikal yang dihasilkan ORM: kuerinya sendiri sederhana, tetapi join menarik banyak tabel. Tanpa JIT selesai dalam beberapa milidetik, tetapi JIT menambahkan 1–1,5 detik sehingga menjadi sangat lambat bahkan pada data kecil
  Sekarang saya tahu bahwa JIT bisa dimatikan saja, tetapi bagi pengguna yang belum tahu penyebab lambatnya, ini bisa sangat merusak kesan terhadap PostgreSQL. Saya suka PostgreSQL, tetapi membiarkan JIT aktif secara default terasa terlalu berisiko

  • Sebagai penulis blog sekaligus committer PostgreSQL, saya sangat setuju bahwa kode yang menentukan apakah JIT digunakan atau tidak perlu diperbaiki
    Di PG16, keputusan hanya melihat estimasi total biaya rencana, tanpa mempertimbangkan jumlah ekspresi yang harus dikompilasi
    Mengompilasi beberapa ekspresi itu cepat, tetapi jika mengkueri tabel berpartisi dengan ratusan partisi dan semua partisi itu masuk ke rencana, kompiler JIT jadi punya banyak pekerjaan
    Saya bersama seorang kolega punya kode untuk memperbaiki ini, tetapi saat ini belum pasti apakah akan masuk ke PG17
  • Hal lain yang terasa aneh pada JIT adalah kode yang dihasilkan tidak di-cache. Sering kali itu bagian paling mahal dari eksekusi kueri, jadi saya tidak mengerti mengapa tidak di-cache
    Saya mencari diskusi terkait JIT di mailing list PostgreSQL, tetapi tidak menemukan alasan yang meyakinkan
    Untuk beban kerja OLTP, mematikan JIT adalah pilihan yang benar
  • Menurut saya JIT pada dasarnya mendekati kegagalan. Niatnya baik, tetapi LLVM bukan alat yang cocok untuk ini. Saya mematikannya secara global
    Karena saya tidak memakai ORM, ini juga bukan sekadar karena pola kueri yang aneh
    Sebaliknya, paralelisasi kueri bisa benar-benar berguna, dan yang terpenting jarang merugikan
  • Baru-baru ini saya mengalami bug aneh terkait JIT di produksi
    Setelah memperbarui beberapa paket dengan apt, sebuah kueri besar yang berjalan setiap 5 menit tiba-tiba mulai gagal. Tepatnya, PostgreSQL diam-diam memutus koneksi di tengah eksekusi kueri tanpa meninggalkan log
    Saat saya mengecek dengan menjalankan EXPLAIN secara manual, hanya varian kueri yang memakai JIT yang rusak, sementara yang tidak memakai JIT baik-baik saja. Setelah JIT dimatikan, semuanya kembali normal
  • Saya penasaran apakah Anda sudah mencoba memakai prepared statement agar kompilasi hanya dilakukan sekali, lalu hasil kompilasinya digunakan ulang setiap kali kueri itu dijalankan

• Saya penasaran seberapa sering perubahan seperti ini efektif pada query nyata. Khususnya perubahan “menggunakan Limit alih-alih Unique untuk implementasi DISTINCT jika memungkinkan” terasa seperti hanya akan berlaku untuk query yang sangat bodoh
  Saya penasaran apakah para pengembang PostgreSQL punya sumber informasi untuk menilai hal ini

  • Sepertinya akan cukup sering berguna. DISTINCT sering ditambahkan oleh developer yang kurang berpengalaman untuk mencoba memperbaiki query yang buruk, dan biasanya hal pertama yang dilakukan saat mulai meningkatkan performa adalah menulis ulang query agar itu tidak diperlukan
    Jika perbaikan DISTINCT membuatnya lebih tangguh terhadap query buruk, manfaatnya besar. Memang tidak akan memperbaiki semua masalah, tetapi perbaikan apa pun tetap disambut baik
  • Sebagai penulis blog sekaligus pembuat fitur tersebut, kasus ini benar-benar muncul di mailing list pgsql-hackers
    Saya setuju kemungkinan penerapannya sering itu rendah, tetapi hal baiknya adalah mendeteksi apakah ini bisa diterapkan sesederhana memeriksa apakah pointer bernilai NULL
    Deteksinya sangat sederhana dan kebanyakan tidak akan berlaku, tetapi jika bisa diterapkan, ini dapat memberi peningkatan performa yang cukup besar
  • Masalahnya, ORM punya kebiasaan membuat query yang sangat bodoh, dan developer menolak memperbaikinya dengan menulis SQL langsung karena entah mengapa terasa tidak murni
    Mungkin bukan masalah yang sangat umum, tetapi saya tidak akan terkejut jika sesekali muncul
  • Di tempat kerja lama, karena alasan legacy, tabel pengguna mengizinkan alamat email duplikat, tetapi kami tidak ingin memasukkan duplikat baru, jadi sebelum membuat pengguna baru kami menjalankan query select distinct email from users where email = ?
    Sepertinya jumlah baris dengan email yang sama tidak pernah lebih dari 100. Kebanyakan adalah pengguna uji yang sebenarnya boleh dihapus, tetapi ceritanya jadi agak melebar

• Saya berharap PostgreSQL punya mode ketat untuk pengujian aplikasi. Mode yang hanya melihat query itu sendiri, terlepas dari statistik, lalu jika adanya indeks akan memperbaiki query secara asimtotik tetapi indeks itu tidak ada, mode tersebut mengembalikan error
  Untuk upgrade aplikasi, akan bagus juga jika ada perintah CREATE INDICES FOR yang membuat indeks tersebut, dan untuk penggunaan interaktif/pengembangan ada mode pembuatan indeks otomatis
  Secara umum, sistem seharusnya dirancang agar eksekusi yang secara asimtotik tidak optimal tidak pernah terjadi

• Saya tidak mengerti kenapa hint tidak diimplementasikan

  • Ada ekstensi pg_hint_plan. Risiko dari hint adalah meskipun benar saat ditulis, hint bisa justru menjadi buruk ketika ukuran tabel atau skew data berubah
    Saat dulu melihat diskusi tentang hint, seingat saya tidak ada penolakan umum jika caranya tidak mengikat planner terlalu kuat dan tetap bisa beradaptasi dengan perubahan data dasar
    Misalnya, alih-alih menetapkan bahwa predikat tertentu cocok dengan 10 baris, memberi tahu bahwa ada korelasi antara dua kolom
  • Diskusi terkait: Why PostgreSQL doesn't have query hints
    https://news.ycombinator.com/item?id=2179433 (60 komentar, 2011)
    Sikap resmi di wiki PostgreSQL ada di https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
    Posisinya adalah “tidak tertarik pada hint dengan bentuk persis seperti yang umum diimplementasikan di database lain”
    Masalah pada sistem hint yang ada antara lain menurunkan kemudahan pemeliharaan kode aplikasi, menghambat upgrade, mendorong kebiasaan DBA yang buruk, dan tidak cocok ketika ukuran data membesar
    Saya tidak ingin menyalahkan sikap itu, tetapi ketika PostgreSQL memilih rencana yang bodoh dan kita tidak bisa meyakinkannya untuk membuat pilihan yang masuk akal, rasanya membuat frustrasi

• Seorang teman saya yang merupakan DBA Microsoft untuk perusahaan menengah mengatakan bahwa Anda tidak bisa melakukan pekerjaan serius dengan PostgreSQL. Bahkan katanya ia terkejut saat tahu PostgreSQL bahkan tidak punya query planner
  Terlepas dulu dari ejekannya, saya penasaran apakah klaim yang lebih besar bahwa MSSQL bisa menangani skala yang tidak cocok untuk PostgreSQL itu ada benarnya. Secara insting rasanya tidak masuk akal, tapi saya sama sekali bukan DBA

  • Ada benarnya dari sisi itu. Kalau yang dibutuhkan adalah database yang bisa menangani hampir apa pun dengan cukup baik, MSSQL dan Oracle besar kemungkinan bisa melakukannya
    Mereka menyelesaikannya dengan cara menggelontorkan uang dan hardware—artinya lebih banyak uang—sampai masalahnya beres. Tentu ada teknologi cerdas juga di dalamnya, tetapi pada dasarnya, selama waktu yang lama ada jauh lebih banyak rekayasa yang dicurahkan
    Mereka bisa melakukan horizontal scaling ke skala yang lebih besar daripada yang secara wajar bisa dilakukan PostgreSQL
    Namun PostgreSQL juga sedang mengejar, dan MySQL/MariaDB bisa dibilang sejak dulu cukup baik dalam cerita ini. Opsi horizontal scaling terus membaik
    Sekarang juga sudah lebih mudah menjalankan cluster PostgreSQL multi-terabyte dengan sedikit mesin untuk menangani traffic besar, lalu menaruh “big data” di database yang lebih khusus. Cara lama memasukkan semuanya ke MSSQL/Oracle mungkin sudah agak ketinggalan zaman
  • Saya pernah banyak mengembangkan dengan MSSQL, dan ada beberapa fitur yang agak mengejutkan tidak ada di PostgreSQL
    Yang dimaksud teman Anda mungkin adalah fakta bahwa PostgreSQL tidak punya cara untuk meng-cache atau mematok query plan. PostgreSQL merencanakan ulang setiap statement kecuali Anda memakai prepared statement secara manual, dan itu pun hanya bekerja per koneksi
    MSSQL sejak lama meng-cache dan memakai ulang plan, sehingga planner bisa menghabiskan lebih banyak waktu untuk menyusun plan. MSSQL juga punya hint dan bisa mematok plan
    PostgreSQL benar-benar butuh hint. Sekalipun optimizer-nya hebat, kadang saya lebih tahu, dan ingin membuatnya mendengarkan saya
    Selain itu PostgreSQL tidak punya clustered index yang benar-benar asli, dan semua tabel adalah heap. Di MSSQL ini sering dipakai; biasanya primary key dijadikan clustered index sehingga tabel itu sendiri menjadi index, dan tidak ada dereference tidak langsung untuk lookup berdasarkan key
    Menariknya, SQLite justru sebaliknya: tabel selalu memiliki clustered index, entah Anda membuatnya atau tidak, sedangkan MSSQL membiarkan Anda memilih antara heap dan index-organized table
  • PostgreSQL punya query planner. Seluruh artikel ini membahas peningkatannya. Jadi sepertinya ada miskomunikasi, atau teman Anda sama sekali tidak mengenal PostgreSQL
    Ada juga contoh database PostgreSQL yang sangat besar berjalan dengan baik, jadi PostgreSQL jelas bisa diskalakan
    Namun SQL Server memang punya fitur yang tidak ada di PostgreSQL, dan kalau fitur-fitur itu penting, ia bisa lebih cocok untuk use case tertentu. Pada akhirnya keduanya adalah database berbeda dengan kekuatan dan kelemahan masing-masing
  • Saya pernah memakai keduanya, baik untuk OLTP maupun data warehousing, dan keduanya oke
    Awalnya saya hendak menulis bahwa kalau bukan karena aplikasi dari vendor yang membutuhkan SQL Server, saya akan merekomendasikan perusahaan untuk pindah ke PostgreSQL
    Namun kemudian saya sadar betapa banyak hal yang perlu diganti dari yang sudah disertakan Microsoft, seperti reporting services, integration services, job, integrasi AD, dan service broker. notify/listen tidak punya tipe pesan
    analysis services sudah tidak kami pakai lagi, tetapi dulu saat masih dipakai, itu juga akan sulit diganti
    Hal-hal seperti inilah yang membuat orang bertahan. Saya bahkan tidak punya gambaran berapa lama waktu yang dibutuhkan untuk mengganti semua ini, dan menghabiskan 1 tahun hanya untuk mengganti sesuatu yang sudah dimiliki bukanlah ROI yang bagus
  • Aurora dari AWS tampaknya menangani ini dengan cukup baik, dan menargetkan diri sebagai pengganti drop-in untuk PostgreSQL dan MySQL

• Saya penasaran kenapa konten ini dipublikasikan di citusdata, bukan postgresql.org. Tidak tahu apakah ini khusus fitur berbayar atau tambahan open source

  • Karena penulisnya bekerja di Citus Data, dan ia juga menulis sendiri sebagian dari optimisasi tersebut

• Kapan kira-kira index bisa dipakai untuk mempercepat query IS NOT DISTINCT FROM ;)