# Database Bulk Loading > Designing and diagnosing high-volume database ingestion from the JVM across PostgreSQL, MySQL, and SQL Server: JDBC batching and statement rewrite, native COPY/LOAD DATA/Bulk Copy, staging, transaction and partial-error semantics, idempotent restart, upsert races, logging, parallelism, and post-load validation. Use when a backfill, import, migration, or batch window is too slow or unsafe. Not routine ORM fetch/write tuning, which belongs to orm-fetch-and-batching-performance. - Skill: `robsonkades/database-bulk-loading` (Agent Skill, multi-file: 4 files) - Install (CLI): `npx skillmds@latest add robsonkades/database-bulk-loading` - Raw SKILL.md: https://api.skillmd.com/api/skills/robsonkades/database-bulk-loading/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: robsonkades (https://skillmd.com/u/robsonkades) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/robsonkades/database-bulk-loading --- # Database Bulk Loading ## Purpose Choose the least costly ingestion mechanism that still preserves the required validation, atomicity, recoverability, and online workload. “More threads” and “larger batch” are not goals; the job is done when useful rows per second rise without violating correctness or guardrails. ## Inputs required ```text engine, server/driver versions, topology, durability and replication mode: source format/location/trust, rows and bytes, row width, and error distribution: target constraints, triggers, indexes, generated keys, and online traffic: required atomicity, duplicate/upsert semantics, rejection policy, and restart point: current mechanism, batch size, transaction size, throughput, CPU/I/O/log/network, and heap: window/SLO, staging/disk/log headroom, privileges, and rollback constraints: ``` For JVM implementation changes, inspect compiler release/toolchains, runtime images, resolved JDBC driver and ORM versions, connection properties and transaction-manager ownership. This skill imposes no Java baseline; preserve the project's target and dependencies. Without phase timings or failure semantics, propose a bounded pilot and mark mechanism/sizing conclusions conditional. ## Workflow 1. Measure rows/s and bytes/s by phase. Attribute time to client materialization, network round-trips, statement processing, per-row engine work, log/WAL flush, indexes/constraints, or replication. A single total duration cannot select a mechanism. 2. Choose the mechanism level deliberately: individual statements, JDBC batch, driver statement rewrite, native bulk API, or server-side set operation from staging. 3. Define transaction and error semantics before tuning. `executeBatch()` is not atomic; verify an explicit transaction encompasses all intended writes on transactional storage, including native API participation. DDL, sequences and external trigger effects can escape rollback. Capture update counts, SQL state/vendor code, warnings, rejected rows, and what remains committable after an error. 4. Prefer staging when validation, deduplication, transformation, index suspension, or online isolation matters. Load into a table with intentionally minimal structures, validate, then move with set-based SQL. 5. Find the batch-size knee under representative data. Network benefit approaches saturation while memory, lock duration, retry granularity, statement size, and replication lag keep growing. 6. Pilot parallelism only with a bottleneck hypothesis, spare capacity and independently owned key ranges/partitions. It may overlap client waits or use idle server resources; it cannot remove a saturated shared log or lock bottleneck. Stop when CPU/I/O/log, lock waits, replica lag, or online latency reaches its guardrail. 7. Commit the destination checkpoint with the data wherever they share a transaction resource. Acknowledge an external source only after destination commit, with idempotent replay across that gap. Reconcile unknown commit outcomes before retrying; see the recovery reference. 8. Finish by checking accepted/rejected/warning counts, constraints, samples or checksums, target invariants, statistics, replica convergence, and online SLOs. ## Rules - Separate three costs: round-trips, work per statement, and work per row. JDBC batching attacks the first; rewrite/native APIs attack the first two; only server-side choices reduce index, constraint, trigger, logging, and data-work cost. - If a batch of B rows uses one round-trip instead of B, its round-trip component falls by `1 - 1/B`. This is not total elapsed-time improvement: row work, commits and driver behavior remain. Measure the knee and vary JDBC batch size separately from transaction/chunk size. - Never infer batch from an API name or ORM log. Verify server statement/round-trip counts and the driver's effective properties. - Native APIs have different correctness defaults. PostgreSQL `COPY` validates constraints and fires triggers; SQL Server Bulk Copy skips some checks/triggers unless enabled; MySQL `LOAD DATA` can convert bad input into warnings. Make these choices explicit. - A durability relaxation needs named data-loss semantics, authority, a timed restoration step, and a crash test. A faster import is not evidence that correctness remained intact. - Dropping indexes on a hot final table can turn the load into an outage and alter constraints. Staging is the default location for aggressive optimization. - Upsert syntax is not portable: conflict target, row-locking behavior, triggers, no-op updates, and races differ. Test concurrent writers and avoid rewriting unchanged rows. - Do not materialize the entire source in the JVM. Stream with bounded buffers and account for driver buffering; a fetch/input API that accepts a size does not prove bounded memory. - Update optimizer statistics after the load and validate the first online plans. Completion before statistics refresh can defer the incident until traffic resumes. - “No exception” is not data quality. Treat warnings and rejected rows as first-class outcomes. ## Output Produce a load plan with: ```text chosen mechanism and why the next simpler/faster level was rejected: transaction, partial-error, warning, retry, and idempotency semantics: staging/final-table design and index/constraint/trigger handling: batch/chunk/parallelism values as hypotheses with guardrails: progress checkpoint and interruption recovery: pre/post measurements and data-quality assertions: operational rollback and configuration restoration: confidence and untested failure modes: ``` ## References - [Mechanisms by engine](references/engine-mechanisms.md) — read before selecting or configuring a native API, driver rewrite, or minimal-logging path. - [Failure, restart, and validation](references/recovery-and-validation.md) — read when partial input failure, upsert, restart, cutover, or data-quality guarantees matter.