Main bulk DB Experiment Script

main.go

package main

import (
	"log"
	"time"

	"github.com/lib/pq"

	"github.com/jmoiron/sqlx"
)

var database *sqlx.DB

func initDB() {
	db, err := sqlx.Connect("postgres", "host=127.0.0.1 user=postgres password=password dbname=postgres sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	database = db
}

func main() {
	initDB()

	ids := []int{5, 8, 29, 55, 99, 64, 22, 93, 77, 39}
	benchmarkSinglePerformance(ids)
	benchmarkBatchPerformance(ids)

	ids = append(ids, []int{93, 22, 45, 1, 9, 88, 41, 32, 37, 95}...)
	benchmarkSinglePerformance(ids)
	benchmarkBatchPerformance(ids)
}

func benchmarkSinglePerformance(ids []int) {
	// try 10 times for single user
	for i := 0; i < 10; i++ {
		result := make([]string, 0)
		now := time.Now()
		for _, id := range ids {
			name, err := getSingleUser(id)
			if err != nil {
				log.Fatal(err)
			}
			result = append(result, name)
		}
		elapsed := time.Since(now)

		log.Printf(`Success single get data, data length %d, elapsed time %v`, len(result), elapsed)
	}
}

func benchmarkBatchPerformance(ids []int) {
	// try 10 times for multiple user
	for i := 0; i < 10; i++ {
		now := time.Now()
		names, err := getMultiUsers(ids)
		elapsed := time.Since(now)
		if err != nil {
			log.Fatal(err)
		}

		log.Printf(`Success bulk get data, data length %d, elapsed time %v`, len(names), elapsed)
	}
}

func getSingleUser(id int) (string, error) {
	var name string

	query := `
		SELECT name FROM users WHERE id = $1 LIMIT 1
	`

	err := database.Get(&name, query, id)
	return name, err
}

func getMultiUsers(ids []int) ([]string, error) {
	var names []string

	err := database.Select(&names, `SELECT name FROM users WHERE id = ANY($1)`, pq.Array(ids))
	return names, err
}