Part 003 — SQL Foundation for Backend Engineers

1. Posisi Part Ini Dalam Seri

Part ini membangun fondasi SQL untuk backend engineer yang bekerja dengan PostgreSQL di sistem enterprise Java/JAX-RS.

Targetnya bukan sekadar bisa menulis query yang jalan. Targetnya adalah mampu menulis, membaca, dan mereview SQL sebagai bagian dari production correctness boundary.

Di aplikasi enterprise, SQL bukan hanya string yang dikirim dari MyBatis ke database. SQL adalah tempat beberapa keputusan penting terjadi:

• data mana yang dianggap valid untuk dibaca,
• relasi mana yang dianggap sah,
• baris mana yang dikunci atau diubah,
• urutan data mana yang stabil untuk pagination,
• apakah query akan tetap aman ketika data tumbuh 100x,
• apakah hasil query konsisten dengan lifecycle bisnis.

Untuk backend Java/JAX-RS, jalurnya sering seperti ini:

HTTP request
  -> JAX-RS resource
  -> service layer
  -> transaction boundary
  -> repository / DAO
  -> MyBatis mapper
  -> JDBC PreparedStatement
  -> PostgreSQL planner
  -> executor
  -> rows returned / rows changed
  -> domain response / event / audit

Jika SQL buruk, masalahnya tidak selalu langsung terlihat saat data masih kecil. Masalah biasanya muncul ketika:

• tabel bertambah besar,
• request concurrent meningkat,
• query dipakai untuk endpoint penting,
• migration mengubah cardinality data,
• index tidak lagi cocok dengan query shape,
• deployment memperbanyak replica service dan memperbesar tekanan koneksi/query.

Prinsip utama: SQL production-grade harus benar secara data, stabil secara performa, mudah direview, dan eksplisit terhadap asumsi bisnis.

2. SQL Bukan Sekadar Syntax

Backend engineer sering memperlakukan SQL sebagai detail persistence. Itu berbahaya.

SQL menentukan bentuk realitas data yang dilihat aplikasi. Query yang salah dapat menghasilkan bug domain walaupun kode Java terlihat bersih.

Contoh sederhana:

SELECT *
FROM quote
WHERE customer_id = #{customerId}
LIMIT 1;

Query ini tampak harmless, tetapi secara production review banyak pertanyaan muncul:

• Quote yang mana?
• Apakah urutan hasil deterministik?
• Apakah hanya quote aktif?
• Apakah quote draft termasuk?
• Apakah tenant/account boundary sudah dicek?
• Apakah soft-deleted row harus dikecualikan?
• Apakah ada index yang mendukung predicate?
• Apakah LIMIT 1 menyembunyikan data anomaly?

Versi yang lebih reviewable:

SELECT q.id,
       q.quote_number,
       q.status,
       q.updated_at
FROM quote q
WHERE q.tenant_id = #{tenantId}
  AND q.customer_id = #{customerId}
  AND q.status IN ('DRAFT', 'IN_REVIEW')
  AND q.deleted_at IS NULL
ORDER BY q.updated_at DESC,
         q.id DESC
LIMIT 1;

Query kedua tetap perlu direview, tetapi intent-nya lebih jelas.

SQL Production Lens

Saat membaca SQL, gunakan lensa berikut:

3. Logical Order of SQL Execution

SQL tidak dieksekusi persis sesuai urutan penulisan. Untuk reasoning, gunakan urutan logis berikut:

FROM / JOIN
  -> WHERE
  -> GROUP BY
  -> HAVING
  -> SELECT
  -> DISTINCT
  -> ORDER BY
  -> LIMIT / OFFSET

Contoh:

SELECT q.customer_id,
       count(*) AS active_quote_count
FROM quote q
WHERE q.status = 'ACTIVE'
GROUP BY q.customer_id
HAVING count(*) > 5
ORDER BY active_quote_count DESC
LIMIT 20;

Mental model:

1. Ambil row dari quote.
2. Filter hanya status ACTIVE.
3. Kelompokkan per customer_id.
4. Hitung jumlah row per customer.
5. Filter group yang count-nya lebih dari 5.
6. Urutkan hasil aggregate.
7. Ambil 20 teratas.

Kesalahan umum backend engineer adalah menganggap alias di SELECT bisa selalu dipakai di semua clause atau mengira LIMIT terjadi sebelum join/filter. Planner bisa melakukan optimasi fisik, tetapi reasoning correctness sebaiknya tetap mengikuti urutan logis.

4. SELECT: Pilih Kolom Secara Sengaja

Hindari SELECT * pada query aplikasi production.

SELECT *
FROM order_item
WHERE order_id = #{orderId};

Masalah:

• payload lebih besar dari kebutuhan,
• coupling ke perubahan schema,
• mapping MyBatis bisa ikut membaca kolom yang tidak perlu,
• sulit mereview data exposure,
• bisa merusak index-only scan,
• risiko mengambil kolom besar seperti JSONB/description/blob metadata.

Lebih baik:

SELECT oi.id,
       oi.order_id,
       oi.product_offering_id,
       oi.quantity,
       oi.status,
       oi.created_at,
       oi.updated_at
FROM order_item oi
WHERE oi.order_id = #{orderId};

Rule untuk Backend Query

Pilih kolom berdasarkan use case:

• API list view: kolom ringkas.
• API detail view: kolom detail yang memang ditampilkan.
• command validation: kolom minimal untuk memutuskan valid/tidak.
• event publishing: kolom yang masuk event contract.
• audit/reconciliation: kolom yang dibutuhkan untuk traceability.

Java/JAX-RS Impact

SELECT * sering membuat DTO dan persistence model terlalu menyatu. Akibatnya:

• response API berubah karena schema berubah,
• field internal tidak sengaja keluar,
• query list menjadi lambat karena mengambil field detail,
• mapper menjadi sulit dioptimalkan.

5. WHERE: Predicate Adalah Business Boundary

WHERE bukan hanya filter teknis. Ia sering menjadi boundary domain.

Contoh query quote:

SELECT q.id,
       q.status
FROM quote q
WHERE q.id = #{quoteId};

Pertanyaan review:

• Apakah perlu tenant_id?
• Apakah perlu account_id?
• Apakah soft delete diperhitungkan?
• Apakah status tertentu saja yang boleh dibaca?
• Apakah user permission diterapkan di aplikasi atau query?

Versi lebih eksplisit:

SELECT q.id,
       q.status
FROM quote q
WHERE q.tenant_id = #{tenantId}
  AND q.id = #{quoteId}
  AND q.deleted_at IS NULL;

Predicate dan Index

Predicate yang sering muncul di query production biasanya menjadi kandidat index.

Contoh:

WHERE tenant_id = ?
  AND status = ?
  AND updated_at >= ?

Ini memberi sinyal bahwa index komposit mungkin perlu dipertimbangkan, tetapi urutan kolom dan jenis index tetap harus berbasis query pattern dan cardinality.

NULL Semantics

SQL memiliki three-valued logic: TRUE, FALSE, dan UNKNOWN.

Kesalahan umum:

WHERE deleted_at = NULL

Benar:

WHERE deleted_at IS NULL

Untuk nilai tidak sama dengan nullable column:

WHERE status <> 'CANCELLED'

Jika status bisa NULL, row dengan status IS NULL tidak otomatis masuk. Jika memang harus masuk:

WHERE status IS DISTINCT FROM 'CANCELLED'

Atau eksplisit:

WHERE status <> 'CANCELLED'
   OR status IS NULL

Correctness Concern

Nullable column harus direview sangat hati-hati. Banyak bug production berasal dari query yang secara manusia tampak benar tetapi secara SQL melewatkan NULL.

6. ORDER BY: Determinism untuk API dan Pagination

Query dengan LIMIT tanpa ORDER BY adalah red flag.

SELECT id,
       status
FROM quote
WHERE customer_id = #{customerId}
LIMIT 10;

PostgreSQL tidak menjamin urutan row tanpa ORDER BY. Hasil bisa berubah karena:

• query plan berubah,
• index baru ditambahkan,
• vacuum/rewrite terjadi,
• PostgreSQL version berubah,
• data distribution berubah.

Versi lebih aman:

SELECT id,
       status,
       updated_at
FROM quote
WHERE customer_id = #{customerId}
ORDER BY updated_at DESC,
         id DESC
LIMIT 10;

Tambahkan tie-breaker unik seperti id agar urutan stabil ketika beberapa row punya timestamp sama.

ORDER BY dan Index

ORDER BY dapat menjadi mahal jika PostgreSQL harus sort banyak row. Index yang cocok dapat membantu jika predicate dan order sejalan.

Contoh query:

WHERE tenant_id = ?
  AND status = ?
ORDER BY updated_at DESC,
         id DESC
LIMIT 50

Kemungkinan index yang perlu dievaluasi:

CREATE INDEX idx_quote_tenant_status_updated_id
ON quote (tenant_id, status, updated_at DESC, id DESC);

Tetapi index bukan keputusan otomatis. Harus dicek dengan EXPLAIN, cardinality, write overhead, dan query frequency.

7. LIMIT/OFFSET: Berguna, Tapi Tidak Selalu Scalable

Offset pagination umum di API:

SELECT id,
       quote_number,
       status,
       updated_at
FROM quote
WHERE tenant_id = #{tenantId}
ORDER BY updated_at DESC,
         id DESC
LIMIT #{limit}
OFFSET #{offset};

Masalahnya, offset besar tetap membuat database harus melewati banyak row sebelum mengembalikan halaman yang diminta.

Untuk halaman awal, ini sering cukup. Untuk dataset besar atau infinite scroll, keyset pagination lebih stabil.

SELECT id,
       quote_number,
       status,
       updated_at
FROM quote
WHERE tenant_id = #{tenantId}
  AND (updated_at, id) < (#{lastUpdatedAt}, #{lastId})
ORDER BY updated_at DESC,
         id DESC
LIMIT #{limit};

API Design Impact

Offset pagination cocok untuk:

• admin table kecil/menengah,
• halaman awal,
• kebutuhan jump-to-page.

Keyset pagination cocok untuk:

• feed/list besar,
• event/history table,
• audit log,
• high-traffic API,
• data yang terus bertambah.

Review Red Flag

OFFSET 500000

Biasanya ini tanda API desain, index strategy, atau reporting pattern perlu diperbaiki.

8. JOIN Mental Model

JOIN menggabungkan row dari beberapa relation. Risiko utamanya adalah cardinality explosion.

Contoh:

SELECT q.id,
       qi.id AS item_id
FROM quote q
JOIN quote_item qi ON qi.quote_id = q.id
WHERE q.id = #{quoteId};

Jika satu quote punya 100 item, hasilnya 100 row. Itu benar.

Tapi query berikut bisa berbahaya:

SELECT q.id,
       qi.id AS item_id,
       a.id AS approval_id
FROM quote q
JOIN quote_item qi ON qi.quote_id = q.id
JOIN approval a ON a.quote_id = q.id
WHERE q.id = #{quoteId};

Jika quote punya 100 item dan 5 approval, hasil bisa menjadi 500 row karena kombinasi item x approval.

JOIN Cardinality Checklist

Untuk setiap JOIN, tanyakan:

• one-to-one?
• one-to-many?
• many-to-one?
• many-to-many?
• apakah join key unik?
• apakah filter diletakkan di tempat yang benar?
• apakah hasil duplikat memang diinginkan?

9. INNER JOIN

INNER JOIN hanya mengembalikan row yang match di kedua sisi.

SELECT o.id,
       o.order_number,
       c.customer_name
FROM customer_order o
JOIN customer c ON c.id = o.customer_id
WHERE o.tenant_id = #{tenantId};

Gunakan INNER JOIN ketika relasi wajib ada secara domain.

Jika order tanpa customer adalah data anomaly, INNER JOIN akan menyembunyikan anomaly tersebut dari hasil. Kadang itu baik, kadang berbahaya.

Correctness Question

Jika row parent ada tetapi child tidak ada, apakah API harus:

• tetap menampilkan parent?
• menyembunyikan parent?
• mengembalikan error?
• memunculkan data integrity alert?

Jawaban menentukan apakah memakai INNER JOIN atau LEFT JOIN.

10. LEFT JOIN

LEFT JOIN mengembalikan semua row dari kiri, dan NULL untuk sisi kanan jika tidak match.

SELECT q.id,
       q.quote_number,
       a.status AS latest_approval_status
FROM quote q
LEFT JOIN approval a ON a.quote_id = q.id
WHERE q.id = #{quoteId};

Masalah umum: filter terhadap tabel kanan diletakkan di WHERE, sehingga LEFT JOIN berubah efeknya menjadi seperti INNER JOIN.

SELECT q.id,
       a.status
FROM quote q
LEFT JOIN approval a ON a.quote_id = q.id
WHERE a.status = 'APPROVED';

Jika quote tidak punya approval, a.status NULL, sehingga row hilang.

Jika intent-nya tetap menampilkan quote tanpa approval, filter harus dipindah ke ON:

SELECT q.id,
       a.status
FROM quote q
LEFT JOIN approval a
  ON a.quote_id = q.id
 AND a.status = 'APPROVED';

Review Rule

Pada LEFT JOIN, review semua predicate terhadap tabel kanan. Apakah predicate itu memang harus memfilter hasil akhir, atau hanya memfilter row yang di-join?

11. SEMI JOIN dan ANTI JOIN Mental Model

SQL tidak selalu menulis SEMI JOIN secara eksplisit, tetapi mental model ini penting.

SEMI JOIN: Cari Parent yang Punya Child

SELECT q.id,
       q.quote_number
FROM quote q
WHERE EXISTS (
  SELECT 1
  FROM quote_item qi
  WHERE qi.quote_id = q.id
);

Intent: ambil quote yang punya minimal satu item.

Ini sering lebih tepat daripada JOIN + DISTINCT:

SELECT DISTINCT q.id,
                q.quote_number
FROM quote q
JOIN quote_item qi ON qi.quote_id = q.id;

JOIN + DISTINCT bisa menyembunyikan cardinality problem.

ANTI JOIN: Cari Parent yang Tidak Punya Child

SELECT q.id,
       q.quote_number
FROM quote q
WHERE NOT EXISTS (
  SELECT 1
  FROM quote_item qi
  WHERE qi.quote_id = q.id
);

Use case:

• quote kosong,
• order tanpa fulfillment record,
• outbox event yang belum dipublish,
• customer tanpa active agreement.

Correctness Concern

NOT IN dengan NULL dapat menghasilkan behaviour mengejutkan. Untuk anti-join, NOT EXISTS biasanya lebih aman dan jelas.

12. GROUP BY dan Aggregation

Aggregation mengubah row-level data menjadi group-level result.

SELECT order_id,
       count(*) AS item_count,
       sum(quantity) AS total_quantity
FROM order_item
WHERE tenant_id = #{tenantId}
GROUP BY order_id;

Pertanyaan review:

• Apakah filter tenant/status/date sudah sebelum aggregate?
• Apakah group key benar?
• Apakah aggregate bisa double count akibat JOIN?
• Apakah NULL memengaruhi sum, avg, atau count(column)?

COUNT(*) vs COUNT(column)

COUNT(*)

Menghitung semua row.

COUNT(cancelled_at)

Menghitung hanya row yang cancelled_at IS NOT NULL.

Ini sering berguna, tetapi harus disengaja.

Aggregation dengan JOIN

Hati-hati aggregate setelah join one-to-many ganda.

Contoh berbahaya:

SELECT q.id,
       count(qi.id) AS item_count,
       count(a.id) AS approval_count
FROM quote q
LEFT JOIN quote_item qi ON qi.quote_id = q.id
LEFT JOIN approval a ON a.quote_id = q.id
GROUP BY q.id;

Jika satu quote punya 10 item dan 3 approval, count bisa menjadi 30 dan 30.

Lebih aman memakai pre-aggregation:

WITH item_count AS (
  SELECT quote_id,
         count(*) AS item_count
  FROM quote_item
  GROUP BY quote_id
),
approval_count AS (
  SELECT quote_id,
         count(*) AS approval_count
  FROM approval
  GROUP BY quote_id
)
SELECT q.id,
       coalesce(ic.item_count, 0) AS item_count,
       coalesce(ac.approval_count, 0) AS approval_count
FROM quote q
LEFT JOIN item_count ic ON ic.quote_id = q.id
LEFT JOIN approval_count ac ON ac.quote_id = q.id
WHERE q.id = #{quoteId};

13. HAVING

WHERE memfilter row sebelum aggregation. HAVING memfilter group setelah aggregation.

SELECT customer_id,
       count(*) AS order_count
FROM customer_order
WHERE tenant_id = #{tenantId}
GROUP BY customer_id
HAVING count(*) > 10;

Jangan memakai HAVING untuk filter row biasa jika bisa memakai WHERE.

Kurang baik:

SELECT customer_id,
       count(*)
FROM customer_order
GROUP BY customer_id
HAVING tenant_id = #{tenantId};

Ini salah atau tidak valid tergantung query shape, dan secara mental model juga keliru. Filter row harus masuk WHERE.

14. Subquery

Subquery membantu mengekspresikan query bertahap.

SELECT q.id,
       q.quote_number
FROM quote q
WHERE q.total_amount > (
  SELECT avg(total_amount)
  FROM quote
  WHERE tenant_id = #{tenantId}
);

Subquery bisa berada di:

• SELECT,
• FROM,
• WHERE,
• JOIN,
• EXISTS.

Correlated Subquery

SELECT q.id,
       q.quote_number,
       (
         SELECT count(*)
         FROM quote_item qi
         WHERE qi.quote_id = q.id
       ) AS item_count
FROM quote q
WHERE q.tenant_id = #{tenantId};

Ini mudah dibaca, tetapi bisa menjadi mahal jika planner tidak dapat mengoptimalkan atau dataset besar. Untuk list besar, pre-aggregation sering lebih aman.

15. CTE: Common Table Expression

CTE membuat query kompleks lebih mudah dibaca.

WITH active_quote AS (
  SELECT id,
         customer_id,
         total_amount
  FROM quote
  WHERE tenant_id = #{tenantId}
    AND status = 'ACTIVE'
),
large_quote AS (
  SELECT *
  FROM active_quote
  WHERE total_amount > 100000
)
SELECT *
FROM large_quote
ORDER BY total_amount DESC;

Kapan CTE Berguna

Gunakan CTE untuk:

• memecah query kompleks,
• pre-aggregation,
• deduplication step,
• recursive traversal,
• membuat intent mudah direview,
• menghindari nested subquery terlalu dalam.

Performance Caution

Di PostgreSQL modern, CTE tidak selalu menjadi optimization fence, tetapi tetap harus dibaca dengan EXPLAIN. Jangan menganggap CTE otomatis lebih cepat atau lebih lambat. Fokus pada plan aktual.

16. Recursive CTE

Recursive CTE berguna untuk data hierarki.

Contoh: product offering hierarchy atau organizational tree.

WITH RECURSIVE offering_tree AS (
  SELECT id,
         parent_id,
         name,
         1 AS depth
  FROM product_offering
  WHERE id = #{rootOfferingId}

  UNION ALL

  SELECT child.id,
         child.parent_id,
         child.name,
         parent.depth + 1 AS depth
  FROM product_offering child
  JOIN offering_tree parent ON child.parent_id = parent.id
)
SELECT *
FROM offering_tree
ORDER BY depth, id;

Failure Modes

Recursive query dapat bermasalah jika:

• ada cycle dalam data,
• depth tidak dibatasi,
• join key tidak di-index,
• hierarchy terlalu besar,
• query dipanggil di endpoint high-traffic.

Tambahkan cycle prevention jika model memungkinkan cycle.

WITH RECURSIVE tree AS (
  SELECT id,
         parent_id,
         ARRAY[id] AS path
  FROM product_offering
  WHERE id = #{rootId}

  UNION ALL

  SELECT child.id,
         child.parent_id,
         tree.path || child.id
  FROM product_offering child
  JOIN tree ON child.parent_id = tree.id
  WHERE NOT child.id = ANY(tree.path)
)
SELECT *
FROM tree;

17. Window Function

Window function menghitung nilai berdasarkan window row tanpa collapse seperti GROUP BY.

Contoh ranking quote per customer:

SELECT q.id,
       q.customer_id,
       q.total_amount,
       row_number() OVER (
         PARTITION BY q.customer_id
         ORDER BY q.total_amount DESC, q.id DESC
       ) AS rank_per_customer
FROM quote q
WHERE q.tenant_id = #{tenantId};

Gunakan window function untuk:

• ranking,
• running total,
• latest row per group,
• comparing current vs previous state,
• analytics query,
• deduplication.

Latest Row per Group

WITH ranked_status AS (
  SELECT osh.order_id,
         osh.status,
         osh.changed_at,
         row_number() OVER (
           PARTITION BY osh.order_id
           ORDER BY osh.changed_at DESC, osh.id DESC
         ) AS rn
  FROM order_status_history osh
  WHERE osh.tenant_id = #{tenantId}
)
SELECT order_id,
       status,
       changed_at
FROM ranked_status
WHERE rn = 1;

Performance Concern

Window function sering membutuhkan sort. Untuk dataset besar, pastikan:

• filter cukup selektif,
• index mendukung partition/order jika memungkinkan,
• work_mem dan temp file usage dimonitor,
• query tidak dipakai secara naif di endpoint latency-sensitive.

18. LATERAL JOIN

LATERAL memungkinkan subquery di sisi kanan mengacu ke row dari sisi kiri.

Use case: ambil latest item/status per parent.

SELECT o.id,
       o.order_number,
       latest_status.status,
       latest_status.changed_at
FROM customer_order o
LEFT JOIN LATERAL (
  SELECT osh.status,
         osh.changed_at
  FROM order_status_history osh
  WHERE osh.order_id = o.id
  ORDER BY osh.changed_at DESC,
           osh.id DESC
  LIMIT 1
) latest_status ON true
WHERE o.tenant_id = #{tenantId}
ORDER BY o.created_at DESC
LIMIT 50;

Kapan LATERAL Bagus

LATERAL bagus ketika:

• per parent perlu top-N child,
• child lookup didukung index,
• parent result sudah dibatasi,
• query lebih jelas daripada window untuk use case kecil.

Kapan LATERAL Berbahaya

LATERAL bisa mahal jika parent row sangat banyak dan subquery dieksekusi berulang tanpa index yang tepat.

Index yang mungkin dibutuhkan:

CREATE INDEX idx_order_status_history_order_changed
ON order_status_history (order_id, changed_at DESC, id DESC);

19. DISTINCT dan DISTINCT ON

DISTINCT menghapus duplikat dari hasil. Tetapi gunakan hati-hati karena sering menyembunyikan join yang salah.

SELECT DISTINCT q.id,
                q.quote_number
FROM quote q
JOIN quote_item qi ON qi.quote_id = q.id;

Jika intent-nya mencari quote yang punya item, EXISTS lebih jelas.

PostgreSQL memiliki DISTINCT ON, berguna untuk mengambil satu row per group berdasarkan ordering.

SELECT DISTINCT ON (q.customer_id)
       q.customer_id,
       q.id,
       q.quote_number,
       q.updated_at
FROM quote q
WHERE q.tenant_id = #{tenantId}
ORDER BY q.customer_id,
         q.updated_at DESC,
         q.id DESC;

Mental model: ambil row pertama untuk setiap customer_id berdasarkan ORDER BY.

Review Rule

Untuk DISTINCT ON, pastikan:

• kolom DISTINCT ON cocok dengan prefix ORDER BY,
• tie-breaker deterministik,
• hasil “row pertama” sesuai intent bisnis.

20. INSERT Pattern

INSERT sederhana:

INSERT INTO quote (
  tenant_id,
  quote_number,
  customer_id,
  status,
  created_at,
  updated_at
) VALUES (
  #{tenantId},
  #{quoteNumber},
  #{customerId},
  'DRAFT',
  now(),
  now()
)
RETURNING id;

Gunakan RETURNING untuk mengambil generated id atau computed value tanpa query tambahan.

Java/MyBatis Impact

Dengan PostgreSQL, RETURNING sering lebih jelas daripada bergantung pada generated keys via JDBC, terutama jika perlu mengembalikan beberapa kolom:

RETURNING id, quote_number, created_at

Correctness Concern

Jangan biarkan default aplikasi dan default database bertentangan. Misalnya created_at di Java memakai timezone lokal, sedangkan database memakai now(). Pilih policy konsisten.

21. UPDATE Pattern

UPDATE harus selalu direview dari sisi predicate.

Berbahaya:

UPDATE quote
SET status = 'EXPIRED';

Lebih aman:

UPDATE quote
SET status = 'EXPIRED',
    updated_at = now()
WHERE tenant_id = #{tenantId}
  AND status = 'DRAFT'
  AND valid_until < now()
RETURNING id,
          quote_number,
          status;

Optimistic Update

UPDATE quote
SET status = #{newStatus},
    version = version + 1,
    updated_at = now()
WHERE id = #{quoteId}
  AND tenant_id = #{tenantId}
  AND version = #{expectedVersion}
RETURNING id,
          version;

Jika row count 0, kemungkinan:

• quote tidak ada,
• tenant salah,
• version conflict,
• status tidak memenuhi precondition.

Aplikasi harus membedakan error domain jika diperlukan.

22. DELETE Pattern

DELETE fisik harus hati-hati di enterprise system.

DELETE FROM quote
WHERE id = #{quoteId}
  AND tenant_id = #{tenantId};

Pertanyaan review:

• Apakah data boleh dihapus secara fisik?
• Bagaimana dengan audit trail?
• Bagaimana dengan FK child table?
• Apakah ada retention policy?
• Apakah delete harus soft delete?
• Apakah event harus dipublish?

Soft delete pattern:

UPDATE quote
SET deleted_at = now(),
    deleted_by = #{userId},
    updated_at = now()
WHERE id = #{quoteId}
  AND tenant_id = #{tenantId}
  AND deleted_at IS NULL
RETURNING id;

Correctness Concern

Soft delete bukan gratis. Semua query aktif harus konsisten memakai:

deleted_at IS NULL

Jika lupa, data “terhapus” bisa muncul lagi.

23. UPSERT: INSERT ON CONFLICT

PostgreSQL mendukung upsert dengan ON CONFLICT.

INSERT INTO idempotency_key (
  tenant_id,
  key,
  request_hash,
  response_payload,
  created_at
) VALUES (
  #{tenantId},
  #{key},
  #{requestHash},
  #{responsePayload}::jsonb,
  now()
)
ON CONFLICT (tenant_id, key)
DO NOTHING
RETURNING id;

Atau update:

INSERT INTO tenant_config (
  tenant_id,
  config_key,
  config_value,
  updated_at
) VALUES (
  #{tenantId},
  #{configKey},
  #{configValue}::jsonb,
  now()
)
ON CONFLICT (tenant_id, config_key)
DO UPDATE SET
  config_value = EXCLUDED.config_value,
  updated_at = now()
RETURNING tenant_id,
          config_key,
          updated_at;

Review Questions

• Conflict target sesuai unique constraint?
• Apakah update aman untuk concurrent request?
• Apakah overwriting data lama valid?
• Apakah perlu compare version/hash?
• Apakah perlu audit history?
• Apakah DO NOTHING menyembunyikan error?

24. RETURNING

RETURNING sangat berguna untuk command query.

UPDATE quote
SET status = 'SUBMITTED',
    submitted_at = now(),
    updated_at = now()
WHERE id = #{quoteId}
  AND tenant_id = #{tenantId}
  AND status = 'DRAFT'
RETURNING id,
          quote_number,
          status,
          submitted_at;

Manfaat:

• menghindari query kedua,
• memastikan data yang dikembalikan adalah data yang benar-benar berubah,
• membantu optimistic locking,
• membantu event/outbox construction,
• membantu audit.

Jika RETURNING tidak mengembalikan row, aplikasi bisa menganggap command tidak memenuhi precondition.

25. Bulk Insert

Bulk insert lebih efisien daripada insert satu per satu.

INSERT INTO order_item (
  order_id,
  product_offering_id,
  quantity,
  created_at
) VALUES
  (#{orderId}, #{items[0].productOfferingId}, #{items[0].quantity}, now()),
  (#{orderId}, #{items[1].productOfferingId}, #{items[1].quantity}, now());

Di MyBatis, bulk insert sering dibuat dengan <foreach>.

Caution

Bulk statement terlalu besar dapat menyebabkan:

• SQL string sangat panjang,
• memory pressure,
• lock lebih lama,
• transaction lebih panjang,
• timeout,
• sulit retry parsial.

Gunakan batching/chunking untuk jumlah besar.

26. Bulk Update dan Bulk Delete

Bulk update harus dirancang agar tidak mengunci terlalu banyak row sekaligus.

UPDATE quote
SET status = 'EXPIRED',
    updated_at = now()
WHERE tenant_id = #{tenantId}
  AND status = 'DRAFT'
  AND valid_until < now();

Untuk tabel besar, pertimbangkan chunking:

WITH candidate AS (
  SELECT id
  FROM quote
  WHERE tenant_id = #{tenantId}
    AND status = 'DRAFT'
    AND valid_until < now()
  ORDER BY id
  LIMIT 1000
)
UPDATE quote q
SET status = 'EXPIRED',
    updated_at = now()
FROM candidate c
WHERE q.id = c.id
RETURNING q.id;

Chunking membantu:

• mengurangi lock duration,
• membuat job resumable,
• mengontrol WAL generation,
• mengurangi blast radius,
• memudahkan observability progress.

27. Avoiding Accidental Full Table Scan

Full table scan tidak selalu buruk. Untuk tabel kecil atau filter tidak selektif, sequential scan bisa benar.

Yang berbahaya adalah accidental full scan di tabel besar pada endpoint latency-sensitive.

Red flags:

WHERE lower(email) = lower(#{email})

Tanpa expression index, fungsi di kolom bisa menghambat penggunaan index biasa.

WHERE created_at::date = #{date}

Lebih baik range predicate:

WHERE created_at >= #{startOfDay}
  AND created_at < #{nextDay}

Red flags lain:

• leading wildcard: LIKE '%abc',
• filter nullable tanpa index partial,
• OR kompleks tanpa strategi index,
• implicit cast,
• query tidak memakai tenant predicate,
• ORDER BY kolom tidak terindeks pada dataset besar,
• pagination offset besar.

28. SQL Readability for Code Review

SQL production harus mudah direview.

Gunakan alias yang jelas:

SELECT q.id,
       q.quote_number,
       qi.id AS quote_item_id
FROM quote q
JOIN quote_item qi ON qi.quote_id = q.id
WHERE q.tenant_id = #{tenantId};

Hindari alias terlalu pendek jika query besar:

FROM quote q
JOIN quote_item qi
JOIN quote_item_price qip
JOIN product_offering po

Ini masih wajar. Tetapi pada query kompleks, pastikan alias tidak membuat reviewer bingung.

Formatting Rule

Prefer format:

SELECT column_a,
       column_b,
       column_c
FROM table_a a
JOIN table_b b ON b.a_id = a.id
WHERE a.tenant_id = #{tenantId}
  AND a.status = #{status}
ORDER BY a.updated_at DESC,
         a.id DESC
LIMIT #{limit};

Manfaat:

• diff PR lebih mudah,
• predicate terlihat jelas,
• join condition mudah dicek,
• ORDER BY dan LIMIT terlihat eksplisit.

29. MyBatis-Specific SQL Concerns

MyBatis memberi kontrol SQL eksplisit. Itu kekuatan sekaligus risiko.

#{} vs ${}

Gunakan #{} untuk parameter binding:

WHERE q.id = #{quoteId}

Hindari ${} kecuali untuk identifier/fragment yang sudah di-whitelist secara ketat.

Berbahaya:

ORDER BY ${sortColumn}

Lebih aman: validasi sort column di Java, lalu mapping ke whitelist.

enum QuoteSort {
  UPDATED_AT("q.updated_at"),
  QUOTE_NUMBER("q.quote_number")
}

Lalu mapper hanya menerima fragment yang sudah dipilih dari enum internal, bukan input user mentah.

Dynamic WHERE

Dynamic SQL dapat menghasilkan query tanpa predicate penting jika tidak hati-hati.

Pastikan tenant predicate tidak opsional jika wajib:

WHERE q.tenant_id = #{tenantId}
<if test="status != null">
  AND q.status = #{status}
</if>

Jangan membuat tenant_id masuk <if> kecuali memang ada use case internal yang aman.

30. JDBC-Specific SQL Concerns

JDBC PreparedStatement membantu parameter binding dan mengurangi SQL injection risk.

Tetapi beberapa hal tetap perlu diperhatikan:

• type mapping Java ke PostgreSQL,
• timestamp/timezone,
• JSONB binding,
• array binding,
• fetch size untuk result besar,
• statement timeout,
• transaction timeout,
• generated keys vs RETURNING,
• SQLState mapping.

Contoh issue: Java mengirim UUID sebagai string dan query melakukan cast tidak konsisten.

Lebih baik pastikan TypeHandler/JDBC binding sesuai tipe kolom.

31. Microservices and Event-Driven Impact

SQL di satu service dapat memengaruhi event-driven architecture.

Contoh command:

UPDATE customer_order
SET status = 'SUBMITTED'
WHERE id = #{orderId}
  AND status = 'DRAFT'
RETURNING id, status;

Jika update berhasil, aplikasi mungkin membuat outbox event OrderSubmitted dalam transaksi yang sama.

Correctness questions:

• Apakah event dibuat hanya jika row benar-benar berubah?
• Apakah command idempotent?
• Apakah duplicate request menghasilkan duplicate event?
• Apakah query memakai expected current state?
• Apakah outbox insert berada dalam transaksi yang sama?

SQL command dan event consistency tidak bisa dipisahkan.

32. Cloud, Kubernetes, and On-Prem Operational Impact

Query yang sama bisa berdampak berbeda tergantung deployment:

Kubernetes

Jika service replica bertambah, jumlah query concurrent bertambah. Query yang “cukup cepat” di satu replica bisa menjadi bottleneck saat 20 replica berjalan bersamaan.

AWS/Azure Managed PostgreSQL

Query berat bisa terlihat melalui metric seperti CPU, IO, temp file, Performance Insights/Query Store/monitoring setara. Tetapi akses parameter dan extension bisa dibatasi.

On-Prem

Query buruk bisa langsung menekan disk, WAL, checkpoint, atau storage controller. Observability mungkin bergantung pada stack internal.

Hybrid

Latency network dan cross-region read dapat memperbesar dampak query chatty atau N+1.

33. Failure Modes

SQL failure tidak hanya syntax error.

34. How to Detect SQL Problems

Sinyal yang perlu dilihat:

• slow query log,
• pg_stat_statements,
• EXPLAIN ANALYZE,
• query latency dashboard,
• database CPU/IO,
• lock wait,
• rows returned vs rows expected,
• API error rate,
• timeout count,
• connection pool saturation,
• application logs dengan query id/correlation id.

Untuk query yang mengubah data, cek juga:

• row count affected,
• outbox event count,
• audit trail,
• domain state transition history,
• reconciliation result.

35. How to Debug SQL Problems

Workflow praktis:

1. Reproduksi query dengan parameter yang aman.
2. Pastikan intent bisnis query benar.
3. Cek actual row count dan cardinality.
4. Jalankan EXPLAIN atau EXPLAIN ANALYZE di environment aman.
5. Bandingkan estimated rows vs actual rows.
6. Cek index yang tersedia.
7. Cek apakah filter/join/order cocok dengan index.
8. Cek lock/wait jika query lambat hanya saat concurrent.
9. Cek apakah query dipanggil terlalu sering dari aplikasi.
10. Perbaiki query shape sebelum menambah index.
11. Jika perlu index, hitung write overhead dan rollout plan.
12. Tambahkan test atau regression guard.

36. Correctness Concerns

SQL correctness concerns paling umum:

• missing tenant predicate,
• missing soft-delete predicate,
• wrong JOIN type,
• duplicate counting,
• NULL semantics,
• non-deterministic ordering,
• update tanpa expected current state,
• delete tanpa retention/audit concern,
• upsert yang overwrite data tanpa guard,
• query list yang tidak konsisten dengan detail endpoint,
• domain invariant hanya dicek di Java tetapi tidak dilindungi constraint.

37. Performance Concerns

Performance concerns paling umum:

• accidental full table scan,
• sort besar,
• aggregation besar,
• N+1 query,
• large offset,
• large IN clause,
• over-fetching columns,
• JSONB query tanpa index tepat,
• function pada indexed column tanpa expression index,
• implicit cast,
• stale statistics,
• query plan regression,
• query terlalu chatty antar service atau cross-region.

38. Security and Privacy Concerns

SQL harus mendukung security/privacy boundary:

• tenant isolation,
• account/customer authorization,
• least privilege access,
• PII minimization,
• no accidental SELECT *,
• log redaction untuk parameter sensitif,
• safe parameter binding,
• dynamic SQL whitelist,
• auditability untuk data sensitive mutation.

SQL injection bukan hanya masalah string concatenation. Dynamic ORDER BY, dynamic table name, dynamic filter fragment, dan raw SQL provider juga harus direview.

39. Observability Concerns

Query production perlu bisa diobservasi.

Praktik yang membantu:

• beri nama mapper/method yang jelas,
• log correlation id di aplikasi,
• aktifkan slow query logging sesuai policy,
• gunakan comment query jika diizinkan internal,
• mapping SQLState ke error category,
• ukur row count untuk command penting,
• dashboard query latency untuk endpoint penting,
• simpan EXPLAIN untuk query kritikal di PR/design doc.

Contoh query comment jika policy mengizinkan:

/* service=quote-order api=submitQuote mapper=QuoteMapper.submit */
UPDATE quote
SET status = 'SUBMITTED'
WHERE id = #{quoteId}
  AND tenant_id = #{tenantId}
  AND status = 'DRAFT'
RETURNING id;

Harus diverifikasi apakah komentar SQL diizinkan oleh internal logging/security policy.

40. SQL PR Review Checklist

Gunakan checklist ini saat review query di mapper, migration, function, atau script.

Correctness

• Apakah predicate mencerminkan business invariant?
• Apakah tenant/account boundary wajib sudah ada?
• Apakah soft delete/status/effective date diperhitungkan?
• Apakah JOIN type benar?
• Apakah NULL semantics benar?
• Apakah aggregate tidak double count?
• Apakah ORDER BY deterministik?
• Apakah UPDATE/DELETE punya predicate aman?
• Apakah command memakai expected current state jika perlu?

Performance

• Apakah query bisa memakai index yang ada?
• Apakah query berpotensi full scan di tabel besar?
• Apakah sort/aggregate bisa besar?
• Apakah pagination scalable?
• Apakah mengambil kolom terlalu banyak?
• Apakah ada N+1 pattern?
• Apakah dynamic SQL menghasilkan plan buruk?
• Apakah perlu EXPLAIN dilampirkan?

Concurrency

• Apakah row yang sama bisa diubah concurrent?
• Apakah perlu optimistic locking?
• Apakah perlu SELECT FOR UPDATE?
• Apakah upsert aman terhadap race?
• Apakah transaction terlalu panjang?

Security/Privacy

• Apakah parameter binding aman?
• Apakah dynamic fragment di-whitelist?
• Apakah PII hanya diambil jika perlu?
• Apakah data access boundary jelas?

Operations

• Apakah query high-traffic?
• Apakah timeout sudah masuk akal?
• Apakah query mudah dilacak di monitoring?
• Apakah rollback behavior jelas jika query gagal?

41. Internal Verification Checklist

Cek hal berikut di repository, database, pipeline, atau diskusi internal:

MyBatis Mapper

• Mapper mana yang memakai SELECT *.
• Query mana yang memakai ${}.
• Dynamic ORDER BY/filter yang berasal dari input API.
• Query dengan JOIN banyak tabel.
• Query dengan pagination offset.
• Query dengan large IN clause.
• Query dengan recursive CTE/window/LATERAL.

JDBC/Java Layer

• PreparedStatement usage.
• SQLState exception mapping.
• Generated keys vs RETURNING.
• Fetch size untuk result besar.
• Timeout configuration.
• TypeHandler untuk UUID, JSONB, enum, timestamp.

Database Schema

• Index yang mendukung query utama.
• Constraint yang melindungi invariant.
• FK/unique/check constraint yang relevan.
• Soft delete pattern.
• Tenant/account partitioning secara logical.

Observability

• Slow query log aktif atau tidak.
• pg_stat_statements tersedia atau tidak.
• Dashboard query latency.
• Lock wait/timeout dashboard.
• Incident notes terkait slow query.

Team Process

• Apakah PR query wajib menyertakan EXPLAIN untuk tabel besar?
• Apakah ada SQL style guide?
• Apakah DBA/SRE review wajib untuk migration/index besar?
• Apakah ada query regression test?

42. Senior Engineer Heuristics

Gunakan heuristik berikut:

1. Query tanpa ORDER BY tidak boleh dipakai untuk hasil yang mengandalkan urutan.
2. Query dengan LIMIT 1 harus menjelaskan “satu yang mana”.
3. JOIN yang menghasilkan duplicate harus dianggap suspicious sampai terbukti benar.
4. DISTINCT sering merupakan patch untuk join yang salah.
5. WHERE adalah domain boundary, bukan detail teknis.
6. UPDATE tanpa expected state rawan lost update atau invalid transition.
7. DELETE harus direview bersama audit, retention, dan FK impact.
8. SELECT * adalah coupling tersembunyi.
9. Offset pagination besar adalah API smell.
10. SQL yang tidak bisa dijelaskan secara bisnis biasanya belum siap production.

43. Ringkasan

SQL production-grade di PostgreSQL bukan sekadar syntax. Ia adalah kontrak antara domain, aplikasi, transaction boundary, planner, index, dan operasi production.

Setelah part ini, Anda harus mampu:

• membaca query dari sisi correctness dan performance,
• mengenali JOIN/cardinality problem,
• memahami NULL semantics,
• memilih pagination pattern,
• memakai CTE/window/LATERAL secara tepat,
• mereview INSERT/UPDATE/DELETE/UPSERT dengan aman,
• menghubungkan SQL dengan MyBatis/JDBC,
• melihat SQL sebagai bagian dari reliability dan security boundary.

Part berikutnya akan masuk ke data modelling enterprise: schema, table, key, constraint, normalization, denormalization, audit, soft delete, temporal validity, multi-tenancy, dan review checklist.