Akar Masalah: Index Bloat dan Buffer Cache Thrashing

Pola soft delete menggunakan kolom penanda seperti deleted_at TIMESTAMP NULL umum digunakan untuk menjaga riwayat data tanpa menghapusnya secara fisik. Namun, seiring bertambahnya volume transaksi, jumlah baris dengan status terhapus (deleted_at IS NOT NULL) dapat mendominasi tabel hingga 70-90% dari total data.

Ketika B-tree index standar dibuat pada kolom pencarian (misalnya CREATE INDEX idx_orders_user_id ON orders(user_id, deleted_at);), setiap baris yang telah dihapus tetap menempati leaf page pada struktur B-tree. Kondisi ini memicu dua masalah performa mendasar pada database PostgreSQL:

  • Index Bloat: Ukuran index membengkak secara linear mengikuti seluruh riwayat baris, bukan baris aktif yang relevan bagi aplikasi.
  • Buffer Cache Thrashing: Saat PostgreSQL membaca index besar dari disk ke shared_buffers, halaman index data mati mendesak data aktif keluar dari memori RAM. Hal ini memaksa database melakukan random disk I/O berulang kali, yang mendegradasi performa p99 API Go Fiber.

Solusi: Partial Index PostgreSQL

Partial index mengecualikan baris yang tidak memenuhi kondisi predikat tertentu dari struktur pohon B-tree. Untuk pola soft delete, index hanya perlu mencakup baris aktif di mana deleted_at IS NULL.

-- Hapus index komposit konvensional
DROP INDEX IF EXISTS idx_orders_user_deleted;

-- Buat partial index hanya untuk data aktif
CREATE INDEX idx_orders_active_user_id 
ON orders (user_id) 
WHERE deleted_at IS NULL;

Dengan predikat WHERE deleted_at IS NULL, ukuran index menyusut drastis karena hanya menyimpan pointer baris aktif. Hasilnya, seluruh working set index dapat tertampung penuh di dalam shared_buffers.

Analisis Eksekusi: EXPLAIN (ANALYZE, BUFFERS)

Pengujian dilakukan pada tabel orders dengan 10.000.000 baris, di mana 8.500.000 baris berstatus soft-deleted.

1. Query Menggunakan B-tree Index Standar

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, amount, created_at 
FROM orders 
WHERE user_id = 4501 AND deleted_at IS NULL;

Hasil eksekusi:

Index Scan using idx_orders_user_deleted on orders (cost=0.56..89.40 rows=25 width=24) (actual time=2.140..8.412 rows=22 loops=1)
  Index Cond: ((user_id = 4501) AND (deleted_at IS NULL))
  Buffers: shared hit=14 read=48
Planning Time: 0.180 ms
Execution Time: 8.448 ms

2. Query Menggunakan Partial Index

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, amount, created_at 
FROM orders 
WHERE user_id = 4501 AND deleted_at IS NULL;

Hasil eksekusi:

Index Scan using idx_orders_active_user_id on orders (cost=0.42..12.30 rows=25 width=24) (actual time=0.035..0.068 rows=22 loops=1)
  Index Cond: (user_id = 4501)
  Buffers: shared hit=4 read=0
Planning Time: 0.125 ms
Execution Time: 0.089 ms

Partial index mengeliminasi disk read sepenuhnya (read=0) karena index berukuran kompak dan tetap berada di cache. Latensi eksekusi turun dari 8.44 ms ke 0.08 ms.

Penting: Query planner PostgreSQL hanya akan memilih partial index jika klausa WHERE pada query SQL secara eksplisit memuat kondisi yang identik atau subset dari predikat index (yaitu WHERE deleted_at IS NULL).

Implementasi Handler Go Fiber dengan pgx/v5

Di layer aplikasi, Go Fiber menggunakan fasthttp yang mengelola siklus hidup request secara non-standar. Gunakan c.UserContext() untuk mengalirkan cancellation signal dan pasang timeout ketat agar koneksi pool database tidak tertahan saat trafik tinggi.

package main

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

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

type Order struct {
	ID        int64     `json:"id"`
	Amount    float64   `json:"amount"`
	CreatedAt time.Time `json:"created_at"`
}

type OrderHandler struct {
	db *pgxpool.Pool
}

func (h *OrderHandler) GetActiveOrders(c *fiber.Ctx) error {
	userID, err := c.ParamsInt("userId")
	if err != nil {
		return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "Invalid user ID"})
	}

	// Jembatani Fiber context ke context standar dengan timeout ketat
	ctx, cancel := context.WithTimeout(c.UserContext(), 1500*time.Millisecond)
	defer cancel()

	// Predikat deleted_at IS NULL wajib ada agar partial index digunakan
	const query = `
		SELECT id, amount, created_at 
		FROM orders 
		WHERE user_id = $1 AND deleted_at IS NULL
		ORDER BY created_at DESC 
		LIMIT 50;
	`

	rows, err := h.db.Query(ctx, query, userID)
	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": "Database error"})
	}
	defer rows.Close()

	orders := make([]Order, 0, 50)
	for rows.Next() {
		var o Order
		if err := rows.Scan(&o.ID, &o.Amount, &o.CreatedAt); err != nil {
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "Scan error"})
		}
		orders = append(orders, o)
	}

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

Trade-off Arsitektural: Write Amplification vs Memory Footprint

Meskipun partial index memangkas latensi pembacaan data aktif, pertimbangkan trade-off operasional berikut sebelum menerapkannya di skala produksi:

1. Write Amplification saat Soft Delete

Ketika operasi update menjalankan UPDATE orders SET deleted_at = NOW() WHERE id = $1, PostgreSQL harus menghapus entri kunci dari idx_orders_active_user_id. Operasi pemindahan ini menambah beban I/O write ke Write-Ahead Log (WAL) dibanding update kolom yang tidak diindeks.

2. Penurunan Peluang HOT (Heap-Only Tuples)

PostgreSQL memiliki optimasi HOT untuk menghindari pembaruan index jika pembaruan tuple muat di page data yang sama. Mengubah kolom yang menjadi predikat partial index membatalkan kelayakan optimasi HOT untuk tuple tersebut pada saat status soft delete diperbarui.

3. Efisiensi Memory Footprint dan Maintenance

Di sisi positif, penghematan memori dari partial index sangat signifikan:

  • RAM Footprint: Index 5-10x lebih kecil, mengurangi resiko page eviction di shared_buffers.
  • Beban VACUUM & REINDEX: Waktu eksekusi proses maintenance autovacuum pada tabel besar menjadi lebih singkat karena index yang dipindai memiliki ukuran fisik yang jauh lebih padat.