Dan's profile photo
GitHub icon LinkedIn icon

go / postgres

updated October 16, 2026

I use PostgreSQL with Go when I need concurrent writes and full SQL.

Setup

I use github.com/jackc/pgx/v5 as the driver. It is faster than lib/pq and maintained.

go mod init server
go get github.com/jackc/pgx/v5

pgxpool pools connections:

import (
	"context"
	"log"
	"net/http"

	"github.com/jackc/pgx/v5/pgxpool"
)

// DB wraps a PostgreSQL connection pool
type DB struct {
	*pgxpool.Pool
}

func NewDB(databaseURL string) (*DB, error) {
	cfg, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return nil, err
	}
	if cfg.ConnConfig.RuntimeParams["plan_cache_mode"] == "" {
		cfg.ConnConfig.RuntimeParams["plan_cache_mode"] = "force_custom_plan"
	}
	pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
	if err != nil {
		return nil, err
	}
	if err := pool.Ping(context.Background()); err != nil {
		pool.Close()
		return nil, err
	}
	return &DB{Pool: pool}, nil
}

type Server struct {
	db *DB
}

func (s *Server) health(w http.ResponseWriter, r *http.Request) {
	if err := s.db.Ping(r.Context()); err != nil {
		http.Error(w, "Database error", 500)
		return
	}
	w.Write([]byte("OK"))
}

func main() {
	db, err := NewDB("postgres://app:secret@localhost/app?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	server := &Server{db: db}
	http.HandleFunc("/health", server.health)
	log.Fatal(http.ListenAndServe(":8080", nil))
}

By default, pgxpool caps the pool at four connections or the CPU count, whichever is larger. Every pgx method accepts a context.Context for cancellation. Plan cache explains the plan_cache_mode line. Warm connections explains the pool floor.

Migrations

I keep plain SQL files in db/migrate/ and run them with a small Go runner. Each version is an *.up.sql file and an optional *.down.sql file. Each file manages its own BEGIN and COMMIT.

db/migrate/20260430165330_clear_bad_estimates.up.sql
db/migrate/20260430165330_clear_bad_estimates.down.sql

A migration with no .down.sql file is not reversible. The runner ignores a file that does not match <14-digit-stamp>_<name>.{up,down}.sql.

A transactional migration:

BEGIN;

DELETE FROM tam_estimates
WHERE updated_by_user_id = 1 AND tam_usd >= 200000000000;

COMMIT;

PostgreSQL cannot run CONCURRENTLY inside a transaction block, so a CREATE INDEX CONCURRENTLY migration omits BEGIN and COMMIT:

SET lock_timeout = '0';
CREATE INDEX CONCURRENTLY index_people_on_email
  ON people (email);

Runner

runMigrate lints the files, opens a dedicated connection, sets timeouts, and applies pending migrations:

func runMigrate(ctx context.Context) error {
	if err := lintBeforeRun(); err != nil {
		return err
	}
	conn, err := connectMigrate(ctx)
	if err != nil {
		return err
	}
	defer conn.Close(ctx)

	if err := setMigrateTimeouts(ctx, conn); err != nil {
		return err
	}

	files, err := loadMigrations(migrateDir)
	if err != nil {
		return err
	}
	applied, err := appliedVersions(ctx, conn)
	if err != nil {
		return err
	}

	for _, ver := range pendingVersions(files, applied) {
		sql, err := os.ReadFile(files[ver].upPath)
		if err != nil {
			return err
		}
		if err := applyMigration(ctx, conn, injectBookkeeping(string(sql), ver, false)); err != nil {
			return fmt.Errorf("migrate %s: %w", ver, err)
		}
		if err := setMigrateTimeouts(ctx, conn); err != nil {
			return err
		}
		fmt.Printf("pg migrated %s\n", ver)
	}
	return regenerateSchemaArtifacts(ctx, conn)
}

connectMigrate uses pgx.Connect, not a pool. A pool does not keep a session-level SET across queries.

The runner runs a transactional file as one batch. It splits a non-transactional file into statements, because PostgreSQL treats a batch as an implicit transaction block.

The runner resets the timeouts after each migration, because a CONCURRENTLY migration set lock_timeout = '0' on the same connection.

func setMigrateTimeouts(ctx context.Context, conn *pgx.Conn) error {
	if _, err := conn.Exec(ctx, "SET lock_timeout = '10s'"); err != nil {
		return err
	}
	_, err := conn.Exec(ctx, "SET statement_timeout = '10min'")
	return err
}

Bookkeeping

The runner records each applied version in schema_migrations. The first migration creates that table with version text PRIMARY KEY.

In a transactional file, the runner splices the INSERT before the final COMMIT;, so the migration and its record commit together. A CONCURRENTLY file has no COMMIT, so the runner appends the INSERT and runs it auto-committed. A crash between the two needs manual recovery.

The record also holds the file's checksum. The runner refuses to run when an applied file no longer matches its checksum. See postgres / safe migrations.

Linter

The linter matches unsafe DDL: non-concurrent indexes, foreign keys without NOT VALID, SET NOT NULL, and CHECK constraints without NOT VALID.

type lintRule struct {
	name              string
	pattern           *regexp.Regexp
	exclude           *regexp.Regexp
	skipIfCreateTable bool
	message           string
}

The linter strips -- comments before it matches. It also rejects CONCURRENTLY beside BEGIN or COMMIT. See postgres / safe migrations for why each pattern is unsafe.

Connection guard

The runner refuses a non-localhost host unless APP_ENV names a deployed environment:

func guardConnectionTarget(dsn, appEnv string) error {
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return err
	}
	switch cfg.Host {
	case "localhost", "127.0.0.1", "::1":
		return nil
	}
	switch appEnv {
	case "production", "staging":
		return nil
	}
	return fmt.Errorf("refusing to migrate non-localhost host %q; "+
		"set APP_ENV=production or staging", cfg.Host)
}

Schema dump

After a migrate or rollback that changes the database in development, the runner regenerates db/schema.sql with pg_dump --schema-only. Then it rebuilds the test database from that file. I check in db/schema.sql, so a reviewer sees the schema diff beside the migration. The rebuild targets a fixed port, so a stray DATABASE_URL cannot point it at the dev database (see postgres / dev test clusters).

CLI

One binary with subcommands:

go run ./cmd/db migrate              # apply pending
go run ./cmd/db rollback [steps]     # revert last N (default 1)
go run ./cmd/db genmigration <name>
go run ./cmd/db genmigration --concurrent <table> <column>

genmigration --concurrent writes a CREATE INDEX CONCURRENTLY template named index_<table>_on_<column>.

Scaling it down

The runner above is sized for a database under traffic.

A CI coordinator I run embeds the migrations with //go:embed migrate/*.sql, so a schema change ships in the binary. The deploy tool installs the binary, stops the service, migrates, and starts it again.

I left out down files, the linter, and the schema dump. The tables are small, and that schema.sql is hand-written.

I kept the filename convention, schema_migrations, and the spliced INSERT, so one design covers both codebases.

Inserts with RETURNING

RETURNING returns generated values:

func createUser(ctx context.Context, db *DB, email string) (int64, error) {
	var id int64
	err := db.QueryRow(ctx,
		"INSERT INTO users (email) VALUES ($1) RETURNING id",
		email,
	).Scan(&id)
	return id, err
}

Batches

A loop that runs one query for each row pays one round trip for each row. I pass the whole set as one array parameter, and one statement runs the loop. A read takes the ids in = ANY ($1). A write reads them out of unnest:

INSERT INTO list_items (
  list_id,
  person_id
)
SELECT
  $1,
  person_id
FROM
  unnest($2::bigint[]) AS input(person_id)
ON CONFLICT (
  list_id,
  person_id
)
DO NOTHING;

pgx sends a Go []int64 as a bigint[]. unnest with two arrays of the same length gives one row for each index, so a write can carry more than one column:

unnest($1::text[], $2::int[]) AS input(name, stars)

WITH ORDINALITY adds the index as a column, for a write that must keep the input order.

Query organization

Each package keeps its SQL files beside the Go code and embeds them with //go:embed:

people/
  queries/
    fetch_person.sql
    upsert_person.sql
  sql.go
  person.go

One query per file. A loader reads them at startup and panics on a missing file:

package people

import "embed"

//go:embed queries/*.sql
var fs embed.FS

func sqlFile(name string) string {
	b, err := fs.ReadFile("queries/" + name + ".sql")
	if err != nil {
		panic(err)
	}
	return string(b)
}

var (
	qFetchPerson  = sqlFile("fetch_person")
	qUpsertPerson = sqlFile("upsert_person")
)

The pgx row helpers map columns to struct fields with db:"column_name" tags:

rows, err := db.Query(ctx, qFetchPerson, email)
if err != nil {
	return nil, err
}
return pgx.CollectRows(rows, pgx.RowToStructByName[PersonRow])

I use no code generation. The query and the call site share a package, so a rename touches the .sql file and the compile errors it causes.

I name query files by verb: fetch_<thing>.sql, upsert_<thing>.sql. I cast an ambiguous parameter ($1::bigint) and format the files with cmd / pgfmt:

go tool pgfmt -w <pkg>/queries/*.sql

Nothing checks the Go types against the schema as sqlc does. A scan of a nullable column into a non-nullable type panics on the first NULL row. nullscan checks that in CI.

Rows as data

My Ruby code had the same shape without structs. DB#exec took SQL text and parameters and returned an array of hashes, one per row, keyed by column name:

rows = db.exec(<<~SQL, [db.fuzzy_like("acme")])
  SELECT id, name, status
  FROM companies
  WHERE name ILIKE $1
  ORDER BY score DESC
  LIMIT 50
SQL

There was no model class, no query builder, and no ORM. Rubyists usually reach for ActiveRecord. I treated a query as SQL text and a result as plain data. pp rows in a script showed exactly what the database returned, and a handler passed the same array to a template or to JSON.

It cost me one gotcha. numeric columns came back as strings, so row["price"] * quantity was String * Integer until I added a decoder. A mistyped key such as row["stauts"] was nil, with no error.

Go keeps the idea of SQL as text and rows as data. A struct adds what the hash lacked: a mistyped field is a compile error, and a numeric has a type from the start.

nullscan

nullscan is a static check. It flags a pgx scan where the SQL column can be NULL and the Go destination (int64, string, time.Time) cannot. The schema and the SQL text decide the answer. If the schema allows NULL, and the query neither uses COALESCE nor joins to a NOT NULL column, the destination must accept NULL.

It loads db/schema.sql for nullability. Then it finds every pgx.RowToStructByName[T] and pgx.RowToStructByPos[T] call, and resolves the query through the q<Name> = sqlFile("<file>") pattern. Fields map to columns by db:"col" tag, or by index for a positional scan.

It handles bare table.col, coalesce(...), count(...), literals, and LEFT JOIN propagation. It skips subqueries in the projection, UNION, CTEs, and other expressions. There is no per-field ignore. If it flags a column, COALESCE it or project from a NOT NULL column. If the analyzer is wrong, fix the analyzer.

sockeye runs it as one Checkfile line:

nullscan: go run ./cmd/nullscan

Each finding is one line:

<query>.sql: field PersonRow.Email (string) scans nullable column "email" from people/queries/fetch_person.sql

I paste a failed run into an agent prompt. It checks one property, the one that caused incidents. sqlc also generates typed methods and validates whole result shapes.

Plan cache

A query file has no string substitution, so a parameter supplies each choice: a sort key in CASE WHEN $3 = 'name' THEN name END, an optional filter behind cardinality($2::text[]) = 0 OR ..., an id array in = ANY ($1), a page size in LIMIT $2.

pgx prepares each query once per connection. Postgres plans the first five executions of a prepared statement with the real values. Then it switches to one generic plan when the generic estimate is close to the average of the five. A custom plan folds $3 = 'name' to a constant and drops the other branches. A generic plan keeps every branch and estimates each parameter with a default. Five broad calls can lock in a plan that a narrow call then runs for the life of the connection.

So NewDB sets plan_cache_mode = force_custom_plan on the pool. Postgres plans every execution with its values. The cost is one plan step per query, under a millisecond on my largest list queries, and never a worse plan. EXPLAIN with literal values in psql shows the plan the app runs. A plan_cache_mode in the URL wins, so one service can go back to auto without a code change.

Warm connections

pgxpool opens a connection on demand, and MinConns defaults to 0. So a request after an idle period pays a dial, a TLS handshake, and SCRAM auth to the database host: about 280 ms to a hosted database. Then it pays a Parse and Describe round trip to prepare each statement on the new connection. One POST handler that ran twenty fast statements in sequence took 1.4 seconds after an idle period.

A page that runs its reads together needs one connection for each read in flight. A helper runs them with errgroup and a fixed limit:

// ReadFanout is the most reads one ParallelReads call runs at once,
// and the count of connections every pool keeps open.
const ReadFanout = 8

func (db *DB) ParallelReads(ctx context.Context, reads ...func(context.Context) error) error {
	g, gctx := errgroup.WithContext(ctx)
	g.SetLimit(ReadFanout)
	for _, read := range reads {
		g.Go(func() error { return read(gctx) })
	}
	return g.Wait()
}

Before, each caller chose its own limit, from 2 to 8 or none, and the pool kept 4 connections open. A page that ran 8 reads at once opened 4 new connections on a quiet server. Its SQL took about 50 ms, and it waited over a second for connections. Now one constant sets both numbers, and NewDB keeps that many connections open:

if !hasPoolParam(databaseURL, "pool_min_conns") {
	cfg.MinConns = min(int32(ReadFanout), cfg.MaxConns)
}

A pool_min_conns in the URL wins. When a page is slow with 8 reads at once, I make it run fewer queries, not more at once.

In tests, the DB wraps one transaction. A pgx.Tx is not safe for concurrent use, so ParallelReads runs the reads in sequence there.

Acquire time

A slow request alert that says only the total SQL time cannot explain a request whose queries are fast. Each request context carries a QueryStats. The pgx tracer adds each query's time to it. pgxpool reads the same tracer as its AcquireTracer, so one type also adds the time each query waited for a connection:

type queryTracer struct{}

var (
	_ pgx.QueryTracer       = queryTracer{}
	_ pgxpool.AcquireTracer = queryTracer{}
)

func (queryTracer) TraceAcquireStart(ctx context.Context, _ *pgxpool.Pool, _ pgxpool.TraceAcquireStartData) context.Context {
	stats, ok := ctx.Value(queryStatsKey{}).(*QueryStats)
	if !ok {
		return ctx
	}
	return context.WithValue(ctx, acquireStartKey{}, queryStart{stats: stats, at: time.Now()})
}

func (queryTracer) TraceAcquireEnd(ctx context.Context, _ *pgxpool.Pool, _ pgxpool.TraceAcquireEndData) {
	start, ok := ctx.Value(acquireStartKey{}).(queryStart)
	if !ok {
		return
	}
	start.stats.acquireNanos.Add(int64(time.Since(start.at)))
}

NewDB sets cfg.ConnConfig.Tracer = queryTracer{}. The request logger attaches db_ms, db_queries, and acquire_ms to a slow request alert. A db_ms close to the request time means slow SQL. A small db_ms means slow Go code or a slow call to another host. A large acquire_ms means the request waited on the pool, for a free connection or for a new one. Reads that run together add their times, so db_ms can be larger than the request time.

Error handling

pgconn.PgError includes the SQLSTATE code:

import (
	"errors"

	"github.com/jackc/pgx/v5/pgconn"
)

func isUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23505"
}

func isForeignKeyViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23503"
}

Other common error codes:

Testing

A test connects to a database on a separate test cluster, inserts rows through fixture helpers, and asserts with is. See go / test for the helpers and postgres / dev test clusters for the cluster.

← All articles