Optimasi Query PostgreSQL & MongoDB untuk Dashboard Akuntansi

Ringkasan

• Strategi indexing komposit dan covering index untuk mempercepat filter rentang tanggal dan status transaksi keuangan.

• Solusi konkret mengatasi N+1 Query Problem menggunakan JOIN di PostgreSQL dan denormalisasi selektif di MongoDB.

• Teknik agregasi tingkat lanjut menggunakan Materialized Views dan Aggregation Pipeline untuk laporan laba rugi yang instan.

• Checklist optimasi performa database untuk memastikan dashboard akuntansi tetap responsif meski data mencapai jutaan baris.

Saya masih ingat betul wajah klien yang panik saat dashboard akuntansi mereka butuh 8–12 detik hanya untuk menampilkan laporan laba rugi bulanan. Data belum sampai 500 ribu transaksi, tapi sudah terasa lambat. Setelah dicek, masalahnya klasik: indexing yang salah, N+1 query di mana-mana, dan agregasi yang dilakukan di application layer, bukan di database.

Sejak saat itu, setiap kali saya membangun atau mengoptimasi dashboard akuntansi—baik menggunakan PostgreSQL maupun MongoDB—fokus utamanya selalu sama: membuat laporan muncul dalam hitungan milidetik, bukan detik. Artikel ini merangkum strategi mendalam yang sering saya terapkan dan terbukti efektif di lingkungan production.

Mengapa Dashboard Akuntansi Mudah Mengalami Slow Query?

Data keuangan memiliki karakteristik khusus yang membuatnya rentan terhadap penurunan performa jika tidak dikelola dengan benar. Berbeda dengan data blog atau e-commerce sederhana, data akuntansi memiliki pola akses sebagai berikut:

• Filter Rentang Tanggal yang Intensif: Hampir semua laporan (Neraca, Laba Rugi, Arus Kas) bergantung pada filter transaction_date.

• Agregasi Berat: Penggunaan fungsi SUM, COUNT, AVG, dan GROUP BY terjadi secara masif untuk menghitung saldo akun.

• Relasi Data yang Dalam: Alur data biasanya mengalir dari Transaksi → Jurnal → Akun → Kategori/Header Akun.

• Analisis Komparatif: User sering membandingkan periode saat ini dengan periode sebelumnya (Month-over-Month atau Year-to-Date), yang berarti database harus menjalankan dua atau lebih query agregasi besar secara simultan.

Jika query tidak dirancang dengan baik, database akan terpaksa melakukan sequential scan (membaca seluruh isi tabel) atau mengirim data mentah dalam jumlah besar ke aplikasi, yang kemudian membebani RAM server aplikasi dan memperlambat waktu respon (latency).

1. Strategi Indexing yang Tepat dan Efisien

Indexing adalah fondasi performa. Namun, perlu diingat bahwa indexing yang berlebihan justru bisa memperlambat operasi penulisan (WRITE), sementara sistem akuntansi seringkali memiliki volume transaksi yang tinggi.

Optimasi pada PostgreSQL

Untuk tabel transaksi keuangan, saya merekomendasikan penggunaan composite index. Urutan kolom dalam index sangat menentukan efektivitasnya.

-- Index untuk filter tanggal + status (paling sering dipakai untuk dashboard utama)
CREATE INDEX CONCURRENTLY idx_transactions_date_status 
ON transactions (transaction_date, status);

-- Index untuk laporan per akun dengan pengurutan terbaru
CREATE INDEX CONCURRENTLY idx_transactions_account_date 
ON transactions (account_id, transaction_date DESC);

-- Composite index untuk laporan laba rugi yang melibatkan tipe akun dan perusahaan
CREATE INDEX CONCURRENTLY idx_journal_entries_report 
ON journal_entries (entry_date, account_type, company_id);

Tips Pro PostgreSQL:

• CONCURRENTLY: Selalu gunakan keyword ini saat membuat index di production agar tabel tidak terkunci (lock) dan aplikasi tetap bisa berjalan.

• Covering Index (INCLUDE): Pada PostgreSQL 11+, gunakan klausa INCLUDE untuk menambahkan kolom yang sering dipanggil namun tidak digunakan untuk filter. Ini memungkinkan Index Only Scan, sehingga database tidak perlu membaca heap table.

• EXPLAIN ANALYZE: Jangan menebak. Gunakan EXPLAIN (ANALYZE, BUFFERS) untuk melihat apakah index benar-benar digunakan atau justru diabaikan oleh query planner.

Optimasi pada MongoDB

Di MongoDB, compound index sangat krusial karena query biasanya lebih fleksibel dan tidak kaku seperti SQL.

// Index untuk filter tanggal per perusahaan
db.transactions.createIndex({ companyId: 1, transactionDate: -1 })

// Index untuk agregasi saldo per akun
db.transactions.createIndex({ companyId: 1, accountId: 1, transactionDate: -1 })

// Index untuk status transaksi dan tanggal
db.transactions.createIndex({ status: 1, transactionDate: -1 })

Aturan emas di MongoDB adalah mengikuti pola ESR (Equality, Sort, Range). Letakkan field yang menggunakan operator equality (seperti companyId) di depan, diikuti oleh field untuk sorting, dan terakhir field untuk range (seperti transactionDate).

2. Menghindari N+1 Query Problem

N+1 Query adalah musuh terbesar dashboard yang dibangun menggunakan ORM seperti Prisma, TypeORM, atau Mongoose. Masalah ini terjadi ketika aplikasi mengambil daftar utama (1 query), lalu melakukan query tambahan untuk setiap item dalam daftar tersebut (N query).

Contoh Buruk (N+1)

// Mengambil semua transaksi (1 query)
const transactions = await Transaction.find({ companyId });

// Melakukan loop untuk mengambil nama akun (N query) - SANGAT LAMBAT
for (const tx of transactions) {
  tx.account = await Account.findById(tx.accountId);
}

Solusi Efektif

PostgreSQL (JOIN Approach): Gunakan JOIN untuk menarik semua data yang dibutuhkan dalam satu kali request ke database.

SELECT 
  t.*, 
  a.name AS account_name, 
  a.code AS account_code
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.company_id = $1
  AND t.transaction_date BETWEEN $2 AND $3;

MongoDB (Aggregation $lookup): Gunakan pipeline agregasi untuk menggabungkan koleksi.

db.transactions.aggregate([
  { $match: { companyId: ObjectId("..."), transactionDate: { $gte: start, $lte: end } } },
  { 
    $lookup: { 
      from: "accounts", 
      localField: "accountId", 
      foreignField: "_id", 
      as: "account" 
    } 
  },
  { $unwind: "$account" }
])

Strategi Denormalisasi Selektif: Untuk data yang jarang berubah namun sering diakses (seperti accountName atau accountCode), saya menyarankan untuk menyimpan salinan data tersebut langsung di dalam dokumen transaksi. Meskipun melanggar prinsip normalisasi, trade-off ini memberikan peningkatan performa yang signifikan karena menghilangkan kebutuhan akan JOIN atau $lookup.

3. Teknik Agregasi Data Keuangan untuk Performa Milidetik

Jangan pernah menarik ribuan baris data ke Node.js atau Python hanya untuk menjumlahkannya menggunakan .reduce() atau loop. Biarkan database yang melakukan perhitungan karena mereka dioptimalkan untuk hal tersebut.

PostgreSQL: Materialized Views & Window Functions

Untuk laporan yang tidak harus real-time detik itu juga (misalnya laporan bulanan), Materialized View adalah penyelamat. Ia menyimpan hasil query secara fisik di disk.

CREATE MATERIALIZED VIEW mv_monthly_profit_loss AS
SELECT 
  company_id, 
  date_trunc('month', entry_date) AS month, 
  account_type, 
  SUM(debit) AS total_debit, 
  SUM(credit) AS total_credit, 
  SUM(credit - debit) AS net_amount
FROM journal_entries
GROUP BY 1, 2, 3;

CREATE UNIQUE INDEX ON mv_monthly_profit_loss (company_id, month, account_type);

Anda bisa melakukan REFRESH MATERIALIZED VIEW CONCURRENTLY setiap 15 menit atau setelah proses closing periode akuntansi selesai.

MongoDB: Aggregation Pipeline yang Efisien

Kunci dari MongoDB Aggregation adalah memfilter data sebanyak mungkin di tahap awal.

db.transactions.aggregate([
  { 
    $match: { 
      companyId: ObjectId("..."), 
      transactionDate: { $gte: ISODate("2025-01-01"), $lte: ISODate("2025-12-31") },
      status: "posted"
    } 
  },
  { 
    $group: { 
      _id: { 
        month: { $dateToString: { format: "%Y-%m", date: "$transactionDate" } },
        accountId: "$accountId"
      },
      totalDebit: { $sum: "$debit" },
      totalCredit: { $sum: "$credit" },
      count: { $sum: 1 }
    } 
  },
  { 
    $lookup: { 
      from: "accounts", 
      localField: "_id.accountId", 
      foreignField: "_id", 
      as: "account" 
    } 
  },
  { $sort: { "_id.month": 1 } }
])

Perbandingan Strategi Optimasi

Masalah	PostgreSQL Solution	MongoDB Solution	Dampak Performa
Filter Tanggal & Status	Composite Index	Compound Index (ESR)	Sangat Tinggi
N+1 Query	JOIN / Prisma include	$lookup / Denormalisasi	Tinggi
Agregasi Bulanan	Materialized View	Aggregation Pipeline	Sangat Tinggi
Laporan Real-time	Partial Index + Caching	Covered Query + Caching	Tinggi
Write-heavy Period	Index Minimalis	Background Indexing	Sedang

Checklist Optimasi Akhir

Sebelum melakukan deploy ke production, pastikan Anda telah melewati checklist berikut:

• Semua query laporan utama telah diuji dengan EXPLAIN ANALYZE.

• Index dibuat berdasarkan pola filter yang paling sering muncul (bukan asal buat index di semua kolom).

• Tidak ada loop query (N+1) di endpoint dashboard utama.

• Perhitungan total/sum dilakukan di level database.

• Implementasi caching (seperti Redis) untuk data yang jarang berubah (misal: daftar akun).

• Monitoring slow query aktif menggunakan pg_stat_statements (Postgres) atau MongoDB Profiler.

Penutup

Dashboard akuntansi yang cepat bukan hanya soal kenyamanan teknis, tetapi juga soal membangun kepercayaan pengguna. Ketika seorang CFO atau akuntan bisa melihat laporan laba rugi muncul dalam 200–400 ms, mereka akan merasa sistem tersebut handal dan profesional.

Kombinasi antara indexing yang presisi, penghindaran N+1, dan pemanfaatan fitur agregasi bawaan database sudah cukup untuk membuat sebagian besar dashboard akuntansi terasa sangat responsif, bahkan dengan volume data jutaan baris. Jika Anda masih mengalami bottleneck, jangan ragu untuk meninjau kembali skema database dan pola query Anda.

Perlu aplikasi akunting yang dapat disesuaikan dengan kebutuhan perusahaanmu? mari kita diskusikan kebutuhanya disini https://www.pradha.id/products/smart-accounting