copyin_vs_batch.go

package main

import (
	"bytes"
	"database/sql"
	"fmt"
	"time"

	"github.com/lib/pq"
)

func main() {
	copyIn()
	batch()
}

func copyIn() {
	db, err := sql.Open("postgres", "user=bartek dbname=horizon sslmode=disable")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec("truncate history_operations;")
	if err != nil {
		panic(err)
	}

	start := time.Now()
	defer func() {
		elapsed := time.Now().Sub(start)
		fmt.Println("copyIn", elapsed)
	}()

	txn, err := db.Begin()
	if err != nil {
		panic(err)
	}

	stmt, err := txn.Prepare(pq.CopyIn("history_operations", "id", "transaction_id", "application_order", "type", "details", "source_account"))
	if err != nil {
		panic(err)
	}

	for i := 0; i < 65000; i++ {
		_, err = stmt.Exec(i, i, 0, 0, `{"abc": "def"}`, "test")
		if err != nil {
			panic(err)
		}
	}

	_, err = stmt.Exec()
	if err != nil {
		panic(err)
	}

	err = stmt.Close()
	if err != nil {
		panic(err)
	}

	err = txn.Commit()
	if err != nil {
		panic(err)
	}
}

func batch() {
	db, err := sql.Open("postgres", "user=bartek dbname=horizon sslmode=disable")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec("truncate history_operations;")
	if err != nil {
		panic(err)
	}

	start := time.Now()
	defer func() {
		elapsed := time.Now().Sub(start)
		fmt.Println("batch", elapsed)
	}()

	var buffer bytes.Buffer

	buffer.WriteString("INSERT INTO history_operations (id, transaction_id, application_order, type, details, source_account) VALUES ")

	for i := 0; i < 65000; i++ {
		buffer.WriteString(fmt.Sprintf(`(%d, %d, 0, 0, '{"abc": "def"}', 'test'),`, i, i))
	}

	query := buffer.String()
	query = query[0:len(query)-1] + ";"

	_, err = db.Exec(query)
	if err != nil {
		panic(err)
	}
}