1M rows into Postgres in 1.29 seconds

I loaded the same CSV of 1M GitHub event rows into PostgreSQL 18 five ways. Row-by-row inserts took 16.95 minutes. COPY into a typed table took 3.91 seconds, and COPY into a text-only staging table took 1.29 seconds.

I wanted to know how much time I could save by changing how the client sent data and how the receiving table stored it. The fastest result deferred type conversion, so it needs to be read alongside the typed-table result.

Setup

I ran the benchmark on an AWS EC2 m7i.2xlarge instance.

The five approaches were:

• row-by-row INSERT
• batched INSERT
• logged COPY
• unlogged COPY
• raw landing COPY

The times depend on the schema, hardware, disk cache, Postgres configuration, and client implementation. This comparison uses one input file and the setup above.

Dataset

The input was a CSV generated from public GitHub Events data.

If your input is JSON, I also tested streaming large JSON in Python to reduce memory use before sending records to the database.

For every benchmark method, the runner created a fresh PostgreSQL Docker volume. Each method started with a clean Postgres data directory, WAL state, and table state.

It did not clear the host OS page cache. I used repeated runs and medians instead of trusting one run.

Methods compared

I compared five methods:

• naive_insert
• batched_insert
• production_copy
• unlogged_copy
• raw_landing_copy

The benchmark measured:

• ingest time
• rows per second
• speedup compared with naive INSERT

Results

How the methods differ

Row-by-row INSERT

The baseline used:

• one row at a time
• autocommit on
• one commit per row

For 1M rows, this took:

16.95 minutes
983 rows/sec

Each row required a separate commit. Repeating that a million times made this by far the slowest method.

Batched INSERT

Next I used multi-row INSERT with the same data and typed table. This dropped the load time from 16.95 minutes to 30.37 seconds, a 33.5x improvement. The client sent batches instead of talking to Postgres one row at a time.

In my PgBouncer multicore benchmark, proxy CPU was the first bottleneck. Here, changing how the client sent data removed much of the delay.

When I inspect ingestion code, I check:

• Are we sending rows one by one?
• Are we committing too often?
• Can this be batched?

Typed COPY

Then I used PostgreSQL COPY.

This loaded the same typed table in:

3.91 seconds
255,754 rows/sec

The table used these column types:

Compared with naive INSERT, this was 260.1x faster. The loaded data was already in the types the final table needed.

UNLOGGED COPY

Next I used an UNLOGGED table.

If Postgres crashes, an unlogged table can be truncated. I'd consider it for staging data that I can reload from the input file. I wouldn't use this result as a substitute for a durable load.

This loaded in:

3.24 seconds
308,642 rows/sec

That saved 0.67 seconds over logged COPY. Most of the improvement had already come from replacing INSERT.

COPY into a text-only staging table

This was the fastest result:

1.29 seconds
775,194 rows/sec

This method loaded the same CSV into a staging table where every column was text. During the load, Postgres skipped conversion to these final types:

The 1.29-second result measures loading text. Validation and conversion still have to happen before the data reaches the final table. I'd use this as a staging step, with the source file available to replay.

The cost of type conversion

After COPY, I expected less room for improvement.

The raw landing result was still faster:

Production COPY:   3.91 s
Raw landing COPY:  1.29 s

Deferring type conversion reduced the measured load time further. It moved work to a later stage, so the 1.29-second figure doesn't measure the complete path to query-ready data.

What I'd check in an ingestion job

Before tuning Postgres settings, I'd check how the client sends rows and what the receiving table needs to do:

• Can this be COPY instead of INSERT?
• Can writes be batched?
• Does this stage need full durability?
• Can raw data land before type conversion?
• Can validation and transformation happen after landing?

If the table must be query-ready immediately, I'd use the typed COPY result for comparison:

1M rows in 3.91 seconds

If the system already has a staging step, I'd also measure a text-only load and the validation and conversion that follow it.

Code

The benchmark code and raw results are available on GitHub ↗.

Limits of this benchmark

These times cover loading this CSV with the listed setup. They exclude index creation and any downstream validation and transformation. The host OS page cache remained in place between runs. The unlogged result also has different durability guarantees from a logged load.

For this file, the fastest staging load was 788.4x faster than naive INSERT. Typed COPY was 260.1x faster and produced the column types I needed. I'd start there unless I had a reason to separate loading from conversion.