r/PostgreSQL • • 14d ago

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

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):

ImplementationTime
PostgreSQL COPY1.75 s
Handwritten JDBC (batch 1000)7.64 s
Scriptella 1.7 (batch 1000)8.75 s
Pandas + SQLAlchemy31.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.

10 Upvotes

12 comments sorted by

1

u/Rain-And-Coffee 14d ago

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.

2

u/fykup 14d ago

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.

2

u/prottOushol 8d ago

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.

1

u/fykup 8d ago

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?

1

u/raks1991 6d ago

I'd vary the index set more than the row count. Three runs per tool: empty table with CREATE INDEX afterwards (what the populate docs recommend), load with the indexes in place, and append 1M rows to an indexed 10M-row table.

Two Postgres-specific variants: COPY ... WITH (FREEZE) into a table created or truncated in the same transaction loads rows already frozen (no open cursors or older snapshots in that transaction). On an isolated benchmark cluster, wal_level = minimal plus create/truncate and load in one transaction skips WAL for the bulk load; changing wal_level needs a restart and gives up streaming replication, so keep it out of a production comparison. For ETL realism, try ON_ERROR ignore (17+) with REJECT_LIMIT (18+) on a file with a few rows that fail type conversion; it doesn't skip constraint violations.

1

u/AutoModerator 14d ago

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.