Massive Data Handling: Bulk Loads, Batching & ETL

-- ❌ The slow way: one INSERT statement per row
-- Each statement is its own round trip + parse + commit.
INSERT INTO orders (customer_id, total) VALUES (101, 99.99);
INSERT INTO orders (customer_id, total) VALUES (102, 149.50);
INSERT INTO orders (customer_id, total) VALUES (103, 12.00);
-- ... imagine 9,999,997 more of these.

-- Loading 10 million rows like this can take 30–60 MINUTES,
-- because the database pays the overhead 10 million times.

-- ✅ The fast way: bulk-load straight from a file with COPY
-- COPY streams rows in one operation — no per-row overhead.

-- PostgreSQL: read a CSV from disk on the server
COPY orders (customer_id, total, order_date)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- MySQL does the same job with LOAD DATA INFILE:
-- LOAD DATA INFILE '/data/orders.csv'
-- INTO TABLE orders
-- FIELDS TERMINATED BY ','
-- LINES TERMINATED BY '\n'
-- IGNORE 1 ROWS;            -- skip the head
...

-- When the data comes from your APP (not a file), batch it.
-- ❌ One INSERT per row = one round trip per row.
-- ✅ One INSERT with many VALUES = one round trip per batch.

INSERT INTO orders (customer_id, total) VALUES
    (101, 99.99),
    (102, 149.50),
    (103, 12.00),
    (104, 45.20),
    (105, 7.99);
-- Five rows, ONE round trip. Repeat in batches of
-- 1,000–10,000 rows — that is the sweet spot for most drivers.

-- Wrap a batch in a transaction so it commits ONCE, not per row.
-- A "transaction" is an all-or-nothing unit of work.
BEGIN;                                    -- start the unit of work
  INSERT INTO orders (customer_id, total) VALUES (201, 10.00);
  INSERT INTO orders (customer_id, total) VALUES (202, 20.00);
  -- ... thousands more inside this same transaction ...
COMMIT;                                   -- flush everything to disk ONCE

-- One giant transaction is just as bad, though: it h
...

-- Around a HUGE load, drop the indexes first, then rebuild.
-- Updating an index for every one of 10M rows is the slow part.

DROP INDEX idx_orders_customer;            -- 1) remove the index

COPY orders FROM '/data/orders.csv'        -- 2) bulk-load with no index upkeep
    WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_customer           -- 3) rebuild ONCE at the end
    ON orders (customer_id);

ANALYZE orders;                            -- 4) refresh stats for the planner

-- Bui
...

-- UPSERT = "insert, but UPDATE the row if it already exists".
-- Perfect for re-running a load without creating duplicates.

-- PostgreSQL: ON CONFLICT (the conflicting column) DO UPDATE
INSERT INTO inventory (sku, on_hand) VALUES
    ('A-100', 50),
    ('A-200', 30),
    ('A-300', 75)
ON CONFLICT (sku) DO UPDATE
    SET on_hand = EXCLUDED.on_hand;       -- EXCLUDED = the row you tried to insert

-- MySQL says the same thing as ON DUPLICATE KEY UPDATE:
-- INSERT INTO inventory (sku, on_hand) VA
...

-- Deleting/updating MILLIONS of rows in ONE statement locks the
-- table for a long time and bloats the write-ahead log.
-- ❌ DELETE FROM events WHERE created_at < '2023-01-01';  -- 50M rows, one lock

-- ✅ Delete in chunks so each statement is short and releases locks.
DELETE FROM events
WHERE id IN (
    SELECT id FROM events
    WHERE created_at < '2023-01-01'
    LIMIT 10000                            -- 10k rows at a time
);
-- Run this in a loop until 0 rows are affected.
-- Same idea for
...