MyBatis with PostgreSQL Advanced Features

1. Core Thesis

MyBatis menjadi sangat kuat ketika aplikasi Java/JAX-RS perlu memakai kemampuan PostgreSQL secara eksplisit.

Berbeda dari JPA/Hibernate yang berusaha memodelkan database sebagai graph entity dan persistence context, MyBatis membiarkan engineer menulis SQL secara langsung. Ini membuat MyBatis sangat cocok untuk fitur PostgreSQL yang sering tidak nyaman diekspresikan lewat ORM:

• jsonb query,
• PostgreSQL array,
• enum database,
• full-text search,
• RETURNING,
• ON CONFLICT DO UPDATE,
• SELECT ... FOR UPDATE SKIP LOCKED,
• advisory lock,
• function/procedure,
• trigger-aware write path,
• reporting query,
• read model projection,
• query dengan CTE/window function,
• query yang perlu EXPLAIN dan tuning langsung.

Prinsip utama:

MyBatis adalah alat yang tepat ketika SQL adalah bagian penting dari desain. Tetapi semakin banyak PostgreSQL-specific feature dipakai, semakin besar kebutuhan untuk transaction discipline, migration discipline, testing dengan PostgreSQL asli, dan PR review yang SQL-aware.

Advanced PostgreSQL feature bukan sekadar syntax. Ia membawa konsekuensi terhadap:

• data correctness,
• lock behaviour,
• query planner,
• index design,
• transaction boundary,
• migration compatibility,
• observability,
• cloud/on-prem portability,
• dan ownership antara application layer dan database layer.

2. Mental Model: MyBatis as PostgreSQL Capability Gateway

Dalam MyBatis, mapper adalah gateway dari Java code ke SQL PostgreSQL.

JAX-RS Resource
   -> Application Service
      -> Transaction Boundary
         -> Repository / DAO
            -> MyBatis Mapper
               -> JDBC
                  -> PostgreSQL

Ketika mapper memakai fitur advanced PostgreSQL, mapper tidak lagi sekadar “data access method”. Ia menjadi kontrak eksplisit terhadap behavior database.

Contoh:

<select id="findPendingJobs" resultMap="JobResultMap">
  SELECT
    j.id,
    j.payload,
    j.status,
    j.created_at
  FROM job_queue j
  WHERE j.status = 'PENDING'
  ORDER BY j.created_at ASC
  FOR UPDATE SKIP LOCKED
  LIMIT #{limit}
</select>

Method ini bukan hanya read query. Ia:

• mengambil row,
• mengunci row,
• mengabaikan row yang sedang dikunci worker lain,
• bergantung pada transaction aktif,
• bergantung pada ordering,
• bergantung pada index,
• cocok untuk worker queue,
• tidak cocok dipanggil di endpoint read-only biasa.

Nama method, package, documentation, test, dan transaction boundary harus mencerminkan behavior tersebut.

3. Why PostgreSQL-Specific SQL Belongs Naturally in MyBatis

MyBatis cocok untuk PostgreSQL advanced feature karena:

1. SQL terlihat langsung.
2. Operator PostgreSQL bisa digunakan tanpa translasi ORM.
3. Query plan bisa dianalisis dari SQL yang sama dengan production.
4. Projection DTO lebih natural daripada entity graph.
5. Function/procedure call bisa dikontrol eksplisit.
6. RETURNING dapat meminimalkan round trip.
7. Locking SQL seperti FOR UPDATE SKIP LOCKED mudah ditulis.
8. JSONB/array/full-text search tidak dipaksa menjadi object relation model.
9. Dynamic SQL bisa tetap explicit jika dijaga dengan whitelist dan tests.
10. Query ownership lebih jelas: mapper method memiliki SQL.

Tetapi trade-off-nya:

• portable antar database lebih rendah,
• mapper menjadi lebih dekat ke PostgreSQL internals,
• test harus memakai PostgreSQL asli,
• migration harus sinkron dengan SQL,
• developer harus paham planner/index/operator,
• SQL yang kompleks bisa menjadi business logic tersembunyi,
• fitur seperti trigger/procedure bisa menyulitkan debugging.

4. PostgreSQL-Specific Feature Decision Rule

Sebelum memakai fitur advanced PostgreSQL di MyBatis, tanyakan:

1. Apakah fitur ini menyederhanakan correctness atau hanya membuat query “pintar”?
2. Apakah behavior-nya jelas bagi reviewer Java?
3. Apakah test integration memakai PostgreSQL asli?
4. Apakah index mendukung operator yang dipakai?
5. Apakah SQL ini aman terhadap tenant, soft delete, effective date, dan security filter?
6. Apakah transaction boundary terlihat jelas?
7. Apakah failure mode-nya diketahui?
8. Apakah observability cukup untuk debug production?
9. Apakah migration sudah mencakup function/type/index terkait?
10. Apakah ada alternatif JPA/native query/JDBC yang lebih tepat?

Rule praktis:

Pakai PostgreSQL-specific SQL di MyBatis jika SQL tersebut adalah bagian dari model operasi yang eksplisit, bukan shortcut tersembunyi yang hanya dipahami penulis PR.

5. JSONB in PostgreSQL and MyBatis

jsonb berguna untuk data semi-structured, extension attributes, payload event, external system response, configuration, audit metadata, atau catalog attributes yang tidak selalu cocok menjadi kolom relational normal.

Tetapi jsonb bukan alasan untuk menghindari modelling.

Gunakan jsonb ketika:

• shape data fleksibel dan tidak semua field sering difilter,
• payload perlu disimpan sebagai dokumen,
• attribute extension berubah antar tenant/product/version,
• data berasal dari external system,
• data lebih sering dibaca sebagai whole document daripada di-join.

Hati-hati memakai jsonb ketika:

• field di dalam JSON sering dipakai untuk filter/sort/join,
• constraint relational penting,
• referential integrity dibutuhkan,
• query harus sangat cepat dan predictable,
• field menjadi domain invariant utama.

Jika sebuah field JSONB selalu difilter, di-sort, di-join, dan divalidasi, kemungkinan ia seharusnya menjadi kolom normal atau minimal memiliki generated column/index expression.

6. JSONB Query Example

Contoh table:

CREATE TABLE quote_attribute_snapshot (
  quote_id UUID NOT NULL,
  version INTEGER NOT NULL,
  attributes JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (quote_id, version)
);

Contoh query MyBatis:

<select id="findQuotesByProductFamily" resultMap="QuoteAttributeRowResultMap">
  SELECT
    q.quote_id,
    q.version,
    q.attributes,
    q.created_at
  FROM quote_attribute_snapshot q
  WHERE q.attributes ->> 'productFamily' = #{productFamily}
  ORDER BY q.created_at DESC
  LIMIT #{limit}
</select>

Hal yang perlu direview:

• apakah key productFamily stabil,
• apakah ada index expression,
• apakah null/missing key ditangani,
• apakah comparison case-sensitive sesuai requirement,
• apakah query ini tenant-aware,
• apakah limit dibatasi,
• apakah order stabil,
• apakah result mapping JSONB benar.

Index expression bisa seperti:

CREATE INDEX idx_quote_attr_product_family
ON quote_attribute_snapshot ((attributes ->> 'productFamily'));

Jika memakai containment:

WHERE q.attributes @> #{attributesFilter}::jsonb

pertimbangkan GIN index:

CREATE INDEX idx_quote_attr_attributes_gin
ON quote_attribute_snapshot
USING GIN (attributes);

7. JSONB TypeHandler

Di Java, JSONB sering dimap ke:

• String,
• Map<String, Object>,
• domain-specific value object,
• Jackson JsonNode,
• custom class.

Mapping paling cepat biasanya String, tetapi paling lemah secara type-safety.

Mapping paling nyaman biasanya JsonNode atau custom value object, tetapi perlu TypeHandler dan test.

Contoh skeleton TypeHandler:

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(JsonNode.class)
public final class JsonNodeTypeHandler extends BaseTypeHandler<JsonNode> {

    private final ObjectMapper objectMapper;

    public JsonNodeTypeHandler(ObjectMapper objectMapper) {
        this.objectMapper = objectMapper;
    }

    @Override
    public void setNonNullParameter(
            PreparedStatement ps,
            int i,
            JsonNode parameter,
            JdbcType jdbcType
    ) throws SQLException {
        PGobject jsonObject = new PGobject();
        jsonObject.setType("jsonb");
        jsonObject.setValue(parameter.toString());
        ps.setObject(i, jsonObject);
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getString(columnName));
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getString(columnIndex));
    }

    @Override
    public JsonNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parse(cs.getString(columnIndex));
    }

    private JsonNode parse(String raw) throws SQLException {
        if (raw == null) {
            return null;
        }
        try {
            return objectMapper.readTree(raw);
        } catch (IOException e) {
            throw new SQLException("Failed to parse JSONB value", e);
        }
    }
}

Review concern:

• apakah ObjectMapper sama dengan application standard,
• apakah null handling jelas,
• apakah invalid JSON menjadi SQLException yang terobservasi,
• apakah TypeHandler didaftarkan di konfigurasi MyBatis,
• apakah test memakai PostgreSQL asli,
• apakah serialization format stabil untuk backward compatibility.

8. JSONB Failure Modes

Failure umum:

1. Missing key dianggap sama dengan null.
2. Query JSONB tidak memakai index.
3. JSON string dibandingkan sebagai text padahal seharusnya numeric/date.
4. Key rename di migration tidak memperbarui mapper.
5. JSONB payload berubah shape tetapi TypeHandler/domain parser tidak compatible.
6. Query containment terlalu luas dan scan table besar.
7. JSONB menyimpan data yang seharusnya punya foreign key.
8. PII tersembunyi di JSONB dan ikut masuk log.
9. JSONB field menjadi dependency event contract tanpa versioning.
10. Dynamic JSONB path dibangun dengan ${} dan rawan injection.

Debugging steps:

• log SQL final dengan parameter aman,
• jalankan EXPLAIN (ANALYZE, BUFFERS) di query representative,
• cek index expression/GIN index,
• sample payload JSONB,
• cek null/missing key semantics,
• cek migration history,
• cek data distribution,
• cek slow query log,
• cek test fixture yang mencakup shape lama dan baru.

9. Full-Text Search with MyBatis

PostgreSQL full-text search berguna untuk search internal, catalog search, quote/order notes, product descriptions, atau textual lookup.

Contoh query:

<select id="searchProductText" resultMap="ProductSearchRowResultMap">
  SELECT
    p.id,
    p.product_code,
    p.name,
    ts_rank(
      to_tsvector('english', coalesce(p.name, '') || ' ' || coalesce(p.description, '')),
      plainto_tsquery('english', #{query})
    ) AS rank
  FROM product p
  WHERE to_tsvector('english', coalesce(p.name, '') || ' ' || coalesce(p.description, ''))
        @@ plainto_tsquery('english', #{query})
  ORDER BY rank DESC, p.id ASC
  LIMIT #{limit}
</select>

Review concern:

• apakah language configuration benar,
• apakah ada generated tsvector column,
• apakah ada GIN index,
• apakah ranking deterministic,
• apakah input query di-bind aman,
• apakah limit dibatasi,
• apakah fallback search behavior jelas,
• apakah search tidak menggantikan authorization/tenant filter.

Untuk production, hindari recompute to_tsvector besar di runtime tanpa index. Pertimbangkan generated column atau materialized search column.

10. PostgreSQL Array Types

Array berguna untuk data kecil yang memang list sederhana, misalnya tags, flags, external identifiers, feature codes, atau attribute codes.

Tetapi array bukan pengganti join table jika:

• item perlu foreign key,
• item perlu metadata,
• item perlu query kompleks,
• item perlu audit individual,
• item sering dimutasi partial,
• cardinality besar.

Contoh query array containment:

<select id="findPlansContainingFeature" resultMap="PlanRowResultMap">
  SELECT
    p.id,
    p.plan_code,
    p.feature_codes
  FROM product_plan p
  WHERE p.feature_codes @> ARRAY[#{featureCode}]::text[]
  ORDER BY p.plan_code ASC
</select>

Review concern:

• apakah array cardinality kecil,
• apakah operator didukung index GIN jika volume besar,
• apakah order array bermakna,
• apakah null vs empty array jelas,
• apakah Java mapping stabil,
• apakah array menyembunyikan relationship yang seharusnya relational.

11. Enum Types: PostgreSQL Enum vs Text + Check Constraint

PostgreSQL enum memberi type safety di database, tetapi evolusi enum perlu migration.

Pilihan umum:

1. PostgreSQL enum type.
2. TEXT/VARCHAR dengan CHECK constraint.
3. Reference table.

PostgreSQL enum cocok jika values stabil dan tidak perlu metadata.

TEXT + CHECK lebih fleksibel untuk migration dan rollback.

Reference table cocok jika value punya label, order, metadata, status, effective date, atau ownership business.

Contoh mapper:

<select id="findQuotesByStatus" resultMap="QuoteRowResultMap">
  SELECT
    q.id,
    q.quote_number,
    q.status
  FROM quote q
  WHERE q.status = #{status}::quote_status
</select>

Review concern:

• apakah Java enum sinkron dengan database enum,
• apakah migration enum backward-compatible,
• apakah rollback mungkin,
• apakah unknown enum value membuat deserialization gagal,
• apakah integration test mencakup semua enum values,
• apakah enum lebih tepat menjadi reference data.

12. Function Calls

PostgreSQL function dapat digunakan untuk:

• calculation yang dekat dengan data,
• reusable SQL expression,
• validation berbasis database,
• derived value,
• data transformation,
• search helper,
• security helper.

Contoh:

<select id="calculateQuoteTotal" resultType="java.math.BigDecimal">
  SELECT calculate_quote_total(#{quoteId}::uuid)
</select>

Pertanyaan review:

• Apakah function pure atau punya side effect?
• Apakah function deterministic?
• Apakah function membaca banyak table?
• Apakah function menulis data?
• Apakah function versioned melalui migration?
• Apakah function terobservasi ketika lambat?
• Apakah error function dipetakan dengan benar ke Java exception?
• Apakah function mengandung business logic yang seharusnya di application layer?

Rule:

Function yang dipakai sebagai helper query masih relatif aman. Function yang mengubah state harus diperlakukan seperti command path dengan transaction, audit, retry, dan test yang serius.

13. Procedure Calls and CallableStatement Semantics

Procedure biasanya lebih command-like daripada function. Ia dapat melakukan write, mengelola flow database-side, atau memanggil logic lain.

Di MyBatis, procedure bisa dipanggil dengan statement type CALLABLE.

<select id="repriceQuote" statementType="CALLABLE">
  { call reprice_quote(
      #{quoteId, jdbcType=OTHER, mode=IN},
      #{requestedBy, jdbcType=VARCHAR, mode=IN},
      #{resultCode, jdbcType=VARCHAR, mode=OUT}
    ) }
</select>

Review concern:

• apakah procedure mengubah state,
• apakah transaction dikelola oleh application atau procedure,
• apakah procedure melakukan commit/rollback internal,
• apakah OUT parameter jelas,
• apakah error mapping jelas,
• apakah audit dilakukan konsisten,
• apakah procedure versioned,
• apakah procedure dipanggil dari lebih dari satu service,
• apakah procedure menjadi hidden business process.

Untuk enterprise system, procedure call harus punya ownership jelas. Jangan biarkan procedure menjadi “sistem kedua” yang tidak terlihat oleh Java reviewer.

14. Trigger Side Effect Awareness

Trigger sering dipakai untuk:

• audit trail,
• updated timestamp,
• denormalized summary,
• validation,
• partition routing,
• history table,
• event/outbox insert,
• data normalization.

Risikonya: mapper SQL terlihat sederhana, tetapi efek sebenarnya lebih besar.

Contoh mapper:

<update id="updateQuoteStatus">
  UPDATE quote
  SET
    status = #{status},
    updated_at = now()
  WHERE id = #{quoteId}
</update>

Jika ada trigger quote_status_history_trigger, update ini mungkin juga:

• insert history row,
• update aggregate table,
• validate transition,
• publish via outbox,
• reject status tertentu.

Review concern:

• apakah mapper method name mencerminkan side effect,
• apakah trigger diketahui oleh team backend,
• apakah trigger ada di migration repository,
• apakah integration test memverifikasi side effect,
• apakah observability mencakup trigger cost,
• apakah retry/idempotency aman jika trigger insert history,
• apakah audit terjadi dua kali: di Java dan trigger.

15. RETURNING Clause

PostgreSQL RETURNING sangat berguna untuk mengambil data hasil insert/update/delete tanpa round trip tambahan.

Contoh insert:

<select id="insertQuote" resultMap="QuoteRowResultMap">
  INSERT INTO quote (
    customer_id,
    quote_number,
    status,
    created_at,
    updated_at
  ) VALUES (
    #{customerId},
    #{quoteNumber},
    #{status},
    now(),
    now()
  )
  RETURNING
    id,
    customer_id,
    quote_number,
    status,
    created_at,
    updated_at
</select>

Di MyBatis, INSERT ... RETURNING sering dimodelkan sebagai <select> karena SQL mengembalikan row.

Keuntungan:

• mengurangi round trip,
• mengambil generated id dan timestamp database,
• memastikan value yang dipakai adalah value final database,
• berguna jika trigger/default mengisi kolom.

Review concern:

• apakah mapper XML memakai tag yang sesuai dengan framework convention internal,
• apakah transaction behavior jelas,
• apakah returned column lengkap sesuai model,
• apakah trigger/default side effect tercermin,
• apakah method name tidak membingungkan,
• apakah result map cocok.

16. UPDATE RETURNING for Command Result

Untuk command path, UPDATE ... RETURNING bisa menghindari update buta.

<select id="transitionQuoteStatus" resultMap="QuoteStatusTransitionResultMap">
  UPDATE quote
  SET
    status = #{newStatus},
    updated_at = now(),
    version = version + 1
  WHERE id = #{quoteId}
    AND status = #{expectedCurrentStatus}
    AND version = #{expectedVersion}
  RETURNING
    id,
    status,
    version,
    updated_at
</select>

Jika row tidak returned:

• quote tidak ada,
• status tidak sesuai,
• version stale,
• row tidak visible karena tenant/security filter,
• atau data sudah berubah.

Aplikasi harus membedakan error jika business membutuhkan detail. Tetapi jangan bocorkan detail yang tidak aman.

Pattern ini bagus untuk:

• optimistic locking manual,
• state transition,
• idempotent command,
• audit command result,
• avoiding read-before-write race.

17. UPSERT with ON CONFLICT

ON CONFLICT sering dipakai untuk idempotency, cache table, materialized read model, counter, external reference, atau deduplication.

Contoh idempotency:

<select id="tryCreateIdempotencyRecord" resultMap="IdempotencyRecordResultMap">
  INSERT INTO idempotency_record (
    idempotency_key,
    request_fingerprint,
    status,
    created_at,
    updated_at
  ) VALUES (
    #{key},
    #{fingerprint},
    'PROCESSING',
    now(),
    now()
  )
  ON CONFLICT (idempotency_key)
  DO UPDATE SET
    updated_at = idempotency_record.updated_at
  RETURNING
    idempotency_key,
    request_fingerprint,
    status,
    response_payload,
    created_at,
    updated_at
</select>

Catatan: contoh DO UPDATE SET updated_at = idempotency_record.updated_at adalah no-op update untuk memaksa RETURNING. Ini harus direview hati-hati karena tetap bisa mengambil lock/update semantics.

Review concern:

• conflict target benar,
• unique index mendukung conflict,
• update clause tidak merusak data existing,
• idempotency semantics jelas,
• concurrent request behaviour diuji,
• audit/timestamp tidak misleading,
• retry aman,
• deadlock risk dipahami.

18. UPSERT Failure Modes

Failure umum:

1. Conflict target tidak sesuai invariant bisnis.
2. DO UPDATE overwrite data penting.
3. Race condition karena unique constraint kurang lengkap.
4. Idempotency key tidak memasukkan tenant/customer context.
5. Request fingerprint berbeda tetapi key sama tidak ditolak.
6. updated_at berubah pada duplicate request sehingga audit misleading.
7. Upsert dipakai untuk state transition yang seharusnya explicit.
8. Partial unique index tidak dipahami oleh reviewer.
9. Upsert menyembunyikan insert vs update semantics dari service layer.
10. Retry menghasilkan side effect berulang.

Debugging:

• cek unique index definition,
• cek conflict target,
• cek row history/audit,
• cek concurrent test,
• cek transaction isolation,
• cek application retry logs,
• cek duplicate key error vs successful upsert metrics.

19. SKIP LOCKED for Worker Queues

FOR UPDATE SKIP LOCKED memungkinkan beberapa worker mengambil job tanpa menunggu row yang dikunci worker lain.

Pattern:

<select id="claimPendingJobs" resultMap="JobRowResultMap">
  SELECT
    j.id,
    j.payload,
    j.attempt_count,
    j.available_at
  FROM job_queue j
  WHERE j.status = 'PENDING'
    AND j.available_at <= now()
  ORDER BY j.available_at ASC, j.id ASC
  FOR UPDATE SKIP LOCKED
  LIMIT #{limit}
</select>

Lalu dalam transaction yang sama:

<update id="markJobsProcessing">
  UPDATE job_queue
  SET
    status = 'PROCESSING',
    locked_by = #{workerId},
    locked_at = now(),
    attempt_count = attempt_count + 1
  WHERE id IN
  <foreach collection="jobIds" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</update>

Critical rule:

Row yang dipilih dengan FOR UPDATE SKIP LOCKED hanya terkunci selama transaction aktif. Jika select dan update tidak berada dalam transaction yang sama, pattern ini rusak.

20. SKIP LOCKED Review Concerns

Checklist:

• Apakah method hanya dipanggil dalam transaction?
• Apakah select dan mark/claim berada dalam transaction yang sama?
• Apakah ordering stabil?
• Apakah ada index untuk status + available_at?
• Apakah batch size dibatasi?
• Apakah worker crash handling ada?
• Apakah stuck PROCESSING job bisa direcover?
• Apakah retry/backoff ada?
• Apakah starvation mungkin?
• Apakah job idempotent?
• Apakah monitoring queue depth/age ada?
• Apakah transaction tidak melakukan external call terlalu lama?

Anti-pattern:

BEGIN
  SELECT ... FOR UPDATE SKIP LOCKED
COMMIT

call external system

BEGIN
  UPDATE selected jobs
COMMIT

Row sudah tidak locked setelah commit pertama. Worker lain bisa mengambil job yang sama.

21. Advisory Locks

PostgreSQL advisory lock adalah lock application-defined berbasis key numerik, bukan row/table.

Dipakai untuk:

• mencegah dua proses mengerjakan resource logical yang sama,
• leader-like task ringan,
• scheduled job mutual exclusion,
• per-customer/per-quote operation lock,
• migration/maintenance guard.

Contoh transaction-level advisory lock:

<select id="tryAcquireQuoteAdvisoryLock" resultType="boolean">
  SELECT pg_try_advisory_xact_lock(#{lockKey})
</select>

Jika hasil false, lock tidak didapat.

Keuntungan transaction-level lock:

• otomatis release saat commit/rollback,
• lebih aman daripada session-level untuk connection pool,
• scope jelas.

Hindari session-level advisory lock kecuali benar-benar paham connection pooling. Session-level lock bisa bocor jika connection kembali ke pool dengan lock belum dilepas.

22. Advisory Lock Key Design

Lock key harus deterministic dan collision-resistant dalam batas kebutuhan.

Contoh:

lock namespace: quote-operation
business key: quote_id
lock key: hash64("quote-operation:" + quote_id)

Concern:

• collision risk,
• tenant isolation,
• namespace antar fitur,
• observability key mapping,
• deadlock ordering jika mengambil banyak lock,
• timeout/fallback behavior,
• test concurrent acquisition.

Rule:

Advisory lock tidak menggantikan database constraint. Ia hanya koordinasi concurrency tambahan. Invariant tetap harus ditegakkan dengan constraint atau conditional update.

23. CTE and Window Function Queries

MyBatis sangat cocok untuk query PostgreSQL kompleks seperti CTE dan window function.

Contoh ranking latest version:

<select id="findLatestQuoteVersions" resultMap="QuoteVersionRowResultMap">
  WITH ranked AS (
    SELECT
      qv.*,
      row_number() OVER (
        PARTITION BY qv.quote_id
        ORDER BY qv.version DESC
      ) AS rn
    FROM quote_version qv
    WHERE qv.quote_id IN
    <foreach collection="quoteIds" item="id" open="(" separator="," close=")">
      #{id}
    </foreach>
  )
  SELECT
    quote_id,
    version,
    status,
    payload,
    created_at
  FROM ranked
  WHERE rn = 1
</select>

Review concern:

• CTE materialization behavior sesuai PostgreSQL version,
• partition/order index mendukung,
• IN clause size dibatasi,
• result set size predictable,
• query explainable,
• test mencakup multiple versions.

24. PostgreSQL-Specific SQL and JAX-RS Backend Lifecycle

Dalam Java/JAX-RS backend, query advanced harus dipahami dalam request lifecycle:

HTTP request
  -> authentication / authorization
  -> resource method
  -> application service
  -> transaction boundary
  -> MyBatis mapper
  -> PostgreSQL feature
  -> transaction commit/rollback
  -> response mapping

Pertanyaan penting:

• Apakah query membutuhkan transaction aktif?
• Apakah query mengambil lock?
• Apakah query bisa lama?
• Apakah query memegang connection selama stream?
• Apakah timeout HTTP lebih pendek dari DB statement timeout?
• Apakah error SQL dipetakan menjadi HTTP error tepat?
• Apakah query memakai tenant/security filter dari request context?
• Apakah PII dari JSONB/parameter tidak ter-log?

Advanced query tidak boleh dianggap detail internal mapper saja. Ia memengaruhi latency, concurrency, response semantics, dan failure behaviour endpoint.

25. Transaction Boundary Implications

Beberapa fitur PostgreSQL hanya benar jika transaction boundary tepat:

Rule:

Jika SQL advanced memiliki concurrency meaning, mapper method harus dipanggil dari service method yang transaction boundary-nya eksplisit dan mudah dibaca.

26. Microservices and Event-Driven Implications

Dalam microservices dan event-driven architecture, PostgreSQL advanced feature sering muncul di:

• outbox polling dengan SKIP LOCKED,
• inbox deduplication dengan unique constraint/UPSERT,
• idempotency record dengan ON CONFLICT,
• read model projection dengan CTE/window function,
• event payload JSONB,
• reconciliation query,
• saga state transition dengan conditional update,
• distributed-ish coordination dengan advisory lock.

Correctness rule:

• database write dan outbox insert harus satu transaction,
• event publisher harus idempotent,
• consumer inbox harus dedupe,
• retry harus aman,
• SKIP LOCKED worker harus handle crash recovery,
• ON CONFLICT harus merepresentasikan invariant bisnis,
• JSONB event payload harus versioned.

27. Kubernetes, Cloud, and On-Prem Considerations

Advanced PostgreSQL SQL di production dipengaruhi environment:

• jumlah pod memengaruhi concurrency ke queue table,
• replica scaling bisa meningkatkan lock contention,
• rolling deployment bisa menjalankan dua versi SQL terhadap schema yang sama,
• cloud database memiliki parameter timeout/connection limit spesifik,
• network latency membuat round trip reduction seperti RETURNING lebih bernilai,
• managed PostgreSQL bisa membatasi extension tertentu,
• on-prem mungkin punya version/extension berbeda,
• migration job di Kubernetes harus sinkron dengan mapper SQL,
• secret rotation tidak boleh memutus worker transaction panjang.

Checklist deployment:

• PostgreSQL version sama antara local/test/prod?
• Extension tersedia?
• Index sudah dibuat sebelum query baru aktif?
• Mapper compatible dengan old dan new schema selama rollout?
• Worker replica count sesuai lock/queue design?
• Statement timeout cukup tetapi tidak terlalu longgar?
• Slow query dashboard mencakup mapper baru?

28. Performance Concerns

Advanced feature bisa cepat atau lambat tergantung index dan data distribution.

Concern utama:

• JSONB tanpa index,
• GIN index terlalu besar,
• expression index tidak match dengan expression query,
• function pada kolom mencegah index biasa,
• dynamic SQL menghasilkan plan tidak stabil,
• large IN clause,
• ORDER BY tanpa index,
• SKIP LOCKED scan banyak locked rows,
• UPSERT hot key contention,
• trigger memperlambat write,
• function/procedure melakukan query tersembunyi,
• advisory lock menyebabkan serialization bottleneck di application level.

Performance review harus selalu menyertakan:

• expected cardinality,
• expected frequency,
• index plan,
• EXPLAIN evidence,
• timeout,
• concurrency assumption,
• fallback behavior.

29. Security and Privacy Concerns

MyBatis memberi kontrol SQL eksplisit, tetapi juga membuka risiko:

• dynamic JSON path via raw string,
• dynamic ORDER BY dengan ${},
• full-text query logging PII,
• JSONB payload berisi sensitive data,
• function/procedure memiliki permission terlalu besar,
• RLS/tenant filter terlewat,
• advisory lock key mengandung data sensitif jika di-log,
• error database membocorkan schema/function name,
• migration user permission dipakai runtime user.

Rule:

Semua SQL fragment yang bukan value binding harus dianggap dangerous sampai dibuktikan di-whitelist.

Gunakan:

• #{} untuk value,
• whitelist untuk column/sort/operator,
• redaction untuk log,
• least privilege DB user,
• explicit tenant predicate,
• integration tests untuk forbidden access,
• review khusus untuk function/procedure permissions.

30. Testing Strategy

Untuk fitur PostgreSQL advanced, mock database hampir tidak berguna.

Gunakan PostgreSQL asli via Testcontainers atau test environment yang setara.

Test minimum:

1. SQL syntax test.
2. ResultMap mapping test.
3. JSONB serialization/deserialization test.
4. Null/missing key test.
5. Index-supported query plan smoke test untuk critical query.
6. UPSERT concurrent behaviour test.
7. SKIP LOCKED multi-worker test.
8. Advisory lock concurrent acquisition test.
9. Function/procedure call test.
10. Trigger side effect test.
11. Migration compatibility test.
12. SQL injection negative test untuk dynamic fragments.

Contoh concurrency test target:

Given 100 pending jobs
And 5 workers claim jobs concurrently
When each worker uses FOR UPDATE SKIP LOCKED in transaction
Then no job is claimed by more than one worker
And total claimed jobs equals expected count

31. Debugging Playbook

Ketika advanced query bermasalah di production:

1. Identifikasi mapper method dari log/trace.
2. Ambil SQL final dengan parameter yang sudah aman/redacted.
3. Cek transaction boundary caller.
4. Cek statement timeout dan lock timeout.
5. Cek query plan dengan representative parameter.
6. Cek index existence dan bloat/statistics.
7. Cek locks/deadlock/blocked sessions.
8. Cek trigger/function/procedure side effect.
9. Cek row cardinality dan data distribution.
10. Cek deployment version dan migration state.
11. Cek apakah ada mixed JPA access ke table yang sama.
12. Cek apakah Redis/cache/read model stale.

Jangan langsung menambah index tanpa memahami predicate, cardinality, write cost, dan rollout risk.

32. Code Review Checklist

Saat review mapper yang memakai PostgreSQL advanced feature, tanyakan:

SQL correctness

• Apakah SQL valid PostgreSQL?
• Apakah predicate wajib lengkap?
• Apakah null semantics jelas?
• Apakah duplicate row risk ada?
• Apakah ordering deterministic?
• Apakah limit/batch size dibatasi?

Transaction correctness

• Apakah method butuh transaction?
• Apakah lock lifetime sesuai?
• Apakah external call terjadi di dalam transaction?
• Apakah retry aman?
• Apakah rollback side effect jelas?

Mapping correctness

• Apakah ResultMap lengkap?
• Apakah TypeHandler teruji?
• Apakah JSONB/array/enum mapping compatible?
• Apakah returned column sesuai DTO?

Performance

• Apakah ada index yang sesuai?
• Apakah query explainable?
• Apakah cardinality diketahui?
• Apakah query bisa jadi full scan?
• Apakah function/trigger cost diketahui?

Security/privacy

• Apakah tidak ada SQL injection?
• Apakah dynamic fragment di-whitelist?
• Apakah tenant/security filter ada?
• Apakah PII tidak ter-log?
• Apakah DB permission minimal?

Operational readiness

• Apakah metrics/logs cukup?
• Apakah slow query observable?
• Apakah migration sudah mencakup schema/index/function?
• Apakah rollback/roll-forward aman?
• Apakah test integration cukup?

33. Internal Verification Checklist

Cek di codebase/team:

• Mapper XML yang memakai jsonb, @>, ->, ->>, ?, ?|, ?&.
• Custom TypeHandler untuk JSONB, array, enum.
• Query full-text search dan index pendukung.
• Function/procedure yang dipanggil dari mapper.
• Trigger yang memengaruhi table yang di-update mapper.
• Penggunaan RETURNING di insert/update/delete.
• Penggunaan ON CONFLICT dan conflict target.
• Penggunaan FOR UPDATE, SKIP LOCKED, NOWAIT.
• Penggunaan advisory lock.
• Migration untuk type/function/index/trigger.
• Integration tests yang memakai PostgreSQL asli.
• Slow query dashboard untuk mapper advanced.
• Incident notes terkait deadlock, stale data, duplicate processing, failed migration, atau query plan regression.
• Policy internal tentang PostgreSQL-specific SQL vs portability.
• Diskusi dengan DBA/platform/backend senior tentang allowed patterns.

34. Senior Engineer Heuristics

Gunakan MyBatis + PostgreSQL advanced feature ketika:

• SQL harus terlihat dan dikontrol,
• query memakai fitur PostgreSQL yang sulit di-ORM-kan,
• projection/read model lebih penting daripada entity lifecycle,
• performance diagnosis butuh SQL eksplisit,
• correctness bergantung pada constraint/lock/returning/upsert,
• team punya test dan review discipline yang memadai.

Hindari atau eskalasi design review ketika:

• SQL menyimpan business workflow besar tersembunyi,
• function/procedure melakukan banyak side effect,
• mapper dan JPA entity menulis table sama tanpa governance,
• query PostgreSQL-specific tidak punya test asli,
• index tidak jelas,
• transaction boundary tidak jelas,
• dynamic SQL membuat semua branch sulit direview,
• security/tenant filter bergantung pada copy-paste.

35. Key Takeaways

• MyBatis adalah fit natural untuk PostgreSQL advanced SQL karena SQL terlihat dan bisa dituning langsung.
• JSONB, array, enum, full-text search, function, procedure, trigger, RETURNING, UPSERT, SKIP LOCKED, dan advisory lock adalah feature dengan correctness implication, bukan hanya syntax.
• Setiap SQL advanced harus punya transaction story, index story, testing story, migration story, dan debugging story.
• RETURNING dan ON CONFLICT dapat mengurangi race dan round trip, tetapi bisa menyembunyikan insert/update semantics jika tidak direview.
• SKIP LOCKED dan advisory lock sangat berguna untuk worker/concurrency, tetapi hanya benar jika transaction boundary tepat.
• Function/procedure/trigger harus diperlakukan sebagai production code, bukan database magic.
• PostgreSQL-specific mapper harus dites dengan PostgreSQL asli.
• Dalam enterprise system, advanced SQL harus mudah dijelaskan di PR review dan mudah didiagnosis saat incident.