Batch Inserts and COPY: The Throughput Difference

The main chapter showed the end-to-end throughput difference. This section isolates each variable: protocol overhead, parse/plan cost, network round trips, and buffer management. Understanding where time is spent at each layer lets you choose the right write method for your throughput target.

The Cost of a Single INSERT

Every INSERT statement goes through these server-side phases:

1. Parse: Tokenize SQL, build parse tree
2. Analyze: Resolve table/column names, check permissions
3. Plan: Generate execution plan (trivial for INSERT, but still happens)
4. Execute: Find target heap page, write tuple, write WAL record
5. Commit processing: If autocommit, flush WAL to disk

Measuring each phase with pg_stat_statements:

-- Enable detailed timing
ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();

-- Reset stats
SELECT pg_stat_statements_reset();

-- Run 10,000 single INSERTs from Java application...

-- Analyze results
SELECT
    calls,
    total_plan_time / calls AS avg_plan_ms,
    total_exec_time / calls AS avg_exec_ms,
    (total_exec_time - total_plan_time) / calls AS avg_exec_only_ms,
    shared_blks_written / calls AS avg_blocks_written,
    wal_bytes / calls AS avg_wal_bytes_per_call
FROM pg_stat_statements
WHERE query LIKE '%INSERT INTO articles%';

 calls | avg_plan_ms | avg_exec_ms | avg_exec_only_ms | avg_blocks_written | avg_wal_bytes_per_call
-------+-------------+-------------+------------------+--------------------+------------------------
 10000 |        0.02 |        1.28 |             1.26 |                  1 |                  29184

Planning costs 0.02ms per statement (negligible). Execution costs 1.26ms, dominated by the WAL flush on commit. Each row generates approximately 28KB of WAL (the article body plus metadata).

Prepared Statements: Eliminating Parse/Plan

Without prepared statements, the driver sends the SQL text every time:

// SLOW: unprepared statement, parse/plan every call
try (Statement stmt = conn.createStatement()) {
    for (Article a : articles) {
        String sql = String.format(
            "INSERT INTO articles (external_id, title, body, category_id, source_id, " +
            "published_at, word_count) VALUES ('%s', '%s', '%s', %d, %d, '%s', %d)",
            escape(a.externalId()), escape(a.title()), escape(a.body()),
            a.categoryId(), a.sourceId(), a.publishedAt(), a.wordCount());
        stmt.executeUpdate(sql);
    }
}

With prepared statements, parse and plan happen once:

// FAST: prepared statement, parse/plan once
String sql = """
    INSERT INTO articles (external_id, title, body, category_id, source_id,
                         published_at, word_count)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    """;
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (Article a : articles) {
        ps.setString(1, a.externalId());
        ps.setString(2, a.title());
        ps.setString(3, a.body());
        ps.setInt(4, a.categoryId());
        ps.setInt(5, a.sourceId());
        ps.setObject(6, a.publishedAt());
        ps.setInt(7, a.wordCount());
        ps.executeUpdate();
    }
}

Benchmark comparison (10,000 rows, autocommit on):

Unprepared statement:  672 rows/sec (avg 1.49ms/row)
Prepared statement:    704 rows/sec (avg 1.42ms/row)
Improvement:           4.7%

The improvement is minimal because planning an INSERT is trivial (no join optimization, no index selection). Prepared statements matter more for complex SELECT queries. For writes, the dominant cost is the commit fsync, not the parse/plan cycle.

Multi-Row VALUES: Reducing Round Trips

Instead of one row per statement, pack multiple rows into a single INSERT:

// FAST: multi-row VALUES clause
public int insertMultiRow(Connection conn, List<Article> articles, int rowsPerStatement)
        throws SQLException {
    conn.setAutoCommit(false);
    int count = 0;

    for (int i = 0; i < articles.size(); i += rowsPerStatement) {
        int end = Math.min(i + rowsPerStatement, articles.size());
        List<Article> chunk = articles.subList(i, end);

        StringBuilder sql = new StringBuilder(
            "INSERT INTO articles (external_id, title, body, category_id, " +
            "source_id, published_at, word_count) VALUES ");

        for (int j = 0; j < chunk.size(); j++) {
            if (j > 0) sql.append(',');
            sql.append("(?,?,?,?,?,?,?)");
        }

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            int paramIdx = 1;
            for (Article a : chunk) {
                ps.setString(paramIdx++, a.externalId());
                ps.setString(paramIdx++, a.title());
                ps.setString(paramIdx++, a.body());
                ps.setInt(paramIdx++, a.categoryId());
                ps.setInt(paramIdx++, a.sourceId());
                ps.setObject(paramIdx++, a.publishedAt());
                ps.setInt(paramIdx++, a.wordCount());
            }
            count += ps.executeUpdate();
        }
    }
    conn.commit();
    return count;
}

Benchmark varying rows per statement (10,000 total rows, single COMMIT at end):

Rows/Statement | Total Time | Rows/sec | Statements | Parse Overhead
1              |    2.1s    |   4,762  |   10,000   |    200ms total
10             |    1.4s    |   7,143  |    1,000   |     20ms total
50             |    1.1s    |   9,091  |      200   |      4ms total
100            |    1.0s    |  10,000  |      100   |      2ms total
500            |    0.95s   |  10,526  |       20   |     0.4ms total
1000           |    0.93s   |  10,753  |       10   |     0.2ms total

Diminishing returns above 100 rows per statement. The improvement from 1 to 100 rows/statement is 2.1x. Beyond that, the bottleneck shifts from protocol overhead to I/O.

Note: the single COMMIT at end removes fsync as a variable. This isolates the protocol and planning overhead.

JDBC Batch Execution: The Middle Ground

JDBC’s addBatch() / executeBatch() sends multiple statements in one network round trip but still executes them individually server-side:

// FAST: JDBC batch execution
public int insertJdbcBatch(Connection conn, List<Article> articles, int batchSize)
        throws SQLException {
    conn.setAutoCommit(false);
    String sql = """
        INSERT INTO articles (external_id, title, body, category_id, source_id,
                             published_at, word_count)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """;
    int count = 0;
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (Article a : articles) {
            ps.setString(1, a.externalId());
            ps.setString(2, a.title());
            ps.setString(3, a.body());
            ps.setInt(4, a.categoryId());
            ps.setInt(5, a.sourceId());
            ps.setObject(6, a.publishedAt());
            ps.setInt(7, a.wordCount());
            ps.addBatch();
            count++;
            if (count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch();
        conn.commit();
    }
    return count;
}

With reWriteBatchedInserts=true in the JDBC URL, the driver rewrites batched INSERTs into multi-row VALUES:

jdbc:postgresql://localhost/content?reWriteBatchedInserts=true

Benchmark (10,000 rows, single COMMIT):

JDBC batch (rewrite=false): 5,882 rows/sec
JDBC batch (rewrite=true):  9,524 rows/sec
Improvement:                62%

The reWriteBatchedInserts flag is critical. Without it, the driver sends 10,000 individual INSERT statements in one batch (saving network round trips but not server-side work). With it, the driver packs rows into multi-row VALUES clauses, reducing server-side parse/plan cycles.

COPY Protocol: Bypassing SQL Entirely

COPY uses a dedicated streaming protocol that skips SQL parsing:

// FAST: COPY with text format
public long copyText(Connection conn, List<Article> articles) throws SQLException {
    PGConnection pgConn = conn.unwrap(PGConnection.class);
    CopyManager cm = pgConn.getCopyAPI();

    String copySql = """
        COPY articles (external_id, title, body, category_id, source_id,
                      published_at, word_count)
        FROM STDIN WITH (FORMAT text, DELIMITER E'\\t', NULL '\\N')
        """;

    var buffer = new ByteArrayOutputStream(articles.size() * 1024);
    for (Article a : articles) {
        buffer.write(escapeTsv(a.externalId()));
        buffer.write('\t');
        buffer.write(escapeTsv(a.title()));
        buffer.write('\t');
        buffer.write(escapeTsv(a.body()));
        buffer.write('\t');
        buffer.write(Integer.toString(a.categoryId()).getBytes());
        buffer.write('\t');
        buffer.write(Integer.toString(a.sourceId()).getBytes());
        buffer.write('\t');
        buffer.write(a.publishedAt().toString().getBytes());
        buffer.write('\t');
        buffer.write(Integer.toString(a.wordCount()).getBytes());
        buffer.write('\n');
    }

    return cm.copyIn(copySql, new ByteArrayInputStream(buffer.toByteArray()));
}

// FAST: COPY with binary format (saves text parsing server-side)
public long copyBinary(Connection conn, List<Article> articles) throws SQLException {
    PGConnection pgConn = conn.unwrap(PGConnection.class);
    CopyManager cm = pgConn.getCopyAPI();

    String copySql = """
        COPY articles (external_id, title, body, category_id, source_id,
                      published_at, word_count)
        FROM STDIN WITH (FORMAT binary)
        """;

    var buffer = new ByteArrayOutputStream(articles.size() * 1024);
    writeBinaryHeader(buffer);
    for (Article a : articles) {
        writeBinaryRow(buffer, a);
    }
    writeBinaryTrailer(buffer);

    return cm.copyIn(copySql, new ByteArrayInputStream(buffer.toByteArray()));
}

Benchmark comparing COPY formats (10,000 rows):

COPY text format:    11,111 rows/sec
COPY binary format:  12,500 rows/sec
Improvement:         12.5%

Binary format eliminates text-to-internal conversion on the server (parsing timestamps, integers from text). The improvement is modest because the article body (TEXT type) dominates row size and requires no conversion in either format.

COPY with Streaming: Avoiding Memory Pressure

For large batches, buffer the COPY stream rather than materializing all data in memory:

// FAST: streaming COPY avoids holding all data in memory
public long copyStreaming(Connection conn, Iterator<Article> articles) throws SQLException {
    PGConnection pgConn = conn.unwrap(PGConnection.class);
    CopyManager cm = pgConn.getCopyAPI();

    String copySql = """
        COPY articles (external_id, title, body, category_id, source_id,
                      published_at, word_count)
        FROM STDIN WITH (FORMAT text, DELIMITER E'\\t', NULL '\\N')
        """;

    var out = cm.copyIn(copySql);
    byte[] rowBuffer = new byte[8192];
    long count = 0;

    while (articles.hasNext()) {
        Article a = articles.next();
        byte[] row = formatTsvRow(a);
        ((CopyIn) out).writeToCopy(row, 0, row.length);
        count++;
    }
    ((CopyIn) out).endCopy();
    return count;
}

Memory profile comparison for 100,000 articles (avg 2KB body):

Method                  | Peak Heap | GC Pauses
COPY buffered (byte[]) |   210 MB  |    12
COPY streaming          |    18 MB  |     2

Streaming COPY maintains constant memory regardless of batch size. The content platform processes feeds of variable size (100 to 50,000 articles), making streaming essential.

Server-Side Timing: pg_stat_io

PostgreSQL 16’s pg_stat_io view reveals I/O patterns per write method:

-- Reset before benchmark
SELECT pg_stat_reset_shared('io');

-- Run 10,000 rows via single INSERT...

SELECT context, reads, writes, write_time, fsyncs, fsync_time
FROM pg_stat_io
WHERE backend_type = 'client backend'
  AND object = 'relation';

After single INSERT (autocommit):

 context | reads | writes | write_time | fsyncs | fsync_time
---------+-------+--------+------------+--------+------------
 normal  |   412 |   3847 |   1842.3ms |      0 |     0.0ms

After COPY (single transaction):

 context | reads | writes | write_time | fsyncs | fsync_time
---------+-------+--------+------------+--------+------------
 normal  |    18 |   3521 |    312.7ms |      0 |     0.0ms

The COPY method generates fewer reads (no per-statement index lookups for constraint checking between rows) and faster total write time (sequential page allocation vs scattered writes).

The Decision Framework

Choose the write method based on throughput requirements:

Throughput Target	Method	Key Setting
< 100 rows/sec	Single INSERT, autocommit	Default
100-1000 rows/sec	Prepared statement, batch commit	batchSize=100
1000-5000 rows/sec	JDBC batch + rewrite	reWriteBatchedInserts=true
5000-15000 rows/sec	COPY text	Single transaction
> 15000 rows/sec	COPY binary + unlogged staging	Two-phase pipeline

For the content platform at 10,000 articles/hour (2.78/sec average, 50/sec peak during feed bursts), COPY with 500-row batches provides 4x headroom over peak load.

Index Impact on Write Throughput

Every index on the target table adds overhead per INSERT. Measuring the write cost per index:

-- Baseline: no indexes except PK
-- Add indexes one at a time and measure

-- Index 1: category lookup
CREATE INDEX idx_articles_category ON articles (category_id, published_at DESC);

-- Index 2: source lookup
CREATE INDEX idx_articles_source ON articles (source_id, published_at DESC);

-- Index 3: full-text (hypothetical)
CREATE INDEX idx_articles_title_gin ON articles USING gin (to_tsvector('english', title));

COPY throughput degradation per index (10,000 rows):

Indexes | Rows/sec | Degradation
0 (PK only)     | 12,820  |   baseline
1 B-tree        | 10,526  |   -18%
2 B-tree        |  8,929  |   -30%
2 B-tree + GIN  |  5,556  |   -57%

Each B-tree index adds approximately 15-18% overhead. GIN indexes are expensive because they maintain posting lists. For bulk ingestion, consider dropping indexes, loading data, then recreating them:

-- For initial bulk load only (not ongoing ingestion)
DROP INDEX idx_articles_category;
DROP INDEX idx_articles_source;

-- COPY 1,000,000 articles...

CREATE INDEX CONCURRENTLY idx_articles_category ON articles (category_id, published_at DESC);
CREATE INDEX CONCURRENTLY idx_articles_source ON articles (source_id, published_at DESC);

For the content platform’s steady-state ingestion (10k articles/hour), the 30% overhead from two B-tree indexes is acceptable because read performance requires those indexes to exist.