Part 035 — Batch Processing and Bulk Operations

Batch processing terlihat sederhana: lakukan banyak insert, update, atau delete sekaligus.

Namun di production enterprise system, batch operation adalah salah satu sumber terbesar dari:

• long-running transaction
• table/row lock terlalu lama
• connection pool exhaustion
• memory pressure
• deadlock
• timeout
• partial failure yang sulit direkonsiliasi
• duplicate processing saat retry
• stale persistence context pada JPA/Hibernate
• audit/outbox inconsistency
• migration/backfill incident

Core principle:

Batch operation bukan hanya optimasi performa. Batch operation adalah desain transaction, memory, retry, lock, observability, dan recovery.

Dalam sistem CPQ, quote, order, catalog, price, billing, workflow, dan event-driven processing, batch biasanya muncul di:

• import catalog/product
• repricing banyak quote
• mass status update
• order migration
• retry failed events
• outbox publisher
• backfill data migration
• sync reference data
• cleanup expired records
• generate read model
• reconciliation job

Pertanyaan persistence engineer:

• Apakah batch ini atomic semua atau boleh partial?
• Berapa ukuran chunk yang aman?
• Apakah satu transaction menahan lock terlalu lama?
• Apakah retry idempotent?
• Apakah memory akan tumbuh karena object/entity terlalu banyak?
• Apakah JPA persistence context akan stale setelah bulk update?
• Apakah MyBatis batch executor benar-benar flush pada waktu tepat?
• Apakah PostgreSQL index/constraint membuat batch lambat?
• Apakah outbox/audit tetap konsisten?
• Apakah job bisa dilanjutkan setelah crash?

1. Batch vs Bulk: Jangan Dicampur Mental Model-nya

Ada dua kategori besar:

Batch operation:

INSERT INTO quote_line_item (...) VALUES (...);
INSERT INTO quote_line_item (...) VALUES (...);
INSERT INTO quote_line_item (...) VALUES (...);

Bulk operation:

UPDATE quote
SET status = 'EXPIRED'
WHERE status = 'DRAFT'
  AND valid_until < now();

Keduanya punya risiko berbeda.

Batch risk:

• banyak round trip jika tidak dibatch benar
• memory object besar
• partial statement failure
• batch result sulit dipetakan ke item asal
• transaction terlalu besar

Bulk risk:

• terlalu banyak row berubah sekaligus
• lock besar
• stale JPA persistence context
• audit per-row bisa hilang jika tidak dirancang
• WHERE clause salah bisa menghancurkan data banyak
• sulit melakukan per-item validation

Rule:

Batch cocok ketika setiap item punya lifecycle/validation sendiri. Bulk cocok ketika perubahan adalah operasi set-based yang invariant-nya bisa dinyatakan dalam SQL.

2. Batch Insert

Batch insert dipakai untuk memasukkan banyak row dengan overhead lebih rendah.

Contoh domain:

• import price list
• insert quote line items
• insert outbox events
• insert audit rows
• seed reference data

JDBC batch insert

String sql = """
    INSERT INTO quote_line_item (
        quote_id,
        product_id,
        quantity,
        unit_price,
        created_at
    ) VALUES (?, ?, ?, ?, ?)
    """;

try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {

    connection.setAutoCommit(false);

    int count = 0;

    for (LineItem item : items) {
        statement.setObject(1, item.quoteId());
        statement.setObject(2, item.productId());
        statement.setInt(3, item.quantity());
        statement.setBigDecimal(4, item.unitPrice());
        statement.setObject(5, item.createdAt());
        statement.addBatch();

        count++;

        if (count % 500 == 0) {
            statement.executeBatch();
        }
    }

    statement.executeBatch();
    connection.commit();
} catch (SQLException e) {
    // rollback, map SQLState, log safely, propagate domain-safe error
    throw new PersistenceException("Failed to batch insert quote line items", e);
}

Key decision:

• batch size
• transaction size
• generated key requirement
• error handling per item
• retry/idempotency

Batch size bukan angka universal.

Starting point yang sering masuk akal:

100 - 1000 rows per batch execution

Tetapi harus divalidasi dengan:

• row size
• index count
• constraint cost
• network latency
• PostgreSQL config
• driver behavior
• memory profile
• lock duration

3. Batch Update

Batch update dipakai saat setiap row punya value berbeda.

Contoh:

UPDATE quote_line_item
SET quantity = ?, unit_price = ?, updated_at = ?
WHERE id = ?;

Jika semua row punya perubahan sama, bulk update mungkin lebih baik.

Bad batch candidate:

Set semua expired quote menjadi EXPIRED.

Better as bulk:

UPDATE quote
SET status = 'EXPIRED', updated_at = now()
WHERE status = 'DRAFT'
  AND valid_until < now();

Good batch candidate:

Update 10.000 line items dengan price hasil kalkulasi berbeda per item.

Batch update correctness questions:

• Apakah update harus memakai optimistic version check?
• Apakah row yang tidak ter-update harus dianggap conflict?
• Apakah audit event perlu dibuat per item?
• Apakah order update bisa menyebabkan deadlock?
• Apakah item processing idempotent?

Optimistic batch update example:

UPDATE quote_line_item
SET quantity = ?,
    unit_price = ?,
    version = version + 1,
    updated_at = now()
WHERE id = ?
  AND version = ?;

Jika affected row = 0, artinya:

• row tidak ada
• row sudah berubah
• version stale
• tenant filter salah
• soft delete filter mengecualikan row

Jangan diam-diam mengabaikan affected row = 0 pada command path kritikal.

4. Batch Delete

Batch delete rawan karena efeknya destruktif.

Preferensi umum enterprise:

• soft delete untuk business entity
• hard delete untuk staging/temp/outbox processed retention
• hard delete untuk cleanup data teknis yang jelas lifecycle-nya

Batch hard delete harus hati-hati dengan:

• foreign key
• cascade database
• cascade JPA
• audit requirement
• retention policy
• lock duration
• vacuum impact

Bad:

DELETE FROM quote WHERE status = 'CANCELLED';

Better:

DELETE FROM quote_cleanup_candidate
WHERE processed_at < now() - interval '30 days'
LIMIT ???;

PostgreSQL tidak mendukung DELETE ... LIMIT secara langsung seperti beberapa database lain. Gunakan CTE:

WITH rows_to_delete AS (
    SELECT id
    FROM outbox_event
    WHERE status = 'PUBLISHED'
      AND published_at < now() - interval '30 days'
    ORDER BY published_at ASC, id ASC
    LIMIT 1000
)
DELETE FROM outbox_event e
USING rows_to_delete d
WHERE e.id = d.id;

Pattern ini membantu:

• membatasi lock
• membatasi transaction duration
• membuat cleanup resumable
• mengurangi vacuum shock

5. JDBC Batch

JDBC batch adalah fondasi batch MyBatis dan Hibernate.

API inti:

statement.addBatch();
statement.executeBatch();

Important details:

• executeBatch() mengembalikan array update counts
• driver dapat mengoptimasi network round trip
• exception batch bisa terjadi setelah sebagian statement berhasil di database tergantung transaction mode
• autocommit harus dikontrol
• generated keys dalam batch perlu diverifikasi support/behavior driver

Autocommit risk

Bad:

connection.setAutoCommit(true);
// execute many batch operations

Jika autocommit true, setiap statement bisa commit sendiri-sendiri. Ini membuat partial write sulit dikendalikan.

Better:

connection.setAutoCommit(false);
try {
    statement.executeBatch();
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    throw e;
}

BatchUpdateException

Batch failure harus dilihat sebagai signal bahwa sebagian item mungkin gagal karena:

• unique constraint
• foreign key constraint
• check constraint
• deadlock
• timeout
• serialization failure
• invalid data

Pseudo handling:

catch (BatchUpdateException e) {
    int[] updateCounts = e.getUpdateCounts();
    String sqlState = e.getSQLState();

    // log counts length, sqlState, batch name, chunk id
    // do not log sensitive row data
    // rollback transaction unless partial commit is explicitly allowed

    throw mapBatchFailure(sqlState, e);
}

Review question:

Can the system explain which chunk failed and whether any data was committed?

6. MyBatis Batch Executor

MyBatis menyediakan executor type untuk batch.

Biasanya konsepnya:

try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
    QuoteLineItemMapper mapper = session.getMapper(QuoteLineItemMapper.class);

    int count = 0;
    for (LineItemCommand command : commands) {
        mapper.insertLineItem(command);
        count++;

        if (count % 500 == 0) {
            session.flushStatements();
        }
    }

    session.flushStatements();
    session.commit();
}

Dalam framework-managed transaction, pattern detail bisa berbeda. Jangan mengarang sebelum cek konfigurasi internal.

Yang penting secara mental model:

• mapper call belum tentu langsung execute ke database
• statement bisa ditahan sampai flush
• exception bisa muncul saat flushStatements() atau commit
• batch result harus diobservasi
• transaction boundary harus jelas

MyBatis batch risk

Risiko utama:

• developer mengira setiap mapper call sudah execute
• flush terlalu jarang sehingga memory meningkat
• batch exception muncul jauh dari item penyebab
• mixed select/update dalam batch session membingungkan
• generated key behavior tidak dipahami
• transaction manager framework tidak sinkron dengan manual session management

Recommendation:

• gunakan dedicated batch service
• chunk eksplisit
• log chunk id/range
• hindari select kompleks di tengah batch executor
• pisahkan validation phase dan write phase jika memungkinkan
• test dengan real PostgreSQL

7. JPA/Hibernate Batching

JPA/Hibernate batching berbeda dari JDBC/MyBatis batch karena ada persistence context dan dirty checking.

Contoh naive:

@Transactional
public void importItems(List<LineItem> items) {
    for (LineItem item : items) {
        entityManager.persist(item);
    }
}

Masalah:

• semua entity menjadi managed
• persistence context membesar
• dirty checking cost naik
• memory pressure
• flush terjadi di akhir transaction
• SQL visibility tertunda

Better:

@Transactional
public void importItems(List<LineItem> items) {
    int count = 0;

    for (LineItem item : items) {
        entityManager.persist(item);
        count++;

        if (count % 500 == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
}

flush() mengirim SQL ke database.

clear() melepaskan managed entities dari persistence context.

Tanpa clear(), memory tetap tumbuh.

Hibernate JDBC batching config

Hal yang biasanya perlu diverifikasi:

hibernate.jdbc.batch_size=50
hibernate.order_inserts=true
hibernate.order_updates=true
hibernate.jdbc.batch_versioned_data=true

Namun jangan copy-paste config tanpa validasi.

Checklist:

• apakah identity generation mematikan batching?
• apakah sequence allocation size mendukung batch?
• apakah insert order bisa berubah dan memengaruhi constraint?
• apakah versioned update dibatch?
• apakah batch size cocok dengan row size?

Identity vs sequence

Hibernate batching sering lebih efektif dengan sequence dibanding identity.

Identity generated id biasanya membutuhkan insert segera untuk mendapatkan generated key. Sequence bisa dialokasikan sebelum insert.

Review question:

Does the ID generation strategy allow Hibernate to batch inserts efficiently?

8. Bulk JPQL Update/Delete

JPA mendukung bulk operation:

int updated = entityManager.createQuery("""
    update Quote q
       set q.status = :expiredStatus,
           q.updatedAt = :now
     where q.status = :draftStatus
       and q.validUntil < :now
    """)
    .setParameter("expiredStatus", QuoteStatus.EXPIRED)
    .setParameter("draftStatus", QuoteStatus.DRAFT)
    .setParameter("now", now)
    .executeUpdate();

Bulk JPQL penting, tetapi punya jebakan besar:

Bulk JPQL bypass persistence context.

Artinya managed entity yang sudah ada di persistence context tidak otomatis sinkron.

Contoh bug:

Quote quote = entityManager.find(Quote.class, quoteId);

entityManager.createQuery("""
    update Quote q
       set q.status = 'EXPIRED'
     where q.id = :id
    """)
    .setParameter("id", quoteId)
    .executeUpdate();

// quote masih bisa terlihat status lama di memory
quote.approve();

Setelah bulk update/delete:

• flush sebelum bulk jika ada pending changes
• clear persistence context setelah bulk jika entity terkait mungkin stale
• jangan lanjut memakai managed entity lama tanpa refresh

Pattern:

entityManager.flush();
int updated = query.executeUpdate();
entityManager.clear();

9. Persistence Context Stale After Bulk Update

Ini salah satu bug paling mahal dalam JPA.

Sequence:

1. Load entity Quote id=Q1 into persistence context.
2. Bulk update changes Quote Q1 in database.
3. Entity object in memory still has old state.
4. Later flush writes old state back or business logic reads stale state.

Failure mode:

• stale status
• overwritten update
• wrong audit
• event payload salah
• decision logic salah
• optimistic lock tidak naik jika bulk update tidak update version

Jika bulk update memengaruhi entity versioned, tentukan apakah version harus dinaikkan.

Example:

UPDATE quote
SET status = 'EXPIRED',
    version = version + 1,
    updated_at = now()
WHERE status = 'DRAFT'
  AND valid_until < now();

Jika bulk update tidak menaikkan version, concurrent writer mungkin tidak mendeteksi perubahan.

10. Chunking

Chunking adalah memecah batch besar menjadi unit kecil.

Tanpa chunking:

Process 1,000,000 rows in one transaction.

Risiko:

• transaction terlalu lama
• lock terlalu lama
• WAL besar
• replication lag
• memory besar
• rollback mahal
• timeout
• sulit resume

Dengan chunking:

Process 1,000 rows per transaction.
Commit.
Record progress.
Continue.

Chunk boundary bisa berdasarkan:

• offset, tidak ideal untuk table berubah
• keyset id
• created_at + id
• status claim
• work queue with SKIP LOCKED
• partition/date window

Preferred for mutable large data:

SELECT id
FROM quote
WHERE status = 'PENDING_REPRICE'
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED;

Then update selected rows in same transaction.

Chunking design questions:

• Apakah chunk idempotent?
• Bagaimana progress disimpan?
• Apa yang terjadi jika crash setelah sebagian chunk commit?
• Apakah chunk bisa diproses paralel?
• Apakah ordering penting?
• Apakah lock strategy aman?

11. Transaction Size

Transaction size bukan hanya jumlah row.

Transaction size dipengaruhi:

• jumlah statement
• jumlah row touched
• jumlah index updated
• foreign key checks
• trigger execution
• outbox/audit insert
• lock duration
• WAL volume
• persistence context size
• network round trip
• timeout setting

Rule:

Transaction batch harus cukup besar untuk efisiensi, cukup kecil untuk recovery.

Bad sign:

• transaction berjalan menit/jam
• rollback sangat lama
• pool connection tertahan
• lock wait meningkat
• deadlock meningkat
• replication lag naik
• memory naik tajam

Review question:

Can this batch fail halfway without corrupting business state, and can it resume safely?

12. Retry Strategy

Retry batch operation berbahaya jika tidak idempotent.

Retryable failures:

• deadlock detected
• serialization failure
• transient connection issue
• lock timeout depending on business rule
• statement timeout depending on operation design

Non-retryable failures:

• not null violation
• foreign key violation
• invalid enum value
• malformed JSONB
• data validation error
• permission error

Retry design harus punya:

• retry boundary jelas
• max attempt
• backoff
• idempotency key atau deterministic write
• duplicate-safe insert
• progress checkpoint
• error classification
• dead-letter/manual review path

Example idempotent insert:

INSERT INTO import_item_result (
    import_job_id,
    item_key,
    status,
    created_at
)
VALUES (:jobId, :itemKey, :status, now())
ON CONFLICT (import_job_id, item_key)
DO UPDATE SET
    status = EXCLUDED.status,
    updated_at = now();

Without idempotency, retry can create duplicates.

13. Batch and Outbox Consistency

Jika batch write harus publish event, jangan publish event langsung di tengah loop tanpa transaction strategy.

Bad:

Update database row.
Send Kafka event.
Update next row.
Send next event.
Crash.

Risk:

• event published but transaction rollback
• transaction commit but event missing
• duplicate event on retry
• wrong ordering

Better:

Within same DB transaction:
1. Update business row.
2. Insert outbox event row.
Commit.
Outbox publisher sends event asynchronously.

Batch with outbox:

UPDATE quote
SET status = 'EXPIRED', updated_at = now()
WHERE id = :quoteId;

INSERT INTO outbox_event (
    aggregate_type,
    aggregate_id,
    event_type,
    payload,
    status,
    created_at
) VALUES (
    'Quote',
    :quoteId,
    'QuoteExpired',
    :payload::jsonb,
    'PENDING',
    now()
);

Review question:

Does every committed batch state transition have a corresponding durable event or audit record if required?

14. Batch and Audit

Bulk update can bypass application-level audit.

Example:

UPDATE quote
SET status = 'EXPIRED'
WHERE valid_until < now();

Questions:

• Who changed it?
• Why changed?
• Which job/request caused it?
• How many rows changed?
• Can we reconstruct old value?
• Is per-row audit required?

Options:

1. Application inserts audit rows per affected id.
2. Database trigger captures old/new values.
3. Job writes summary audit only.
4. Outbox/change table records transitions.

Correct choice depends on compliance and domain importance.

Do not assume summary audit is enough for regulated or customer-visible data.

15. Batch with MyBatis vs JPA vs JDBC

Decision smell:

Using JPA loop over 500k rows while keeping all entities managed.

Decision smell:

Using MyBatis bulk update behind already-loaded JPA entities without flush/clear.

Decision smell:

Using raw JDBC because framework feels annoying, not because the path needs low-level control.

16. PostgreSQL Considerations

Batch/bulk operations in PostgreSQL can stress:

• WAL volume
• indexes
• foreign key checks
• triggers
• vacuum/autovacuum
• lock table
• replication lag
• statement timeout
• lock timeout
• deadlock detector

Index cost

Every insert/update/delete may update indexes.

Table with many indexes:

1 row update = table update + N index updates

Before batch update:

• check indexes on columns being updated
• check whether update changes indexed columns
• check HOT update possibility
• check whether batch generates table bloat

Constraint cost

Foreign key and unique constraints are correctness tools, but they add cost.

Batch import order matters:

parent rows before child rows
reference data before transactional data

Trigger cost

Trigger can turn one statement into many hidden operations.

Internal verification required:

• are there audit triggers?
• are there denormalization triggers?
• are there notify/event triggers?
• are there validation triggers?

17. Kubernetes and Cloud Runtime Concerns

Batch job in Kubernetes is not isolated from database capacity.

Risks:

• multiple pods run same job
• rolling deployment duplicates batch worker
• retry policy restarts job after partial commit
• connection pool per pod overwhelms DB
• migration job runs beside app traffic
• cloud database throttles I/O
• network timeout during long transaction

Controls:

• leader election or job singleton
• idempotency/progress table
• limited pool size for batch worker
• schedule outside peak traffic
• statement timeout appropriate to job
• lock timeout to avoid blocking online traffic
• observability per job/chunk
• manual kill/resume runbook

Review question:

If two pods start this batch at the same time, will they duplicate work or corrupt state?

18. Observability for Batch

Batch must expose progress and failure context.

Minimum metrics:

• job started/completed/failed count
• chunk duration
• rows read/written/skipped/failed
• retry count
• deadlock/serialization failure count
• database time per chunk
• pool usage
• lock wait
• lag/backlog

Minimum logs:

jobName=batch-expire-quotes
jobId=...
chunkId=42
fromId=100000
toId=101000
rowsSelected=1000
rowsUpdated=998
rowsSkipped=2
durationMs=843
attempt=1

Avoid logging:

• full payload with PII
• sensitive pricing/customer fields
• raw SQL parameter values if sensitive

Trace useful boundary:

job -> chunk -> select candidates -> update rows -> insert outbox/audit -> commit

19. Failure Modes

20. PR Review Checklist

Use this checklist when reviewing batch or bulk changes.

Scope and semantics

• Is this batch or bulk?
• Is atomic all-or-nothing required?
• Is partial commit acceptable?
• Is resume after crash defined?
• Is idempotency guaranteed?

Transaction

• Where is the transaction boundary?
• How many rows per transaction?
• Is chunk size explicit?
• Are rollback semantics clear?
• Are timeout settings appropriate?

Locking/concurrency

• Can multiple workers run concurrently?
• Is work claiming safe?
• Is update order deterministic?
• Are deadlocks retried safely?
• Does it use FOR UPDATE SKIP LOCKED where appropriate?

MyBatis/JPA/JDBC

• If MyBatis batch executor is used, where is flush?
• If JPA is used, are flush/clear handled?
• If bulk JPQL/native update is used, is persistence context stale risk handled?
• If JDBC is used, are connection/autocommit/resource closing correct?

Data correctness

• Are affected row counts checked?
• Are version columns updated when needed?
• Are tenant/soft-delete/effective-date filters included?
• Are audit/outbox requirements satisfied?
• Are constraints relied on intentionally?

Performance

• Is there an index supporting candidate selection?
• Is batch size justified?
• Is memory bounded?
• Is generated SQL visible?
• Was EXPLAIN checked for bulk candidate query?

Operations

• Are metrics/logs enough to debug chunk failure?
• Is there a runbook to resume/stop job?
• Is it safe during rolling deployment?
• Does it respect database capacity?
• Is there a rollback or roll-forward plan?

21. Internal Verification Checklist

Verify in the actual CSG/team codebase before applying conclusions.

Codebase

• Where are batch jobs implemented?
• Are they Java services, scheduled jobs, migration scripts, Camunda workers, Kafka consumers, or Kubernetes Jobs?
• Are batch operations using JDBC, MyBatis, JPA, Hibernate, or raw SQL migration?
• Are there shared batch utilities?

Transaction framework

• What transaction manager is used?
• How is transaction propagation configured?
• Are batch jobs declarative or programmatic transactions?
• Are there transaction timeout conventions?

MyBatis

• Is ExecutorType.BATCH used?
• Where is flushStatements() called?
• Are mapper methods batch-safe?
• Are generated keys used in batch?

JPA/Hibernate

• Is Hibernate JDBC batching enabled?
• What is hibernate.jdbc.batch_size?
• Are flush() and clear() used in large loops?
• Are bulk JPQL updates followed by clear/refresh?

PostgreSQL

• Are batch candidate queries indexed?
• Are triggers involved?
• Are constraints/indexes making batch slow?
• Are statement/lock timeouts configured?
• Is vacuum/replication lag monitored after bulk operations?

CI/CD and operations

• Are batch jobs included in integration tests?
• Are Testcontainers used for batch behavior?
• Are migration/backfill scripts reviewed?
• Are job metrics visible in dashboards?
• Are incident notes available for previous batch failures?

22. Mental Model Summary

Batch processing is not merely looping faster.

A senior engineer sees a batch operation as:

candidate selection
+ transaction boundary
+ chunking
+ locking
+ idempotency
+ framework behavior
+ database constraints
+ audit/outbox
+ observability
+ recovery path

Healthy batch design is explicit about:

• how much work happens per transaction
• how progress is recorded
• how duplicate execution is prevented
• how failures are classified
• how database locks are bounded
• how memory is bounded
• how events/audit are kept consistent
• how operators debug and resume safely

Final rule:

If a batch cannot be safely stopped, retried, resumed, and explained, it is not production-ready.