So how do you insert a bunch of values into a postgres database?
You could call insert a bunch of times.
You could do the same thing but in a transaction.
There's also the unnest function (which is sadly not portable to sqlite),
or there's the magic CopyFrom function which goes zoom.
1package main
2
3import (
4 "context"
5 "crypto/rand"
6 "encoding/hex"
7 mathrand "math/rand"
8 "os"
9 "testing"
10
11 "github.com/jackc/pgx/v5"
12)
13
14func BenchmarkBatchInsert(b *testing.B) {
15 dataStrings := make([]string, 1048576)
16 dataInt := make([]int, 1048576)
17 for i := 0; i < len(dataStrings); i++ {
18 dataInt[i] = mathrand.Int()
19 buf := make([]byte, 16)
20 rand.Read(buf)
21 dataStrings[i] = hex.EncodeToString(buf)
22 }
23
24 b.Run("separate", func(b *testing.B) {
25 ctx, conn := getConn("separate")
26 b.ResetTimer()
27
28 for i := 0; i < b.N; i++ {
29 _, err := conn.Exec(ctx, `insert into separate (a, b) values ($1, $2)`, dataStrings[i], dataInt[i])
30 if err != nil {
31 panic(err)
32 }
33 }
34 })
35 b.Run("single_tx", func(b *testing.B) {
36 ctx, conn := getConn("single_tx")
37 b.ResetTimer()
38
39 tx, err := conn.BeginTx(ctx, pgx.TxOptions{})
40 if err != nil {
41 panic(err)
42 }
43 defer tx.Commit(ctx)
44
45 for i := 0; i < b.N; i++ {
46 _, err := tx.Exec(ctx, `insert into single_tx (a, b) values ($1, $2)`, dataStrings[i], dataInt[i])
47 if err != nil {
48 panic(err)
49 }
50 }
51 })
52 b.Run("unnest", func(b *testing.B) {
53 ctx, conn := getConn("unnest")
54 b.ResetTimer()
55 _, err := conn.Exec(ctx, `insert into unnest select * from unnest($1::text[], $2::numeric[]);`, dataStrings[:b.N], dataInt[:b.N])
56 if err != nil {
57 panic(err)
58 }
59 })
60 b.Run("copy", func(b *testing.B) {
61 ctx, conn := getConn("copy")
62
63 rows := make([][]any, 0, len(dataStrings))
64 for i := 0; i < len(dataStrings); i++ {
65 rows = append(rows, []any{dataStrings[i], dataInt[i]})
66 }
67
68 b.ResetTimer()
69 _, err := conn.CopyFrom(ctx, pgx.Identifier{"copy"}, []string{"a", "b"}, pgx.CopyFromRows(rows[:b.N]))
70 if err != nil {
71 panic(err)
72 }
73 })
74}
75
76func getConn(name string) (context.Context, *pgx.Conn) {
77 ctx := context.Background()
78 conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
79 if err != nil {
80 panic(err)
81 }
82
83 _, err = conn.Exec(ctx, `drop table if exists `+name+`;`)
84 if err != nil {
85 panic(err)
86 }
87 _, err = conn.Exec(ctx, `create table `+name+` ( a text, b numeric);`)
88 if err != nil {
89 panic(err)
90 }
91
92 return ctx, conn
93}
Benchmark results:
1$ go test -bench=. -benchtime=100000x
2goos: linux
3goarch: amd64
4pkg: go.seankhliao.com/testrepo0482
5cpu: 12th Gen Intel(R) Core(TM) i7-1260P
6BenchmarkBatchInsert/separate-16 100000 253591 ns/op
7BenchmarkBatchInsert/single_tx-16 100000 26578 ns/op
8BenchmarkBatchInsert/unnest-16 100000 1267 ns/op
9BenchmarkBatchInsert/copy-16 100000 352.2 ns/op
10PASS
11ok go.seankhliao.com/testrepo0482 28.838s