Cara Meningkatkan Kecepatan Query Rekursif dengan Fitur Baru Postgres
TuBrief 편집팀
2026년 8월 7일
0
Computing/Software원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
커뮤니티의 다른 글
댓글 (0)
Log in to leave a comment
아직 작성된 글이 없습니다
원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
Log in to leave a comment
아직 작성된 글이 없습니다
CTE rekursif yang digunakan untuk menavigasi hubungan induk-anak atau jalur ganda dalam sistem warisan runtuh saat data menjadi semakin dalam. Begitu fanout indeks goyah, mesin membuat tabel sementara di setiap langkah dan mengulangi join mandiri. Kumpulan hasil memenuhi memori dan terjadilah spillover yang tumpah ke diska. I/O tersumbat dan CPU kehabisan tenaga. Sintaks CYCLE natif yang diperkenalkan di PostgreSQL 14 memang mengurangi biaya pencarian susunan, tetapi seiring bertambahnya kedalaman penjelajahan, biaya pencarian indeks sekunder berulang dan penyalinan memori tupel berukuran besar tetap ada.
Untuk meningkatkan kecepatan kueri dan menghemat CPU, Anda harus beralih ke sintaks grafik natif berbasis SQL/PGQ. Pertama, gunakan sintaks CREATE PROPERTY GRAPH dengan tetap mempertahankan struktur fisik tabel relasional yang ada. Nyatakan tabel entitas tunggal sebagai VERTEX TABLES dan tabel persimpangan banyak-ke-banyak sebagai EDGE TABLES. Kedua, karena kolom yang ditentukan sebagai kunci tidak otomatis masuk ke properti grafik, cantumkan kolom di klausa PROPERTIES untuk memastikan hak penapisan GRAPH_TABLE. Ketiga, untuk kueri dengan kedalaman penjelajahan melebihi 3 langkah, rasio percabangan rata-rata 10 atau lebih, atau jumlah tupel melebihi 10 juta, ubah ke pola GRAPH_TABLE MATCH. Kode rekursif yang rumit berkurang menjadi pola satu baris saja, dan penggunaan buffer memori berkurang secara drastis.
Kesalahan penyisipan duplikat yang terjadi ketika beberapa utas mendorong data secara bersamaan di lingkungan terdistribusi tidak dapat diatasi dengan kunci aplikasi. Overhead RTT jaringan terjadi dan race condition meledak di celah tepat sebelum komitmen transaksi. Anda harus menginternalisasi kunci tingkat baris dengan sintaks INSERT ON CONFLICT dan MERGE. Klausa ON CONFLICT mengunci halaman indeks unik menggunakan teknik penyisipan spekulatif dan mencoba penyisipan. Jika terjadi konflik, ia langsung bercabang ke DO UPDATE atau DO NOTHING untuk menurunkan frekuensi sesi kebuntuan (deadlock).
Mengubah tingkat isolasi transaksi saat menggunakan sintaks atomik memiliki efek riak yang besar. Dalam READ COMMITTED, transaksi pengikut menunggu komitmen transaksi pendahulu dan kemudian membaca kembali tupel terbaru untuk memprosesnya dengan aman. Namun, jika Anda meningkatkannya ke REPEATABLE READ atau SERIALIZABLE, ketika terjadi konflik dengan transaksi pendahulu, transaksi tersebut langsung mengeluarkan kesalahan kegagalan serialisasi tanpa menunggu dan melakukan rollback. Untuk mengurangi biaya rollback dan mencegah kehabisan kumpulan koneksi (connection pool), Anda harus menerapkan perulangan coba lagi hanya pada kode kesalahan 40001 dan 40P01. Tambahkan nilai jitter acak ke waktu tunggu default untuk menyebarkan waktu percobaan, batasi jumlah maksimum percobaan ulang antara 3 hingga 5 kali, dan teruskan ke lapisan bisnis atas.
Menangkap titik waktu di mana efisiensi pemindaian indeks menurun karena fragmentasi blok dan penumpukan tupel mati adalah awal dari manajemen penyimpanan. Mengingat karakteristik arsitektur MVCC, tupel mati yang disebabkan oleh UPDATE atau DELETE tidak langsung dikembalikan ke OS melainkan tersisa sebagai fragmentasi. Saat melihat metrik n_dead_tup dari tampilan pg_stat_user_tables bersama dengan ekstensi pgstattuple, jika dead_tuple_ratio melebihi 20 persen atau rasio free_space mencapai 30 persen, Anda harus memulihkan ruang diska. Jika dibiarkan, pembacaan blok I/O meningkat dan efisiensi buffer pool akan rusak.
Untuk memulihkan penyimpanan di latar belakang tanpa menghentikan layanan, gunakan alat pg_repack. Pertama, buat tabel bayangan dari tabel sumber target dan pasang pemicu pencatatan (logging trigger) untuk melacak perubahan. Kedua, salin tupel valid secara massal ke tabel bayangan, buat ulang indeks secara asinkron dan paralel, lalu ubah katalog sistem. Ketiga, jalankan perintah proses latar belakang secara langsung di terminal.
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
Untuk mencegah kontensi penguncian (lock contention), tetapkan SET lock_timeout = '3s'; pada tingkat sesi sehingga jika kunci tidak dapat diperoleh dalam waktu 3 detik, sistem langsung menghasilkan kesalahan tanpa tinggal di antrean. Tambahkan opsi --wait-timeout=10 di sini untuk memutus situasi di mana kueri berikutnya diblokir secara berantai.
Jika siklus pengumpulan informasi statistik tidak selaras dan rencana kueri tiba-tiba berubah, hal itu akan mengarah pada gangguan produksi. Di area di mana pekerjaan skala besar terkonsentrasi, statistik dan distribusi data aktual tidak cocok, sehingga memicu pergeseran rencana (plan flip). Jika pengoptimal memilih nested loop join alih-alih pemindaian indeks, lonjakan CPU akan meledak dan hambatan I/O akan terjadi. Anda harus menyertakan modul auto_explain di shared_preload_libraries dan menetapkan log_min_duration ke 500 milidetik untuk mencatat rencana eksekusi aktual dari kueri lambat ke log server agar diagnosis dapat dilakukan.
Untuk memperbaiki jalur eksekusi kueri tertentu secara paksa, gunakan modul petunjuk (hint). Setelah menangkap kueri yang bermasalah dari log, muat ekstensi pg_hint_plan untuk menambahkan komentar di depan pernyataan SQL atau daftarkan kueri ke tabel katalog hint_plan.hints. Jika Anda berada dalam situasi di mana kode sumber sulit didebarkan, Anda dapat menyuntikkan string petunjuk statis langsung ke tingkat katalog untuk segera memperbaiki rencana tanpa perlu menyebarkan ulang. Ini adalah cara tercepat untuk melindungi kecepatan respons kueri dalam situasi darurat di mana pengumpulan ulang statistik atau pembuatan ulang indeks membutuhkan waktu.