Kalkulasi saldo akun pada sistem pembukuan berpasangan (double-entry bookkeeping) bergantung pada agregasi data historis dari tabel jurnal atau mutasi (split ledger entries). Seiring pertumbuhan data hingga jutaan baris, eksekusi query SUM(amount) secara reguler memicu high disk I/O, cache thrashing, dan peningkatan latensi yang signifikan.

Masalah utama bukan terletak pada operasi aritmatika SUM(), melainkan pola akses data pada heap page PostgreSQL. Mengatasi slow query ledger finansial memerlukan desain indeks yang presisi serta strategi partisi data komputasi melalui balance checkpoints.

Skema Database Double-Entry (GnuCash Pattern)

Pola perancangan buku besar ini memisahkan transaksi (informasi tingkat tinggi seperti tanggal dan deskripsi) dengan rincian pemindahan saldo (entries). Setiap transaksi wajib memiliki minimal dua entry yang jika dijumlahkan menghasilkan nilai nol (debit = kredit).

CREATE TABLE accounts (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    account_type VARCHAR(32) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'IDR',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    reference_num VARCHAR(100) UNIQUE,
    post_date TIMESTAMPTZ NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE entries (
    id BIGSERIAL PRIMARY KEY,
    transaction_id BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    post_date TIMESTAMPTZ NOT NULL,
    amount NUMERIC(18, 4) NOT NULL,
    description TEXT
);

-- Invariant: Redundansi post_date pada entries diwajibkan demi performa filter range
CREATE INDEX idx_entries_transaction_id ON entries(transaction_id);

Kolom post_date diduplikasi ke tabel entries untuk menghindari operasi JOIN ke tabel transactions setiap kali query saldo berbasis periode dieksekusi.

Bottleneck Agregasi Saldo Historis

Query standar untuk mendapatkan saldo suatu akun per tanggal tertentu menggunakan agregasi:

SELECT COALESCE(SUM(amount), 0) AS balance
FROM entries
WHERE account_id = 42
  AND post_date <= '2025-01-31 23:59:59+00';

Jika tabel hanya memiliki indeks default pada foreign key (account_id), eksekusi query menghasilkan execution plan yang tidak efisien:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COALESCE(SUM(amount), 0) AS balance
FROM entries
WHERE account_id = 42
  AND post_date <= '2025-01-31 23:59:59+00';

-- Execution Plan:
Aggregate  (cost=18432.10..18432.11 rows=1 width=32) (actual time=142.311..142.312 rows=1 loops=1)
  Buffers: shared hit=410 read=12980
  ->  Bitmap Heap Scan on entries  (cost=312.45..18320.10 rows=44800 width=9) (actual time=8.120..135.200 rows=45120 loops=1)
        Recheck Cond: (account_id = 42)
        Filter: (post_date <= '2025-01-31 23:59:59+00'::timestamptz)
        Buffers: shared hit=410 read=12980
        ->  Bitmap Index Scan on idx_entries_account_id  (cost=0.00..301.25 rows=48000 width=0) (actual time=6.450..6.450 rows=45120 loops=1)
              Index Cond: (account_id = 42)
              Buffers: shared hit=12 read=85
Planning Time: 0.182 ms
Execution Time: 142.365 ms

Penyebab latensi:

  • Bitmap Heap Scan: Database menemukan pointer baris dari indeks, lalu membaca heap page secara acak untuk memvalidasi post_date dan mengambil nilai amount.
  • Tingginya I/O (Buffers Read): Sebanyak 12.980 blok data harus dibaca dari memori/disk hanya untuk menghitung satu angka akumulasi.

Optimasi 1: Covering Index (Index-Only Scan)

PostgreSQL menyediakan fitur covering index menggunakan klausa INCLUDE. Indeks ini menyimpan kolom amount langsung pada leaf node B-Tree tanpa memasukkannya ke dalam kunci pohon pencarian.

-- Drop indeks parsial lama jika ada
DROP INDEX IF EXISTS idx_entries_account_id;

-- Buat covering index terarah
CREATE INDEX idx_entries_balance_lookup
ON entries (account_id, post_date)
INCLUDE (amount);

Kriteria urutan indeks:

  1. account_id (Equality): Penyaring utama berbasis kardinalitas akun.
  2. post_date (Range): Memungkinkan traversal langsung ke batas rentang tanggal tanpa membaca seluruh entri akun.
  3. INCLUDE (amount) (Payload): Membawa nilai yang diagregasi ke struktur indeks.

Hasil EXPLAIN ANALYZE setelah penerapan covering index:

Aggregate  (cost=1250.40..1250.41 rows=1 width=32) (actual time=11.210..11.211 rows=1 loops=1)
  Buffers: shared hit=342
  ->  Index Only Scan using idx_entries_balance_lookup on entries  (cost=0.56..1137.60 rows=45120 width=9) (actual time=0.045..7.810 rows=45120 loops=1)
        Index Cond: ((account_id = 42) AND (post_date <= '2025-01-31 23:59:59+00'::timestamptz))
        Heap Fetches: 0
        Buffers: shared hit=342
Planning Time: 0.150 ms
Execution Time: 11.250 ms

Waktu eksekusi terpangkas lebih dari 90% (dari ~142ms ke ~11ms). Nilai Heap Fetches: 0 mengonfirmasi bahwa PostgreSQL tidak menyentuh tabel utama sama sekali (Index-Only Scan penuh).

Penting: Index-Only Scan memerlukan Visibility Map yang selalu terbarui. Jalankan VACUUM secara teratur atau pastikan daemon autovacuum dikonfigurasi agresif pada tabel dengan mutasi tinggi.

Optimasi 2: Pola Checkpoint Saldo Berkala

Meskipun Index-Only Scan sangat cepat untuk puluhan ribu baris, kalkulasi akun aktif yang memiliki jutaan baris historis tetap akan mengalami degradasi kinerja seiring berjalannya tahun (karena ukuran leaf node index yang harus dipindai tetap linear).

Solusinya adalah membuat tabel checkpoint (snapshot saldo) periodik (misal: penutupan buku akhir bulan).

CREATE TABLE account_balance_checkpoints (
    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    checkpoint_date TIMESTAMPTZ NOT NULL,
    balance NUMERIC(18, 4) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (account_id, checkpoint_date)
);

Query Saldo Gabungan (Checkpoint + Mutasi Berjalan)

Untuk mengetahui saldo akun per titik waktu tertentu, ambil saldo dari checkpoint terakhir, lalu agregasikan hanya sisa mutasi (delta) dari waktu checkpoint hingga waktu target.

WITH latest_checkpoint AS (
    SELECT checkpoint_date, balance
    FROM account_balance_checkpoints
    WHERE account_id = 42
      AND checkpoint_date <= '2025-02-15 10:00:00+00'
    ORDER BY checkpoint_date DESC
    LIMIT 1
),
delta_entries AS (
    SELECT COALESCE(SUM(e.amount), 0) AS delta_balance
    FROM entries e
    LEFT JOIN latest_checkpoint cp ON true
    WHERE e.account_id = 42
      AND e.post_date > COALESCE(cp.checkpoint_date, '-infinity'::timestamptz)
      AND e.post_date <= '2025-02-15 10:00:00+00'
)
SELECT 
    COALESCE((SELECT balance FROM latest_checkpoint), 0) + 
    (SELECT delta_balance FROM delta_entries) AS current_balance;

Dampaknya, sistem hanya membaca puluhan baris entri mutasi delta alih-alih seluruh baris sejak akun pertama kali dibuka. Query ini memiliki kompleksitas waktu nyaris konstan O(k) di mana k adalah jumlah mutasi sejak checkpoint terakhir.

Trade-off dan Mitigasi

  • Write Overhead: Penambahan kolom pada INCLUDE memperbesar ukuran indeks B-Tree. Untuk tabel dengan throughput penulisan jutaan row per jam, batasi indeks hanya pada kolom yang benar-benar esensial untuk jalur query agregasi.
  • Integritas Checkpoint: Snapshot saldo adalah data turunan. Lakukan re-kalkulasi (reconciliation job) secara asynchronous jika terdapat transaksi backdated yang diizinkan masuk ke periode sebelum checkpoint.
  • HOT Updates: Modifikasi data pada baris yang terindeks mematikan optimasi Heap-Only Tuples (HOT). Pastikan tabel entries bersifat append-only (immutable), yang merupakan standar industri akuntansi.