How to batch SQL statements in Postgres using Golang

Question

What is the fastest way to send batch requests to Postgres database using Golang? Each request contains 500-200000 rows.
Methods I know about are-
1. Using database/sql package's transaction Begin, Prepare, Commit.
2. Sending all data in one statement.
3. Sending a list of statements using sql.Exec() method.
Is there some other way to send batch requests without making a connection at every statement? If not which is the best way of these?

This question is similar to question at- Golang how do I batch sql statements with package database.sql

Any question that begins with "What is the fastest way" is best answered by benchmarks. — Jonathan Hall, Dec 27 '17 at 08:49

score 1 · Accepted Answer · answered Dec 27 '17 at 08:34

There is a bit old depesz blog post on that. His programs are Perl scripts, but if you concentrate on SQL... Anyway - from DB perspective, you can use COPY, or INSERT with many rows in VALUES. It looks that around 20 is good choice, but it is worth to test that in your case. If performance is key factor, I would put around 2000-5000 rows per transaction. Also, from DB perspective transaction, and session are two separate things. So you can open session, and to many transactions in it.

For PostgreSQL starting new session per operation is really bad idea - DB spawns new process for each session. One of answers for the question you referred contains this. So you open connection, and then transaction, as it should be done.

In the blog post, `COPY` outperforms the `INSERT` with many rows. Is there any reason not to use `COPY` over `INSERT`? — Shubham Chaudhary, Dec 27 '17 at 08:39
not really. `pg_dump` generates dumps with `COPY` by default. — Michał Zaborowski, Dec 27 '17 at 08:40

How to batch SQL statements in Postgres using Golang

1 Answers1