Go and PostgreSQL with pgx, sqlc and goose
Reviewed goose migrations, sqlc queries for pgx/v5, one pgxpool per process, a transaction helper that rolls back on error, idempotent inserts with ON CONFLICT and PgError mapping.
Phase P4 replaces InferenceFlow’s in-memory store with PostgreSQL while every HTTP and domain test stays green. The Roadmap’s stack is SQL-first: migrations written by hand and applied by goose, queries written in SQL and turned into typed Go by sqlc, and pgx/v5 as the driver. Nothing generates SQL I have not read, which matters because the concurrency guarantees live in that SQL.
Versions I tested against: pgx v5.13.0, sqlc v1.31.1, goose v3.30.1 and the postgres:18 image.
Migrations with goose
A migration is a plain SQL file with annotated up and down sections:
-- +goose Up
CREATE TABLE jobs (
id uuid PRIMARY KEY,
tenant_id text NOT NULL,
idempotency_key text NOT NULL,
request_hash text NOT NULL,
status text NOT NULL CHECK (status IN
('received', 'queued', 'running', 'succeeded', 'failed', 'cancelled')),
rule_set_version text NOT NULL,
document_ref text NOT NULL,
attempt_count integer NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT jobs_tenant_key_uq UNIQUE (tenant_id, idempotency_key)
);
-- +goose Down
DROP TABLE jobs;
This follows the Roadmap’s illustrative schema, plus a named constraint so errors can be mapped precisely. With goose pinned as a tool in go.mod, the CLI reads its settings from the environment:
export GOOSE_DRIVER=postgres GOOSE_DBSTRING="$DATABASE_URL" GOOSE_MIGRATION_DIR=db/migrations
go tool goose status
go tool goose up
go tool goose -s create add_results sql # -s: sequential 00004_ rather than a timestamp
The README is explicit that migrations are a deployment step, not something every replica races to run at start-up. In tests I apply them from Go with goose’s provider API over a database/sql handle borrowed from the pool:
db := stdlib.OpenDBFromPool(pool)
defer db.Close()
p, err := goose.NewProvider(goose.DialectPostgres, db, os.DirFS("db/migrations"))
if err != nil {
return err
}
_, err = p.Up(ctx)
Typed queries with sqlc
sqlc reads the migrations as the schema and annotated queries as input:
version: "2"
sql:
- engine: postgresql
schema: db/migrations
queries: db/queries
gen:
go:
package: sqlcdb
out: internal/postgres/sqlcdb
sql_package: pgx/v5
overrides:
- db_type: uuid
go_type:
import: uuid
type: UUID
- db_type: timestamptz
go_type:
import: time
type: Time
The overrides map uuid to the standard library’s uuid.UUID (new in Go 1.27) and timestamptz to time.Time instead of pgtype wrappers; both scanned correctly with pgx in my tests. Queries use named parameters so generated structs have readable fields:
-- name: InsertJob :one
INSERT INTO jobs (id, tenant_id, idempotency_key, request_hash, status, rule_set_version, document_ref)
VALUES (@id, @tenant_id, @idempotency_key, @request_hash, @status, @rule_set_version, @document_ref)
ON CONFLICT (tenant_id, idempotency_key) DO NOTHING
RETURNING id, status, updated_at;
-- name: GetJobByKey :one
SELECT id, request_hash, status, rule_set_version, document_ref, updated_at
FROM jobs
WHERE tenant_id = @tenant_id AND idempotency_key = @idempotency_key;
sqlc generate writes InsertJob(ctx, InsertJobParams) (InsertJobRow, error) and friends, plus a DBTX interface satisfied by both the pool and a transaction. sqlc vet and sqlc diff belong in CI so generated code never drifts from the SQL. sqlc does not choose transaction boundaries or make a slow query fast; that is still my job.
One pool per process
pool, err := pgxpool.New(ctx, cfg.DatabaseURL) // pool_max_conns etc. can go in the URL
if err != nil {
return err
}
defer pool.Close()
The pool is built once in main and passed to adapters. Its size is a budget shared with worker concurrency: if 32 workers each hold a connection while 8 exist, 24 wait in Acquire, and that wait counts against their context deadlines. Pool contention is one of the metrics the Roadmap asks for later.
A transaction helper
func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(q *sqlcdb.Queries) error) error {
tx, err := pool.Begin(ctx)
if err != nil {
return fmt.Errorf("begin: %w", err)
}
defer func() { _ = tx.Rollback(ctx) }() // no-op after a successful Commit
if err := fn(sqlcdb.New(tx)); err != nil {
return err
}
if err := tx.Commit(ctx); err != nil {
return fmt.Errorf("commit: %w", err)
}
return nil
}
The deferred rollback covers early returns and panics. pgx also ships pgx.BeginFunc(ctx, pool, func(pgx.Tx) error) with the same semantics, and sqlc generates q.WithTx(tx) for mixing generated and hand-written statements. Transactions stay short: never hold one open across the inference call.
Idempotent create-or-get
The README’s invariant is that (tenant_id, idempotency_key) identifies one accepted submission, equivalent replays reuse it and different content conflicts. A SELECT followed by an INSERT races: two requests both see nothing and both insert. The unique constraint plus ON CONFLICT DO NOTHING makes the insert itself the arbiter:
err = WithTx(ctx, s.pool, func(q *sqlcdb.Queries) error {
row, err := q.InsertJob(ctx, params)
switch {
case err == nil: // we created it: audit and outbox rows join the same transaction
created = true
if err := q.InsertAuditEvent(ctx, auditParams(row)); err != nil {
return err
}
return q.InsertOutboxEvent(ctx, executeRequested(tenantID, id))
case errors.Is(err, pgx.ErrNoRows): // conflict: the key already exists
existing, err := q.GetJobByKey(ctx, sqlcdb.GetJobByKeyParams{TenantID: tenantID, IdempotencyKey: key})
if err != nil {
return err
}
if existing.RequestHash != hash {
return job.ErrIdempotencyConflict
}
out, err = restore(existing)
return err
default:
return err
}
})
On conflict, RETURNING yields no row, so a :one query returns pgx.ErrNoRows. Under the default READ COMMITTED level, ON CONFLICT waits for a concurrent inserter to commit, so the follow-up SELECT (a new statement with a new snapshot) sees its row. My integration test fires 20 concurrent submissions with the same key: exactly one reports created, all return the same job ID, and one outbox row exists.
Mapping driver errors
Callers above the adapter should see domain errors, not pgx types:
func mapErr(err error) error {
if errors.Is(err, pgx.ErrNoRows) {
return job.ErrNotFound
}
if pgErr, ok := errors.AsType[*pgconn.PgError](err); ok {
switch pgErr.Code {
case "23505": // unique_violation
if pgErr.ConstraintName == "jobs_tenant_key_uq" {
return job.ErrIdempotencyConflict
}
case "40001", "40P01": // serialization_failure, deadlock_detected
return fmt.Errorf("%w: %w", ErrRetryableTx, err)
}
}
return err
}
github.com/jackc/pgerrcode provides named constants if the literals feel too magic. Matching on ConstraintName keeps one unique violation from being mistaken for another.
Proving it
Two integration tests carry the P4 exit gate: the concurrent duplicate submission above, and the Roadmap’s rollback exercise, where fn inserts a job and then returns an injected error before the audit insert, and the test asserts the jobs table is still empty.
Gotchas
- Catching 23505 inside a transaction does not save it. After any error, PostgreSQL aborts the transaction and every later statement fails with 25P02. That is why the conflict is handled with
ON CONFLICT, not by catching the error and carrying on. DO NOTHING ... RETURNINGreturns nothing on conflict. Treatpgx.ErrNoRowsas “exists”, not as a failure.- Nullable columns need nullable Go types. sqlc generates
pgtype.Textand friends (or pointers withemit_pointers_for_null_types), and a plainstringscan of NULL fails. - Rows hold a connection until closed.
pgx.CollectRowsand sqlc’s generated code close them; hand-written loops mustdefer rows.Close()and checkrows.Err(). - Functions and
DOblocks with semicolons need-- +goose StatementBeginand-- +goose StatementEndaround them.
Check yourself
- Why is
SELECTthenINSERTnot a safe idempotency check, even inside a transaction? - What does
InsertJobreturn when the key already exists, and how does the code tell replay from conflict? - Why is the rollback deferred unconditionally, and why is that safe after
Commit? - How do pool size and worker concurrency interact, and where does the waiting show up?
- Why should migrations run as a separate deployment step rather than at every replica’s start-up?