Go SQLite benchmark parallel

go.mod

module sqlite

go 1.19

require github.com/mattn/go-sqlite3 v1.14.16

go.sum

github.com/mattn/go-sqlite3 v1.14.16 h1:yOQRA0RpS5PFz/oikGwBEqvAWhWg5ufRz4ETLjwpU1Y=
github.com/mattn/go-sqlite3 v1.14.16/go.mod h1:2eHXhiwb8IkHr+BDWZGa96P6+rkvnG63S2DGjv9HUNg=

Makefile

.PHONY: benchmark
benchmark:
	go test -bench=.

sqlite.go

package sqlite

import (
	"database/sql"
	"sync"
)

func Setup(db *sql.DB) error {
	_, err := db.Exec(`
			create table posts (
				id integer primary key,
				title text not null,
				content text not null,
				created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ'))
			)`)
	return err
}

func WriteBlogPost(db *sql.DB, title, content string) error {
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

var m sync.Mutex

func WriteBlogPostMutexed(db *sql.DB, title, content string) error {
	m.Lock()
	defer m.Unlock()
	_, err := db.Exec(`insert into posts (title, content) values (?, ?)`, title, content)
	return err
}

sqlite_test.go

package sqlite_test

import (
	"database/sql"
	"path"
	"testing"

	_ "github.com/mattn/go-sqlite3"

	"sqlite"
)

func BenchmarkWriteBlogPost(b *testing.B) {
	b.Run("write blog post without WAL", func(b *testing.B) {
		db := setupDB(b, "?_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPost(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})

	b.Run("write blog post with WAL", func(b *testing.B) {
		db := setupDB(b, "?_journal=WAL&_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPost(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})

	b.Run("write blog post with WAL and Go mutex", func(b *testing.B) {
		db := setupDB(b, "?_journal=WAL&_timeout=5000&_fk=true")

		b.SetParallelism(64)

		b.RunParallel(func(pb *testing.PB) {
			for pb.Next() {
				if err := sqlite.WriteBlogPostMutexed(db, "Some title", "Some content"); err != nil {
					b.Fatal(err)
				}
			}
		})
	})
}

func setupDB(b *testing.B, options string) *sql.DB {
	dbPath := path.Join(b.TempDir(), "benchmark.db")
	db, err := sql.Open("sqlite3", dbPath+options)
	if err != nil {
		b.Fatal(err)
	}

	if err := sqlite.Setup(db); err != nil {
		b.Fatal(err)
	}
	return db
}

Ben Johnson suggested .. to try different drivers as well,

I tried that at; https://gist.github.com/komuw/7fe652eece1850d2148e2bf3392101b0
I specifically tried with mattn/go-sqlite3 and modernc.org/sqlite. Unfortunately modernc.org/sqlite had too many database is locked errors.
I need to try out tailscale/sqlite. Their interface is a bit different from the other drivers so I might need to re-arrange the code.

edit: I have now included tailscale/sqlite in my gist.