Menjalankan platform katalog media terkurasi seperti repositori gambar AI berlisensi terbuka melibatkan query metadata yang kompleks. Saat tabel aset mencapai jutaan baris, pendekatan relasional tradisional dengan tabel pivot multi-JOIN (images, image_tags, tags) sering mengalami degradasi performa drastis ketika pengguna menyaring beberapa tag sekaligus, memfilter lisensi, dan mengurutkan berdasarkan tanggal terbaru.

Akar Masalah: Bottleneck Normalisasi Ketat dan Offset Pagination

Pola desain database ter-normalisasi penuh memisahkan data ke dalam tabel images, tags, dan relasi many-to-many image_tags. Pola ini memicu dua masalah utama:

  • JOIN Explosion: Memfilter gambar yang memiliki 3 tag berbeda mengharuskan query melakukan 3 kali INNER JOIN terpisah atau agregasi relasi dengan klausul HAVING COUNT(DISTINCT tag_id) = 3. Operasi ini memaksa database memproses jutaan baris sementara.
  • Scan Overhead pada Offset: Query OFFSET 50000 LIMIT 24 memaksa mesin database membaca 50.024 baris, menyortirnya, lalu membuang 50.000 baris pertama. Ini menghasilkan I/O disk tinggi dan latensi tinggi.

Query Awal (Lambat)

-- Model Normalisasi Lama: 3x JOIN + OFFSET Pagination
SELECT i.id, i.title, i.license, i.created_at
FROM images i
JOIN image_tags it1 ON i.id = it1.image_id
JOIN tags t1 ON it1.tag_id = t1.id AND t1.slug = 'robotics'
JOIN image_tags it2 ON i.id = it2.image_id
JOIN tags t2 ON it2.tag_id = t2.id AND t2.slug = 'generative-ai'
WHERE i.license = 'CC-BY-4.0'
ORDER BY i.created_at DESC
OFFSET 20000 LIMIT 24;

Hasil EXPLAIN ANALYZE pada query di atas menunjukkan Nested Loop berulang dan Sort Method: external merge Disk karena dataset hasil filter intermediate terlalu besar untuk ditampung memori kerja (work_mem).

Denormalisasi Terkendali: Kolom Array dan GIN Index

Untuk menghilangkan JOIN dinamis pada tagging aset, simpan tag langsung di dalam tabel utama menggunakan tipe data array teks (text[]) atau JSONB. PostgreSQL menyediakan indeks Generalized Inverted Index (GIN) untuk mengindeks elemen di dalam struktur data tersebut.

Skema Baru dan Pembuatan Indeks

ALTER TABLE images ADD COLUMN tags text[] DEFAULT '{}';

-- GIN Index untuk pencarian containment operator (@>)
CREATE INDEX idx_images_tags_gin ON images USING gin (tags);

-- B-Tree Composite Index untuk filter lisensi dan pengurutan tanggal
CREATE INDEX idx_images_license_created_at_id ON images (license, created_at DESC, id DESC);

Refactor: GIN Operator dan Keyset Pagination

Gunakan operator containment @> untuk menyaring baris yang memiliki seluruh tag yang dicari. Ganti pula OFFSET dengan Keyset Pagination (cursor pagination) menggunakan tuple (created_at, id).

Query Hasil Optimasi

-- Keyset pagination: Menyaring data setelah cursor tertentu
SELECT id, title, license, tags, created_at
FROM images
WHERE license = 'CC-BY-4.0'
  AND tags @> ARRAY['robotics', 'generative-ai']::text[]
  AND (created_at, id) < ('2024-03-01 10:15:00+00', 481920)
ORDER BY created_at DESC, id DESC
LIMIT 24;

Analisis Eksekusi (EXPLAIN ANALYZE)

-- Query Plan Pasca-Optimasi:
Limit  (cost=0.56..82.30 rows=24 width=98) (actual time=0.082..0.215 rows=24 loops=1)
  ->  Index Scan using idx_images_license_created_at_id on images
        Index Cond: ((license = 'CC-BY-4.0'::text) AND (ROW(created_at, id) < ROW('2024-03-01 10:15:00+00'::timestamptz, 481920)))
        Filter: (tags @> '{robotics,generative-ai}'::text[])
        Rows Removed by Filter: 12
Planning Time: 0.150 ms
Execution Time: 0.245 ms

Database memanfaatkan B-Tree index untuk langsung melompat ke record cursor tanpa membaca baris sebelumnya (zero offset read), kemudian mengevaluasi tag filter secara efisien.

Strategi Indeks Lanjutan: Memilih GIN vs Composite B-Tree

Pemilihan indeks bergantung pada selektivitas filter:

  • Jika filter lisensi sangat selektif, index scan pada idx_images_license_created_at_id lebih diutamakan oleh query planner.
  • Jika tag filter sangat langka (misal: 100 gambar dari 10 juta gambar), query planner PostgreSQL akan menggunakan Bitmap Index Scan pada idx_images_tags_gin, lalu menyortir subset hasil di memori.
  • Untuk query teks bebas di dalam metadata metadata JSONB yang dinamis, GIN dengan operator class jsonb_path_ops memberikan ukuran indeks yang lebih kecil dan scan lebih cepat dibanding default jsonb_ops.

Caching Agregasi Tag Populer

Menghitung jumlah aset per tag (misal: untuk sidebar filter) menggunakan COUNT(*) secara real-time pada jutaan baris menghasilkan full-table scan berulang. Terapkan strategi agregasi asinkron:

  • Materialized Views: Buat materialized view yang di-refresh berkala (misal: tiap jam) via pg_cron atau worker background.
  • Redis In-Memory Counter: Simpan counter agregasi tag populer di Redis hash atau sorted set. Update nilai counter secara atomik saat aset baru diterbitkan via queue.
-- Refresh non-blocking untuk materialized view statistik tag
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_popular_tags;

Kesimpulan dan Trade-Offs

Denormalisasi tag ke dalam array/JSONB dengan GIN index memangkas latensi query filter multi-tag dari ratusan milidetik menjadi hitungan sub-milidetik. Namun, pendekatan ini memiliki kompromi: biaya penulisan (write overhead) saat INSERT/UPDATE menjadi lebih tinggi karena pembaruan struktur GIN index. Pastikan parameter maintenance_work_mem dioptimalkan saat melakukan reindex skala besar pada database production.