Insert multiple rows into a table using a single statement

[stasvo]

Is optimal way for insert multiple rows into a table using a single statement is this way?
Imagine that in real situation there around 1000 rows for insert in signle statement.

query := "INSERT INTO some_table (name, age) values ($1, $2), ($3, $4), ($5, $6);"
args := []any{"R1", 10, "R2", 20, "R3", 30}
_, err := r.Pool().Exec(ctx, query, args...)
if err != nil {
	return err
}

Or better use some another way? Maybe exists some method or function?

[jackc]

The COPY protocol is the fastest way to insert many rows at a time. https://pkg.go.dev/github.com/jackc/pgx/[email protected]#hdr-Copy_Protocol

Next in speed would be one insert statement with multiple rows as you have above. That is a little awkward to use because you have to build the SQL. You also are limited to ~64k parameters.

A batch operation that bundles bunch of simple inserts together would be next in performance. https://pkg.go.dev/github.com/jackc/pgx/[email protected]#Conn.SendBatch

And slowest, of course, would be inserting each row one at a time.

[stasvo]

Looks like copy protocol is good option. Is I understood correct that possible to use CopyFrom inside transaction just same as any standart query with possibility of rollback?

[jackc]

Yes, copy protocol is transactional.

[stasvo]

Thank you!

[stasvo]

Do I understand correct that batch (SendBatch) works faster compare to insert multiple rows one by one in single transaction without batch? My tests by insert 1000 rows shows that batch in this case works in 10 times faster compare to insert rows one by one in single transaction.
And also I have question about transaction in batch. While use batch in case if I want to use tranacaction for have guarantee that all batch queries will be executed or no one of them, shoud I create transaction by myself or batch already has transaction mode and not necessary to create transaction for this case? And another case if I will want to have in single transaction not only batch queries but also not batch queries and will create and use transaction for it, there will not be problems?

[jackc]

Do I understand correct that batch (SendBatch) works faster compare to insert multiple rows one by one in single transaction without batch? My tests by insert 1000 rows shows that batch in this case works in 10 times faster compare to insert rows one by one in single transaction.

Yes. Batching avoids a network round trip for each query.

And also I have question about transaction in batch. While use batch in case if I want to use tranacaction for have guarantee that all batch queries will be executed or no one of them, shoud I create transaction by myself or batch already has transaction mode and not necessary to create transaction for this case?

Batches are implicitly transactional. No explicit begin statement is necessary for simple atomicity.

And another case if I will want to have in single transaction not only batch queries but also not batch queries and will create and use transaction for it, there will not be problems?

It will work fine.