📚 N+1 Query — Deteksi & Eliminasi Sesi 15:00 Technical Exploration · Database · level 1

🎯 Tujuan Belajar

Setelah sesi ini kamu bisa:

1. Mendeteksi N+1 dari luar aplikasi (log Postgres, pg_stat_statements) tanpa membaca kode.
2. Memilih strategi loading yang benar per bentuk relasi (to-one vs collection), bukan asal with().
3. Mengunci regresi N+1 di CI supaya tidak balik lagi setelah 6 bulan.

Skill yang dipakai besok: baca calls di pg_stat_statements → langsung tahu endpoint mana yang bocor.

🏗️ Arsitektur & Alur Teknis

Definisi operasional. 1 query untuk mengambil N parent + 1 query per parent untuk child = N+1 query. Total 1 + N. Kalau child punya child lagi: 1 + N + N·M (disebut juga N+1 berlapis).

Kenapa mahal. Biayanya bukan CPU database — biayanya round-trip. Setiap query = 1 network RTT + 1 parse/plan + 1 context switch. Jadi biaya ≈ N × RTT. Ini alasan N+1 sering tak terlihat di lokal (RTT 0.05 ms) tapi meledak di produksi (RTT 0.5–2 ms app→DB, apalagi cross-AZ).

Tiga jalur perbaikan, beda karakter:

Aturan pemilihan (INI yang penting, bukan "best practice"):

• to-one → JOIN. Tidak ada duplikasi baris, 1 query.
• collection → IN-batch. JOIN pada collection menggandakan kolom parent per child → traffic naik, dan kalau 2 collection di-JOIN bersamaan → kartesian product.
• collection besar (>500–1000 parent) → IN-batch tetap, tapi sadar ada chunking. SQLAlchemy selectin memecah per 500 PK (docs SQLAlchemy 2.1).

Alur deteksi yang benar (dari luar ke dalam):

1. pg_stat_statements  → query mana yang calls-nya tinggi, mean rendah
2. log Postgres        → pola pengulangan dalam 1 request (application_name)
3. OpenTelemetry span  → parent span punya N child span SQL identik
4. test / CI           → assert jumlah query per request

Langkah 1 & 2 tidak butuh akses kode atau deploy. Itu sebabnya dilakukan dulu.

💻 Kode Nyata / Konfigurasi

1. Deteksi dari Postgres — tanpa sentuh aplikasi

pg_stat_statements menormalkan literal jadi $1, jadi query yang diulang N kali digabung jadi satu baris dengan calls tinggi. Itu sidik jari N+1.

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    calls,
    round(mean_exec_time::numeric, 3)  AS mean_ms,
    round(total_exec_time::numeric, 1) AS total_ms,
    round((total_exec_time / NULLIF(calls,0))::numeric, 3) AS avg_ms,
    left(regexp_replace(query, '\s+', ' ', 'g'), 110) AS q
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat%'
  AND calls > 1000                     -- sering dipanggil
  AND mean_exec_time < 1               -- tapi tiap panggilan murah
ORDER BY calls DESC
LIMIT 20;

Cara baca: baris dengan calls sangat tinggi + mean_ms < 1 ms + query SELECT ... WHERE "x_id" = $1 = hampir pasti N+1. Query sehat untuk endpoint list biasanya calls rendah, mean_ms lebih besar.

Versi 18 (dan 17) menambahkan kolom toplevel — pakai WHERE toplevel supaya query di dalam function/PL-pgSQL tidak mendistorsi angka (PostgreSQL docs, pg_stat_statements).

Konfirmasi di level request — application_name per request ID, lalu lihat pola:

-- app set: SET application_name = 'req-8f2a';
SELECT seq, duration, left(query, 80)
FROM pg_stat_activity  -- hanya snapshot; untuk historis pakai log

Log-based (lebih akurat untuk pola berurutan):

# postgresql.conf
log_min_duration_statement = 0      # sementara saja, sangat verbose
log_line_prefix = '%m [%p] app=%a '
log_statement = 'none'

# 1 request = 1 application_name → hitung query per request
grep 'app=req-' /var/log/postgresql/postgresql-18-main.log \
  | awk '{print $3}' | sort | uniq -c | sort -rn | head

Request dengan hitungan query puluhan/ratusan sementara latency DB per query ~0.2 ms = N+1.

2. Laravel 13.x — auto eager load + guard

Laravel 13 (docs current) sudah punya dua alat yang saling melengkapi (Eloquent: Relationships):

// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

public function boot(): void
{
    // 1) Auto eager-load: akses relasi pertama pada collection → langsung
    //    lazy-eager-load untuk SELURUH collection, bukan per model.
    Model::automaticallyEagerLoadRelationships();

    // 2) Guard: di non-produksi, lazy load = exception. Bikin N+1 ketahuan saat dev/test.
    Model::preventLazyLoading(! $this->app->isProduction());

    Model::handleLazyLoadingViolationUsing(function (Model $model, string $relation) {
        report(new \RuntimeException(
            "Lazy load [{$relation}] on ".$model::class." (id={$model->getKey()})"
        ));
    });
}

Per-collection saja (kalau tak mau global):

$users = User::where('vip', true)->get()->withRelationshipAutoloading();

Catatan penting: automaticallyEagerLoadRelationships() memperbaiki bentuk, bukan menggantikan with(). Ia menyelamatkan kode yang sudah terlanjur ditulis, tapi query-nya tetap hasil tebakan — untuk endpoint yang butuh filter/limit pada relasi, tetap deklarasikan eksplisit:

$users = User::query()
    ->with([
        'posts' => fn ($q) => $q->latest()->limit(5),
        'posts.comments:id,post_id,body',   // kolom dibatasi — hindari over-fetch
        'team:id,name',
    ])
    ->paginate(20);

with() + limit() pada collection di Laravel lama menghasilkan query per parent (karena limit diterapkan per group). Di Laravel 12+/13 eager load dengan limit sudah ditangani lewat window function — tapi verifikasi di versi kamu (EXPLAIN), jangan asumsi. Status: DIINFERENSI dari changelog, bukan dokumentasi eksplisit.

Fitur ini masuk sejak Laravel 12.8 (Model::automaticallyEagerLoadRelationships(), withRelationshipAutoloading()).

3. Prisma — relationLoadStrategy (masih Preview)

Kondisi per 30 Oktober 2026 (docs resmi): Prisma ORM 8 release candidate (prisma@8.0.0-rc.29, @prisma/orm-postgres@8.0.0-rc.22); Prisma ORM 7 GA (@prisma/client@7.10.1); Prisma ORM 6 hanya security patch sampai 19 November 2026. Jangan upgrade buta — prisma di npm latest sekarang menunjuk ke CLI v8 yang tidak membaca schema.prisma dan tidak punya generate/migrate dev (release status).

// package.json — kunci ke v7 kalau proyek masih v7
{ "devDependencies": { "prisma": "^7" }, "dependencies": { "@prisma/client": "^7" } }

N+1 di Prisma biasanya muncul justru di GraphQL resolver, karena resolver posts dipanggil sekali per user:

// ❌ N+1: findMany sekali per user
const User = objectType({
  name: "User",
  definition(t) {
    t.field("posts", {
      type: Post,
      resolve: (parent, _a, ctx) =>
        ctx.prisma.post.findMany({ where: { authorId: parent.id } }),
    });
  },
});

Dua perbaikan valid (query optimization v7):

// ✅ A. Dataloader otomatis — findUnique di tick yang sama di-batch oleh Prisma
resolve: (parent, _a, ctx) => ctx.prisma.user.findUnique({ where: { id: parent.id } }).posts(),

// ✅ B. JOIN di level DB, satu query
const posts = await prisma.post.findMany({
  relationLoadStrategy: "join",   // PostgreSQL: LATERAL JOIN + JSON aggregation
  where: { authorId: { in: userIds } },
});

relationLoadStrategy masih Preview di docs v7 → butuh previewFeatures = ["relationJoins"] di generator. Strategi join pakai LATERAL JOIN + JSON aggregation di Postgres, query kirim satu query per tabel lalu join di app. Untuk dataset aneh, query bisa lebih cepat — profil dulu, jangan tebak.

4. Django 6.x — select_related vs prefetch_related vs Prefetch

Django 6.0 rilis 3 Desember 2025, wajib Python ≥ 3.12; 6.0.1 patch 6 Januari 2026.

from django.db.models import Prefetch

# ❌ 1 + N + N*M
for book in Book.objects.all():
    print(book.author.country.name, [a.name for a in book.authors.all()])

# ✅ FK / OneToOne → JOIN. M2M / reverse FK → IN-batch.
books = (
    Book.objects
    .select_related("author__country")            # to-one: JOIN, 0 query tambahan
    .prefetch_related("authors__country")         # collection: 1 query per level
)

# ✅ Prefetch dengan queryset terfilter — tetap 2 query, bukan N
books = Book.objects.prefetch_related(
    Prefetch("reviews", queryset=Review.objects.filter(rating__gte=4).only("id","book_id","rating"),
             to_attr="good_reviews")
)

Jebakan yang bikin prefetch berubah jadi N+1 lagi: - obj.authors.all().order_by("-name") — QuerySet baru di luar prefetch → query DB. Pakai Prefetch(..., queryset=Author.objects.order_by("-name")). - obj.authors.count() — hitung di Python (len(obj.authors.all()) setelah prefetch) atau pakai annotate(Count(...)). - Prefetch tidak menyentuh .filter() di dalam loop.

Guard di test:

from django.test import TestCase
from django.test.utils import CaptureQueriesContext
from django.db import connection

class BookListQueryCountTest(TestCase):
    def test_list_endpoint_query_count(self):
        # seed 50 books, 3 authors each
        with CaptureQueriesContext(connection) as ctx:
            self.client.get("/api/books/")
        # 1 books + 1 authors + 1 countries + session = konstanta, bukan fungsi N
        self.assertLessEqual(len(ctx), 8, [q["sql"] for q in ctx.captured_queries])

Assertion harus konstanta, bukan <= 1 + N. Kalau kamu tulis 1 + N di test, test-nya ikut mensabotase.

5. SQLAlchemy 2.x — pilih loader, jangan lazy="select" untuk list

from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload

# Collection → selectinload (IN-batch, chunk 500 PK)
stmt = (
    select(User)
    .options(selectinload(User.addresses), selectinload(User.orders).selectinload(Order.items))
    .limit(50)
)

# Many-to-one → joinedload (1 query, tanpa duplikasi baris)
stmt = select(Order).options(joinedload(Order.customer)).limit(50)

Kalau joinedload dipakai pada collection, wajib .unique() sebelum .scalars() — kalau tidak, SQLAlchemy raise InvalidRequestError: The unique() method must be invoked on this Result. Ini error yang paling sering muncul saat orang "memperbaiki" N+1.

Guard global untuk dev:

# Set lazy="raise" di relationship yang selalu harus eager
class User(Base):
    __tablename__ = "users"
    orders = relationship("Order", lazy="raise_on_sql", back_populates="user")

lazy="raise_on_sql" = boleh akses kalau sudah ter-load, raise kalau butuh SQL baru. Lebih toleran daripada lazy="raise".

📊 Angka & Benchmark

Pengukuran saya sendiri, dijalankan sesi ini (SQLite in-memory, 200 user × 4000 post, RTT per query disimulasikan 0.5 ms untuk mengisolasi biaya round-trip — RTT tipikal app→Postgres same-AZ):

Angka SQL engine-nya sendiri (tanpa RTT) untuk ketiga strategi semuanya ~3.8–4.4 ms — praktis identik. Artinya: 99% biaya N+1 adalah round-trip, bukan kerja database. Itu inti pelajarannya, dan itu sebabnya optimasi index tidak akan menyelamatkan N+1.

Skrip benchmark: ada di bagian Latihan.

Angka rujukan dari dokumentasi (bukan pengukuran saya): - SQLAlchemy docs: 100 objek + akses collection = 101 statement (lazy) vs 2 (selectin) vs 1 (joined, Σ baris membengkak). selectin maksimal 500 PK per SELECT, jadi >500 parent = 1 SELECT per batch 500. - Prisma docs: contoh GraphQL allUsers { posts } = 1 query user + 1 query per user untuk posts; relationLoadStrategy: "join" = 1 query.

Status versi terverifikasi (30 Okt 2026):

⚖️ Trade-off & Alternatif

Yang sering salah diklaim "solusi": menambah index. Index mempercepat query individual; N+1 tetap N round-trip. Kecuali calls sudah tinggi dan mean_ms juga tinggi — baru index relevan.

🏭 Use Case Produksi & Failure Mode Umum

Failure mode 1 — N+1 yang tidak terlihat di lokal. Dev pakai SQLite/Postgres di localhost: RTT ~0.05 ms. 200 query = 10 ms. Di produksi cross-AZ RTT 1 ms → 200 ms. Endpoint lolos review, meledak saat traffic naik. Mitigasi: ukur jumlah query, bukan waktu lokal.

Failure mode 2 — N+1 di dalam serialization layer. Query-nya tidak ada di controller. Ada di API Resource / serializer / template. Contoh: Laravel PostResource mengakses $this->author->avatar → 1 query per post, padahal controller sudah Post::paginate(50). Mitigasi: preventLazyLoading() di non-produksi menangkap ini di test.

Failure mode 3 — loop yang menyembunyikan query. foreach dengan Http::get() atau Storage::exists() di dalamnya. Ini saudara N+1 (N network call), tapi pg_stat_statements tidak melihatnya — tidak ada SQL. Mitigasi: APM/tracing, cari span child dengan nama sama berulang.

Failure mode 4 — prefetch yang tidak terpakai. ->with('posts') di query yang tidak pernah memakai posts. Biaya lebih besar, hasil nol. Mitigasi: Bullet gem (Rails) / query log check untuk "unused eager loading".

Failure mode 5 — kartesian product terselubung. with('posts', 'comments') pada collection yang keduanya hasMany → Laravel menjalankan 3 query terpisah (aman), tapi select_related berlapis di Django pada reverse FK tidak bisa — error. Di SQLAlchemy joinedload pada 2 collection = N_posts × N_comments baris. Mitigasi: satu collection besar per query, sisanya IN-batch.

Use case produksi yang paling sering kena: dashboard admin dengan tabel + relasi (user → team → plan), feed (post → author → avatar → likes count), dan GraphQL endpoint apa pun yang tidak pakai DataLoader.

🛠️ Latihan Praktis Kecil

Tujuan: reproduksi angka di tabel benchmark, di laptop sendiri, < 2 menit, tanpa install apa pun (stdlib sqlite3).

# nplus1_bench.py — python3 nplus1_bench.py
import sqlite3, time, statistics

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts(id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT);
CREATE INDEX idx_posts_user ON posts(user_id);
""")
db.executemany("INSERT INTO users VALUES (?,?)", [(i, f"u{i}") for i in range(1, 201)])
db.executemany("INSERT INTO posts VALUES (?,?,?)",
               [(i, (i % 200) + 1, f"p{i}") for i in range(1, 4001)])
db.commit()

RTT = 0.0005  # 0.5 ms per round-trip, disimulasikan

def n_plus_one():
    c, t = db.cursor(), RTT
    users = c.execute("SELECT id,name FROM users").fetchall()
    for u in users:
        c.execute("SELECT id,title FROM posts WHERE user_id=?", (u[0],)).fetchall()
        t += RTT
    return t

def batch():
    c, t = db.cursor(), RTT
    users = c.execute("SELECT id,name FROM users").fetchall()
    ids = [u[0] for u in users]
    c.execute("SELECT user_id,id,title FROM posts WHERE user_id IN (%s)"
              % ",".join("?" * len(ids)), ids).fetchall()
    return t + RTT

def joined():
    db.execute("SELECT u.id,u.name,p.id,p.title FROM users u "
               "LEFT JOIN posts p ON p.user_id=u.id").fetchall()
    return RTT

for name, fn in [("N+1", n_plus_one), ("IN-batch", batch), ("JOIN", joined)]:
    ms = statistics.median([fn() * 1000 for _ in range(20)])
    print(f"{name:9s} {ms:7.2f} ms")

Output yang saya dapat: N+1 100.50 / IN-batch 1.00 / JOIN 0.50.

Variasi yang wajib dicoba: naikkan RTT ke 0.002 (2 ms, cross-region). N+1 jadi ~400 ms, IN-batch tetap ~2 ms. Rasio memburuk linear terhadap RTT, bukan terhadap jumlah data. Itu bukti bahwa masalahnya topologi, bukan volume.

Latihan kedua (di repo sendiri, 10 menit): jalankan test suite dengan jumlah query di-assert. Cari satu test endpoint list, tambahkan assertLessEqual(len(ctx), KONSTANTA). Satu test itu akan menangkap regresi N+1 selamanya.

✅ Cek Pemahaman

1. pg_stat_statements menunjukkan query SELECT ... FROM "order_items" WHERE "order_id" = $1 dengan calls = 84.000 dan mean_exec_time = 0.08 ms. Apakah ini masalah? Kalau ya, kenapa mean_exec_time yang kecil justru buktinya?

2. Kamu punya User → hasMany Order → hasMany Item. Endpoint mengambil 100 user. Bandingkan jumlah query dan jumlah baris terkirim untuk (a) joinedload(User.orders).joinedload(Order.items) dan (b) selectinload(User.orders).selectinload(Order.items). Mana yang kamu pilih dan kenapa?

3. Model::automaticallyEagerLoadRelationships() sudah aktif, tapi endpoint masih lambat. Sebutkan dua penyebab yang tidak diselesaikan fitur itu.

🔑 Poin Kunci

1. N+1 = masalah round-trip, bukan masalah database. 201 query vs 2 query: engine cost sama (~4 ms), total beda 100× karena RTT. Index tidak menyelamatkan.
2. Deteksi dulu dari luar aplikasi. pg_stat_statements: calls tinggi + mean_exec_time rendah = sidik jari N+1. Tidak butuh deploy.
3. to-one → JOIN, collection → IN-batch. JOIN pada collection = duplikasi baris; dua collection di-JOIN = kartesian.
4. Guard > disiplin. preventLazyLoading, lazy="raise_on_sql", assertNumQueries dengan konstanta. Test yang menulis 1 + N ikut mensabotase.
5. Auto eager load memperbaiki bentuk, bukan niat. Query hasil tebakan; untuk filter/limit per relasi tetap deklarasikan eksplisit.
6. Versi berubah cepat dan berbahaya. npm install prisma hari ini memberi CLI v8 RC yang tidak punya migrate dev. Kunci versi di package.json.

➡️ Eksplorasi Lanjut

• auto_explain — log execution plan otomatis untuk query > N ms. Pasangan wajib pg_stat_statements: yang satu memprioritaskan, yang lain menjelaskan.
• Postgres 18 pg_stat_statements.toplevel — memisahkan query dari dalam function/PL-pgSQL supaya angka tidak terdistorsi.
• GraphQL DataLoader — batch + cache per request; pahami kenapa context harus request-scoped (kebocoran data lintas user kalau module-scoped).
• pg_stat_monitor (Percona) — grouping multi-dimensi (client_ip, planid), lebih tajam daripada queryid tunggal.
• N+1 di luar SQL — loop berisi HTTP/gRPC call, S3 headObject, Redis GET per key. Tidak terdeteksi pg_stat_statements; butuh tracing.
• EXPLAIN (ANALYZE, BUFFERS) — sesi berikutnya yang natural untuk topik ini.

Batas verifikasi: angka benchmark adalah pengukuran saya sendiri di SQLite in-memory dengan RTT disimulasikan — bukan Postgres nyata, dan bukan tolok ukur absolut. Yang valid adalah rasionya, bukan nilai ms absolutnya. Klaim Laravel with() + limit() untuk collection berstatus DIINFERENSI dari changelog (window function), tidak saya verifikasi di source code — cek EXPLAIN di proyekmu sebelum mengandalkannya. relationLoadStrategy masih Preview per docs Prisma v7 (30 Okt 2026); bisa berubah sebelum v8 GA.