Sistem Automated License Plate Recognition (ALPR) dan sensor telemetri (seperti pelacak BLE/MAC address) menghasilkan data sighting append-only dengan volume masif secara kontinu. Pada skala puluhan juta hingga ratusan juta baris, query pencarian riwayat deteksi kendaraan berdasarkan kombinasi pelat nomor (atau sensor ID), lokasi, dan rentang waktu sering kali mengalami penurunan performa drastis akibat sequential scan dan ukuran B-Tree index yang membengkak melebihi kapasitas memori kerja (RAM).

Untuk mempertahankan latensi query di bawah ambang batas sub-detik tanpa membebani disk I/O, PostgreSQL memerlukan dua strategi arsitektural utama: declarative range partitioning berbasis waktu dan desain indexing spatiotemporal bertingkat menggunakan kombinasi B-Tree, BRIN, atau GiST.

1. Desain Skema dan Declarative Range Partitioning

Pencarian data ALPR hampir selalu menyertakan filter rentang waktu (misalnya: deteksi 24 jam terakhir atau rentang minggu tertentu). Menyimpan seluruh data dalam satu tabel flat memicu overhead pencarian index yang dalam (tree depth tinggi) dan index bloat.

Gunakan declarative range partitioning pada kolom timestamp untuk mengaktifkan partition pruning. Hal ini membatasi eksekusi query hanya pada partisi relevan dan mengabaikan chunk data historis secara langsung pada level query planner.

-- Ekstensi PostGIS untuk query spasial presisi
CREATE EXTENSION IF NOT EXISTS postgis;

-- Tabel utama terpartisi
CREATE TABLE alpr_sightings (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    plate_number VARCHAR(16) NOT NULL,
    camera_id VARCHAR(32) NOT NULL,
    captured_at TIMESTAMPTZ NOT NULL,
    location GEOMETRY(Point, 4326) NOT NULL,
    confidence_score REAL NOT NULL,
    PRIMARY KEY (id, captured_at)
) PARTITION BY RANGE (captured_at);

-- Contoh pembuatan partisi harian
CREATE TABLE alpr_sightings_2025_03_01 PARTITION OF alpr_sightings
    FOR VALUES FROM ('2025-03-01 00:00:00+00') TO ('2025-03-02 00:00:00+00');

CREATE TABLE alpr_sightings_2025_03_02 PARTITION OF alpr_sightings
    FOR VALUES FROM ('2025-03-02 00:00:00+00') TO ('2025-03-03 00:00:00+00');

Pastikan parameter enable_partition_pruning = on selalu aktif di konfigurasi PostgreSQL. Ketika filter captured_at diberikan, planner mengevaluasi metadata partisi dan mengecualikan tabel anak yang tidak masuk cakupan tanpa membaca disk.

2. Strategi Indexing: B-Tree, GiST, vs BRIN

Beban kerja ALPR memiliki dua pola query dominan: tracking spesifik berdasarkan identitas kendaraan, dan spatial bounding-box untuk mendeteksi semua kendaraan di area tertentu.

Composite B-Tree untuk Identitas + Waktu

Ketika mencari riwayat pergerakan kendaraan tertentu, B-Tree komposit memegang peranan utama. Urutan kolom sangat menentukan efisiensi eksekusi:

-- Terapkan index pada tabel induk (otomatis diteruskan ke partisi)
CREATE INDEX idx_sightings_plate_time 
ON alpr_sightings (plate_number, captured_at DESC);

Menempatkan plate_number di posisi pertama (equality filter) diikuti oleh captured_at DESC (range/ordering filter) memungkinkan PostgreSQL melompat langsung ke leaf node pelat tersebut dan membaca data terurut waktu secara sequential tanpa operasi in-memory sort tambahan.

BRIN vs GiST untuk Lokasi dan Waktu

  • BRIN (Block Range Index): Ideal untuk tabel berukuran gigabyte/terabyte jika data ditulis secara berurutan secara fisik (seperti kolom captured_at pada pipeline ingest realtime). BRIN hanya memetakan nilai minimum dan maksimum per rentang halaman disk (page range). Footprint index ini ratusan kali lebih kecil daripada B-Tree, meminimalkan penggunaan RAM buffer cache.
  • GiST (Generalized Search Tree): Diperlukan untuk kolom geospasial (location) jika query menggunakan operator spasial PostGIS seperti ST_DWithin atau bounding-box &&. GiST mengindeks R-Tree multidimensi yang memungkinkan pencarian radius kamera secara efisien.
-- BRIN untuk filter waktu berskala besar pada partisi historis
CREATE INDEX idx_sightings_time_brin 
ON alpr_sightings USING BRIN (captured_at) WITH (pages_per_range = 32);

-- GiST untuk lookup spasial (radius/koordinat kamera)
CREATE INDEX idx_sightings_location_gist 
ON alpr_sightings USING GIST (location);

3. Analisis Performa: Perbandingan EXPLAIN ANALYZE

Berikut perbandingan riil eksekusi query pencarian riwayat plat nomor pada tabel dengan 40 juta baris data sighting.

Sebelum Optimasi (Tanpa Index Komposit & Partisi)

EXPLAIN ANALYZE
SELECT plate_number, camera_id, captured_at
FROM alpr_sightings_flat
WHERE plate_number = 'B1234XYZ'
  AND captured_at BETWEEN '2025-03-01 08:00:00+00' AND '2025-03-01 12:00:00+00'
ORDER BY captured_at DESC;

-- Output Execution Plan:
Gather Merge  (cost=450210.12..451340.50 rows=15 width=34) (actual time=2145.320..2151.102 rows=12 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=449210.10..449210.14 rows=8 width=34) (actual time=2138.100..2138.102 rows=4 loops=3)
        Sort Key: captured_at DESC
        ->  Parallel Seq Scan on alpr_sightings_flat (cost=0.00..449209.95 rows=8 width=34) (actual time=1420.110..2137.890 rows=4 loops=3)
              Filter: ((captured_at >= '2025-03-01 08:00:00+00') AND (captured_at <= '2025-03-01 12:00:00+00') AND ((plate_number)::text = 'B1234XYZ'::text))
              Rows Removed by Filter: 13333329
Planning Time: 0.182 ms
Execution Time: 2151.240 ms

Database terpaksa melakukan Parallel Seq Scan ke seluruh disk, membuang lebih dari 13 juta baris per worker dengan execution time di atas 2,1 detik.

Sesudah Optimasi (Partition Pruning + Composite Index)

EXPLAIN ANALYZE
SELECT plate_number, camera_id, captured_at
FROM alpr_sightings
WHERE plate_number = 'B1234XYZ'
  AND captured_at BETWEEN '2025-03-01 08:00:00+00' AND '2025-03-01 12:00:00+00'
ORDER BY captured_at DESC;

-- Output Execution Plan:
Index Scan using alpr_sightings_2025_03_01_plate_number_captured_at_idx on alpr_sightings_2025_03_01 alpr_sightings (cost=0.56..12.30 rows=12 width=34) (actual time=0.042..0.065 rows=12 loops=1)
  Index Cond: (((plate_number)::text = 'B1234XYZ'::text) AND (captured_at >= '2025-03-01 08:00:00+00') AND (captured_at <= '2025-03-01 12:00:00+00'))
Planning Time: 0.295 ms
Execution Time: 0.088 ms

Partition pruning menolak partisi lain secara instan. Composite index langsung mengembalikan data yang sudah terurut tanpa in-memory sort. Waktu eksekusi turun drastis dari 2.151 ms menjadi 0.088 ms (reduksi latensi >99%).

4. Streaming Riwayat Deteksi dengan Keyset Pagination

Penggunaan OFFSET ... LIMIT pada dashboard monitoring surveillance menimbulkan degradasi performa karena database tetap harus membaca baris-baris offset sebelumnya (offset penalty). Terapkan keyset (cursor) pagination menggunakan tuple comparison:

-- Halaman pertama
SELECT id, plate_number, camera_id, captured_at
FROM alpr_sightings
WHERE captured_at >= '2025-03-01 00:00:00+00'
  AND captured_at < '2025-03-02 00:00:00+00'
ORDER BY captured_at DESC, id DESC
LIMIT 50;

-- Halaman berikutnya (ambil nilai terakhir: last_captured_at dan last_id)
SELECT id, plate_number, camera_id, captured_at
FROM alpr_sightings
WHERE captured_at >= '2025-03-01 00:00:00+00'
  AND captured_at < '2025-03-02 00:00:00+00'
  AND (captured_at, id) < ('2025-03-01 18:42:10.123+00', 4920192)
ORDER BY captured_at DESC, id DESC
LIMIT 50;

Index yang mendukung keyset ini harus mencakup kedua kolom secara berurutan: CREATE INDEX idx_pagination ON alpr_sightings (captured_at DESC, id DESC);. Dengan skema ini, kompleksitas traversal pagination tetap konstan O(log N) baik di halaman pertama maupun halaman ke-10.000.

5. Siklus Hidup Data dan Maintenance Drop Partition

Menjalankan query DELETE FROM alpr_sightings WHERE captured_at < NOW() - INTERVAL '90 days' pada tabel bervolume tinggi akan memicu write amplification, transaksi lock berkepanjangan, dan dead tuples yang membebani VACUUM.

Karena data sudah terpartisi per rentang waktu, retensi data dilakukan via operasi metadata DDL yang tereksekusi instan (sub-milidetik) tanpa memicu I/O disk besar:

-- Lepas partisi dari routing tabel utama (zero locking impact pada ingest)
ALTER TABLE alpr_sightings 
DETACH PARTITION alpr_sightings_2024_11_01 CONCURRENTLY;

-- Hapus tabel fisik langsung dari disk
DROP TABLE alpr_sightings_2024_11_01;
Catatan Operasional: Gunakan opsi CONCURRENTLY saat mendetach partisi pada sistem aktif untuk mencegah ACCESS EXCLUSIVE lock yang dapat memblokir operasi pipeline INSERT yang sedang berjalan pada tabel induk. Otomasi rotasi partisi harian dapat dikelola menggunakan cron script atau ekstensi seperti pg_partman.