Database Connection Pooling

Chapter Connection Pooling di section administrasi sudah membedah fakta dasarnya: satu koneksi PostgreSQL berarti satu proses backend di server, dan membuka koneksi baru berarti membayar fork(), autentikasi, dan memori sejak detik pertama. Kesimpulan itu kini menemui kenyataan aplikasi: empat bahasa di section ini masing-masing menyediakan pool di sisi aplikasi, dan tugas chapter ini adalah mengoperasikannya dengan sadar — bukan sekadar memanggil new Pool(). Kita akan menghitung anggaran koneksi terhadap max_connections, membuktikan secara nyata bahwa pool mendaur ulang sesi server yang sama (di Node.js dengan node-postgres dan di Python dengan psycopg_pool), menentukan ukuran pool yang sehat, dan menutup dengan koordinasi lintas instance: kapan pool aplikasi cukup dan kapan PgBouncer harus ikut bermain.

Kenapa Aplikasi Perlu Pool #

Tanpa pool, setiap permintaan yang butuh database menjalankan siklus lengkap: buka koneksi — bayar fork() di server — jalankan query — tutup koneksi. Biaya itu justru paling terasa saat server sedang sibuk, karena setiap request membayar ongkos yang sama persis di detik-detik termahal. Pool memutus siklus itu dengan menyimpan sejumlah koneksi yang sudah terbentuk, meminjamkannya saat dibutuhkan, dan mengembalikannya ke rak setelah dipakai — koneksi server yang sama bekerja berulang kali tanpa pernah dibongkar.

Dua konsekuensi mengikuti pola pinjam-pakai-kembalikan ini. Pertama, jumlah koneksi ke server menjadi angka yang bisa dianggarkan, bukan hasil kebetulan beban. Kedua, state yang menempel di sesi — parameter SET, prepared statement, temporary table — ikut bertahan hidup antar-pinjaman; hal ini berguna (bukti daur ulang di bawah memanfaatkannya) sekaligus jadi sumber kejutan bila tidak disadari.

Matematika Anggaran Koneksi #

Pool yang terasa “canggih” bisa jadi justru bom waktu: dua belas instance aplikasi masing-masing memelihara pool 100 koneksi menuntut 1.200 koneksi server — jauh melampaui max_connections default 100. File berikut membaca batas server latihan lalu menghitung empat skenario penyebaran Toko Maju Jaya:

-- Matematika anggaran koneksi: jumlah instance aplikasi dikali
-- ukuran pool per instance, dibandingkan batas max_connections
-- server latihan.
SELECT name, setting
FROM pg_settings
WHERE name = 'max_connections'
ORDER BY name;

-- Empat skenario penyebaran aplikasi Toko Maju Jaya.
-- Baris terakhir memperlihatkan jebakan "pool besar = cepat":
-- 1.200 koneksi jauh melampaui max_connections 100.
WITH skenario (instansi, ukuran_pool) AS (
    VALUES (1, 10), (4, 10), (12, 20), (12, 100)
)
SELECT instansi,
       ukuran_pool,
       instansi * ukuran_pool AS total_koneksi
FROM skenario
ORDER BY instansi, ukuran_pool;

Output yang Dihasilkan:

      name       | setting 
-----------------+---------
 max_connections | 100
(1 row)

 instansi | ukuran_pool | total_koneksi 
----------+-------------+---------------
        1 |          10 |            10
        4 |          10 |            40
       12 |          20 |           240
       12 |         100 |          1200
(4 rows)

Skenario ketiga (12 instance × pool 20 = 240 koneksi) sudah melewati batas 100 — koneksi ke-101 akan ditolak dengan error sorry, too many clients already — padahal setiap pool-nya sendiri masih terlihat “kecil”. Diagram berikut memperlihatkan posisi pool di arsitektur dan mengapa perkaliannya menentukan:

Aturan anggarannya satu kalimat: total koneksi seluruh instance ditambah cadangan untuk admin dan tooling (psql, backup, monitoring) harus tetap di bawah max_connections, dengan sisa lega untuk superuser_reserved_connections.

Pool Node.js: Bukti Daur Ulang #

Dokumentasi node-postgres menyebut pool mendaur ulang koneksi; file berikut membuktikannya secara empiris. Triknya memakai parameter sesi app.nama: perintah SET hanya hidup di sesi koneksi tempat ia dijalankan, jadi bila nilai itu masih terbaca pada pinjaman kedua, pinjaman kedua pasti mendarat di sesi server yang sama:

import pg from "pg";

// Pool dengan maksimum 2 koneksi. Parameter koneksi mengikuti
// variabel environment PG* -- konvensi libpq yang sama dengan psql.
const pool = new pg.Pool({
  host: process.env.PGHOST ?? "localhost",
  port: process.env.PGPORT ?? 5432,
  user: process.env.PGUSER ?? "postgres",
  database: process.env.PGDATABASE ?? "toko_db",
  max: 2,
  idleTimeoutMillis: 30000,
});

// Pinjaman pertama: setel parameter sesi, lalu kembalikan koneksi
// ke pool dengan release(). SET hanya hidup di sesi koneksi itu.
const a = await pool.connect();
try {
  await a.query("SET app.nama = 'pinjaman-pertama'");
  console.log("Pinjaman 1: app.nama disetel");
} finally {
  a.release();
}

// Pinjaman kedua: bila pool mendaur ulang koneksi yang sama, nilai
// parameter sesi di atas masih terbaca. current_setting membuktikan
// bahwa pinjaman kedua mendarat di sesi server yang sama.
const b = await pool.connect();
try {
  const hasil = await b.query("SELECT current_setting('app.nama') AS nilai");
  console.log("Pinjaman 2: nilai app.nama =", hasil.rows[0].nilai);
} finally {
  b.release();
}

// pool.query: pinjam koneksi, jalankan, kembalikan -- satu baris
// kode. Empat query serentak dilayani dua koneksi yang sama.
const tugas = [1, 2, 3, 4].map((n) =>
  pool.query("SELECT $1::int AS nomor", [n]),
);
for (const hasil of await Promise.all(tugas)) {
  console.log("query serentak selesai:", hasil.rows[0].nomor);
}

// Tutup seluruh koneksi pool saat aplikasi berhenti.
await pool.end();

Output yang Dihasilkan:

Pinjaman 1: app.nama disetel
Pinjaman 2: nilai app.nama = pinjaman-pertama
query serentak selesai: 1
query serentak selesai: 2
query serentak selesai: 3
query serentak selesai: 4

Output-nya memperlihatkan tiga fakta sekaligus. Nilai app.nama dari pinjaman pertama masih terbaca oleh pinjaman kedua — koneksi server yang sama dipakai ulang, bukan dibuka baru. Empat query serentak selesai semua padahal pool hanya berisi dua koneksi: query yang datang saat kedua koneksi sedang dipakai menunggu di antrean pool. Terakhir, blok finally { a.release() } adalah bagian yang paling sering terlupa — koneksi yang tidak direlease bocor dari pool, dan setelah max kebocoran, seluruh aplikasi menggantung menunggu koneksi yang tidak pernah kembali.

Pool Python: psycopg_pool #

Di sisi Python, paket psycopg_pool menyediakan ConnectionPool untuk psycopg 3 dengan pola yang setara: pool dibuat sekali di startup, pool.connection() menjadi context manager yang meminjam dan mengembalikan koneksi, dan transaksi di-commit otomatis saat blok keluar tanpa error. File berikut menyiapkan bahan latihan sendiri lalu membuktikan dua hal — daur ulang sesi lewat SET/current_setting, dan pool dua koneksi yang melayani empat pekerja serentak lewat ThreadPoolExecutor:

import os
from concurrent.futures import ThreadPoolExecutor

import psycopg
from psycopg_pool import ConnectionPool

# Parameter koneksi mengikuti variabel environment PG* --
# konvensi libpq yang sama dengan psql.
KONEKSI = dict(
    host=os.environ.get("PGHOST", "localhost"),
    port=os.environ.get("PGPORT", "5432"),
    user=os.environ.get("PGUSER", "postgres"),
    dbname=os.environ.get("PGDATABASE", "toko_db"),
)

# Bahan latihan: tabel pencatat kunjungan halaman Toko Maju Jaya.
with psycopg.connect(**KONEKSI) as conn:
    conn.execute("DROP TABLE IF EXISTS kunjungan")
    conn.execute(
        "CREATE TABLE kunjungan ("
        " id integer PRIMARY KEY,"
        " halaman text NOT NULL)"
    )

# Pool dengan tepat satu koneksi: dua pinjaman berurutan ternyata
# mendarat di sesi server yang sama.
with ConnectionPool(kwargs=KONEKSI, min_size=1, max_size=1, open=True) as pool:
    with pool.connection() as conn:
        conn.execute("SET app.nama = 'pinjaman-pertama'")
        conn.execute(
            "INSERT INTO kunjungan (id, halaman) VALUES (1, '/beranda')"
        )
    with pool.connection() as conn:
        nilai = conn.execute(
            "SELECT current_setting('app.nama') AS nilai"
        ).fetchone()[0]
        print("Pinjaman 2 melihat app.nama =", nilai)

# Pool dengan dua koneksi melayani empat pekerja serentak;
# pekerja yang datang saat kedua koneksi dipakai menunggu di
# antrean pool, bukan membuka koneksi baru.
with ConnectionPool(kwargs=KONEKSI, min_size=2, max_size=2, open=True) as pool:
    def catat(nomor):
        with pool.connection() as conn:
            conn.execute(
                "INSERT INTO kunjungan (id, halaman) VALUES (%s, %s)",
                (nomor, f"/produk/{nomor}"),
            )

    with ThreadPoolExecutor(max_workers=4) as eksekutor:
        list(eksekutor.map(catat, [2, 3, 4, 5]))

    with pool.connection() as conn:
        total = conn.execute("SELECT count(*) FROM kunjungan").fetchone()[0]
        print("Kunjungan tercatat:", total)

    statistik = pool.get_stats()
    print(
        "Ukuran pool:",
        statistik["pool_size"],
        "dari maksimum",
        statistik["pool_max"],
    )

Output yang Dihasilkan:

Pinjaman 2 melihat app.nama = pinjaman-pertama
Kunjungan tercatat: 5
Ukuran pool: 2 dari maksimum 2

Bukti daur ulangnya sama dengan versi Node.js: app.nama yang disetel pada pinjaman pertama masih terbaca pinjaman kedua. Empat pekerja serentak menghasilkan empat baris kunjungan padahal pool hanya punya dua koneksi — INSERT dari pekerja yang menunggu tetap tercatat setelah koneksinya giliran tiba. pool.get_stats() menutup contoh dengan angka yang bisa dipantau di produksi: pool_size berapa koneksi yang sedang dipelihara, pool_available berapa yang siap dipinjam, requests_waiting berapa permintaan yang sedang mengantre.

Menentukan Ukuran Pool #

Insting awal banyak pengembang: “pool besar = banyak koneksi siap = cepat”. Realitanya terbalik. Koneksi yang menganggur tetap memakan memori server tanpa kerja, dan 100 koneksi yang serentak menjalankan operasi berat justru saling berebut CPU dan I/O lebih parah daripada 10 koneksi yang bergantian. Panduan praktisnya:

Aturan cepat untuk posisi awal: jumlah koneksi aktif maksimum ≈ (jumlah core server × 2) + jumlah disk, dibagi rata antar instance aplikasi, lalu validasi totalnya terhadap max_connections seperti pada file anggaran di atas. Angka inti server latihan hanya dua — di server sekecil itu pun pool 5–10 per instance sudah lebih dari cukup untuk beban course ini.

Koordinasi dengan PgBouncer #

Pool aplikasi mengelola koneksi untuk satu instance. Begitu instance bertambah banyak — klaster autoscaling, ratusan serverless function yang bangun serentak — anggaran N instance × ukuran pool pecah, dan di titik itu pooler tingkat server seperti PgBouncer mengambil alih: banyak klien berbagi sedikit koneksi server yang benar-benar terbuka, sebagaimana dibedah di chapter Connection Pooling .

Kombinasi pool aplikasi dan PgBouncer bukan pilihan salah-satu. Pool aplikasi tetap berguna untuk mengatur antrean lokal dan membatasi satu instance; PgBouncer menyeimbangkan total lintas instance terhadap anggaran server. Yang wajib disadari: mode transaction pooling PgBouncer memutus fitur yang hidup lebih lama dari satu transaksi — prepared statement dan SET sesi bisa tidak lagi menempel pada koneksi server yang sama antar query.

Dengan pool yang terkelola, aplikasi sampai ke database lewat jalur yang murah dan terukur. Persoalan berikutnya menyentuh kebiasaan yang selalu muncul di chapter-chapter bahasa tadi: query yang sama dijalankan berulang kali dengan parameter berbeda. PostgreSQL menyimpan rencana eksekusinya sekali lewat prepared statement — dan perilakunya di balik pool, PgBouncer, serta tiap driver adalah materi chapter berikutnya.

Referensi #

• node-postgres — Pooling
• psycopg_pool — Connection pools
• Connection and Authentication — max_connections

-- Matematika anggaran koneksi: jumlah instance aplikasi dikali
-- ukuran pool per instance, dibandingkan batas max_connections
-- server latihan.
SELECT name, setting
FROM pg_settings
WHERE name = 'max_connections'
ORDER BY name;

-- Empat skenario penyebaran aplikasi Toko Maju Jaya.
-- Baris terakhir memperlihatkan jebakan "pool besar = cepat":
-- 1.200 koneksi jauh melampaui max_connections 100.
WITH skenario (instansi, ukuran_pool) AS (
    VALUES (1, 10), (4, 10), (12, 20), (12, 100)
)
SELECT instansi,
       ukuran_pool,
       instansi * ukuran_pool AS total_koneksi
FROM skenario
ORDER BY instansi, ukuran_pool;

import os
from concurrent.futures import ThreadPoolExecutor

import psycopg
from psycopg_pool import ConnectionPool

# Parameter koneksi mengikuti variabel environment PG* --
# konvensi libpq yang sama dengan psql.
KONEKSI = dict(
    host=os.environ.get("PGHOST", "localhost"),
    port=os.environ.get("PGPORT", "5432"),
    user=os.environ.get("PGUSER", "postgres"),
    dbname=os.environ.get("PGDATABASE", "toko_db"),
)

# Bahan latihan: tabel pencatat kunjungan halaman Toko Maju Jaya.
with psycopg.connect(**KONEKSI) as conn:
    conn.execute("DROP TABLE IF EXISTS kunjungan")
    conn.execute(
        "CREATE TABLE kunjungan ("
        " id integer PRIMARY KEY,"
        " halaman text NOT NULL)"
    )

# Pool dengan tepat satu koneksi: dua pinjaman berurutan ternyata
# mendarat di sesi server yang sama.
with ConnectionPool(kwargs=KONEKSI, min_size=1, max_size=1, open=True) as pool:
    with pool.connection() as conn:
        conn.execute("SET app.nama = 'pinjaman-pertama'")
        conn.execute(
            "INSERT INTO kunjungan (id, halaman) VALUES (1, '/beranda')"
        )
    with pool.connection() as conn:
        nilai = conn.execute(
            "SELECT current_setting('app.nama') AS nilai"
        ).fetchone()[0]
        print("Pinjaman 2 melihat app.nama =", nilai)

# Pool dengan dua koneksi melayani empat pekerja serentak;
# pekerja yang datang saat kedua koneksi dipakai menunggu di
# antrean pool, bukan membuka koneksi baru.
with ConnectionPool(kwargs=KONEKSI, min_size=2, max_size=2, open=True) as pool:
    def catat(nomor):
        with pool.connection() as conn:
            conn.execute(
                "INSERT INTO kunjungan (id, halaman) VALUES (%s, %s)",
                (nomor, f"/produk/{nomor}"),
            )

    with ThreadPoolExecutor(max_workers=4) as eksekutor:
        list(eksekutor.map(catat, [2, 3, 4, 5]))

    with pool.connection() as conn:
        total = conn.execute("SELECT count(*) FROM kunjungan").fetchone()[0]
        print("Kunjungan tercatat:", total)

    statistik = pool.get_stats()
    print(
        "Ukuran pool:",
        statistik["pool_size"],
        "dari maksimum",
        statistik["pool_max"],
    )