Massive Data

By the end of this lesson you'll be able to load and reshape millions of rows without locking up your database — using COPY / LOAD DATA instead of row-by-row inserts, batching and sizing transactions sensibly, dropping and rebuilding indexes around big loads, running idempotent upserts, and deleting in safe chunks. These are the techniques that turn a 45-minute load into a 15-second one.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Throughout this lesson, picture a nightly job that loads a fresh export of 10 million order rows into an orders table, then deletes last year's events. Every technique below is judged by one question: how do we do that in seconds instead of an hour, without freezing the live database?

Moving house, you don't carry items one at a time across town — you pack boxes and load a truck. Row-by-row INSERT is carrying one item per trip; COPY is the truck. Same boxes, a tiny fraction of the trips.

Each separate INSERT statement pays a fixed tax: a network round trip to the server, parsing and planning the query, and a transaction commit that forces a write to the write-ahead log (the on-disk journal the database uses to stay crash-safe). For one row that tax is invisible. For ten million rows you pay it ten million times — and that is where your minutes go.

PostgreSQL's COPY and MySQL's LOAD DATA INFILE are bulk loaders: they stream an entire file into a table in a single operation, skipping the per-row overhead entirely. This is almost always the fastest way to get data in — typically orders of magnitude faster than individual inserts.

Fill in the blanks to bulk-load customers.csv (which includes a header row). The expected result is in the comments so you can check yourself.

Sometimes the data lives in your application, not a file. You still don't want one statement per row — instead, list many rows in a single INSERT ... VALUES . One statement, one round trip, thousands of rows. The sweet spot is usually 1,000–10,000 rows per batch : big enough to amortise the overhead, small enough to keep memory and transaction size sane.

A transaction groups statements into one all-or-nothing unit. Committing once per transaction instead of once per row removes a huge amount of disk-flushing overhead. But don't swing too far the other way: wrapping all ten million rows in a single transaction holds locks for the whole load and balloons the write-ahead log. Commit in chunks — every 5,000–50,000 rows is a good range.

Think of COMMIT as saving a document. Saving after every keystroke is slow; never saving risks losing everything. Save in sensible chunks.

Indexes make reads fast, but they slow writes: every inserted row must also update every index on the table. During a one-off bulk load that maintenance is pure waste, because you can rebuild the whole index once at the end far more cheaply. The pattern is: drop the indexes, load, recreate the indexes, then ANALYZE so the query planner has fresh statistics.

An upsert means "insert this row, but if a row with the same key already exists, update it instead". It makes a load idempotent — safe to run twice without creating duplicates or crashing on a duplicate-key error. PostgreSQL spells it ON CONFLICT ... DO UPDATE ; MySQL spells it ON DUPLICATE KEY UPDATE . In Postgres, the special table EXCLUDED refers to the row you tried to insert.

Fill in the blanks so the load updates the price when a sku already exists, and inserts it otherwise.

Deleting or updating millions of rows in a single statement takes one long lock and writes a giant chunk of the write-ahead log — other queries stall and disk usage spikes. Instead, work in chunks : delete a few thousand rows, let the locks release, repeat until nothing is left. The same idea drives ETL/ELT batch jobs (Extract → Transform → Load): pull raw data into a no-constraints staging table, clean and validate it there, then load the good rows into production in sized batches.

Q: How big should each batch or transaction be?

There's no universal number, but 1,000–10,000 rows per multi-row INSERT and a COMMIT every 5,000–50,000 rows works well for most systems. Measure with your real data — too small wastes round trips, too large holds locks and grows the WAL.

Q: COPY or multi-row INSERT — which should I reach for?

If the data is (or can become) a file the server can read, COPY / LOAD DATA wins by a wide margin. If the data is generated in your application, batched multi-row INSERT s are the practical choice.

Q: Is it always worth dropping indexes before a load?

For large one-off loads into a table that's mostly idle, yes — rebuilding once is cheaper. For small incremental loads into a live, heavily-queried table, no: dropping indexes would slow every other query in the meantime.

Running the same load twice produces the same final state — no duplicates, no errors. UPSERT gives you that: a re-run updates existing rows instead of failing on a duplicate key.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

Practice quiz

Why is row-by-row INSERT so slow for millions of rows?

  • INSERT statements cannot use indexes
  • The database sorts the whole table after every insert
  • Each statement pays a round trip, parse/plan, and commit overhead — paid per row
  • Row-by-row inserts always run inside a single giant transaction

Answer: Each statement pays a round trip, parse/plan, and commit overhead — paid per row. Each separate INSERT pays a fixed tax (round trip, parse, commit). For ten million rows you pay it ten million times.

Which command is the fastest way to bulk-load a CSV in PostgreSQL?

  • COPY
  • A loop of single-row INSERT statements
  • MERGE
  • TRUNCATE

Answer: COPY. COPY streams an entire file into a table in one operation, skipping per-row overhead — often around 100x faster than individual inserts.

What is MySQL's equivalent of PostgreSQL's COPY for file loads?

  • BULK INSERT
  • IMPORT TABLE
  • INSERT FROM FILE
  • LOAD DATA INFILE

Answer: LOAD DATA INFILE. MySQL/MariaDB use LOAD DATA INFILE to bulk-load a file straight into a table.

What is the recommended commit size for a big load?

  • Commit after every single row
  • Commit every 5,000–50,000 rows
  • Wrap all 10 million rows in one transaction
  • Never commit until the server restarts

Answer: Commit every 5,000–50,000 rows. Committing once per row is slow; one giant transaction holds locks and bloats the WAL. The lesson recommends committing every 5,000–50,000 rows.

What does an UPSERT (ON CONFLICT DO UPDATE) make a load?

  • Idempotent — safe to run twice without duplicates or errors
  • Faster but non-repeatable
  • Read-only
  • Unable to insert new rows

Answer: Idempotent — safe to run twice without duplicates or errors. An upsert inserts a row or updates it if the key already exists, so re-running the load produces the same final state with no duplicate-key errors.

In a PostgreSQL upsert, what does EXCLUDED refer to?

  • The rows that were filtered out by a WHERE clause
  • Rows deleted by a previous statement
  • The row you tried to insert
  • The set of columns not in the index

Answer: The row you tried to insert. In ON CONFLICT ... DO UPDATE, the special table EXCLUDED refers to the row you tried to insert, e.g. SET on_hand = EXCLUDED.on_hand.

Why drop indexes before a large one-off bulk load?

  • Indexes prevent COPY from running at all
  • Every inserted row must update every index, so rebuilding once at the end is cheaper
  • Dropping indexes frees up disk needed for the CSV file
  • Indexes cause duplicate-key errors during loads

Answer: Every inserted row must update every index, so rebuilding once at the end is cheaper. Maintaining an index for each of millions of rows is the slow part; dropping indexes, loading, then rebuilding once is often 5–10x faster.

Why delete millions of rows in chunks rather than one statement?

  • Chunked deletes can run without a WHERE clause
  • DELETE cannot remove more than 10,000 rows at once
  • Chunking automatically rebuilds indexes
  • A single huge DELETE takes one long lock and bloats the write-ahead log

Answer: A single huge DELETE takes one long lock and bloats the write-ahead log. One giant DELETE holds a long lock and writes a huge chunk of WAL, stalling other queries. Deleting in chunks lets locks release between batches.

Why use a staging table in an ETL flow?

  • It makes COPY skip the header row automatically
  • To validate, deduplicate, and reject bad rows before they touch production data
  • It removes the need to ever commit
  • Staging tables load faster because they have more indexes

Answer: To validate, deduplicate, and reject bad rows before they touch production data. A staging table lets you clean and validate raw, untrusted data before loading the good rows into production.

After bulk-loading and rebuilding indexes, why run ANALYZE?

  • To delete the dropped indexes permanently
  • To compress the loaded data on disk
  • To refresh table statistics so the query planner can choose good plans
  • To re-validate every foreign key

Answer: To refresh table statistics so the query planner can choose good plans. ANALYZE refreshes statistics so the query planner has fresh information after a large load.