Optimasi Query Metadata Build: Indexing, COUNT, dan Pagination

Pada layanan metadata build atau kompilasi berskala besar, masalah performa database biasanya tidak muncul saat sistem masih kecil. Gejalanya baru terasa ketika tabel artefak, log build, hasil analisis, atau status pipeline tumbuh menjadi jutaan baris: halaman daftar build melambat, API listing timeout, query pencarian artefak melonjak latensinya, dan beban I/O database naik tanpa perubahan logika aplikasi yang berarti.

Optimasi query metadata build hampir selalu berpusat pada tiga hal: pola index yang sesuai dengan query nyata, pengurangan biaya COUNT untuk pagination, dan penggantian OFFSET pagination dengan pendekatan yang lebih stabil di data besar. Jika ditambah strategi arsip atau partisi ringan, banyak bottleneck bisa diturunkan tanpa perlu perubahan arsitektur besar.

Model data yang sering menimbulkan bottleneck

Konteks layanan metadata build mirip dengan sistem compiler besar atau layanan analisis artefak: satu build bisa menghasilkan banyak file, log, hasil lint, hasil analisis dependensi, dan status eksekusi. Skema persisnya berbeda-beda, tetapi pola datanya sering serupa.

-- Tabel build utama
CREATE TABLE builds (
  id BIGINT PRIMARY KEY,
  repo_id BIGINT NOT NULL,
  branch_name VARCHAR(255) NOT NULL,
  commit_sha VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  finished_at TIMESTAMP NULL
);

-- Artefak hasil build
CREATE TABLE artifacts (
  id BIGINT PRIMARY KEY,
  build_id BIGINT NOT NULL,
  artifact_type VARCHAR(64) NOT NULL,
  path TEXT NOT NULL,
  size_bytes BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

-- Log build
CREATE TABLE build_logs (
  id BIGINT PRIMARY KEY,
  build_id BIGINT NOT NULL,
  level VARCHAR(16) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

-- Hasil analisis/lint
CREATE TABLE analysis_results (
  id BIGINT PRIMARY KEY,
  build_id BIGINT NOT NULL,
  file_path TEXT NOT NULL,
  severity VARCHAR(16) NOT NULL,
  rule_id VARCHAR(128) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Tabel seperti build_logs dan analysis_results biasanya tumbuh paling cepat. Di sinilah query listing, filter, dan agregasi mulai mahal jika index tidak mengikuti pola akses aplikasi.

Gejala query lambat yang perlu diaudit

Jangan mulai dari asumsi. Mulailah dari gejala dan query nyata yang paling mahal.

Halaman daftar build makin lambat saat pindah ke halaman 100, 500, atau 1000.
API yang menampilkan artefak terbaru per build tiba-tiba menggunakan CPU database tinggi.
Query COUNT(*) untuk total halaman lebih mahal daripada query daftar datanya sendiri.
Query dengan filter sederhana tetap melakukan full scan pada tabel besar.
Order berdasarkan created_at DESC memicu operasi sort besar di memori atau disk.
Latensi query memburuk setelah menambah banyak index karena write path ikut melambat.

Di PostgreSQL, cek slow query log, pg_stat_statements, dan EXPLAIN (ANALYZE, BUFFERS). Di MySQL, cek slow query log, Performance Schema bila tersedia, dan EXPLAIN. Tujuannya sama: cari query yang sering dipanggil, mahal, atau keduanya.

Cara membaca EXPLAIN untuk query metadata build

EXPLAIN bukan sekadar melihat apakah index dipakai atau tidak. Yang lebih penting adalah memahami berapa banyak baris yang harus dibaca, apakah ada sort tambahan, dan apakah urutan akses data sesuai dengan filter dan order query.

Contoh query lambat

Misalnya UI menampilkan 50 build terbaru untuk satu repository dengan status tertentu.

SELECT id, branch_name, commit_sha, status, created_at
FROM builds
WHERE repo_id = 42
  AND status = 'success'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalah query ini ada dua:

Jika index tidak cocok, database harus memfilter banyak baris lalu melakukan sort.
Meski index cocok, OFFSET 5000 tetap memaksa engine melompati ribuan baris sebelum mengambil 50 baris berikutnya.

Apa yang dicari di EXPLAIN

Scan type: apakah memakai index scan/range scan atau table scan/full scan.
Rows examined: apakah jauh lebih besar daripada baris yang dikembalikan.
Sort step: apakah ada operasi sort eksplisit karena index tidak mendukung ORDER BY.
Filter placement: apakah filter diterapkan awal atau setelah banyak data dibaca.
Join order: bila query melibatkan tabel artefak/log, apakah tabel terbesar dibaca terlalu awal.

Jika query hanya mengembalikan 50 baris tetapi harus membaca ratusan ribu atau jutaan baris, bottleneck biasanya ada pada desain index, pola pagination, atau keduanya.

Memilih index komposit sesuai pola WHERE + ORDER BY

Kesalahan umum adalah menambah index satu kolom untuk setiap kolom filter. Pada query metadata build, itu sering tidak cukup. Yang dibutuhkan biasanya adalah index komposit yang mengikuti pola akses aplikasi: kolom filter utama lebih dulu, lalu kolom pengurutan.

Contoh sebelum: index terpisah yang tidak efektif

CREATE INDEX idx_builds_repo_id ON builds(repo_id);
CREATE INDEX idx_builds_status ON builds(status);
CREATE INDEX idx_builds_created_at ON builds(created_at);

Untuk query berikut, tiga index terpisah di atas sering tidak ideal:

SELECT id, branch_name, commit_sha, status, created_at
FROM builds
WHERE repo_id = 42
  AND status = 'success'
ORDER BY created_at DESC
LIMIT 50;

Database mungkin hanya memakai salah satu index sebagai titik masuk utama, lalu tetap perlu membaca banyak baris dan melakukan sort tambahan.

Contoh sesudah: index komposit yang mengikuti query

CREATE INDEX idx_builds_repo_status_created_at
ON builds(repo_id, status, created_at DESC);

Mengapa ini lebih baik:

repo_id dan status menyaring subset data lebih dulu.
created_at di dalam index membantu memenuhi ORDER BY.
LIMIT 50 bisa berhenti lebih cepat karena engine membaca urutan yang sudah sesuai.

Prinsip praktisnya:

Mulai dari query yang benar-benar sering dipakai.
Letakkan kolom dengan kondisi kesetaraan seperti = di bagian awal index komposit.
Tambahkan kolom ORDER BY setelah kolom filter utama bila urutan datanya penting.
Jangan membuat terlalu banyak variasi index yang mirip, karena akan memperlambat insert/update/delete.

Contoh query artefak per build

SELECT id, artifact_type, path, size_bytes, created_at
FROM artifacts
WHERE build_id = 9001
ORDER BY created_at DESC
LIMIT 100;

Index yang cocok:

CREATE INDEX idx_artifacts_build_created_at
ON artifacts(build_id, created_at DESC);

Jika UI juga sering memfilter berdasarkan tipe artefak:

SELECT id, artifact_type, path, size_bytes, created_at
FROM artifacts
WHERE build_id = 9001
  AND artifact_type = 'binary'
ORDER BY created_at DESC
LIMIT 100;

Maka index alternatif yang lebih sesuai adalah:

CREATE INDEX idx_artifacts_build_type_created_at
ON artifacts(build_id, artifact_type, created_at DESC);

Perhatikan bahwa urutan kolom bergantung pada pola query dominan. Tidak ada satu index yang optimal untuk semua query.

Trade-off: write amplification

Setiap index tambahan membuat operasi tulis lebih mahal:

Insert build/log/artefak harus memperbarui lebih banyak struktur index.
Update kolom yang menjadi bagian index bisa lebih mahal.
Ukuran penyimpanan bertambah dan cache menjadi lebih cepat penuh.

Karena itu, audit index harus menimbang dua sisi: mempercepat query baca yang penting, tetapi tidak membebani pipeline ingest metadata secara berlebihan. Pada sistem build besar, write path sering sama pentingnya dengan read path.

Masalah OFFSET pagination pada data besar

OFFSET terlihat sederhana dan nyaman untuk UI, tetapi kinerjanya memburuk seiring nomor halaman membesar. Engine tetap harus menghitung atau melewati baris-baris sebelumnya sebelum mengembalikan halaman berikutnya.

Contoh yang umum tetapi mahal

SELECT id, repo_id, status, created_at
FROM builds
WHERE repo_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

Walaupun ada index yang baik, database tetap harus berjalan cukup jauh di urutan index untuk melewati 100.000 baris. Pada data besar, ini menambah latensi dan I/O secara linear terhadap nilai OFFSET.

Alternatif: keyset pagination

Keyset pagination memakai nilai dari baris terakhir halaman sebelumnya sebagai penanda untuk mengambil halaman berikutnya. Ini jauh lebih stabil untuk tabel build, artefak, atau log yang diurutkan berdasarkan waktu.

-- Halaman pertama
SELECT id, repo_id, status, created_at
FROM builds
WHERE repo_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Halaman berikutnya, pakai cursor dari baris terakhir halaman sebelumnya
SELECT id, repo_id, status, created_at
FROM builds
WHERE repo_id = 42
  AND (
    created_at < '2026-06-30 10:15:00'
    OR (created_at = '2026-06-30 10:15:00' AND id < 123456)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang mendukung pola ini:

CREATE INDEX idx_builds_repo_created_id
ON builds(repo_id, created_at DESC, id DESC);

Mengapa keyset pagination bekerja lebih baik

Database tidak perlu melompati ribuan atau jutaan baris.
Query langsung melanjutkan dari titik terakhir yang diketahui.
Performa lebih konsisten meski pengguna berada jauh di data lama.

Kapan OFFSET masih masuk akal

Dataset kecil dan stabil.
Kebutuhan UI memang harus melompat langsung ke halaman bernomor tertentu.
Daftar internal/administratif dengan volume rendah.

Namun untuk layanan metadata build besar, default yang lebih aman biasanya adalah keyset pagination untuk endpoint listing utama.

Jebakan SELECT COUNT(*) untuk total halaman

Banyak API pagination menghitung total halaman seperti ini:

SELECT COUNT(*)
FROM builds
WHERE repo_id = 42
  AND status = 'success';

Secara fungsional benar, tetapi pada tabel besar query ini bisa mahal, terutama jika dipanggil di setiap request list. Masalahnya makin buruk bila filter kompleks atau melibatkan join.

Mengapa COUNT bisa menjadi bottleneck

Database tetap harus menghitung jumlah baris yang memenuhi kondisi.
Jika filter tidak sangat selektif, banyak blok data harus dibaca.
Biaya count sering tidak terlihat sampai trafik listing meningkat.

Pilihan mitigasi yang praktis

Jangan tampilkan total halaman jika tidak benar-benar dibutuhkan.
Banyak UI cukup menampilkan tombol next dan previous.
Pakai keyset pagination dan ambil satu baris ekstra.
Ambil LIMIT 51 untuk halaman berukuran 50. Jika ada baris ke-51, berarti masih ada halaman berikutnya.
Cache hasil count untuk filter yang sering dipakai.
Cocok untuk dashboard yang toleran terhadap keterlambatan kecil.
Gunakan tabel ringkasan atau counter teragregasi.
Misalnya jumlah build per repo dan status yang diperbarui secara periodik atau event-driven.
Berikan estimasi, bukan angka presisi.
Untuk beberapa layar observabilitas, estimasi sudah cukup.

Contoh menghindari COUNT di API list

SELECT id, repo_id, status, created_at
FROM builds
WHERE repo_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 51;

Jika hasilnya 51 baris, kirim 50 baris pertama dan set has_next = true. Ini jauh lebih murah daripada menjalankan query daftar ditambah query COUNT(*) setiap kali.

Jika kebutuhan bisnis memang memerlukan total akurat, pisahkan jalur query untuk total dan daftar. Jangan anggap keduanya harus selalu dieksekusi bersama.

Strategi arsip dan partisi ringan

Bila tabel log atau hasil analisis terus tumbuh, optimasi index saja tidak selalu cukup. Data lama yang jarang diakses sebaiknya dipisahkan agar working set aktif tetap kecil.

Arsip sederhana berbasis waktu

Pendekatan paling aman sering kali bukan partisi canggih, melainkan memindahkan data lama ke tabel arsip terpisah.

-- Tabel aktif: build_logs
-- Tabel arsip: build_logs_archive

Strategi ini berguna jika:

90% akses hanya ke data 30-90 hari terakhir.
Data lama tetap perlu disimpan untuk audit, tetapi jarang dibaca.
Tim ingin menghindari kompleksitas operasional yang tinggi.

Kelebihan pendekatan arsip:

Ukuran tabel aktif dan index aktif tetap terkendali.
Vacuum/maintenance dan cache bekerja pada data yang lebih relevan.
Risiko perubahan skema atau query biasanya lebih mudah dikelola.

Kekurangannya:

Query lintas data aktif + arsip perlu logika tambahan.
Proses pemindahan harus aman dan terjadwal.

Partisi ringan berbasis tanggal

Jika volume sangat besar dan pola akses jelas berdasarkan waktu, partisi per bulan atau per rentang waktu bisa membantu pruning data. Namun partisi bukan obat universal.

Partisi membantu jika:

Hampir semua query menyertakan filter waktu.
Data lama sering dihapus atau dipindahkan per rentang waktu.
Tabel tumbuh sangat cepat dan maintenance tabel tunggal mulai berat.

Partisi kurang membantu jika:

Query utama tidak memfilter berdasarkan kolom partisi.
Masalah utama sebenarnya index yang salah atau OFFSET pagination.
Tim belum siap menangani kompleksitas operasional tambahan.

Prinsipnya: benahi query dan index dulu, baru pertimbangkan partisi. Banyak sistem lambat bukan karena belum dipartisi, tetapi karena query dasarnya tidak mengikuti pola akses data.

Contoh audit query sebelum dan sesudah

Kasus 1: daftar hasil analisis per build

Sebelum:

SELECT id, file_path, severity, rule_id, created_at
FROM analysis_results
WHERE build_id = 9001
ORDER BY created_at DESC
LIMIT 100 OFFSET 5000;

Masalah:

Offset besar.
Jika hanya ada index pada build_id, query bisa tetap perlu sort.

Sesudah:

CREATE INDEX idx_analysis_build_created_id
ON analysis_results(build_id, created_at DESC, id DESC);

SELECT id, file_path, severity, rule_id, created_at
FROM analysis_results
WHERE build_id = 9001
  AND (
    created_at < '2026-06-30 10:15:00'
    OR (created_at = '2026-06-30 10:15:00' AND id < 888888)
  )
ORDER BY created_at DESC, id DESC
LIMIT 100;

Hasil yang diharapkan:

Pembacaan data lebih terarah.
Tidak ada biaya linear akibat offset besar.
Urutan stabil karena memakai created_at dan id sebagai tie-breaker.

Kasus 2: hitung log error untuk satu build

Sebelum:

SELECT COUNT(*)
FROM build_logs
WHERE build_id = 9001
  AND level = 'ERROR';

Query ini tidak selalu salah. Jika kebutuhan memang jumlah presisi dan ada index yang tepat, ia bisa tetap layak. Namun bila dipanggil terus-menerus untuk banyak build, lebih efisien menyimpan agregat terpisah.

Alternatif:

Simpan counter error per build pada tabel ringkasan.
Perbarui secara async setelah ingest log.
Gunakan query count langsung hanya untuk verifikasi atau fallback.

Trade-off-nya jelas: membaca jadi murah, tetapi jalur penulisan dan konsistensi agregat menjadi lebih kompleks.

Kesalahan umum saat optimasi query metadata build

Menambah index tanpa melihat query nyata. Index harus mengikuti pola WHERE, JOIN, dan ORDER BY, bukan intuisi semata.
Menganggap semua COUNT murah. Pada tabel besar, count bisa menjadi query termahal di endpoint list.
Mengandalkan OFFSET untuk semua pagination. Ini nyaman di awal, tetapi memburuk secara sistematis.
Mengejar partisi terlalu cepat. Jika query dan index salah, partisi tidak akan menyelamatkan banyak hal.
Membuat terlalu banyak index mirip. Read mungkin membaik, tetapi throughput ingest metadata turun.
Tidak mengukur sebelum dan sesudah. Optimasi tanpa baseline sering menghasilkan perubahan yang tidak jelas manfaatnya.

Checklist audit bottleneck untuk PostgreSQL atau MySQL

Ambil 5-10 query termahal dari slow query log atau statistik database.
Kelompokkan berdasarkan use case: listing build, log, artefak, hasil analisis, pencarian per repo, dan dashboard.
Jalankan EXPLAIN untuk melihat scan, sort, rows examined, dan akses index.
Cocokkan index dengan pola query dominan, terutama kombinasi WHERE + ORDER BY.
Evaluasi pagination: jika ada OFFSET besar pada endpoint penting, rencanakan migrasi ke keyset pagination.
Audit query COUNT: cek apakah benar-benar perlu akurat dan sinkron di setiap request.
Periksa write amplification: hitung dampak penambahan index terhadap insert log, artefak, dan hasil analisis.
Tentukan retensi data aktif: misalnya 30, 60, atau 90 hari untuk tabel panas.
Arsipkan atau partisi data lama jika ukuran tabel aktif sudah mengganggu query utama.
Ukur ulang setelah perubahan dengan query plan, latensi p95/p99 aplikasi, dan beban I/O database.

Penutup

Optimasi query metadata build bukan soal menambahkan sebanyak mungkin index. Yang paling menentukan adalah kesesuaian antara pola query aplikasi dan cara database mengakses data. Untuk layanan metadata build berskala besar, tiga perbaikan yang paling sering berdampak nyata adalah: membuat index komposit yang mengikuti WHERE + ORDER BY, mengganti OFFSET dengan keyset pagination, dan mengurangi ketergantungan pada SELECT COUNT(*) untuk total halaman.

Jika tabel log, artefak, atau hasil analisis terus tumbuh, pertimbangkan arsip atau partisi ringan agar data aktif tetap kecil. Lakukan semuanya dengan pengukuran yang jelas, karena bottleneck yang sebenarnya hampir selalu terlihat dari query plan dan pola akses produksi, bukan dari asumsi desain awal.

Quick Search

shopping_cart Keranjang