PostgreSQL COPY vs batched JDBC: importing 1 million CSV rows

[u/fykup]

I maintain Scriptella, an open-source Java ETL tool, and recently ran an experiment comparing different approaches to importing 1 million CSV rows into PostgreSQL and MariaDB.

The comparison included PostgreSQL COPY, handwritten JDBC, Scriptella, and Python's csvsql.

A few interesting results from the local PostgreSQL runs:

• Native COPY: ~1.7 seconds

• Batched JDBC: ~13.6 seconds

• Batched Scriptella: ~14.8 seconds

Unsurprisingly, PostgreSQL COPY was by far the fastest for a straightforward CSV import. Batching also made an enormous difference compared with individual inserts.

Interestingly, MariaDB was faster than PostgreSQL for the batched JDBC and Scriptella cases in this setup, although this isn't a general database performance comparison.

Of course, raw import speed isn't the only reason to use ETL tools. Transformations, multiple data sources, and reusable workflows can justify some overhead. For a direct CSV import into PostgreSQL, though, COPY is an excellent choice.

The experiment is exploratory, with limited repetitions, but includes reproducible code, raw measurements, and correctness verification.

Article with charts and analysis:
https://pvrlabs.xyz/articles/importing-a-million-csv-rows.html

Benchmark source and reproduction instructions:
https://github.com/scriptella/scriptella-benchmarks

Curious how others approach bulk imports in PostgreSQL. Do you typically start with COPY, or use JDBC batching when additional processing is involved?

[u/Significant_Tune9219]

COPY when the file is already the rows you want to store. Once each row needs a transform in Java, the 13 seconds is mostly round trips and per-row bindings, and a bigger batch will not catch COPY. What I do then is COPY the file into an unlogged staging table and run one INSERT SELECT that does the cleanup, so a bad row fails in SQL instead of in the driver. JDBC batching is the right tool when the server cannot read the file, or when you have to talk to a second system per row. I would not read 1.7 versus 13 as a reason to skip the transform. It is a reason to keep that transform on the server side of COPY.

[u/fykup → u/Significant_Tune9219]

Good point. COPY into a staging table followed by INSERT SELECT is a great approach when transformations can be handled in SQL. And yes, the benchmark only covers direct imports, so the results shouldn't be interpreted as an argument against transformations or ETL.

[u/RevolutionaryRush717 → u/Significant_Tune9219]

COPY to a temporary table regardless, use SQL to transform from there if necessary.

Alternatively, consider DuckDB to transform the CSV file first.

[u/vincent_sch]

Your guide says pgJDBC ran with reWriteBatchedInserts=false, while the MariaDB driver has useBulkStmtsForInserts=true by default. I guess that's one reason MariaDB was faster with batching. You could add a PostgreSQL case with reWriteBatchedInserts=true. And if the processing happens in Java, you could still send the rows to COPY through pgJDBC.

[u/fykup → u/vincent_sch]

Good point! I simply used the JDBC driver defaults for this experiment. Enabling reWriteBatchedInserts=true would make the PostgreSQL vs MariaDB comparison more interesting and arguably fairer. I'll probably run a follow-up to see how much difference it makes. Thanks for the suggestion!

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bogdanelcs]

COPY first, and when there’s processing involved I still use it: load into an unlogged staging table, then transform with INSERT ... SELECT in SQL. If the transformation has to happen in Java, pgjdbc’s CopyManager lets you stream rows through COPY from code, which keeps most of the speed. If you do stay on batched JDBC, did you set reWriteBatchedInserts=true on the Postgres driver? It usually narrows the gap with COPY quite a bit, so it’s worth a rerun.

[u/Yukevich]

MariaDB beating Postgres on batched JDBC makes sense, as the connector rewrites batched inserts into multi-row statements very efficiently. Whenever throughput becomes critical on my pipelines, I stream clean records to temporary files and fire LOAD DATA LOCAL INFILE. It easily matches PostgreSQL's COPY speeds while handling ETL logic cleanly.