How to speed up inserting 100 billion records into a database? Currently: generate + insert + commit in a loop for each row.

To speed up the insertion of 100 billion records into a database, you need to avoid inserting one record at a time with a commit for each row. This is very slow due to transaction overhead and network operations.

Main approaches:

• Use batch inserts, grouping records into several thousand or tens of thousands per request.
• Disable indexes and triggers during the loading process to avoid spending resources on updating indexes.
• Use bulk load tools if supported by the DBMS (e.g., COPY in PostgreSQL, LOAD DATA INFILE in MySQL).
• Disable autocommit and commit after inserting large batches.
• Parallelize the loading if the architecture and DBMS allow.

Example in pseudocode:

connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement("INSERT INTO table (col1, col2) VALUES (?, ?)");
int batchSize = 10000;
int count = 0;
for (Record r : records) {
    ps.setString(1, r.getCol1());
    ps.setString(2, r.getCol2());
    ps.addBatch();
    if (++count % batchSize == 0) {
        ps.executeBatch();
        connection.commit();
    }
}
ps.executeBatch();
connection.commit();

This approach significantly speeds up data loading.