GIN Index di Go Fiber: Pangkas Slow Query LIKE via pg_trgm

Masalah Utama: Full Table Scan pada Operator ILIKE

Pencarian data teks menggunakan operator LIKE atau ILIKE dengan pola wildcard di awal (misalnya %keyword%) adalah penyebab umum terjadinya latensi tinggi pada PostgreSQL. Kondisi ini memaksa query planner mengeksekusi Sequential Scan (Seq Scan), yaitu pemindaian baris demi baris pada seluruh tabel.

Indeks standar B-Tree bekerja secara hierarkis dari kiri ke kanan (left-to-right). Ketika karakter pertama berupa wildcard (%), B-Tree kehilangan titik referensi untuk menelusuri leaf nodes, sehingga indeks diabaikan. Untuk tabel dengan ratusan ribu hingga jutaan baris, operasi ini meningkatkan pembacaan disk I/O dan membebani utilisasi CPU backend Go Fiber.

Solusi: Ekstensi pg_trgm dan Generalized Inverted Index (GIN)

PostgreSQL menyediakan modul pg_trgm untuk memecah teks menjadi trigram, yaitu urutan 3 karakter berturut-turut. Contohnya, string "fiber" dipecah menjadi trigram: " f", " fi", "fib", "ibe", "ber", "er ".

Ketika dipadukan dengan GIN (Generalized Inverted Index), PostgreSQL memetakan setiap trigram unik ke baris data (tuple pointer) tempat trigram tersebut muncul. Saat query wildcard dijalankan, PostgreSQL mencari trigram dari kata kunci pencarian pada indeks GIN, bukan memindai tabel heap secara penuh.

1. Setup Ekstensi dan Pembuatan Indeks

Aktifkan modul pg_trgm dan buat indeks GIN pada kolom target:

-- Aktifkan ekstensi pg_trgm di database target
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Buat tabel percontohan jika belum ada
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(64) NOT NULL,
    price NUMERIC(12, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Buat GIN Index menggunakan operator class gin_trgm_ops
CREATE INDEX idx_products_name_trgm_gin ON products USING gin (name gin_trgm_ops);

Analisis Query Execution: Seq Scan vs Bitmap Index Scan

Eksekusi EXPLAIN ANALYZE memperlihatkan transformasi rencana eksekusi query planner sebelum dan sesudah indeks diterapkan.

Sebelum Optimasi (Tanpa GIN Index)

EXPLAIN ANALYZE
SELECT id, name, price 
FROM products 
WHERE name ILIKE '%keyboard%';

-- Output:
-- Seq Scan on products  (cost=0.00..18450.00 rows=120 width=38) (actual time=12.415..95.312 rows=150 loops=1)
--   Filter: ((name)::text ~~* '%keyboard%'::text)
--   Rows Removed by Filter: 499850
-- Planning Time: 0.120 ms
-- Execution Time: 95.360 ms

Sesudah Optimasi (Dengan GIN Trigram Index)

EXPLAIN ANALYZE
SELECT id, name, price 
FROM products 
WHERE name ILIKE '%keyboard%';

-- Output:
-- Bitmap Heap Scan on products  (cost=16.80..450.20 rows=120 width=38) (actual time=0.820..2.115 rows=150 loops=1)
--   Recheck Cond: ((name)::text ~~* '%keyboard%'::text)
--   Heap Blocks: exact=145
--   ->  Bitmap Index Scan on idx_products_name_trgm_gin  (cost=0.00..16.77 rows=120 width=0) (actual time=0.785..0.785 rows=150 loops=1)
--         Index Cond: ((name)::text ~~* '%keyboard%'::text)
-- Planning Time: 0.210 ms
-- Execution Time: 2.180 ms

Database bertransisi dari Seq Scan ke Bitmap Index Scan yang dilanjutkan dengan Bitmap Heap Scan. Waktu eksekusi terpangkas signifikan karena PostgreSQL hanya membaca heap page yang relevan.

Implementasi Handler di Go Fiber

Berikut implementasi pencarian efisien menggunakan router Fiber dan driver standar database/sql:

package main

import (
	"context"
	"database/sql"
	"errors"
	"log"
	"net/http"
	"time"

	"github.com/gofiber/fiber/v2"
	_ "github.com/jackc/pgx/v5/stdlib"
)

type Product struct {
	ID    int64   `json:"id"`
	Name  string  `json:"name"`
	Price float64 `json:"price"`
}

type ProductHandler struct {
	DB *sql.DB
}

func (h *ProductHandler) Search(c *fiber.Ctx) error {
	query := c.Query("q")
	if len(query) < 3 {
		return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{
			"error": "Parameter pencarian 'q' minimal harus 3 karakter",
		})
	}

	ctx, cancel := context.WithTimeout(c.UserContext(), 2*time.Second)
	defer cancel()

	sqlQuery := `
		SELECT id, name, price 
		FROM products 
		WHERE name ILIKE $1 
		ORDER BY id DESC 
		LIMIT 50;
	`

	// ponytail: sanitasi input LIKE langsung dengan placeholder parameter, upgrade ke similarity score jika butuh ordering relevansi
	rows, err := h.DB.QueryContext(ctx, sqlQuery, "%"+query+"%")
	if err != nil {
		if errors.Is(ctx.Err(), context.DeadlineExceeded) {
			return c.Status(fiber.StatusGatewayTimeout).JSON(fiber.Map{"error": "Query timeout"})
		}
		return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Gagal mengeksekusi pencarian"})
	}
	defer rows.Close()

	products := make([]Product, 0)
	for rows.Next() {
		var p Product
		if err := rows.Scan(&p.ID, &p.Name, &p.Price); err != nil {
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Gagal parsing data"})
		}
		products = append(products, p)
	}

	if err := rows.Err(); err != nil {
		return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Cursor error"})
	}

	return c.JSON(fiber.Map{"data": products})
}

func main() {
	db, err := sql.Open("pgx", "postgres://user:password@localhost:5432/store?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(10)
	db.SetConnMaxLifetime(5 * time.Minute)

	h := &ProductHandler{DB: db}
	app := fiber.New()

	app.Get("/products/search", h.Search)

	log.Fatal(app.Listen(":3000"))
}

Validasi Latensi: Go Benchmark

Gunakan pengujian benchmark bawaan Go untuk mengukur efisiensi handler saat melayani request pencarian:

package main

import (
	"net/http/httptest"
	"testing"

	"github.com/gofiber/fiber/v2"
)

func BenchmarkSearchHandler(b *testing.B) {
	db, err := sql.Open("pgx", "postgres://user:password@localhost:5432/store?sslmode=disable")
	if err != nil {
		b.Fatal(err)
	}
	defer db.Close()

	h := &ProductHandler{DB: db}
	app := fiber.New()
	app.Get("/products/search", h.Search)

	req := httptest.NewRequest("GET", "/products/search?q=mechanical", nil)

	b.ResetTimer()
	b.ReportAllocs()
	for i := 0; i < b.N; i++ {
		resp, err := app.Test(req, -1)
		if err != nil || resp.StatusCode != 200 {
			b.Fatalf("Request failed: %v, status: %d", err, resp.StatusCode)
		}
	}
}

Trade-Off dan Pertimbangan Teknis

• Ukuran Storage: GIN index memetakan setiap trigram ke pointer baris, menghasilkan footprint disk yang jauh lebih besar dibandingkan B-Tree (bisa mencapai 50-100% ukuran tabel asal).
• Overhead Operasi Write: Setiap operasi INSERT, UPDATE, atau DELETE harus memecah string menjadi trigram dan memperbarui inverted list. Penulisan batch besar akan mengalami penurunan throughput.
• Tuning Pending List: PostgreSQL menggunakan mekanisme buffering fastupdate untuk GIN. Jika write latency meningkat tiba-tiba, periksa parameter gin_pending_list_limit agar proses pembersihan buffer (vacuuming index) tidak memblokir query baca.
• Panjang Karakter Minimal: Query dengan keyword di bawah 3 karakter tidak dapat membentuk trigram utuh secara efektif, sehingga PostgreSQL dapat kembali melakukan full scan. Terapkan validasi input minimum 3 karakter di level Go Fiber handler.