Terrible insert performance

package main

import (
	"database/sql"
	"fmt"
	"github.com/google/uuid"
	"log"
	_ "github.com/mattn/go-sqlite3"
	_ "modernc.org/sqlite"
	"os"
	"time"
)

func main() {
	const dbName = "test.db"

	if err := os.Remove(dbName); err != nil && !os.IsNotExist(err) {
		log.Fatal(err)
	}

	db, err := sql.Open("sqlite", dbName)

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	sqlText := "create table test(c1 text, c2 text, c3 text)"

	if _, err := db.Exec(sqlText); err != nil {
		log.Fatal(err)
	}

	n := 1000000
	bulkSize := 10_000

	log.Printf("inserting %d records (bulk size = %d)...", n, bulkSize)
	start := time.Now()

	for i := 0; i < n; i += bulkSize {
		log.Printf("bulk #%d/%d...", i/bulkSize, n/bulkSize)

		tx, err := db.Begin()

		if err != nil {
			log.Fatal(err)
		}

		stmt, err := tx.Prepare("insert into test(c1, c2, c3) values (?, ?, ?)")

		if err != nil {
			log.Fatal(err)
		}

		for j := 0; j < bulkSize; j++ {
			c1, _ := uuid.NewRandom()
			c2, _ := uuid.NewRandom()
			c3, _ := uuid.NewRandom()

			_, err = stmt.Exec(c1, c2, c3)

			if err != nil {
				log.Fatal(err)
			}
		}

		if err := stmt.Close(); err != nil {
			log.Fatal(err)
		}

		if err := tx.Commit(); err != nil {
			log.Fatal(err)
		}
	}

	fmt.Println("creating the index...")

	sqlText = `create unique index test__c1_index on test (c1)`

	if _, err := db.Exec(sqlText); err != nil {
		log.Fatal(err)
	}

	log.Printf("done in %v", time.Now().Sub(start))
}

output:

2021/01/08 19:38:35 inserting 1000000 records (bulk size = 10000)...
2021/01/08 19:38:35 bulk #0/100...
2021/01/08 19:38:35 bulk #1/100...
[...skipped...]
2021/01/08 19:39:34 bulk #98/100...
2021/01/08 19:39:35 bulk #99/100...
creating the index...
2021/01/08 19:39:38 done in 1m3.066270399s

Now change the driver name to "sqlite3" and run again:

GOROOT=/Users/vaskir/sdk/go1.15.6 #gosetup
GOPATH=/Users/vaskir/go #gosetup
/Users/vaskir/sdk/go1.15.6/bin/go build -o /private/var/folders/zl/ysbq7y390ts6ydtdzpbpv02m0000gn/T/___go_build_project1_cgofreesqlitetest project1/cgofreesqlitetest #gosetup
/private/var/folders/zl/ysbq7y390ts6ydtdzpbpv02m0000gn/T/___go_build_project1_cgofreesqlitetest #gosetup
2021/01/08 19:41:26 inserting 1000000 records (bulk size = 10000)...
2021/01/08 19:41:26 bulk #0/100...
2021/01/08 19:41:26 bulk #1/100...
[...skipped...]
2021/01/08 19:41:29 bulk #98/100...
2021/01/08 19:41:29 bulk #99/100...
creating the index...
2021/01/08 19:41:30 done in 4.117596388s

So, sqlite3 package is about 15 times faster.

$ go version
go version go1.15.6 darwin/amd64