diff options
Diffstat (limited to 'benchmark')
| -rw-r--r-- | benchmark/go.mod | 21 | ||||
| -rw-r--r-- | benchmark/go.sum | 48 | ||||
| -rw-r--r-- | benchmark/internal/testhelpers/must/must.go | 26 | ||||
| -rw-r--r-- | benchmark/oblast/benchmark_test.go | 641 | ||||
| -rw-r--r-- | benchmark/oblast/postgres_test.go | 393 | ||||
| -rw-r--r-- | benchmark/pathrouter/pathrouter_test.go (renamed from benchmark/pathrouter_test.go) | 0 |
6 files changed, 1126 insertions, 3 deletions
diff --git a/benchmark/go.mod b/benchmark/go.mod index bc404df..d712500 100644 --- a/benchmark/go.mod +++ b/benchmark/go.mod @@ -3,6 +3,25 @@ module go.xyrillian.de/gg/benchmark go 1.26 require ( + github.com/go-gorp/gorp/v3 v3.1.0 github.com/gorilla/mux v1.8.1 - go.xyrillian.de/gg v1.13.0 + github.com/jackc/pgx/v5 v5.10.0 + github.com/lib/pq v1.12.3 + github.com/mattn/go-sqlite3 v1.14.48 + go.xyrillian.de/gg v1.15.0 + go.xyrillian.de/gg-pgx v0.1.1 + gorm.io/driver/sqlite v1.6.0 + gorm.io/gorm v1.31.2 ) + +require ( + github.com/jackc/pgpassfile v1.0.0 // indirect + github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect + github.com/jackc/puddle/v2 v2.2.2 // indirect + github.com/jinzhu/inflection v1.0.0 // indirect + github.com/jinzhu/now v1.1.5 // indirect + golang.org/x/sync v0.22.0 // indirect + golang.org/x/text v0.40.0 // indirect +) + +replace go.xyrillian.de/gg => .. diff --git a/benchmark/go.sum b/benchmark/go.sum index e5138fd..e3adf8e 100644 --- a/benchmark/go.sum +++ b/benchmark/go.sum @@ -1,4 +1,48 @@ +github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= +github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/go-gorp/gorp/v3 v3.1.0 h1:ItKF/Vbuj31dmV4jxA1qblpSwkl9g1typ24xoe70IGs= +github.com/go-gorp/gorp/v3 v3.1.0/go.mod h1:dLEjIyyRNiXvNZ8PSmzpt1GsWAUK8kjVhEpjH8TixEw= +github.com/go-sql-driver/mysql v1.6.0 h1:BCTh4TKNUYmOmMUcQ3IipzF5prigylS7XXjEkfCHuOE= +github.com/go-sql-driver/mysql v1.6.0/go.mod h1:DCzpHaOWr8IXmIStZouvnhqoel9Qv2LBy8hT2VhHyBg= github.com/gorilla/mux v1.8.1 h1:TuBL49tXwgrFYWhqrNgrUNEY92u81SPhu7sTdzQEiWY= github.com/gorilla/mux v1.8.1/go.mod h1:AKf9I4AEqPTmMytcMc0KkNouC66V3BtZ4qD5fmWSiMQ= -go.xyrillian.de/gg v1.10.1 h1:V6oSU+tl25vaRQaMy6Y3jl/0kNoY/a25x4WIk5zQFAw= -go.xyrillian.de/gg v1.10.1/go.mod h1:DoO4fQSWIrBRlNlCjVyrYM0kAEBt/Jg2GkMH+cGRZ0k= +github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= +github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= +github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo= +github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= +github.com/jackc/pgx/v5 v5.10.0 h1:VhSvgU2jSli8o3AqIEOTJr7rZwAEUVo4E4XhR94Zfr0= +github.com/jackc/pgx/v5 v5.10.0/go.mod h1:mal1tBGAFfLHvZzaYh77YS/eC6IX9OWbRV1QIIM0Jn4= +github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo= +github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= +github.com/jinzhu/inflection v1.0.0 h1:K317FqzuhWc8YvSVlFMCCUb36O/S9MCKRDI7QkRKD/E= +github.com/jinzhu/inflection v1.0.0/go.mod h1:h+uFLlag+Qp1Va5pdKtLDYj+kHp5pxUVkryuEj+Srlc= +github.com/jinzhu/now v1.1.5 h1:/o9tlHleP7gOFmsnYNz3RGnqzefHA47wQpKrrdTIwXQ= +github.com/jinzhu/now v1.1.5/go.mod h1:d3SSVoowX0Lcu0IBviAWJpolVfI5UJVZZ7cO71lE/z8= +github.com/lib/pq v1.12.3 h1:tTWxr2YLKwIvK90ZXEw8GP7UFHtcbTtty8zsI+YjrfQ= +github.com/lib/pq v1.12.3/go.mod h1:/p+8NSbOcwzAEI7wiMXFlgydTwcgTr3OSKMsD2BitpA= +github.com/mattn/go-sqlite3 v1.14.48 h1:7XHIgl0a8HwOaiK4E47ozLkST78rR9+OtNGx27D/TFs= +github.com/mattn/go-sqlite3 v1.14.48/go.mod h1:6JTjA44L93a0QCyJef5YvlPoKXntQPjzWv5gtm9sB6w= +github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= +github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= +github.com/poy/onpar v1.1.2 h1:QaNrNiZx0+Nar5dLgTVp5mXkyoVFIbepjyEoGSnhbAY= +github.com/poy/onpar v1.1.2/go.mod h1:6X8FLNoxyr9kkmnlqpK6LSoiOtrO6MICtWwEuWkLjzg= +github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= +github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= +github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= +github.com/stretchr/testify v1.11.1 h1:7s2iGBzp5EwR7/aIZr8ao5+dra3wiQyKjjFuvgVKu7U= +github.com/stretchr/testify v1.11.1/go.mod h1:wZwfW3scLgRK+23gO65QZefKpKQRnfz6sD981Nm4B6U= +go.xyrillian.de/gg-pgx v0.1.1 h1:Y5mpj0kHEUy7yimo3njjuIUV7ISei55g+RCuo9fbD6A= +go.xyrillian.de/gg-pgx v0.1.1/go.mod h1:rrprin7EhwAm4zluxlWv1e5SE0uUzmN4JuS1aY0dQcI= +golang.org/x/sync v0.22.0 h1:SZjpbeLmrCk4xhRSZFNZW5gFUeCeFgjekvI/+gfScek= +golang.org/x/sync v0.22.0/go.mod h1:9xrNwdLfx4jkKbNva9FpL6vEN7evnE43NNNJQ2LF3+0= +golang.org/x/text v0.40.0 h1:Ub2Z6/xjgF1WrYQz2nuITOEegKFtiIy+rieRJ5lHZKs= +golang.org/x/text v0.40.0/go.mod h1:hpnzDAfGV753zIKo+wk3u1bVKCGPbrnF7+7LBF/UHVY= +gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= +gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= +gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +gorm.io/driver/sqlite v1.6.0 h1:WHRRrIiulaPiPFmDcod6prc4l2VGVWHz80KspNsxSfQ= +gorm.io/driver/sqlite v1.6.0/go.mod h1:AO9V1qIQddBESngQUKWL9yoH93HIeA1X6V633rBwyT8= +gorm.io/gorm v1.31.2 h1:3o8FXNo9v9S858gil+3LlZA1LkCOzgb4g5BL64FgaCo= +gorm.io/gorm v1.31.2/go.mod h1:XyQVbO2k6YkOis7C2437jSit3SsDK72s7n7rsSHd+Gs= diff --git a/benchmark/internal/testhelpers/must/must.go b/benchmark/internal/testhelpers/must/must.go new file mode 100644 index 0000000..7a137c6 --- /dev/null +++ b/benchmark/internal/testhelpers/must/must.go @@ -0,0 +1,26 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package must + +import "testing" + +// Succeed fails the test if err is not nil. +func Succeed(t testing.TB, err error) { + t.Helper() + if err != nil { + t.Fatal(err.Error()) + } +} + +// Return wraps a function returning two output values, +// and either forwards the result value on success, or fails the test on error. +func Return[V any](value V, err error) func(testing.TB) V { + return func(t testing.TB) V { + t.Helper() + if err != nil { + t.Fatal(err.Error()) + } + return value + } +} diff --git a/benchmark/oblast/benchmark_test.go b/benchmark/oblast/benchmark_test.go new file mode 100644 index 0000000..0ea86a1 --- /dev/null +++ b/benchmark/oblast/benchmark_test.go @@ -0,0 +1,641 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package main_test + +import ( + "context" + "crypto/sha256" + "database/sql" + "fmt" + "strconv" + "testing" + "time" + + "github.com/go-gorp/gorp/v3" + _ "github.com/mattn/go-sqlite3" + "go.xyrillian.de/gg/assert" + "go.xyrillian.de/gg/benchmark/internal/testhelpers/must" + "go.xyrillian.de/gg/gsql" + "go.xyrillian.de/gg/oblast" + "gorm.io/driver/sqlite" + "gorm.io/gorm" +) + +// NOTE: In this file, we benchmark different ORMs against each other and against hand-written operations using plain database/sql. +// All benchmarks are called "BenchmarkORM...". + +// Do not use b.Context() within benchmarks, or you will merely demonstrate that using a deep stack of Context objects is expensive. +var noctx = context.Background() + +// This is not a real benchmark (obviously). +// Its purpose is to be the first line that is printed, while having one of the longest names, +// so that all other results are aligned with it and the table looks nice. +func BenchmarkORMHeadingHeadingHeadingHeadingHeadingHeadingHeadingHeading(b *testing.B) { + for b.Loop() { + time.Sleep(time.Microsecond) + } +} + +var ( + totalRecordCountForSelect = 10000 + batchSizesForSelect = []int{1, 10, 100, 1000} + batchSizesForInsertDelete = []int{1, 2, 4, 8, 16, 100} + batchSizesForUpdate = []int{1, 2, 4, 8, 16, 100} +) + +func makeSqliteTestDB(t testing.TB, recordCount int) (db *gsql.DB, dsn string) { + dsn = fmt.Sprintf("file:%s?mode=memory&cache=shared", t.Name()) + db = gsql.NewDB(must.Return(sql.Open("sqlite3", dsn))(t)) + _ = must.Return(db.Exec(`CREATE TABLE entries (id INTEGER, message TEXT, PRIMARY KEY (id AUTOINCREMENT))`))(t) + + if recordCount > 0 { + // fill in some random-looking, but deterministic data + stmt := must.Return(db.Prepare(`INSERT INTO entries (id, message) VALUES (?, ?)`))(t) + for idx := range recordCount { + buf := sha256.Sum256([]byte(strconv.Itoa(idx))) + _ = must.Return(stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:])))(t) + } + must.Succeed(t, stmt.Close()) + } + + return db, dsn +} + +type OblastEntry struct { + ID int `db:"id,auto"` + Message string `db:"message"` +} + +type GorpEntry struct { + ID int `db:"id"` + Message string `db:"message"` +} + +type GormEntry struct { + ID int `gorm:"primaryKey"` + Message string +} + +func (GormEntry) TableName() string { return "entries" } + +func BenchmarkORMSelectMany(b *testing.B) { + db, dsn := makeSqliteTestDB(b, totalRecordCountForSelect) + + // test with different sizes of resultsets (N=1 is an OLTP-like workload, + // then the larger N lean more towards the OLAP side of things) + for _, batchSize := range batchSizesForSelect { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + // prepare the functions that will be benched + store := oblast.MustNewStore[OblastEntry]( + oblast.SqliteDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + gorpDB := gorp.DbMap{Db: db.DB, Dialect: gorp.SqliteDialect{}} + gormDB := must.Return(gorm.Open(sqlite.Open(dsn), &gorm.Config{}))(b) + partialQuery := `id < ` + strconv.Itoa(batchSize) + query := `SELECT * FROM entries WHERE ` + partialQuery + precomputedQuery := store.MustPrepareSelectQueryWhere(partialQuery) + + selectWithOblast := func(b *testing.B) { + records := must.Return(store.Select(noctx, db, query).Collect())(b) + assert.Equal(b, len(records), batchSize) + } + + selectWithOblastWhere := func(b *testing.B) { + records := must.Return(precomputedQuery.Select(noctx, db).Collect())(b) + assert.Equal(b, len(records), batchSize) + } + + selectWithGorp := func(b *testing.B) { + var records []GorpEntry + _ = must.Return(gorpDB.Select(&records, query))(b) + assert.Equal(b, len(records), batchSize) + } + + selectWithGorm := func(b *testing.B) { + records := must.Return(gorm.G[GormEntry](gormDB).Where(partialQuery).Find(b.Context()))(b) + assert.Equal(b, len(records), batchSize) + } + + selectWithSqlite := func(b *testing.B) { + var count int + rows := must.Return(db.Query(query))(b) //nolint:rowserrcheck // false positive + var ( + id int64 + message string + ) + for rows.Next() { + must.Succeed(b, rows.Scan(&id, &message)) + if id != 20000 && message != "" { // always true; ensures that values are not optimized away + count++ + } + } + must.Succeed(b, rows.Close()) + assert.Equal(b, count, batchSize) + } + + // run once to prewarm caches (if any) + selectWithOblast(b) + selectWithGorp(b) + selectWithGorm(b) + if b.Failed() { + b.FailNow() + } + + // run actual benchmark + b.Run("via Gorm using Find", func(b *testing.B) { + for b.Loop() { + selectWithGorm(b) + } + }) + b.Run("via Gorp using Select", func(b *testing.B) { + for b.Loop() { + selectWithGorp(b) + } + }) + b.Run("via Oblast using Select", func(b *testing.B) { + for b.Loop() { + selectWithOblast(b) + } + }) + b.Run("via Oblast using SelectWhere", func(b *testing.B) { + for b.Loop() { + selectWithOblastWhere(b) + } + }) + b.Run("just SQLite", func(b *testing.B) { + for b.Loop() { + selectWithSqlite(b) + } + }) + }) + } +} + +func BenchmarkORMSelectManyValues(b *testing.B) { + db, _ := makeSqliteTestDB(b, totalRecordCountForSelect) + + // test with different sizes of resultsets (N=1 is an OLTP-like workload, + // then the larger N lean more towards the OLAP side of things) + for _, batchSize := range batchSizesForSelect { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + // prepare the functions that will be benched + query := `SELECT message FROM entries WHERE id < ` + strconv.Itoa(batchSize) + selectWithOblast := func(b *testing.B) { + messages := must.Return(oblast.Select[string](noctx, db, query).Collect())(b) + assert.Equal(b, len(messages), batchSize) + } + selectWithSqlite := func(b *testing.B) { + var count int + rows := must.Return(db.Query(query))(b) //nolint:rowserrcheck // false positive + var message string + for rows.Next() { + must.Succeed(b, rows.Scan(&message)) + count++ + } + must.Succeed(b, rows.Close()) + assert.Equal(b, count, batchSize) + } + + // run actual benchmark + b.Run("via Oblast", func(b *testing.B) { + for b.Loop() { + selectWithOblast(b) + } + }) + b.Run("just SQLite", func(b *testing.B) { + for b.Loop() { + selectWithSqlite(b) + } + }) + }) + } +} + +func BenchmarkORMSelectOne(b *testing.B) { + db, dsn := makeSqliteTestDB(b, totalRecordCountForSelect) + + // grab a "random" record from the DB, not just the first or the last + recordID := min(totalRecordCountForSelect*2/3, totalRecordCountForSelect) + + // prepare the functions that will be benched + store := oblast.MustNewStore[OblastEntry]( + oblast.SqliteDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + gorpDB := gorp.DbMap{Db: db.DB, Dialect: gorp.SqliteDialect{}} + gormDB := must.Return(gorm.Open(sqlite.Open(dsn), &gorm.Config{}))(b) + partialQuery := `id = ` + strconv.Itoa(recordID) + query := `SELECT * FROM entries WHERE ` + partialQuery + precomputedQuery := store.MustPrepareSelectQueryWhere(partialQuery) + + selectWithOblast := func(b *testing.B) { + r := must.Return(store.SelectOne(noctx, db, query))(b) + assert.Equal(b, r.ID, recordID) + } + + selectWithOblastWhere := func(b *testing.B) { + r := must.Return(precomputedQuery.SelectOne(noctx, db))(b) + assert.Equal(b, r.ID, recordID) + } + + selectWithGorp := func(b *testing.B) { + var r GorpEntry + must.Succeed(b, gorpDB.SelectOne(&r, query)) + assert.Equal(b, r.ID, recordID) + } + + selectWithGorm := func(b *testing.B) { + r := must.Return(gorm.G[GormEntry](gormDB).Where(partialQuery).First(b.Context()))(b) + assert.Equal(b, r.ID, recordID) + } + + selectWithSqlite := func(b *testing.B) { + var ( + id int64 + message string + ) + must.Succeed(b, db.QueryRow(query).Scan(&id, &message)) + assert.Equal(b, id, int64(recordID)) + } + + // run once to prewarm caches (if any) + selectWithOblast(b) + selectWithGorp(b) + selectWithGorm(b) + if b.Failed() { + b.FailNow() + } + + // run actual benchmark + b.Run("via Gorm using First", func(b *testing.B) { + for b.Loop() { + selectWithGorm(b) + } + }) + b.Run("via Gorp using SelectOne", func(b *testing.B) { + for b.Loop() { + selectWithGorp(b) + } + }) + b.Run("via Oblast using SelectOne", func(b *testing.B) { + for b.Loop() { + selectWithOblast(b) + } + }) + b.Run("via Oblast using SelectOneWhere", func(b *testing.B) { + for b.Loop() { + selectWithOblastWhere(b) + } + }) + b.Run("just SQLite", func(b *testing.B) { + for b.Loop() { + selectWithSqlite(b) + } + }) +} + +func BenchmarkORMSelectOneValue(b *testing.B) { + db, _ := makeSqliteTestDB(b, totalRecordCountForSelect) + + // grab a "random" record from the DB, not just the first or the last + recordID := min(totalRecordCountForSelect*2/3, totalRecordCountForSelect) + + // prepare the functions that will be benched + query := `SELECT message FROM entries WHERE id = ` + strconv.Itoa(recordID) + selectWithOblast := func(b *testing.B) { + message := must.Return(oblast.SelectOne[string](noctx, db, query))(b) + assert.Equal(b, len(message), 71) + } + selectWithSqlite := func(b *testing.B) { + var message string + must.Succeed(b, db.QueryRow(query).Scan(&message)) + assert.Equal(b, len(message), 71) + } + + // run actual benchmark + b.Run("via Oblast", func(b *testing.B) { + for b.Loop() { + selectWithOblast(b) + } + }) + b.Run("just SQLite", func(b *testing.B) { + for b.Loop() { + selectWithSqlite(b) + } + }) +} + +func BenchmarkORMInsertAndDelete(b *testing.B) { + db, dsn := makeSqliteTestDB(b, 0) + + store := oblast.MustNewStore[OblastEntry]( + oblast.SqliteDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + gorpDB := gorp.DbMap{Db: db.DB, Dialect: gorp.SqliteDialect{}} + gorpDB.AddTableWithName(GorpEntry{}, "entries").SetKeys(true, "id") + gormDB := must.Return(gorm.Open(sqlite.Open(dsn), &gorm.Config{}))(b) + + // test with different amounts of records + for _, batchSize := range batchSizesForInsertDelete { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + // prepare the functions that will be benched + insertAndDeleteWithOblast := func(b *testing.B) { + records := make([]OblastEntry, batchSize) + recordsForInsert := make([]*OblastEntry, batchSize) + for idx := range records { + records[idx] = OblastEntry{Message: "hello"} + recordsForInsert[idx] = &records[idx] + } + must.Succeed(b, store.Insert(noctx, db, recordsForInsert...)) + for _, r := range records { + if r.ID == 0 { + b.Errorf("ID was not filled!") + } + } + must.Succeed(b, store.Delete(noctx, db, records...)) + } + if batchSize == 1 { + insertAndDeleteWithOblast = func(b *testing.B) { + record := OblastEntry{Message: "hello"} + must.Succeed(b, store.Insert(noctx, db, &record)) + if record.ID == 0 { + b.Errorf("ID was not filled!") + } + must.Succeed(b, store.Delete(noctx, db, record)) + } + } + + insertAndDeleteWithGorp := func(b *testing.B) { + records := make([]any, batchSize) + for idx := range records { + records[idx] = &GorpEntry{Message: "hello"} + } + must.Succeed(b, gorpDB.Insert(records...)) + for _, r := range records { + if r.(*GorpEntry).ID == 0 { + b.Errorf("ID was not filled!") + } + } + _ = must.Return(gorpDB.Delete(records...))(b) + } + if batchSize == 1 { + insertAndDeleteWithGorp = func(b *testing.B) { + record := GorpEntry{Message: "hello"} + must.Succeed(b, gorpDB.Insert(&record)) + if record.ID == 0 { + b.Errorf("ID was not filled!") + } + _ = must.Return(gorpDB.Delete(&record))(b) + } + } + + insertAndDeleteWithGorm := func(b *testing.B) { + records := make([]GormEntry, batchSize) + for idx := range records { + records[idx] = GormEntry{Message: "hello"} + } + must.Succeed(b, gorm.G[GormEntry](gormDB).CreateInBatches(b.Context(), &records, batchSize)) + for _, r := range records { + if r.ID == 0 { + b.Errorf("ID was not filled!") + } + } + result := gormDB.Delete(&records) + assert.ErrEqual(b, result.Error, nil) + assert.Equal(b, result.RowsAffected, int64(batchSize)) + } + if batchSize == 1 { + insertAndDeleteWithGorm = func(b *testing.B) { + record := GormEntry{Message: "hello"} + must.Succeed(b, gorm.G[GormEntry](gormDB).Create(b.Context(), &record)) + result := gormDB.Delete(&record) + assert.ErrEqual(b, result.Error, nil) + assert.Equal(b, result.RowsAffected, 1) + } + } + + insertAndDeleteWithStraightExec := func(b *testing.B) { + ids := make([]int64, batchSize) + for idx := range ids { + result := must.Return(db.Exec(`INSERT INTO entries (message) VALUES (?)`, "hello"))(b) + ids[idx] = must.Return(result.LastInsertId())(b) + } + for _, id := range ids { + _ = must.Return(db.Exec(`DELETE FROM entries WHERE id = ?`, id))(b) + } + } + + insertAndDeleteWithPreparedExec := func(b *testing.B) { + ids := make([]int64, batchSize) + stmtInsert := must.Return(db.Prepare(`INSERT INTO entries (message) VALUES (?)`))(b) + defer stmtInsert.Close() + for idx := range ids { + result := must.Return(stmtInsert.Exec("hello"))(b) + ids[idx] = must.Return(result.LastInsertId())(b) + } + stmtDelete := must.Return(db.Prepare(`DELETE FROM entries WHERE id = ?`))(b) + defer stmtDelete.Close() + for _, id := range ids { + _ = must.Return(stmtDelete.Exec(id))(b) + } + } + + insertAndDeleteWithStraightQueryRow := func(b *testing.B) { + ids := make([]int64, batchSize) + for idx := range ids { + must.Succeed(b, db.QueryRow(`INSERT INTO entries (message) VALUES (?) RETURNING id`, "hello").Scan(&ids[idx])) + } + for _, id := range ids { + _ = must.Return(db.Exec(`DELETE FROM entries WHERE id = ?`, id))(b) + } + } + + insertAndDeleteWithPreparedQueryRow := func(b *testing.B) { + ids := make([]int64, batchSize) + stmtInsert := must.Return(db.Prepare(`INSERT INTO entries (message) VALUES (?) RETURNING id`))(b) + defer stmtInsert.Close() + for idx := range ids { + must.Succeed(b, stmtInsert.QueryRow("hello").Scan(&ids[idx])) + } + stmtDelete := must.Return(db.Prepare(`DELETE FROM entries WHERE id = ?`))(b) + defer stmtDelete.Close() + for _, id := range ids { + _ = must.Return(stmtDelete.Exec(id))(b) + } + } + + // run once to prewarm caches (if any) + insertAndDeleteWithOblast(b) + insertAndDeleteWithGorp(b) + insertAndDeleteWithGorm(b) + + b.Run("via Gorm", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithGorm(b) + } + }) + b.Run("via Gorp", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithGorp(b) + } + }) + b.Run("via Oblast", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithOblast(b) + } + }) + b.Run("just SQLite (straight Exec)", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithStraightExec(b) + } + }) + b.Run("just SQLite (prepared Exec)", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithPreparedExec(b) + } + }) + b.Run("just SQLite (straight QueryRow)", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithStraightQueryRow(b) + } + }) + b.Run("just SQLite (prepared QueryRow)", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithPreparedQueryRow(b) + } + }) + }) + } +} + +func BenchmarkORMUpdate(b *testing.B) { + db, dsn := makeSqliteTestDB(b, 0) + + store := oblast.MustNewStore[OblastEntry]( + oblast.SqliteDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + gorpDB := gorp.DbMap{Db: db.DB, Dialect: gorp.SqliteDialect{}} + gorpDB.AddTableWithName(GorpEntry{}, "entries").SetKeys(true, "id") + gormDB := must.Return(gorm.Open(sqlite.Open(dsn), &gorm.Config{}))(b) + + // test with different amounts of records + for _, batchSize := range batchSizesForUpdate { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + // prepare a bunch of records that we can update, in a reproducible way + _ = must.Return(db.Exec(`DELETE FROM entries`)) + recordsForOblast := make([]OblastEntry, batchSize) + recordsForOblastForInsert := make([]*OblastEntry, batchSize) + for idx := range recordsForOblast { + recordsForOblast[idx] = OblastEntry{Message: "hello"} + recordsForOblastForInsert[idx] = &recordsForOblast[idx] + } + must.Succeed(b, store.Insert(noctx, db, recordsForOblastForInsert...)) + recordsForGorp := make([]any, batchSize) + for idx, r := range recordsForOblast { + recordsForGorp[idx] = new(GorpEntry(r)) + } + recordsForGorm := make([]GormEntry, batchSize) + for idx, r := range recordsForOblast { + recordsForGorm[idx] = GormEntry(r) + } + + // prepare the functions that will be benched + updateWithOblast := func(b *testing.B, message string) { + for idx := range recordsForOblast { + recordsForOblast[idx].Message = message + } + must.Succeed(b, store.Update(noctx, db, recordsForOblast...)) + } + updateWithGorp := func(b *testing.B, message string) { + for _, r := range recordsForGorp { + r.(*GorpEntry).Message = message + } + _ = must.Return(gorpDB.Update(recordsForGorp...))(b) + } + updateWithGorm := func(b *testing.B, message string) { + for idx := range recordsForGorm { + recordsForGorm[idx].Message = message + } + result := gormDB.Save(&recordsForGorm) + assert.ErrEqual(b, result.Error, nil) + assert.Equal(b, result.RowsAffected, int64(batchSize)) + } + updateWithStraightSqlite := func(b *testing.B, message string) { + for _, r := range recordsForOblast { + _ = must.Return(db.Exec(`UPDATE entries SET message = ? WHERE id = ?`, message, r.ID))(b) + } + } + updateWithPreparedSqlite := func(b *testing.B, message string) { + stmt := must.Return(db.Prepare(`UPDATE entries SET message = ? WHERE id = ?`))(b) + for _, r := range recordsForOblast { + _ = must.Return(stmt.Exec(message, r.ID))(b) + } + must.Succeed(b, stmt.Close()) + } + checkRecordsUpdated := func(b *testing.B, message string) { + var count int64 + must.Succeed(b, db.QueryRow(`SELECT COUNT(*) FROM entries WHERE message = ?`, message).Scan(&count)) + assert.Equal(b, count, int64(batchSize)) + } + + // run once to prewarm caches (if any) + updateWithGorm(b, "warming up") + updateWithGorp(b, "warming up") + updateWithOblast(b, "warming up") + + b.Run("via Gorm", func(b *testing.B) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + updateWithGorm(b, message) + checkRecordsUpdated(b, message) + } + }) + b.Run("via Gorp", func(b *testing.B) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + updateWithGorp(b, message) + checkRecordsUpdated(b, message) + } + }) + b.Run("via Oblast", func(b *testing.B) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + updateWithOblast(b, message) + checkRecordsUpdated(b, message) + } + }) + b.Run("just SQLite (straight)", func(b *testing.B) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + updateWithStraightSqlite(b, message) + checkRecordsUpdated(b, message) + } + }) + b.Run("just SQLite (prepared)", func(b *testing.B) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + updateWithPreparedSqlite(b, message) + checkRecordsUpdated(b, message) + } + }) + }) + } +} diff --git a/benchmark/oblast/postgres_test.go b/benchmark/oblast/postgres_test.go new file mode 100644 index 0000000..5772619 --- /dev/null +++ b/benchmark/oblast/postgres_test.go @@ -0,0 +1,393 @@ +// SPDX-FileCopyrightText: 2026 Stefan Majewsky <majewsky@gmx.net> +// SPDX-License-Identifier: Apache-2.0 + +package main_test + +import ( + "cmp" + "crypto/sha256" + "database/sql" + "fmt" + "os" + "strconv" + "testing" + "time" + + "github.com/jackc/pgx/v5" + _ "github.com/lib/pq" + gg_pgx "go.xyrillian.de/gg-pgx" + "go.xyrillian.de/gg/assert" + "go.xyrillian.de/gg/benchmark/internal/testhelpers/must" + "go.xyrillian.de/gg/gsql" + "go.xyrillian.de/gg/oblast" +) + +// NOTE: In this file, we benchmark different PostgreSQL database drivers against each other with or without Oblast in between. +// All benchmarks are called "BenchmarkPostgres...". +// To run these benchmarks, you need to have provide a DSN to a PostgreSQL database in $BENCHMARK_POSTGRES_DSN. + +// This is not a real benchmark (obviously). +// Its purpose is to be the first line that is printed, while having one of the longest names, +// so that all other results are aligned with it and the table looks nice. +func BenchmarkPostgresHeadingHeadingHeadingHeadingHeadingHeadingHeadingHeading(b *testing.B) { + for b.Loop() { + time.Sleep(time.Microsecond) + } +} + +const defaultPostgresDSN = "host=localhost user=postgres dbname=oblast_benchmark sslmode=disable" + +func connectToPostgresTestDB(t testing.TB, recordCount int) *gsql.DB { + dsn := cmp.Or(os.Getenv("BENCHMARK_POSTGRES_DSN"), defaultPostgresDSN) + db := gsql.NewDB(must.Return(sql.Open("postgres", dsn))(t)) + _ = must.Return(db.Exec(`CREATE TEMPORARY TABLE entries (id BIGSERIAL, message TEXT)`))(t) + + if recordCount > 0 { + // fill in some random-looking, but deterministic data + stmt := must.Return(db.Prepare(`INSERT INTO entries (id, message) VALUES ($1, $2)`))(t) + for idx := range recordCount { + buf := sha256.Sum256([]byte(strconv.Itoa(idx))) + _ = must.Return(stmt.Exec(idx, fmt.Sprintf("sha256:%x", buf[:])))(t) + } + must.Succeed(t, stmt.Close()) + } + + return db +} + +func connectToPgxTestDB(t testing.TB, recordCount int) *pgx.Conn { + ctx := t.Context() + dsn := cmp.Or(os.Getenv("BENCHMARK_POSTGRES_DSN"), defaultPostgresDSN) + conn := must.Return(pgx.Connect(ctx, dsn))(t) + _ = must.Return(conn.Exec(ctx, `CREATE TEMPORARY TABLE entries (id BIGSERIAL, message TEXT)`))(t) + + if recordCount > 0 { + // fill in some random-looking, but deterministic data + query := `INSERT INTO entries (id, message) VALUES ($1, $2)` + stmt := must.Return(conn.Prepare(ctx, query, query))(t) + for idx := range recordCount { + buf := sha256.Sum256([]byte(strconv.Itoa(idx))) + _ = must.Return(conn.Exec(ctx, query, idx, fmt.Sprintf("sha256:%x", buf[:])))(t) + } + must.Succeed(t, conn.Deallocate(ctx, stmt.Name)) + } + + return conn +} + +func BenchmarkPostgresSelect(b *testing.B) { + pqDB := connectToPostgresTestDB(b, totalRecordCountForSelect) + pgxConn := connectToPgxTestDB(b, totalRecordCountForSelect) + pgxConnH := gg_pgx.NewConn(pgxConn) + + store := oblast.MustNewStore[OblastEntry]( + oblast.PostgresDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + + for _, batchSize := range batchSizesForSelect { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + partialQuery := `id < ` + strconv.Itoa(batchSize) + query := `SELECT * FROM entries WHERE ` + partialQuery + + b.Run("driver=pq/strategy=oblast", func(b *testing.B) { + for b.Loop() { + records := must.Return(store.Select(noctx, pqDB, query).Collect())(b) + assert.Equal(b, len(records), batchSize) + } + }) + + b.Run("driver=pgx/strategy=oblast", func(b *testing.B) { + for b.Loop() { + records := must.Return(store.Select(noctx, pgxConnH, query).Collect())(b) + assert.Equal(b, len(records), batchSize) + } + }) + + b.Run("driver=pq/strategy=straight", func(b *testing.B) { + for b.Loop() { + var records []OblastEntry + rows := must.Return(pqDB.Query(query))(b) //nolint:rowserrcheck // false positive + for rows.Next() { + var e OblastEntry + must.Succeed(b, rows.Scan(&e.ID, &e.Message)) + records = append(records, e) + } + must.Succeed(b, rows.Close()) + assert.Equal(b, len(records), batchSize) + } + }) + + b.Run("driver=pgx/strategy=straight", func(b *testing.B) { + for b.Loop() { + var records []OblastEntry + rows := must.Return(pgxConn.Query(noctx, query))(b) + for rows.Next() { + var e OblastEntry + must.Succeed(b, rows.Scan(&e.ID, &e.Message)) + records = append(records, e) + } + rows.Close() + assert.Equal(b, len(records), batchSize) + } + }) + }) + } +} + +func BenchmarkPostgresSelectOne(b *testing.B) { + pqDB := connectToPostgresTestDB(b, totalRecordCountForSelect) + pgxConn := connectToPgxTestDB(b, totalRecordCountForSelect) + pgxConnH := gg_pgx.NewConn(pgxConn) + + // grab a "random" record from the DB, not just the first or the last + recordID := min(totalRecordCountForSelect*2/3, totalRecordCountForSelect) + + store := oblast.MustNewStore[OblastEntry]( + oblast.PostgresDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + + partialQuery := `id = ` + strconv.Itoa(recordID) + query := `SELECT * FROM entries WHERE ` + partialQuery + precomputedQuery := store.MustPrepareSelectQueryWhere(partialQuery) + + b.Run("driver=pq/strategy=oblast", func(b *testing.B) { + for b.Loop() { + r := must.Return(precomputedQuery.SelectOne(noctx, pqDB))(b) + assert.Equal(b, r.ID, recordID) + } + }) + + b.Run("driver=pgx/strategy=oblast", func(b *testing.B) { + for b.Loop() { + r := must.Return(precomputedQuery.SelectOne(noctx, pgxConnH))(b) + assert.Equal(b, r.ID, recordID) + } + }) + + b.Run("driver=pq/strategy=straight", func(b *testing.B) { + for b.Loop() { + var ( + id int64 + message string + ) + must.Succeed(b, pqDB.QueryRow(query).Scan(&id, &message)) + assert.Equal(b, id, int64(recordID)) + } + }) + + b.Run("driver=pgx/strategy=straight", func(b *testing.B) { + for b.Loop() { + var ( + id int64 + message string + ) + must.Succeed(b, pgxConn.QueryRow(noctx, query).Scan(&id, &message)) + assert.Equal(b, id, int64(recordID)) + } + }) +} + +func BenchmarkPostgresInsertAndDelete(b *testing.B) { + pqDB := connectToPostgresTestDB(b, 0) + pgxConn := connectToPgxTestDB(b, 0) + pgxConnH := gg_pgx.NewConn(pgxConn) + + store := oblast.MustNewStore[OblastEntry]( + oblast.PostgresDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + + // test with different amounts of records + for _, batchSize := range batchSizesForInsertDelete { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + insertAndDeleteWithOblast := func(b *testing.B, dbh gsql.Handle) { + records := make([]OblastEntry, batchSize) + recordsForInsert := make([]*OblastEntry, batchSize) + for idx := range records { + records[idx] = OblastEntry{Message: "hello"} + recordsForInsert[idx] = &records[idx] + } + must.Succeed(b, store.Insert(noctx, dbh, recordsForInsert...)) + for _, r := range records { + if r.ID == 0 { + b.Errorf("ID was not filled!") + } + } + must.Succeed(b, store.Delete(noctx, dbh, records...)) + } + + b.Run("driver=pq/strategy=oblast", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithOblast(b, pqDB) + } + }) + + b.Run("driver=pgx/strategy=oblast", func(b *testing.B) { + for b.Loop() { + insertAndDeleteWithOblast(b, pgxConnH) + } + }) + + insertQuery := `INSERT INTO entries (message) VALUES ($1) RETURNING id` + deleteQuery := `DELETE FROM entries WHERE id = $1` + + b.Run("driver=pq/strategy=straight", func(b *testing.B) { + for b.Loop() { + ids := make([]int64, batchSize) + for idx := range ids { + must.Succeed(b, pqDB.QueryRow(insertQuery, "hello").Scan(&ids[idx])) + } + for _, id := range ids { + _ = must.Return(pqDB.Exec(deleteQuery, id))(b) + } + } + }) + + b.Run("driver=pgx/strategy=straight", func(b *testing.B) { + for b.Loop() { + ids := make([]int64, batchSize) + for idx := range ids { + must.Succeed(b, pgxConn.QueryRow(noctx, insertQuery, "hello").Scan(&ids[idx])) + } + for _, id := range ids { + _ = must.Return(pgxConn.Exec(noctx, deleteQuery, id))(b) + } + } + }) + + b.Run("driver=pq/strategy=prepared", func(b *testing.B) { + for b.Loop() { + ids := make([]int64, batchSize) + stmtInsert := must.Return(pqDB.Prepare(insertQuery))(b) + defer stmtInsert.Close() + for idx := range ids { + must.Succeed(b, stmtInsert.QueryRow("hello").Scan(&ids[idx])) + } + stmtDelete := must.Return(pqDB.Prepare(deleteQuery))(b) + defer stmtDelete.Close() + for _, id := range ids { + _ = must.Return(stmtDelete.Exec(id))(b) + } + } + }) + + b.Run("driver=pgx/strategy=prepared", func(b *testing.B) { + for b.Loop() { + stmtInsert := must.Return(pgxConn.Prepare(noctx, "my-insert", insertQuery))(b) + ids := make([]int64, batchSize) + for idx := range ids { + must.Succeed(b, pgxConn.QueryRow(noctx, stmtInsert.Name, "hello").Scan(&ids[idx])) + } + must.Succeed(b, pgxConn.Deallocate(noctx, stmtInsert.Name)) + stmtDelete := must.Return(pgxConn.Prepare(noctx, "my-delete", deleteQuery))(b) + for _, id := range ids { + _ = must.Return(pgxConn.Exec(noctx, stmtDelete.Name, id))(b) + } + must.Succeed(b, pgxConn.Deallocate(noctx, stmtDelete.Name)) + } + }) + }) + } +} + +func BenchmarkPostgresUpdate(b *testing.B) { + pqDB := connectToPostgresTestDB(b, 0) + pgxConn := connectToPgxTestDB(b, 0) + pgxConnH := gg_pgx.NewConn(pgxConn) + + store := oblast.MustNewStore[OblastEntry]( + oblast.PostgresDialect(), + oblast.TableNameIs("entries"), + oblast.PrimaryKeyIs("id"), + ) + + // test with different amounts of records + for _, batchSize := range batchSizesForInsertDelete { + b.Run("N="+strconv.Itoa(batchSize), func(b *testing.B) { + // prepare a bunch of records that we can update, in a reproducible way + _ = must.Return(pqDB.Exec(`DELETE FROM entries`)) + _ = must.Return(pgxConn.Exec(noctx, `DELETE FROM entries`)) + pqRecords := make([]OblastEntry, batchSize) + pqRecordsForInsert := make([]*OblastEntry, batchSize) + pgxRecords := make([]OblastEntry, batchSize) + pgxRecordsForInsert := make([]*OblastEntry, batchSize) + for idx := range batchSize { + pqRecords[idx] = OblastEntry{Message: "hello"} + pqRecordsForInsert[idx] = &pqRecords[idx] + pgxRecords[idx] = OblastEntry{Message: "hello"} + pgxRecordsForInsert[idx] = &pgxRecords[idx] + } + must.Succeed(b, store.Insert(noctx, pqDB, pqRecordsForInsert...)) + must.Succeed(b, store.Insert(noctx, pgxConnH, pgxRecordsForInsert...)) + + // each benchmark will, while looping, write changing values each time in the same way + loop := func(b *testing.B, action func(string)) { + idx := 0 + for b.Loop() { + idx++ + message := fmt.Sprintf("round %d", idx) + action(message) + } + } + + updateWithOblast := func(b *testing.B, dbh gsql.Handle, records []OblastEntry) func(string) { + return func(message string) { + for idx := range records { + records[idx].Message = message + } + must.Succeed(b, store.Update(noctx, dbh, records...)) + } + } + + b.Run("driver=pq/strategy=oblast", func(b *testing.B) { + loop(b, updateWithOblast(b, pqDB, pqRecords)) + }) + + b.Run("driver=pgx/strategy=oblast", func(b *testing.B) { + loop(b, updateWithOblast(b, pgxConnH, pgxRecords)) + }) + + updateQuery := `UPDATE entries SET message = $1 WHERE id = $2` + + b.Run("driver=pq/strategy=straight", func(b *testing.B) { + loop(b, func(message string) { + for _, r := range pqRecords { + _ = must.Return(pqDB.Exec(updateQuery, message, r.ID))(b) + } + }) + }) + + b.Run("driver=pgx/strategy=straight", func(b *testing.B) { + loop(b, func(message string) { + for _, r := range pgxRecords { + _ = must.Return(pgxConn.Exec(noctx, updateQuery, message, r.ID))(b) + } + }) + }) + + b.Run("driver=pq/strategy=prepared", func(b *testing.B) { + loop(b, func(message string) { + stmt := must.Return(pqDB.Prepare(updateQuery))(b) + for _, r := range pqRecords { + _ = must.Return(stmt.Exec(message, r.ID))(b) + } + }) + }) + + b.Run("driver=pgx/strategy=prepared", func(b *testing.B) { + loop(b, func(message string) { + stmt := must.Return(pgxConn.Prepare(noctx, "my-update", updateQuery))(b) + for _, r := range pgxRecords { + _ = must.Return(pgxConn.Exec(noctx, stmt.Name, message, r.ID))(b) + } + must.Succeed(b, pgxConn.Deallocate(noctx, stmt.Name)) + }) + }) + }) + } +} diff --git a/benchmark/pathrouter_test.go b/benchmark/pathrouter/pathrouter_test.go index 361cc2f..361cc2f 100644 --- a/benchmark/pathrouter_test.go +++ b/benchmark/pathrouter/pathrouter_test.go |