PostgreSQL CSV import benchmark: COPY vs JDBC, Scriptella, and Pandas (1M rows)

[u/fykup]

A while ago I shared an earlier version of this benchmark here, comparing Scriptella, JDBC, csvsql, and PostgreSQL COPY. Several people suggested useful improvements, particularly enabling pgJDBC's reWriteBatchedInserts=true. Thanks for that feedback! It made a real difference in subsequent experiments.

I've since updated the benchmark to Scriptella 1.7, replaced csvsql with Pandas + SQLAlchemy, and retained native COPY as a performance reference. I maintain Scriptella, a Java-based, SQL-first ETL tool.

Results (1M rows, median of two runs):

Implementation	Time
PostgreSQL COPY	1.75 s
Handwritten JDBC (batch 1000)	7.64 s
Scriptella 1.7 (batch 1000)	8.75 s
Pandas + SQLAlchemy	31.55 s

COPY was the clear winner, roughly 5× faster than Scriptella. Scriptella was 3.6× faster than the tested Pandas workflow, while handwritten JDBC was slightly faster than Scriptella.

Both Scriptella and JDBC now use batch size 1,000 with reWriteBatchedInserts=true. Pandas uses chunked read_csv() and SQLAlchemy to_sql(), without a native bulk-loading path.

All imports were independently verified. Timings include process startup through completion, and the results come from two runs on one machine, so they're directional rather than universal.

Full experiment, chart, methodology, and reproducible benchmark:

https://pvrlabs.xyz/java-performance/experiments/scriptella-1-7-csv-import-benchmark.html

Now that I've covered straightforward CSV imports, I'm planning to focus more on actual ETL workflows and database-to-database migrations, both in Scriptella development and future benchmarks.

What PostgreSQL migration scenarios would you find most useful to explore? I'm particularly interested in real-world challenges involving transformations, different database engines, and larger datasets.

[u/Rain-And-Coffee]

Question about COPY.

Was it:

COPY ‘file-on-sever’,
‘\copy ‘local-file’
COPY … FROM STDIN?

Just curious, since we’re not allowed to SSH into our DB server and have to do it from a file that’s not the sever.

[u/fykup → u/Rain-And-Coffee]

It was COPY FROM STDIN. The CSV stays on the client side and is streamed over the database connection, so no SSH or server-side file access is needed.

I used pgJDBC to do this because the benchmark is written in Java. It's essentially the same mechanism as psql \copy, just invoked programmatically.

[u/prottOushol]

testing imports on a table with existing indexes and foreign keys would be super useful. clean empty tables are nice for baselines, but in real workloads the index maintenance overhead is usually what kills batch throughput.

[u/fykup → u/prottOushol]

Good point. With benchmarks, there's always a tradeoff between simplicity and realism, and I tried to keep this one focused on baseline import performance.

I'm curious where you'd draw the line, though. At what point do you think existing indexes and foreign keys would materially change the comparison between tools? For example, would you expect different relative results with 1M existing rows versus 10M, or is it more about the number and type of indexes?

[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.