go / postgres

updated October 09, 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. cmd / nullscan checks that in CI.

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:

• 23502: not_null_violation
• 23514: check_violation
• 40001: serialization_failure
• 40P01: deadlock_detected
• 55P03: lock_not_available

Testing

Postgres has no :memory: database, unlike SQLite. 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.

Postgres or SQLite

I choose Postgres for multiple app instances, concurrent writes, replication, or its richer SQL (CTEs, window functions, JSONB, full-text search). I choose SQLite for a single binary with low write concurrency.

← All articles