Menyimpan metadata EXIF kamera (seperti ISO, Focal Length, F-Number, dan DateTimeOriginal) ke dalam tipe data JSONB di PostgreSQL menawarkan fleksibilitas skema. Namun, saat tabel mencapai jutaan baris, query filtering dan pagination standar sering mengalami degradasi performa drastis akibat sequential scan dan pembacaan buffer berlebih.

Struktur Data EXIF dan Bottleneck Query

Metadata gambar umumnya memiliki struktur berlapis yang merujuk pada standar EXIF (seperti camera make, exposure settings, dan timestamp). Pertimbangkan skema tabel katalog gambar berikut:

CREATE TABLE photo_catalog (
    id BIGSERIAL PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    exif_data JSONB NOT NULL
);

Contoh payload exif_data tipikal:

{
  "Make": "Sony",
  "Model": "ILCE-7RM4",
  "DateTimeOriginal": "2023-10-14T08:32:15",
  "Exif": {
    "ISO": 400,
    "FNumber": 2.8,
    "FocalLength": 35.0,
    "ExposureTime": "1/500"
  }
}

Dua masalah performa utama pada model ini:

  1. Ekstraksi JSONB Tanpa Index: Query dengan filter numerik seperti (exif_data->'Exif'->>'ISO')::int >= 800 memicu sequential scan karena PostgreSQL harus mem-parsing dokumen JSONB di setiap baris.
  2. Pagination Skala Besar: Pola OFFSET 100000 LIMIT 20 memaksa database membaca dan membuang 100.000 baris sebelum mengembalikan 20 record.

Strategi Indexing: B-Tree Expression vs. GIN

Tipe index harus disesuaikan dengan jenis operasi filtering yang dibutuhkan.

1. B-Tree Functional Index untuk Range Query Spesifik

Index B-Tree sangat optimal untuk operator perbandingan numerik (=, >, <=, BETWEEN) dan sorting. Buat functional index dengan casting tipe data eksplisit:

-- Index untuk filtering ISO (integer)
CREATE INDEX idx_photos_exif_iso 
ON photo_catalog (((exif_data->'Exif'->>'ISO')::int));

-- Index untuk filtering Focal Length (numeric)
CREATE INDEX idx_photos_exif_focal_length 
ON photo_catalog (((exif_data->'Exif'->>'FocalLength')::numeric));

-- Index untuk sorting kronologis capture time (timestamp)
CREATE INDEX idx_photos_exif_capture_time 
ON photo_catalog (((exif_data->>'DateTimeOriginal')::timestamptz));

2. GIN Index untuk Dynamic Key-Value Matching

Jika atribut query dinamis atau menggunakan operator containment (@>), gunakan Generalized Inverted Index (GIN) dengan jsonb_path_ops untuk ukuran index yang lebih ringkas:

CREATE INDEX idx_photos_exif_gin 
ON photo_catalog USING gin (exif_data jsonb_path_ops);
Catatan: Operator jsonb_path_ops hanya mendukung operator @>, bukan pencarian keberadaan key individual (?), namun menghemat ruang disk hingga 40-60% dibanding operator GIN default.

Analisis Eksekusi: EXPLAIN ANALYZE

Berikut evaluasi query pencarian ISO >= 1600 pada dataset 2 juta baris.

Sebelum Indexing (Sequential Scan)

EXPLAIN ANALYZE
SELECT id, filename 
FROM photo_catalog 
WHERE (exif_data->'Exif'->>'ISO')::int >= 1600;
Seq Scan on photo_catalog  (cost=0.00..65430.00 rows=66667 width=40) (actual time=0.082..485.312 rows=70120 loops=1)
  Filter: (((exif_data -> 'Exif'::text) ->> 'ISO'::text)::integer >= 1600)
  Rows Removed by Filter: 1929880
Planning Time: 0.115 ms
Execution Time: 489.201 ms

Setelah B-Tree Functional Index (Index Scan)

EXPLAIN ANALYZE
SELECT id, filename 
FROM photo_catalog 
WHERE (exif_data->'Exif'->>'ISO')::int >= 1600;
Bitmap Heap Scan on photo_catalog  (cost=1452.30..32104.50 rows=66667 width=40) (actual time=8.214..42.110 rows=70120 loops=1)
  Recheck Cond: (((exif_data -> 'Exif'::text) ->> 'ISO'::text)::integer >= 1600)
  ->  Bitmap Index Scan on idx_photos_exif_iso  (cost=0.00..1435.63 rows=66667 width=0) (actual time=7.115..7.115 rows=70120 loops=1)
        Index Cond: (((exif_data -> 'Exif'::text) ->> 'ISO'::text)::integer >= 1600)
Planning Time: 0.142 ms
Execution Time: 45.328 ms

Waktu eksekusi terpangkas signifikan dari ~489 ms menjadi ~45 ms karena PostgreSQL langsung membaca tuple yang valid dari index buffer.

Keyset Pagination Menggantikan OFFSET

Penggunaan OFFSET memiliki kompleksitas waktu O(N). Untuk pagination data besar secara konsisten dan efisien (O(log N)), gunakan keyset pagination (cursor-based) dengan memanfaatkan tuple comparison.

Composite Index untuk Keyset

CREATE INDEX idx_photos_capture_time_id 
ON photo_catalog (
    ((exif_data->>'DateTimeOriginal')::timestamptz) DESC, 
    id DESC
);

Query Keyset Halaman Berikutnya

Ambil halaman data berikutnya menggunakan nilai baris terakhir dari halaman sebelumnya:

SELECT 
    id, 
    filename, 
    (exif_data->>'DateTimeOriginal')::timestamptz AS capture_time
FROM photo_catalog
WHERE 
    -- Kursor: tuple comparison (DateTimeOriginal, ID)
    ((exif_data->>'DateTimeOriginal')::timestamptz, id) < ('2023-10-14 08:32:15+00', 481023)
    AND (exif_data->'Exif'->>'ISO')::int = 400
ORDER BY 
    (exif_data->>'DateTimeOriginal')::timestamptz DESC, 
    id DESC
LIMIT 20;

Pendekatan ini menjamin pembacaan hanya sebanyak nilai LIMIT langsung melalui traversing index tree, tanpa memindai baris-baris sebelumnya.