A database failure rarely begins with a dramatic outage. More often, latency rises, requests retain connections longer, the pool saturates, and retries multiply the load. A transaction that looked perfectly correct in development becomes part of a production feedback loop.

This tutorial builds a small Go transfer service that moves integer currency units between accounts. It uses PostgreSQL transactions, deterministic row locking, idempotency keys, a bounded connection pool, separate connection and query deadlines, and release-managed migrations. The result is deliberately compact, but its reliability boundaries are real.

Architecture and reliability contract

The HTTP service listens on 127.0.0.1:8080 and connects to PostgreSQL on 127.0.0.1:5433. A transfer debits one account, credits another, and records its idempotency key in one transaction.

The important guarantees are:

• Balances and transfer status commit atomically.
• Concurrent transfers lock accounts in ascending ID order, reducing deadlock risk.
• A repeated idempotency key returns success only when its payload matches the original request.
• The pool has a hard size limit; acquiring and using a connection is bounded by the request context.
• Connection establishment, SQL execution, lock acquisition, HTTP reads, and shutdown have distinct budgets.
• Migrations run as a release step, never opportunistically from every application instance.

This does not make an external payment or notification exactly once. If a later worker sends side effects, it must use an outbox or another idempotent effect boundary and accept at-least-once delivery.

Prerequisites and project layout

Use Go 1.22 or newer, Docker or an equivalent PostgreSQL installation, and the PostgreSQL client tools. The only application dependency is github.com/jackc/pgx/v5 from the compatible v5.7.x line. Migration commands use github.com/golang-migrate/migrate/v4 version v4.17.1.

ledger/
├── go.mod
├── main.go
└── migrations/
    └── 000001_init.up.sql

Create a development database. The published port is restricted to loopback; dev-only is intentionally not a production credential.

docker run --name ledger-db \
  -e POSTGRES_PASSWORD=dev-only \
  -e POSTGRES_DB=ledger \
  -p 127.0.0.1:5433:5432 \
  -d postgres:16

mkdir -p ledger/migrations
cd ledger

go mod init example.com/ledger
go get github.com/jackc/pgx/[email protected]
go install -tags postgres github.com/golang-migrate/migrate/v4/cmd/[email protected]

Create a constraint-driven schema

Store money as integer minor units, not floating-point values. Database constraints remain valuable even when Go validates the same conditions: they protect the invariant from maintenance scripts, future services, and programming mistakes.

-- migrations/000001_init.up.sql
BEGIN;

CREATE TABLE accounts (
    id         bigint PRIMARY KEY,
    balance    bigint NOT NULL CHECK (balance >= 0),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE transfer_requests (
    idempotency_key text PRIMARY KEY,
    from_id         bigint NOT NULL REFERENCES accounts(id),
    to_id           bigint NOT NULL REFERENCES accounts(id),
    amount           bigint NOT NULL CHECK (amount > 0),
    status           text NOT NULL CHECK (status IN ('pending', 'completed')),
    created_at       timestamptz NOT NULL DEFAULT now(),
    completed_at     timestamptz
);

COMMIT;

Install migrations with explicit operational limits. The connection timeout covers dialing PostgreSQL; it does not limit migration statements. PostgreSQL’s lock_timeout and statement_timeout provide those separate bounds.

export DATABASE_URL='postgres://postgres:[email protected]:5433/ledger?sslmode=disable'

PGCONNECT_TIMEOUT=3 \
PGOPTIONS='-c lock_timeout=2s -c statement_timeout=15min' \
migrate -path migrations -database "$DATABASE_URL" up

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
INSERT INTO accounts (id, balance)
VALUES (1, 10000), (2, 10000);
SQL

Implement the transaction and its budgets

The application’s two-second context bounds pool waiting and SQL execution. Inside PostgreSQL, the statement timeout is slightly shorter, while lock acquisition receives only 500 milliseconds. This ordering leaves time to translate the failure and return a response.

The pool limit is a backpressure control, not a performance target. Set it below the database’s connection capacity after reserving room for migrations, administration, replicas, and other services.

// main.go
package main

import (
	"context"
	"encoding/json"
	"errors"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

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

var (
	errFunds = errors.New("insufficient funds")
	errKey   = errors.New("idempotency key reused with different payload")
	errAcct  = errors.New("account not found")
)

type request struct {
	FromID int64 `json:"from_id"`
	ToID   int64 `json:"to_id"`
	Amount int64 `json:"amount"`
}

type app struct{ db *pgxpool.Pool }

func (a *app) transfer(ctx context.Context, key string, r request) error {
	tx, err := a.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.ReadCommitted})
	if err != nil {
		return err
	}
	defer tx.Rollback(context.Background())

	if _, err = tx.Exec(ctx, "SET LOCAL statement_timeout = '1500ms'"); err != nil {
		return err
	}
	if _, err = tx.Exec(ctx, "SET LOCAL lock_timeout = '500ms'"); err != nil {
		return err
	}

	tag, err := tx.Exec(ctx, `
		INSERT INTO transfer_requests
		    (idempotency_key, from_id, to_id, amount, status)
		VALUES ($1, $2, $3, $4, 'pending')
		ON CONFLICT DO NOTHING`,
		key, r.FromID, r.ToID, r.Amount)
	if err != nil {
		return err
	}

	if tag.RowsAffected() == 0 {
		var from, to, amount int64
		var status string
		err = tx.QueryRow(ctx, `
			SELECT from_id, to_id, amount, status
			FROM transfer_requests
			WHERE idempotency_key = $1`, key).
			Scan(&from, &to, &amount, &status)
		if err != nil {
			return err
		}
		if from != r.FromID || to != r.ToID || amount != r.Amount {
			return errKey
		}
		if status != "completed" {
			return errors.New("transfer has unexpected state")
		}
		return tx.Commit(ctx)
	}

	rows, err := tx.Query(ctx, `
		SELECT id FROM accounts
		WHERE id IN ($1, $2)
		ORDER BY id
		FOR UPDATE`, r.FromID, r.ToID)
	if err != nil {
		return err
	}
	count := 0
	for rows.Next() {
		count++
	}
	rows.Close()
	if err = rows.Err(); err != nil {
		return err
	}
	if count != 2 {
		return errAcct
	}

	tag, err = tx.Exec(ctx, `
		UPDATE accounts
		SET balance = balance - $1, updated_at = now()
		WHERE id = $2 AND balance >= $1`, r.Amount, r.FromID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() != 1 {
		return errFunds
	}

	if _, err = tx.Exec(ctx, `
		UPDATE accounts
		SET balance = balance + $1, updated_at = now()
		WHERE id = $2`, r.Amount, r.ToID); err != nil {
		return err
	}

	if _, err = tx.Exec(ctx, `
		UPDATE transfer_requests
		SET status = 'completed', completed_at = now()
		WHERE idempotency_key = $1`, key); err != nil {
		return err
	}

	return tx.Commit(ctx)
}

func (a *app) handleTransfer(w http.ResponseWriter, req *http.Request) {
	req.Body = http.MaxBytesReader(w, req.Body, 4096)
	var in request
	dec := json.NewDecoder(req.Body)
	dec.DisallowUnknownFields()
	if dec.Decode(&in) != nil || in.FromID <= 0 || in.ToID <= 0 ||
		in.FromID == in.ToID || in.Amount <= 0 || in.Amount > 1_000_000_000_000 {
		http.Error(w, "invalid request", http.StatusBadRequest)
		return
	}

	key := req.Header.Get("Idempotency-Key")
	if len(key) < 8 || len(key) > 128 {
		http.Error(w, "invalid Idempotency-Key", http.StatusBadRequest)
		return
	}

	ctx, cancel := context.WithTimeout(req.Context(), 2*time.Second)
	defer cancel()
	start := time.Now()
	err := a.transfer(ctx, key, in)
	slog.Info("transfer", "key", key, "duration", time.Since(start), "error", err)

	switch {
	case err == nil:
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(map[string]string{"status": "completed"})
	case errors.Is(err, errFunds), errors.Is(err, errKey), errors.Is(err, errAcct):
		http.Error(w, err.Error(), http.StatusConflict)
	case errors.Is(err, context.DeadlineExceeded):
		http.Error(w, "database deadline exceeded", http.StatusGatewayTimeout)
	default:
		http.Error(w, "internal error", http.StatusInternalServerError)
	}
}

func main() {
	ctx, stop := signal.NotifyContext(context.Background(),
		syscall.SIGINT, syscall.SIGTERM)
	defer stop()

	cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	cfg.MaxConns = 20
	cfg.MinConns = 2
	cfg.MaxConnLifetime = 30 * time.Minute
	cfg.MaxConnIdleTime = 5 * time.Minute
	cfg.HealthCheckPeriod = time.Minute
	cfg.ConnConfig.ConnectTimeout = 3 * time.Second

	db, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	pingCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
	err = db.Ping(pingCtx)
	cancel()
	if err != nil {
		panic(err)
	}

	a := &app{db: db}
	mux := http.NewServeMux()
	mux.HandleFunc("POST /transfers", a.handleTransfer)
	mux.HandleFunc("GET /healthz", func(w http.ResponseWriter, r *http.Request) {
		healthCtx, cancel := context.WithTimeout(r.Context(), 500*time.Millisecond)
		defer cancel()
		if err := db.Ping(healthCtx); err != nil {
			http.Error(w, "unhealthy", http.StatusServiceUnavailable)
			return
		}
		w.WriteHeader(http.StatusNoContent)
	})

	srv := &http.Server{
		Addr:              "127.0.0.1:8080",
		Handler:           mux,
		ReadHeaderTimeout: 2 * time.Second,
		ReadTimeout:       5 * time.Second,
		WriteTimeout:      5 * time.Second,
		IdleTimeout:       60 * time.Second,
	}

	go func() {
		if err := srv.ListenAndServe(); err != nil && err != http.ErrServerClosed {
			slog.Error("server stopped", "error", err)
			stop()
		}
	}()

	<-ctx.Done()
	shutdownCtx, cancel := context.WithTimeout(context.Background(), 8*time.Second)
	defer cancel()
	if err := srv.Shutdown(shutdownCtx); err != nil {
		slog.Error("shutdown", "error", err)
	}
}

Test success, replay, and rollback

Run the service, submit the same request twice, then inspect the balances. Both calls should report completion, but the balance change must occur once.

DATABASE_URL="$DATABASE_URL" go run .

curl --fail-with-body -X POST http://127.0.0.1:8080/transfers \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: transfer-demo-0001' \
  -d '{"from_id":1,"to_id":2,"amount":1250}'

curl --fail-with-body -X POST http://127.0.0.1:8080/transfers \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: transfer-demo-0001' \
  -d '{"from_id":1,"to_id":2,"amount":1250}'

psql "$DATABASE_URL" -c \
  'SELECT id, balance FROM accounts ORDER BY id;'

The expected balances are 8750 and 11250. Reuse the key with another amount and expect HTTP 409. Request more than the available balance and confirm that neither account nor transfer_requests retains a partial change. For contention testing, run concurrent transfers in both directions; deterministic locking should prevent routine deadlocks, while the lock timeout keeps pathological waits finite.

Migrate without turning deployment into a lock incident

For an established table, avoid combining a long backfill, a new constraint, and application rollout in one migration. Use expand and contract:

1. Add a nullable column or compatible table structure with a short lock timeout.
2. Deploy code that understands both old and new representations.
3. Backfill in small, committed batches with a restartable cursor.
4. Add validation separately; PostgreSQL’s NOT VALID constraints can reduce the initial blocking window where applicable.
5. Validate, switch reads, and remove obsolete structures in a later release.

Build large indexes with CREATE INDEX CONCURRENTLY in a migration that is not wrapped in BEGIN. Give it its own generous but finite statement timeout. If migrate reports a dirty version, inspect the database and migration effects before using force; forcing merely changes metadata and does not repair half-applied DDL.

Security, observability, and performance

Production credentials should belong to a login role with only the required table and sequence privileges. Use TLS when the database crosses an untrusted network, load secrets from a root-readable environment file or secret manager, and never log database URLs or request bodies. Keep PostgreSQL firewalled from public interfaces.

The JSON log already records latency and failure class. Add metrics for pool acquired connections, idle connections, acquisition duration, query timeouts, lock timeouts, transaction rollbacks, and HTTP status codes. Alerting on pool wait time is often more useful than watching only active connection count.

Do not automatically retry every database error. A retry is appropriate only for transient, classified failures such as serialization conflicts, and it needs jitter, a small attempt limit, and the original request deadline. Idempotency makes a whole-request retry safer; it does not make unlimited retry pressure harmless.

Deploy as a bounded system

Build a static binary where supported, install it under an explicit application directory, and run it as an unprivileged systemd user. An administrator must create the service account and protect /etc/ledger/ledger.env with mode 0600.

CGO_ENABLED=0 go build -trimpath -o ledger .
sudo install -d -o root -g root -m 0755 /opt/ledger
sudo install -o root -g root -m 0755 ./ledger /opt/ledger/ledger

# Release job: migrate first, then restart only after success.
PGCONNECT_TIMEOUT=3 \
PGOPTIONS='-c lock_timeout=2s -c statement_timeout=15min' \
migrate -path migrations -database "$DATABASE_URL" up

sudo systemctl restart ledger.service
curl --fail http://127.0.0.1:8080/healthz

Place a hardened reverse proxy in front for TLS, authentication, request-rate limits, and public exposure. Keep the application bound to loopback unless the network design explicitly requires otherwise. During rolling deployment, ensure the shutdown allowance exceeds the two-second database deadline and five-second HTTP write timeout.

Final verification checklist

• The pool maximum fits within PostgreSQL’s reserved connection budget.
• Dial, request, statement, lock, and shutdown timeouts are independently configured.
• Account rows are locked in a deterministic order.
• Idempotency keys bind to the complete operation payload.
• Failed transfers leave no debit, credit, or committed pending record.
• Duplicate requests change balances only once.
• Migrations run once per release with bounded locks and inspected failures.
• The database is private, credentials are least-privileged, and secrets are absent from logs.
• Pool waiting, query latency, timeouts, rollbacks, and error responses are observable.

Reliable database code is not defined by the happy-path transaction alone. It is the choreography around that transaction: how long it may wait, how many peers may compete, what a retry means, how schema changes coexist with live traffic, and how shutdown interrupts the work. Make those boundaries explicit, and the database becomes a controlled part of the system instead of its most mysterious failure amplifier.