Covering Index di Go Fiber: Optimasi Index-Only Scan & Pangkas I/O

Studi Kasus: Bottleneck I/O pada Endpoint Read-Heavy

Pada arsitektur microservices berbasis Go Fiber, endpoint read-heavy dengan throughput tinggi sering kali mengalami degradasi performa mendadak saat volume data membengkak. Gejala utamanya berupa lonjakan latensi p99 dan saturasi disk I/O, meskipun CPU database berada dalam batas aman.

Masalah umum terletak pada table heap fetch berulang. Ketika query menggunakan indeks B-Tree standar, PostgreSQL pertama-tama mencari pointer baris (tuple ID/TID) di dalam indeks, lalu mengakses heap tabel untuk mengambil kolom-kolom yang diminta dalam klausa SELECT. Jika ukuran working set melampaui alokasi shared_buffers, pembacaan halaman heap tabel memicu physical random disk read.

Diagnosis Query: Dari Bitmap Heap Scan ke Index-Only Scan

Ambil contoh skenario query riwayat transaksi pengguna berikut:

SELECT user_id, status, total_amount, created_at
FROM transactions
WHERE user_id = 48201 AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

Sebelum optimasi, tabel hanya memiliki indeks komposit standar pada kolom filter: CREATE INDEX idx_trx_user_status ON transactions(user_id, status);.

Eksekusi EXPLAIN (ANALYZE, BUFFERS) menghasilkan profil eksekusi berikut:

Bitmap Heap Scan on transactions  (cost=12.45..854.20 rows=20 width=48) (actual time=0.842..14.210 rows=20 loops=1)
  Recheck Cond: ((user_id = 48201) AND ((status)::text = 'COMPLETED'::text))
  Rows Removed by Index Recheck: 0
  Buffers: shared hit=4 read=182
  ->  Bitmap Index Scan on idx_trx_user_status  (cost=0.00..12.44 rows=320 width=0) (actual time=0.112..0.113 rows=320 loops=1)
        Index Cond: ((user_id = 48201) AND ((status)::text = 'COMPLETED'::text))
        Buffers: shared hit=3
Planning Time: 0.180 ms
Execution Time: 14.320 ms

Metrik shared read=182 menunjukkan PostgreSQL harus membaca 182 blok data secara langsung dari disk/OS page cache menuju heap tabel guna mengambil kolom total_amount dan created_at. Operasi ini menjadi sumber utama tingginya latensi p99.

Solusi DDL: Mengimplementasikan Covering Index dengan Klausa INCLUDE

Covering index menyimpan kolom payload non-kunci langsung di leaf node pohon B-Tree tanpa memasukkannya ke dalam struktur navigasi indeks. Di PostgreSQL (sejak versi 11), fungsionalitas ini disediakan melalui klausa INCLUDE.

-- Eksekusi tanpa mengunci tabel produksi secara eksklusif
CREATE INDEX CONCURRENTLY idx_transactions_covering 
ON transactions (user_id, status, created_at DESC) 
INCLUDE (total_amount);

Klausa INCLUDE membedakan kolom pencarian (search key) dan kolom muatan (payload):

• Index Key Columns (user_id, status, created_at): Digunakan untuk traversal dan sorting B-Tree.
• Included Columns (total_amount): Hanya disimpan di level leaf node, tidak mempengaruhi pohon navigasi indeks, menghemat ruang per cabang node.

Setelah index dibuat dan tabel memiliki visibility map yang valid (dibersihkan via VACUUM), query yang sama menghasilkan rencana eksekusi berikut:

Index Only Scan using idx_transactions_covering on transactions  (cost=0.42..8.65 rows=20 width=48) (actual time=0.024..0.051 rows=20 loops=1)
  Index Cond: ((user_id = 48201) AND (status = 'COMPLETED'::text))
  Heap Fetches: 0
  Buffers: shared hit=4 read=0
Planning Time: 0.145 ms
Execution Time: 0.072 ms

Kondisi Heap Fetches: 0 mengonfirmasi bahwa PostgreSQL tidak menyentuh heap tabel sama sekali. Seluruh data disuplai langsung dari node indeks, memangkas latensi eksekusi dari 14.32 ms ke 0.072 ms.

Catatan: Index-Only Scan mensyaratkan status visibilitas tuple terverifikasi melalui Visibility Map PostgreSQL. Jika autovacuum tertunda pada tabel dengan traffic insert tinggi, nilai Heap Fetches dapat meningkat karena PostgreSQL harus memeriksa heap untuk mengecek multiversion concurrency control (MVCC).

Implementasi Handler Go Fiber dengan pgx/v5

Berikut implementasi endpoint di Go Fiber v2 menggunakan connection pool native github.com/jackc/pgx/v5/pgxpool dengan manajemen context timeout:

package main

import (
	"context"
	"errors"
	"net/http"
	"strconv"
	"time"

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

type TransactionResponse struct {
	UserID      int64     `json:"user_id"`
	Status      string    `json:"status"`
	TotalAmount float64   `json:"total_amount"`
	CreatedAt   time.Time `json:"created_at"`
}

func RegisterTransactionRoutes(app *fiber.App, db *pgxpool.Pool) {
	app.Get("/api/v1/users/:user_id/transactions", func(c *fiber.Ctx) error {
		userID, err := strconv.ParseInt(c.Params("user_id"), 10, 64)
		if err != nil {
			return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "ID pengguna tidak valid"})
		}

		// Terapkan batas waktu ketat untuk mengisolasi kegagalan database
		ctx, cancel := context.WithTimeout(c.Context(), 1500*time.Millisecond)
		defer cancel()

		query := `
			SELECT user_id, status, total_amount, created_at
			FROM transactions
			WHERE user_id = $1 AND status = 'COMPLETED'
			ORDER BY created_at DESC
			LIMIT 20;
		`

		rows, err := db.Query(ctx, query, userID)
		if err != nil {
			if errors.Is(ctx.Err(), context.DeadlineExceeded) {
				return c.Status(fiber.StatusGatewayTimeout).JSON(fiber.Map{"error": "Permintaan waktu habis"})
			}
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Kegagalan internal basis data"})
		}
		defer rows.Close()

		results := make([]TransactionResponse, 0, 20)
		for rows.Next() {
			var item TransactionResponse
			err := rows.Scan(&item.UserID, &item.Status, &item.TotalAmount, &item.CreatedAt)
			if err != nil {
				return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Kegagalan serialisasi data"})
			}
			results = append(results, item)
		}

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

		return c.Status(fiber.StatusOK).JSON(results)
	})
}

Benchmarking: Metrik Sebelum vs Sesudah Optimasi

Pengujian beban dilakukan menggunakan k6 selama 10 menit dengan 250 virtual users konkuren pada dataset 15 juta baris tabel transactions.

Trade-off dan Write Amplification

Covering index bukan solusi tanpa biaya. Setiap penambahan indeks memiliki trade-off yang harus diperhitungkan dalam arsitektur data:

1. Write Amplification

Setiap operasi INSERT, DELETE, atau UPDATE pada kolom yang tercakup di dalam index mewajibkan mesin database menulis ulang entri pada pohon B-Tree indeks tersebut. Hal ini meningkatkan konsumsi WAL (Write-Ahead Logging) dan latensi mutasi data.

2. Disrupsi HOT (Heap-Only Tuple) Optimization

PostgreSQL menyediakan fitur optimasi HOT yang memungkinkan update baris dilakukan di dalam page yang sama tanpa memodifikasi indeks, selama tidak ada kolom terindeks yang nilainya berubah. Memasukkan kolom yang sering diubah ke dalam klausa INCLUDE mematikan kemampuan HOT update untuk baris tersebut, memicu index bloat.

Panduan Batasan Penggunaan

• Gunakan klausa INCLUDE hanya untuk data berkarakteristik write-rarely, read-frequently (misalnya: nominal histori order, timestamp audit).
• Hindari kolom teks berukuran tidak pasti atau panjang (seperti TEXT panjang atau payload JSONB); batas ukuran total tuple B-Tree standar PostgreSQL adalah sekitar sepertiga ukuran buffer page (~2.7 KB).
• Batasi jumlah kolom INCLUDE menjadi maksimal 2–4 kolom esensial untuk membatasi ukuran memori index pada shared_buffers.