Pada platform git forge terdistribusi seperti sistem berbasis AT Protocol atau arsitektur modular Tangled, volume event audit log dan commit feed tumbuh secara monotonik dengan kecepatan tinggi. Setiap aksi push rekursif, perubahan izin akses, atau pembaruan ref menghasilkan jutaan baris data event. Pendekatan standar menggunakan OFFSET pagination dan indeks single-column menyebabkan full index scan, disk I/O tinggi, dan query timeout saat pagination mencapai halaman dalam.

Skema DDL dan Desain Composite Index

Pola akses utama audit log pada git forge terfokus pada repository tertentu, diurutkan dari event terbaru, serta memerlukan tie-breaker deterministik untuk mengatasi timestamp yang identik. Desain skema memerlukan primary key berbasis ID yang monotonic (seperti UUIDv7 atau BIGINT GENERATED ALWAYS AS IDENTITY) dan composite index yang tepat.

CREATE TABLE repository_audit_logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    repo_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    actor_did TEXT NOT NULL,
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Composite index untuk filter repo_id dan sorting temporal deterministik
CREATE INDEX idx_audit_logs_repo_created_id 
ON repository_audit_logs (repo_id, created_at DESC, id DESC);

Urutan kolom dalam composite index krusial: repo_id dievaluasi sebagai equality check, sedangkan created_at DESC, id DESC mendukung sorting langsung pada disk page tanpa perlu alokasi memori tambahan untuk operasi in-memory sort.

Bandingkan Execution Plan: B-Tree vs BRIN Index

Audit log adalah data append-only. Nilai created_at secara fisik tersimpan berurutan di storage engine. PostgreSQL menyediakan Block Range Index (BRIN) sebagai alternatif hemat ruang dari B-Tree.

1. Karakteristik B-Tree

B-Tree menyimpan referensi untuk setiap baris. Untuk query berfilter repo_id spesifik yang mengambil 20-50 baris per halaman, B-Tree memberikan latensi paling rendah karena engine database dapat melompat langsung ke daun indeks yang relevan.

EXPLAIN ANALYZE
SELECT id, created_at, event_type
FROM repository_audit_logs
WHERE repo_id = 'did:plc:ragtjsm2j2vknwk2gnpqvnvi/repo-core'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Output Execution Plan:
-- Limit  (cost=0.56..12.30 rows=20 width=48) (actual time=0.035..0.061 rows=20 loops=1)
--   ->  Index Scan using idx_audit_logs_repo_created_id on repository_audit_logs  (cost=0.56..5882.10 rows=10020 width=48) (actual time=0.034..0.057 rows=20 loops=1)
--         Index Cond: (repo_id = 'did:plc:ragtjsm2j2vknwk2gnpqvnvi/repo-core'::text)
-- Planning Time: 0.110 ms
-- Execution Time: 0.082 ms

2. Karakteristik BRIN (Block Range Index)

BRIN hanya menyimpan nilai minimum dan maksimum dari rentang disk block (biasanya 128 block per rentang). Indeks ini sangat kecil dalam ukuran disk, tetapi tidak mendukung filter komposit yang efisien untuk repository individual yang datanya tersebar di banyak block.

CREATE INDEX idx_audit_logs_brin_created 
ON repository_audit_logs USING BRIN (created_at);

Saat membaca feed per-repository, BRIN memaksa database membaca seluruh page dalam rentang waktu yang sesuai, lalu memfilter repo_id via Bitmap Heap Scan:

-- Output Execution Plan (BRIN):
-- Limit  (cost=120.45..150.10 rows=20 width=48) (actual time=4.120..4.180 rows=20 loops=1)
--   ->  Sort (cost=120.45..145.50 rows=10020 width=48) (actual time=4.118..4.135 rows=20 loops=1)
--         Sort Key: created_at DESC, id DESC
--         ->  Bitmap Heap Scan on repository_audit_logs  (cost=12.00..89.10 rows=10020 width=48) (actual time=0.510..3.210 rows=9800 loops=1)
--               Recheck Cond: (created_at >= '2024-01-01'::timestamptz)
--               Filter: (repo_id = 'did:plc:ragtjsm2j2vknwk2gnpqvnvi/repo-core'::text)
-- Planning Time: 0.125 ms
-- Execution Time: 4.250 ms

Kesimpulan Pemilihan Indeks: Gunakan B-Tree Composite untuk feed commit dan audit log berbasis repository. Gunakan BRIN hanya untuk global activity stream tak berfilter repository atau tabel arsip cold storage agregat bulanan.

Migrasi dari Offset Pagination ke Keyset Pagination

Query Lambat: OFFSET Pagination

Penggunaan LIMIT ... OFFSET ... mengharuskan engine membaca dan membuang N baris sebelum mengambil data target. Pada halaman 5000 (offset 100.000), disk scan menjadi sangat mahal.

-- SEBELUM (Offset Pagination Lambat)
SELECT id, created_at, actor_did, event_type
FROM repository_audit_logs
WHERE repo_id = 'did:plc:ragtjsm2j2vknwk2gnpqvnvi/repo-core'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Engine mengeksekusi Index Scan melewati 100.000 tuple. Biaya eksekusi linear terhadap nomor halaman: O(N).

Query Optimal: Keyset Pagination (Cursor-based)

Keyset pagination menggunakan nilai batas dari baris terakhir halaman sebelumnya sebagai kondisi awal query berikutnya. Kompleksitas query konstan O(1) terlepas dari kedalaman pagination.

-- SESUDAH (Keyset Pagination Cepat)
-- Nilai kursor dari halaman sebelumnya: 
-- created_at = '2024-03-30T10:15:30.123456Z' AND id = 8943211

SELECT id, created_at, actor_did, event_type
FROM repository_audit_logs
WHERE repo_id = 'did:plc:ragtjsm2j2vknwk2gnpqvnvi/repo-core'
  AND (created_at, id) < ('2024-03-30T10:15:30.123456Z'::timestamptz, 8943211)
ORDER BY created_at DESC, id DESC
LIMIT 20;

PostgreSQL mengimplementasikan tuple comparison (created_at, id) < ($1, $2) langsung melalui composite index yang telah dibuat. Engine melompat tepat ke lokasi data berikutnya tanpa scanning baris yang tidak dibutuhkan.

Format Kursor API dan Trade-Off

Pada level API, gabungkan created_at (dalam microsecond ISO-8601) dan id ke dalam string base64url opaque cursor:

// Contoh format payload kursor transparan di backend:
// base64url(JSON.stringify({ t: 1711793730123456, i: 8943211 }))
// Klien menerima query param: ?cursor=eyJ0IjoxNzExNzkzNzMwMTIzNDU2LCJpIjo4OTQzMjExfQ

Trade-Off Keyset Pagination

  • Kelebihan: Performa konstan, stabil terhadap data insert/delete konkuren (tidak ada missing atau duplicate records saat berpindah halaman), meminimalisir buffer pool thrashing.
  • Kekurangan: Tidak mendukung random page jump (misalnya: langsung melompat ke halaman 42). Klien hanya dapat berpindah secara berurutan maju atau mundur.