main_test.go

// To run, create a empty main.go, then run: go test -bench=.
//
// Copyright (C) 2023 by Dave Pifke.
//
// Permission to use, copy, modify, and/or distribute this software for any
// purpose with or without fee is hereby granted.
//
// THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
// WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
// SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
// WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
// ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR
// IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

package main

import (
        "database/sql"
        "fmt"
        "math/rand"
        "os"
        "strconv"
        "testing"
        "time"

        _ "github.com/lib/pq"
)

func createTables(db *sql.DB, tables int) (err error) {
        for i := 0; i < tables; i++ {
                if _, err = db.Exec(fmt.Sprintf(
                        "CREATE TABLE table%d (col1 INT PRIMARY KEY, col2 TEXT, col3 INT)",
                        i,
                )); err != nil {
                        break
                }
        }
        return
}

func populateTables(db *sql.DB, tables, rowsPerTable int) (err error) {
        for i := 0; i < tables; i++ {
                for j := 0; j < rowsPerTable; j++ {
                        if _, err = db.Exec(
                                fmt.Sprintf("INSERT INTO table%d VALUES ($1, $2, $3)", i),
                                rand.Int31(),
                                strconv.Itoa(int(rand.Int31())),
                                rand.Int31(),
                        ); err != nil {
                                return
                        }
                }
        }
        return
}

func truncateTables(db *sql.DB, tables int) (err error) {
        for i := 0; i < tables; i++ {
                if _, err = db.Exec(
                        fmt.Sprintf("TRUNCATE TABLE table%d", i),
                ); err != nil {
                        break
                }
        }
        return
}

func deleteFromTables(db *sql.DB, tables int) (err error) {
        for i := 0; i < tables; i++ {
                if _, err = db.Exec(
                        fmt.Sprintf("DELETE FROM table%d", i),
                ); err != nil {
                        break
                }
        }
        return
}

const (
        tables       = 1500
        rowsPerTable = 10
        dbhost       = "/var/run/postgresql"
)

func BenchmarkTemplate(b *testing.B) {
        var db *sql.DB
        var err error
        if db, err = sql.Open(
                "postgres",
                "postgres:///postgres?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        templatedb := fmt.Sprintf("template_%s%d", b.Name(), os.Getpid())
        if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + templatedb + `"`); err != nil {
                b.Fatal(err)
        }
        if _, err = db.Exec(`CREATE DATABASE "` + templatedb + `"`); err != nil {
                b.Fatal(err)
        }
        if err = db.Close(); err != nil {
                b.Fatal(err)
        }
        if db, err = sql.Open(
                "postgres",
                "postgres:///"+templatedb+"?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        if err = createTables(db, tables); err != nil {
                b.Fatal(err)
        }
        if err = db.Close(); err != nil {
                b.Fatal(err)
        }

        var createElapsed, reconnectElapsed, insertElapsed time.Duration
        b.ResetTimer()
        for i := 0; i < b.N; i++ {
                start := time.Now()
                if db, err = sql.Open(
                        "postgres",
                        "postgres:///postgres?host="+dbhost,
                ); err != nil {
                        b.Fatal(err)
                }
                testdb := fmt.Sprintf("%s%d_%d", b.Name(), os.Getpid(), i)
                if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
                        b.Fatal(err)
                }
                if _, err = db.Exec(
                        `CREATE DATABASE "` + testdb + `" WITH TEMPLATE "` + templatedb + `"`,
                ); err != nil {
                        b.Fatal(err)
                }
                if err = db.Close(); err != nil {
                        b.Fatal(err)
                }
                createElapsed += time.Since(start)
                start = time.Now()
                if db, err = sql.Open(
                        "postgres", "postgres:///"+testdb+"?host="+dbhost,
                ); err != nil {
                        b.Fatal(err)
                }
                reconnectElapsed += time.Since(start)
                start = time.Now()
                if err = populateTables(db, tables, rowsPerTable); err != nil {
                        b.Fatal(err)
                }
                if err = db.Close(); err != nil {
                        b.Fatal(err)
                }
                insertElapsed += time.Since(start)
        }
        b.ReportMetric(createElapsed.Seconds()/float64(b.N), "create-sec/op")
        b.ReportMetric(reconnectElapsed.Seconds()/float64(b.N), "reconnect-sec/op")
        b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
}

func BenchmarkTruncate(b *testing.B) {
        var db *sql.DB
        var err error
        if db, err = sql.Open(
                "postgres", "postgres:///postgres?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        testdb := fmt.Sprintf("%s%d", b.Name(), os.Getpid())
        if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
                b.Fatal(err)
        }
        if _, err = db.Exec(`CREATE DATABASE "` + testdb + `"`); err != nil {
                b.Fatal(err)
        }
        if err = db.Close(); err != nil {
                b.Fatal(err)
        }
        if db, err = sql.Open(
                "postgres",
                "postgres:///"+testdb+"?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        if err = createTables(db, tables); err != nil {
                b.Fatal(err)
        }

        var insertElapsed, truncElapsed time.Duration
        b.ResetTimer()
        for i := 0; i < b.N; i++ {
                start := time.Now()
                if err = populateTables(db, tables, rowsPerTable); err != nil {
                        b.Fatal(err)
                }
                insertElapsed += time.Since(start)
                start = time.Now()
                if err = truncateTables(db, tables); err != nil {
                        b.Fatal(err)
                }
                truncElapsed += time.Since(start)
        }

        if err = db.Close(); err != nil {
                b.Fatal(err)
        }
        b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
        b.ReportMetric(truncElapsed.Seconds()/float64(b.N), "truncate-sec/op")
}

func BenchmarkDelete(b *testing.B) {
        var db *sql.DB
        var err error
        if db, err = sql.Open(
                "postgres",
                "postgres:///postgres?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        testdb := fmt.Sprintf("%s%d", b.Name(), os.Getpid())
        if _, err = db.Exec(`DROP DATABASE IF EXISTS "` + testdb + `"`); err != nil {
                b.Fatal(err)
        }
        if _, err = db.Exec(`CREATE DATABASE "` + testdb + `"`); err != nil {
                b.Fatal(err)
        }
        if err = db.Close(); err != nil {
                b.Fatal(err)
        }
        if db, err = sql.Open(
                "postgres",
                "postgres:///"+testdb+"?host="+dbhost,
        ); err != nil {
                b.Fatal(err)
        }
        if err = createTables(db, tables); err != nil {
                b.Fatal(err)
        }

        var insertElapsed, deleteElapsed time.Duration
        b.ResetTimer()
        for i := 0; i < b.N; i++ {
                start := time.Now()
                if err = populateTables(db, tables, rowsPerTable); err != nil {
                        b.Fatal(err)
                }
                insertElapsed += time.Since(start)
                start = time.Now()
                if err = deleteFromTables(db, tables); err != nil {
                        b.Fatal(err)
                }
                deleteElapsed += time.Since(start)
        }

        if err = db.Close(); err != nil {
                b.Fatal(err)
        }
        b.ReportMetric(insertElapsed.Seconds()/float64(b.N), "insert-sec/op")
        b.ReportMetric(deleteElapsed.Seconds()/float64(b.N), "delete-sec/op")
}

goos: linux
goarch: amd64
pkg: pifke.org/pgbench
cpu: Intel(R) Core(TM) i7-7700 CPU @ 3.60GHz
BenchmarkTemplate-8 1 31195306814 ns/op 3.651 create-sec/op 27.54 insert-sec/op 0.0000176 reconnect-sec/op
BenchmarkTruncate-8 1 49918523039 ns/op 32.13 insert-sec/op 17.79 truncate-sec/op
BenchmarkDelete-8 1 32439070678 ns/op 2.779 delete-sec/op 29.66 insert-sec/op
PASS
ok pifke.org/pgbench 172.183s