Reusing SQL Prepared Statements in Go

28 Apr 2019

How much time is saved by re-using prepared statements in Go?

This simple experiment will give you a rough idea.

Background knowledge: Go's sql package uses prepared statements when filling in parameters in SQL queries.

So this snippet of code:

err = db.QueryRow("select count(*) from pg_stat_activity where datname = $1", "postgres").Scan(&num)

will show the use of a prepared statement in PostgreSQL's logs if you have statement logging turned on:

LOG:  execute : select count(*) from pg_stat_activity where datname = $1
DETAIL:  parameters: $1 = '42'

Let's run the above basic query 1000 times using this program:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/lib/pq"
)

func main() {
	db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost/postgres?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	var num int
	start := time.Now()
	for i := 0; i < 1000; i++ {
		err = db.QueryRow("select count(*) from pg_stat_activity where datname = $1", "postgres").Scan(&num)
		if err != nil {
			log.Fatal(err)
		}
	}
	elapsed := time.Since(start)
	fmt.Printf("got: %d in %s\n", num, elapsed)
}

Here's what we get with 5 runs of the above program:

got: 1 in 411.181744ms
got: 1 in 421.445645ms
got: 1 in 452.958473ms
got: 1 in 419.599104ms
got: 1 in 432.694446ms

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/lib/pq"
)

func main() {
	db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost/postgres?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	stmt, err := db.Prepare("select count(*) from pg_stat_activity where datname = $1")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	var num int
	start := time.Now()
	for i := 0; i < 1000; i++ {
		err = stmt.QueryRow("postgres").Scan(&num)
	}
	elapsed := time.Since(start)
	fmt.Printf("got: %d in %s\n", num, elapsed)
}

We get query times that are about three and a half times faster:

got: 1 in 115.087555ms
got: 1 in 121.813083ms
got: 1 in 121.280645ms
got: 1 in 122.50746ms
got: 1 in 125.474026ms

There are obviously many more sophisticated ways we could look at this, but as a rough guide to see if prepared statements are worth caching, it looks like it's a good practice.