PostgreSQL Performance & Query Optimization · Pelajaran

Mengapa Koneksi Mahal di PostgreSQL

Pahami biaya memori dan penjadwalan per backend yang membuat pooling penting pada skala besar.

Pelajaran 1 dari 413 langkah

Mengapa Koneksi Mahal di PostgreSQL adalah pelajaran PostgreSQL Performance & Query Optimization gratis di CoddyKit. Ini adalah pelajaran 1 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar PostgreSQL Performance & Query Optimization, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Satu Koneksi, Satu Proses

PostgreSQL menggunakan model satu proses per koneksi. Setiap koneksi klien ditangani oleh proses OS khususnya sendiri yang disebut backend, yang dibuat melalui fork dari postmaster ketika koneksi diterima.

Desain ini tangguh dan sederhana, tetapi memiliki biaya nyata: proses jauh lebih berat daripada utas. Tidak seperti basis data yang memultipleks banyak sesi ke dalam kumpulan utas, PostgreSQL membayar biaya per proses untuk setiap koneksi yang terbuka, baik koneksi tersebut sedang menjalankan kueri maupun sedang menganggur.

Anda dapat melihat satu backend untuk setiap koneksi secara langsung di katalog:

SELECT pid, usename, application_name, state
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Biaya Pembuatan Proses

Membuka koneksi tidaklah gratis. Setiap backend baru mengharuskan PostgreSQL untuk:

Membuat fork proses OS baru dari postmaster
Terhubung ke memori bersama dan menyiapkan konteks memori lokalnya
Mengautentikasi klien serta memvalidasi basis data/peran
Memuat entri katalog dan tembolok relasi saat pertama kali diakses

Penyiapan ini dapat memerlukan beberapa milidetik sebelum satu kueri pun dijalankan. Aplikasi yang membuka dan menutup koneksi untuk setiap permintaan HTTP membayar biaya ini ribuan kali per menit, sehingga pergantian koneksi menjadi latensi dan beban CPU yang terukur.

Memori Per Backend Tidak Dibagi

Di luar kumpulan buffer bersama, setiap backend mengalokasikan memori privatnya sendiri. Parameter utama per koneksi bersifat lokal untuk setiap sesi:

work_mem — memori untuk setiap operasi pengurutan, pencincangan, atau pengelompokan
temp_buffers — memori untuk tabel sementara
Tembolok katalog dan rencana yang bertambah seiring sesi mengakses lebih banyak objek

Yang penting, work_mem dialokasikan per operasi, per koneksi. Satu kueri kompleks dengan beberapa pengurutan dan penggabungan pencincangan dapat menggunakan kelipatan work_mem secara bersamaan.

SHOW work_mem;
SHOW temp_buffers;
SHOW shared_buffers;

Mengapa work_mem Berlipat Ganda

Bahaya work_mem adalah bahwa parameter ini bukan batas per koneksi — melainkan alokasi per operasi. Rencana dengan tiga pengurutan dan dua penggabungan pencincangan dapat meminta work_mem lima kali secara bersamaan.

Perkirakan kasus terburuk secara kasar sebagai berikut:

peak_RAM ≈ max_connections × work_mem × avg_operations_per_query

Dengan max_connections = 500, work_mem = 16MB, dan beberapa pengurutan per kueri, secara teoretis Anda dapat mencapai puluhan gigabita memori sementara — bahkan sebelum memperhitungkan buffer bersama atau tembolok halaman OS.

-- Rough back-of-envelope ceiling
SELECT
  current_setting('max_connections')::int AS max_conn,
  current_setting('work_mem') AS work_mem,
  current_setting('max_connections')::int
    * (pg_size_bytes(current_setting('work_mem')) / 1024 / 1024)
    AS naive_worst_case_mb;

Koneksi Menganggur Tetap Membebani

Kesalahpahaman yang umum adalah bahwa koneksi menganggur tidak menimbulkan biaya. Kenyataannya tidak demikian. Bahkan backend yang tidak melakukan apa pun:

Menahan slot proses OS dan tembolok memori privatnya
Menempati slot yang dihitung terhadap max_connections
Harus dikunjungi oleh pemindaian latar belakang terhadap pg_stat_activity dan logika snapshot
Jika berstatus idle in transaction, dapat menahan versi baris lama dan menghalangi vacuum

Menemukan sesi yang menganggur atau menganggur dalam transaksi dalam waktu lama adalah salah satu hal pertama yang perlu diperiksa pada server yang bermasalah:

SELECT pid, state, wait_event_type,
       now() - state_change AS idle_for
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
ORDER BY idle_for DESC;

Snapshot dan Biaya Visibilitas

Model MVCC PostgreSQL berarti setiap kueri mengambil snapshot tentang transaksi mana yang terlihat. Pembuatan dan pemeliharaan snapshot tersebut melibatkan pemindaian daftar backend yang sedang aktif.

Seiring jumlah koneksi bertambah, pencatatan ini menjadi lebih mahal. Secara historis (sebelum PostgreSQL 14), GetSnapshotData() bertumbuh sebanding dengan jumlah total koneksi, sehingga ribuan backend yang sebagian besar menganggur menambah overhead pada setiap transaksi aktif.

Pelajarannya: koneksi yang lebih banyak bukan hanya menggunakan lebih banyak memori — koneksi tersebut juga membuat pekerjaan koordinasi bersama menjadi lebih berat bagi semua pihak.

Batas Penjadwalan CPU

Backend adalah proses OS sungguhan, sehingga penjadwal kernel harus membagi waktu eksekusinya di antara inti CPU Anda. Ketika jumlah backend yang siap dijalankan jauh melebihi jumlah inti, Anda mencapai batas:

Lebih banyak pergantian konteks menghabiskan CPU untuk overhead, bukan pekerjaan kueri
Lokalitas tembolok menurun karena proses dipindahkan masuk dan keluar dari inti
Kontensi kunci pada struktur bersama meningkat seiring konkurensi

Inilah sebabnya throughput sering kali mencapai puncak lalu menurun ketika konkurensi melampaui jumlah inti. Mesin dengan 16 inti jarang memperoleh manfaat dari 400 kueri yang aktif secara bersamaan.

SELECT count(*) AS active_queries
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend';

Menentukan max_connections Secara Realistis

Anda mungkin tergoda mengatur max_connections sangat tinggi "untuk berjaga-jaga", tetapi hal itu justru menjadi bumerang. Setiap koneksi potensial mencadangkan pencatatan di memori bersama dan menaikkan tekanan memori serta penjadwalan.

Pedoman praktis untuk beban kerja yang dibatasi CPU kira-kira seperti berikut:

active_connections ≈ cores × 2 to cores × 4

Atur max_connections cukup tinggi untuk mencakup konkurensi nyata ditambah ruang cadangan — bukan untuk menampung ribuan utas aplikasi yang masing-masing mengambil koneksi mentah.

SHOW max_connections;

SELECT count(*) AS current_connections
FROM pg_stat_activity;

Ke Mana Biaya Digunakan: Model Kecil

Berikut cara mandiri untuk menalar batas maksimum per koneksi hanya dengan menggunakan konstanta. Cara ini memperkirakan memori kueri sementara dalam kondisi terburuk untuk sekumpulan koneksi—tidak memerlukan server atau tabel, sehingga dapat dijalankan di mana saja.

Intinya adalah memperjelas perkaliannya: jumlah koneksi dikali memori per operasi dikali jumlah operasi per kueri adalah angka yang sering mengejutkan orang.

WITH params AS (
  SELECT 400  AS max_conn,
         16   AS work_mem_mb,
         3    AS avg_ops_per_query,
         8192 AS shared_buffers_mb
)
SELECT
  max_conn,
  work_mem_mb,
  avg_ops_per_query,
  max_conn * work_mem_mb * avg_ops_per_query AS worst_case_query_mb,
  shared_buffers_mb
    + max_conn * work_mem_mb * avg_ops_per_query AS total_ceiling_mb
FROM params;

Solusinya: Gunakan Pool, Jangan Menggandakan

Solusi untuk semua biaya ini adalah pengumpulan koneksi. Alih-alih memberikan backend mentahnya sendiri kepada setiap utas aplikasi, pengelola pool mempertahankan sejumlah kecil koneksi PostgreSQL yang siap digunakan dan memultipleks banyak klien melalui koneksi tersebut.

Backend digunakan ulang, sehingga biaya fork/autentikasi/pemanasan tembolok dibayar sekali, bukan pada setiap permintaan
Backend aktif tetap mendekati jumlah inti, sehingga terhindar dari batas penjadwalan
Total memori privat dibatasi oleh ukuran pool, bukan jumlah klien

PgBouncer adalah pengelola pool ringan yang umum digunakan tepat karena alasan ini—dan menjadi topik dalam sisa kursus ini.

Mendiagnosis Tekanan Koneksi

Sebelum menyetel pool, ukur kondisi Anda terlebih dahulu. Cuplikan kesehatan singkat mengelompokkan sesi saat ini berdasarkan status, sehingga Anda dapat melihat berapa banyak yang benar-benar aktif dibandingkan yang menganggur.

Jika Anda melihat ratusan koneksi menganggur dan hanya segelintir yang aktif, Anda membayar penuh biaya memori per backend dan penjadwalan untuk kapasitas yang tidak pernah digunakan—contoh klasik yang menunjukkan perlunya pengumpulan koneksi.

SELECT state,
       count(*) AS sessions,
       max(now() - state_change) AS oldest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

Pemeriksaan Singkat

Uji pemahaman Anda tentang biaya per koneksi di PostgreSQL.

Jawab di pelajaran interaktif

Rangkuman: Mengapa Koneksi Itu Mahal

Hal-hal penting dari pelajaran ini:

PostgreSQL menggunakan model satu proses per koneksi—setiap koneksi adalah backend OS hasil fork, bukan utas murah.
Membuka koneksi menimbulkan biaya nyata untuk fork, autentikasi, dan pemanasan tembolok sebelum kueri apa pun dijalankan.
work_mem dan temp_buffers berlaku per backend, per operasi, sehingga memori berlipat ganda seiring jumlah koneksi dan operasi per kueri.
Koneksi menganggur tetap menahan slot dan memori serta menambah beban snapshot; kondisi menganggur dalam transaksi dapat menghambat vacuum.
Terlalu banyak backend aktif menyebabkan pergantian konteks dan perebutan kunci, sehingga throughput mencapai puncaknya di sekitar jumlah inti.
Solusinya adalah pengumpulan koneksi (PgBouncer): gunakan ulang sejumlah kecil backend yang siap digunakan, bukan satu backend untuk setiap klien.

Gratis untuk memulai

Belajar SQL dengan tutor AI — gratis

Tulis dan jalankan kode asli di browser kamu, dapatkan bantuan instan dari tutor AI 24/7, dan lanjutkan di mana kamu tinggalkan di web atau aplikasi.

Kursus: 22
Pelajaran: 88

Mulai belajar gratis Lihat kursus lengkapnya

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Mengapa Koneksi Mahal di PostgreSQL” gratis?

Ya — teks lengkap “Mengapa Koneksi Mahal di PostgreSQL” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus PostgreSQL Performance & Query Optimization, upgrade ke CoddyKit PRO. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Mengapa Koneksi Mahal di PostgreSQL”?

Pahami biaya memori dan penjadwalan per backend yang membuat pooling penting pada skala besar. Kamu berlatih PostgreSQL Performance & Query Optimization dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai PostgreSQL Performance & Query Optimization?

Tidak diperlukan pengalaman sebelumnya. PostgreSQL Performance & Query Optimization di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 1 dari 4.

Berapa lama pelajaran “Mengapa Koneksi Mahal di PostgreSQL” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran PostgreSQL Performance & Query Optimization ini?

Ya. Setiap pelajaran PostgreSQL Performance & Query Optimization menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Mengapa Koneksi Mahal di PostgreSQL
Mode Pooling Transaksi vs Sesi
Menentukan Ukuran Pool Berdasarkan Jumlah Inti CPU
Mendiagnosis Kejenuhan dan Antrean Pool

← Kembali ke PostgreSQL Performance & Query Optimization