---
title: 'Bulk Data Loading'
source: 'https://academia.sh/en/courses/database-administration/bulk-data-loading'
course: 'Relational Database Administration'
language: en
updated: '2026-08-23T07:00:39+00:00'
license: 'CC BY-SA 4.0'
---

# Bulk Data Loading

The cost of high-volume import and export: the effect of the transaction boundary on load speed, whether indexes are built during or after loading, extracting bad rows with a staging table, and the shape of export.

Monitoring shows what the system does under its ordinary load. Some work sits outside that
ordinary load and deliberately stresses the system: importing an archive, loading a
membership list from another institution, exporting a year's loan records. This work moves
millions of rows at once.

The point where bulk loading departs from ordinary writing is this: in ordinary writing the
cost of a single row is negligible; in bulk loading, every per-row cost gets multiplied by
the row count. This lesson covers where those multipliers come from and how they are
removed.

## The Cost of the Transaction Boundary

Every commit requires the engine to make the change durable: the log is written to disk,
and the write is verified to have genuinely completed. This step was covered in the
course's Engine Architecture topic, presented there as a single row's guarantee. In bulk
loading, that same step turns into a cost repeated once per row.

The block below loads the same thirty thousand rows with five different batch sizes.
Absolute durations depend on the machine, the disk, and the filesystem; what matters is
the ratio between the rows.

```bash
rm -f loading.db loading.db-journal

node - <<'EOF'
const { DatabaseSync } = require("node:sqlite");
const fs = require("node:fs");
const N = 30000;
const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL,
                member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)`;

const measure = (label, batch) => {
  fs.rmSync("loading.db", { force: true });
  fs.rmSync("loading.db-journal", { force: true });
  const db = new DatabaseSync("loading.db");
  db.exec("PRAGMA synchronous=FULL");
  db.exec(SCHEMA);
  const insert = db.prepare(
    "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)");
  const t = process.hrtime.bigint();
  for (let i = 0; i < N; i += batch) {
    if (batch > 1) db.exec("BEGIN");
    for (let j = i; j < Math.min(i + batch, N); j++)
      insert.run(j % 400 + 1, j % 900 + 1, j % 3 + 1, "2025-06-01");
    if (batch > 1) db.exec("COMMIT");
  }
  const ms = Number(process.hrtime.bigint() - t) / 1e6;
  db.close();
  console.log(label.padEnd(26) + " | " + String(Math.ceil(N / batch)).padStart(6) + " | " +
    (ms.toFixed(0) + " ms").padStart(9) + " | " +
    Math.round(N / (ms / 1000)).toLocaleString("en-US").padStart(11));
};

console.log("path                       | txns   | duration  | rows/second");
console.log("---------------------------|--------|-----------|------------");
measure("one transaction per row", 1);
measure("batches of 100 rows", 100);
measure("batches of 1,000 rows", 1000);
measure("batches of 10,000 rows", 10000);
measure("single transaction", N);
EOF
```

```text
path                       | txns   | duration  | rows/second
---------------------------|--------|-----------|------------
one transaction per row    |  30000 |   5483 ms |       5,472
batches of 100 rows        |    300 |     79 ms |     378,721
batches of 1,000 rows      |     30 |     17 ms |   1,731,219
batches of 10,000 rows     |      3 |      9 ms |   3,281,588
single transaction         |      1 |      9 ms |   3,436,082
```

The gap between one transaction per row and a single transaction is more than six hundred
times. The data loaded is the same, the rows written are the same, the work done is the
same — the only difference is how many times a commit happened.

The shape of the curve also matters. Most of the gain is obtained in the first step, the
move to batches of a hundred; anything past a thousand gives a small further improvement.
This does not mean the batch size should be chosen as large as possible. A single giant
transaction carries three costs: if it fails, everything restarts from the beginning,
rollback information accumulates for the duration of the transaction, and a long-running
write delays the dead-row cleanup covered in the course's Engine Architecture topic. A
batch size somewhere between one hundred thousand and one million rows gives nearly all of
the measured gain while bounding all three costs.

The batch boundary is also a progress point. At the end of every batch, how many rows have
loaded is known; if loading is interrupted, it can resume from where it left off. This
only requires the incoming data's order to be deterministic and the last loaded key to be
recorded.

## The Cost of Indexes During Loading

The second multiplier is indexes. Every row added to the table also adds an entry to every
index, and that entry has to be placed at the right position in the tree. When rows do not
arrive sorted by the index key, every insert touches a random location in the tree.

The alternative is building indexes after loading finishes: the engine then sorts all the
keys at once and builds the tree from the bottom up, in order.

```bash
rm -f indexed.db indexed.db-journal

node - <<'EOF'
const { DatabaseSync } = require("node:sqlite");
const fs = require("node:fs");
const N = 200000;
const SCHEMA = `CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL,
                member_id INT NOT NULL, branch_id INT NOT NULL, pickup TEXT NOT NULL)`;
const INDEXES = ["CREATE INDEX loan_member ON loan(member_id)",
               "CREATE INDEX loan_book ON loan(book_id)",
               "CREATE INDEX loan_branch_pickup ON loan(branch_id, pickup)"];
const elapsed = (task) => { const t = process.hrtime.bigint(); task();
                       return Number(process.hrtime.bigint() - t) / 1e6; };

const measure = (label, indexFirst) => {
  fs.rmSync("indexed.db", { force: true });
  fs.rmSync("indexed.db-journal", { force: true });
  const db = new DatabaseSync("indexed.db");
  db.exec(SCHEMA);
  if (indexFirst) for (const d of INDEXES) db.exec(d);
  const insert = db.prepare(
    "INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)");
  const loadTime = elapsed(() => {
    db.exec("BEGIN");
    for (let i = 0; i < N; i++) insert.run(i % 400 + 1, i % 900 + 1, i % 3 + 1, "2025-06-01");
    db.exec("COMMIT");
  });
  const indexTime = indexFirst ? 0 : elapsed(() => { for (const d of INDEXES) db.exec(d); });
  db.close();
  console.log(label.padEnd(24) + " | " + (loadTime.toFixed(0) + " ms").padStart(9) + " | " +
    (indexTime ? indexTime.toFixed(0) + " ms" : "-").padStart(9) + " | " +
    ((loadTime + indexTime).toFixed(0) + " ms").padStart(9) + " | " +
    (Math.round(fs.statSync("indexed.db").size / 1024) + " KB").padStart(9));
};

console.log(N.toLocaleString("en-US") + " rows, three indexes");
console.log();
console.log("path                     | load      | index     | total     | file");
console.log("-------------------------|-----------|-----------|-----------|----------");
measure("indexes exist beforehand", true);
measure("indexes added afterward", false);
EOF
```

```text
200,000 rows, three indexes

path                     | load      | index     | total     | file
-------------------------|-----------|-----------|-----------|----------
indexes exist beforehand |    460 ms |         - |    460 ms |  14128 KB
indexes added afterward  |     60 ms |     83 ms |    142 ms |  13352 KB
```

Load time dropped by close to eight-fold; even with index-building time added in, the
total fell to about a third of what it was. The file also came out smaller: an index built
in bulk fills its pages tightly, while an index grown row by row leaves gaps behind because
of page splits. This gap is the same thing covered under bloat in this course's Indexes
and Partitioning topic, in the lesson on index maintenance.

The cost of this method is that the table's read performance drops for as long as the
indexes are absent. On an empty table receiving its first load, this cost does not exist.
When a large transfer is made into a table that already exists and is in use, dropping the
indexes is not an option; in that case the batch size is tuned instead, and the load is
scheduled for a quiet hour.

The same reasoning applies to constraints. A foreign key check performs a lookup on every
row; a uniqueness constraint requires a check on every row. Most engines allow constraints
to be deferred to the end of the transaction; a deferred check is not done per row, it is
done once.

## Staging Table and Bad Rows

Data coming from outside does not arrive clean. A single bad row stops a load that writes
directly to the target table in the middle of the operation, and the work done up to that
point is rolled back. A load that stops at row one thousand three hundred of twenty
thousand both wastes time and does not show the whole of the problem — the bad rows still
further down have not been seen yet.

The standard solution is a **staging table**: the incoming file is taken in as is, into a
table where every column is text and no constraint is applied. Validation happens by query
after the data is inside the database. Valid rows are moved to the target; invalid ones
are set aside along with the reason.

```bash
rm -f transfer.db loans_incoming.csv clean.csv

# Incoming file: a 20,000-row archive transfer, containing bad rows.
node - <<'EOF' > loans_incoming.csv
console.log("id,book_id,member_id,branch_id,pickup");
for (let i = 1; i <= 20000; i++) {
  if (i === 77) { console.log("seventyseven,12,34,1,2025-05-04"); continue; }   // not a number
  if (i === 512) { console.log("512,12,34,1,04.05.2025"); continue; }           // date format
  if (i === 900) { console.log("900,12,99999,1,2025-05-04"); continue; }        // nonexistent member
  if (i === 1301) { console.log("1301,12,34,1"); continue; }                    // missing field
  console.log([i, (i % 400) + 1, (i % 900) + 1, (i % 3) + 1, "2025-05-04"].join(","));
}
EOF

sqlite3 transfer.db <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<900)
INSERT INTO member(id,name) SELECT n,'Member-'||n FROM s;
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL
                     REFERENCES member(id), branch_id INT NOT NULL, pickup TEXT NOT NULL);
-- Staging table: every column is text, no constraints.
CREATE TABLE staging(id TEXT, book_id TEXT, member_id TEXT, branch_id TEXT, pickup TEXT);
CREATE TABLE rejected(row_text TEXT, reason TEXT);
SQL

sqlite3 transfer.db ".import --csv --skip 1 loans_incoming.csv staging"
echo "rows loaded into staging: $(sqlite3 transfer.db 'SELECT COUNT(*) FROM staging;')"

sqlite3 -box -header transfer.db <<'SQL'
INSERT INTO rejected(row_text, reason)
SELECT id || ',' || IFNULL(book_id,'') || ',' || IFNULL(member_id,'') || ',' ||
       IFNULL(branch_id,'') || ',' || IFNULL(pickup,''),
       CASE
         WHEN id GLOB '*[^0-9]*' OR member_id GLOB '*[^0-9]*' THEN 'non-numeric field'
         WHEN pickup IS NULL OR pickup NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
              THEN 'date format'
         WHEN NOT EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER))
              THEN 'nonexistent member'
       END
FROM staging
WHERE id GLOB '*[^0-9]*' OR member_id GLOB '*[^0-9]*'
   OR pickup IS NULL OR pickup NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
   OR NOT EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER));

INSERT INTO loan(id,book_id,member_id,branch_id,pickup)
SELECT CAST(id AS INTEGER), CAST(book_id AS INTEGER), CAST(member_id AS INTEGER),
       CAST(branch_id AS INTEGER), pickup
FROM staging
WHERE id NOT GLOB '*[^0-9]*' AND member_id NOT GLOB '*[^0-9]*'
  AND pickup GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
  AND EXISTS (SELECT 1 FROM member WHERE member.id = CAST(staging.member_id AS INTEGER));

SELECT (SELECT COUNT(*) FROM staging) AS incoming,
       (SELECT COUNT(*) FROM loan) AS accepted,
       (SELECT COUNT(*) FROM rejected) AS set_aside;
SELECT reason, COUNT(*) AS rows FROM rejected GROUP BY reason ORDER BY rows DESC;
SQL

# Export: a file in the same format is produced.
sqlite3 transfer.db <<'SQL'
.mode csv
.headers on
.output clean.csv
SELECT id, book_id, member_id, branch_id, pickup FROM loan WHERE branch_id=1;
SQL
echo "rows written out: $(($(wc -l < clean.csv) - 1))"
head -2 clean.csv
```

```text
loans_incoming.csv:1302: expected 5 columns but found 4 - filling the rest with NULL
rows loaded into staging: 20000
┌──────────┬──────────┬───────────┐
│ incoming │ accepted │ set_aside │
├──────────┼──────────┼───────────┤
│ 20000    │ 19996    │ 4         │
└──────────┴──────────┴───────────┘
┌────────────────────┬──────┐
│       reason       │ rows │
├────────────────────┼──────┤
│ date format        │ 2    │
│ nonexistent member │ 1    │
│ non-numeric field  │ 1    │
└────────────────────┴──────┘
rows written out: 6665
id,book_id,member_id,branch_id,pickup
3,4,4,1,2025-05-04
```

The load did not stop. All twenty thousand rows entered the database, nineteen thousand
nine hundred ninety-six made it to the target, and four rows were set aside along with a
reason. The list of set-aside rows can be corrected and reloaded; this is far cheaper than
repeating the entire transfer over one bad row.

The output's first line is instructive on its own. The row with the missing field was met
with a warning by the import tool and the missing column was filled with a null value —
meaning it was not silently rejected, it was silently **accepted**. Without the validation
layer, this row would have entered the target with an empty pickup date. How import tools
handle a malformed row varies by engine and by tool; which behavior applies is never
assumed, it is tested.

Export is the same contract in the opposite direction: column order, delimiter, header
row, and date format are set for the other side. If both directions of a transfer are
bound to the same format definition, the file handed out can be tested by loading it back
into its own staging table.

## Other Decisions During Loading

Three more settings determine load time, and all three carry the same trade-off:
temporarily reducing durability.

**Log setting.** Some engines offer a mode that writes less to the log during bulk
loading. The gain is large; in exchange, if a crash happens during loading, the table can
end up unrecoverable and the load has to be redone from scratch. This is acceptable for a
first load into an empty table, not when appending to existing data.

**Sync setting.** A setting that does not wait for the disk write to complete on commit
makes a large difference on small batches. It has to be returned to its previous value
once loading finishes; forgetting this step silently leaves the system at risk for months
afterward.

**Copy command.** Most engines provide a command that takes data as a stream instead of
row-by-row inserts. The import above is one example of this. Its gain is that statement
parsing and per-record processing overhead disappear.

Finally, statistics need to be refreshed after loading. The planner knows how many rows a
table has and how values are distributed from statistics it collected itself; this
information, covered in the Advanced SQL course, loses its connection to reality after a
bulk load. Left unrefreshed, the planner scans a table it thinks is empty, and queries run
unexpectedly slow after the load.

## Summary

- Commit count is bulk loading's first cost; the measurement showed a gap of more than
  six hundred times between one transaction per row and batched loading.
- Most of the gain is obtained in the first few hundred-row batches; very large batches
  grow restart cost and the amount of rollback information.
- Indexes require per-row maintenance during loading; built in bulk afterward instead,
  both duration drops and the index takes up less space.
- A staging table takes incoming data in without constraints and moves validation to a
  query; bad rows are set aside along with a reason without stopping the load.
- Import tools can silently accept malformed rows; the behavior is never assumed, it is
  tested.
- Statistics need to be refreshed after loading; left unrefreshed, the planner works with
  stale information.

## Next Step

The lessons in this topic set up every task a database needs to keep on living:
authorization, backup, recovery, replication, availability, connection management,
monitoring, and bulk transfer. One remains, and it concerns all of them at once. The
engine's version changes over time; security fixes, bug fixes, and end of support
eventually make an upgrade unavoidable. Upgrading means replacing the layer underneath a
running system: it requires an outage window, a compatibility check, a rollback plan, and
the application being able to run against two versions at once. The last lesson covers
this.