Databases & SQL in Go

WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed. INNER JOIN keeps only rows that match on both sides, while LEFT JOIN keeps every row of the left table and fills the right side with NULLs. **Aggregates belong in HAVING**: WHERE count(*) > 5 is an error, because no group exists yet when WHERE runs. Every non-aggregate filter goes in WHERE, so fewer rows reach the grouping. **A WHERE on the right table undoes a LEFT JOIN**: WHERE o.status = 'paid' throws away the NULL-filled rows, and the query silently becomes an inner join. Put that condition in ON. **count(o.id), not count(*), after a LEFT JOIN**: count(*) counts the NULL-filled row and reports 1 for a customer with no orders.

**sql.Open never touches the network.** It builds a *sql.DB (a pool that starts with zero connections), and the first connection is opened by your first query or Ping. *sql.DB is a pool, not a connection, and it is safe for many goroutines. Open it once in main and pass it around. The only errors it returns are an unknown driver name (a forgotten blank import such as _ "github.com/jackc/pgx/v5/stdlib") or an unparseable DSN, never a dead host. db.PingContext(ctx) with a short timeout opens one real connection, so a broken config fails at startup instead of on the first request. Calling sql.Open per request throws the pool away and pays a fresh connection handshake every time; without Close it also leaks the connections.

Exec is for statements that return no rows, Query for a result set and QueryRow for at most one row. A DELETE sent through Query keeps its pool connection until the *sql.Rows is closed (which nobody does), so every call leaks a connection until the pool is empty. **Exec**: INSERT, UPDATE, DELETE, DDL. It releases the connection before it returns, and sql.Result gives you RowsAffected(). Postgres drivers do not support LastInsertId: add RETURNING id and use QueryRow. **Query**: rows you own: defer rows.Close() on the line after the error check, then loop and check rows.Err(). **QueryRow**: Scan closes the row for you, and an empty result comes back as sql.ErrNoRows. The fingerprint of the leak is db.Stats(): InUse stuck at the maximum while WaitCount climbs.

**QueryRow returns no error itself, so it parks one and Scan returns the sentinel sql.ErrNoRows.** Translate it once, at the repository boundary, into your own ErrUserNotFound with errors.Is. The check is if errors.Is(err, sql.ErrNoRows) { return ErrUserNotFound }; the handler maps that to 404, and layers above never import database/sql errors. Use errors.Is, not ==, because the layers in between wrap with %w. Only QueryRow(...).Scan produces it. An empty result from Query is not an error: rows.Next() just returns false. COUNT(*) always returns a row, so it never gives you the sentinel: a missing row is an ordinary outcome there, and zero is an answer.

**A Go string has no room for NULL**, so Scan fails with converting NULL to string is unsupported. Scan into a pointer or a sql.Null[T], or remove the NULL in SQL with COALESCE. ***string**: the driver sets it to nil for NULL, and it marshals to JSON as null. **sql.NullString, sql.NullTime**: a value plus a Valid flag; the generic sql.Null[T] (Go 1.22) covers any type. Writing NULL: pass a nil pointer or a sql.Null[T] with Valid: false as the argument, and the driver sends NULL. **COALESCE(discount, 0)** makes the column non-nullable for that query, and it is the one option that changes the meaning: a NULL discount that becomes 0 now reads as "no discount" instead of "unknown".

**rows.Err() after the loop is missing.** Next returns false both when the rows run out and when the iteration breaks, so a connection dropped at row 4,000 looks exactly like a clean finish. Rows arrive in batches while you iterate, so a network blip or a server-side timeout lands inside the loop, not at QueryContext. The error is parked on the *sql.Rows, and rows.Err() is the only way to ask for it. The Scan error is a different thing: one row that would not convert. The shape that is right every time: check the query error, defer rows.Close(), check Scan inside the loop, then if err := rows.Err(); err != nil after it. While you are there: the ignored fmt.Fprintf error hides a full disk the same way. The rowserrcheck linter in golangci-lint finds every instance; go vet does not look for it.

ACID: a transaction is all or nothing, keeps constraints true, is isolated from concurrent ones and survives a crash once committed. Isolation levels trade anomalies for aborts: Read Committed allows the most, Serializable none. ACID Atomicity: every statement commits or none does; a failure halfway rolls everything back. Consistency: constraints, foreign keys and checks hold before and after each transaction. Isolation: concurrent transactions do not see each other's half-done work; the level says how strictly. Durability: once COMMIT returns, the change is in the write-ahead log and survives a crash. Levels and anomalies Read Committed (Postgres default) shows each statement what was committed before it started. It allows non-repeatable reads, phantoms and lost updates: two read-modify-write cycles can overwrite each other. Repeatable Read (MySQL InnoDB default): one snapshot for the whole transaction, so no non-repeatable reads. The SQL standard still allows phantoms here; Postgres snapshots hide them too and abort a conflicting update with 40001 instead of losing it, but write skew remains. Serializable: the outcome equals some serial order. Postgres detects dangerous patterns and aborts one transaction with 40001, so the caller must retry. Dirty reads never happen in Postgres: Read Uncommitted behaves as Read Committed. In practice Write skew: two doctors each check that someone else is on call, then both go off call. Each read was valid; only Serializable or an explicit lock stops it. In Go: pick the level per transaction: db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelRepeatableRead, ReadOnly: true}) for a consistent report.

An index lets the database find rows without reading the whole table; B-tree is the default for equality, ranges and ORDER BY. A composite (a, b) index serves queries that filter on a first, and EXPLAIN ANALYZE shows whether it was used and where the time went. Types B-tree: =, <, BETWEEN, ORDER BY, LIKE 'abc%' (in a non-C collation the index needs text_pattern_ops). The right choice most of the time. GIN: many values per row: jsonb, arrays, full-text search, LIKE '%abc%' with pg_trgm. GiST for geometry and ranges, BRIN for huge append-only tables ordered by time, Hash for equality only. Partial (WHERE status = 'pending') and covering (INCLUDE (total), for an index-only scan) indexes keep the index small or skip the table. Composite indexes Entries are sorted by the first column, then the second, like a phone book. (user_id, created_at) serves user_id = ? and user_id = ? AND created_at > ?, but not created_at > ? alone. Equality columns first, the range or sort column last. Postgres 18 added skip scan, which can use such an index without the leading column when that column has few distinct values. Do not design around it. Every index slows every write. Drop the ones whose pg_stat_user_indexes.idx_scan stays at zero. Reading EXPLAIN ANALYZE It executes the query, so wrap an UPDATE or DELETE in BEGIN ... ROLLBACK. Read from the innermost node outwards; actual time is per loop, so multiply by loops. Red flags: a Seq Scan on a big table that returns few rows, a large Rows Removed by Filter, and estimated rows far from actual rows (stale statistics, fixed by ANALYZE).

The early return after the first ExecContext never rolls back, so the transaction keeps its pool connection and its row locks; and the error from tx.Commit() is dropped, so a failed commit is reported as a placed order. The leak: an open *sql.Tx owns a connection until Commit or Rollback. database/sql rolls it back when the ctx given to BeginTx is cancelled, so with a request context it lingers until the request ends; with context.Background() it never ends. A few of these and the pool is empty. The commit: Commit is where Postgres reports a deferred constraint, a serialization failure (40001) or a dropped connection. Ignoring its error returns success for an order that does not exist. The fix is two lines: defer tx.Rollback() right after BeginTx covers every error path and a panic; after a successful commit it is a no-op that returns sql.ErrTxDone. Then return tx.Commit().

Values go in placeholders; identifiers cannot. $1 makes the search text safe, but a column name in ORDER BY is safe only if you pick it from a whitelist you wrote yourself. Why a placeholder works: it is not escaping. The SQL text and the value travel as separate protocol messages, so the server parses the query before it has seen the value, and q = "x' OR 1=1 --" stays a string. That is the extended protocol; a driver in simple-protocol or client-side interpolation mode escapes instead, which is safe but a different mechanism. Identifiers cannot be bound: table names, column names and ASC/DESC. Map the API's words to SQL yourself with map[string]string{"newest": "created_at DESC"}, and anything not in the map is a 400. **% and _ in q** are still LIKE wildcards. That is not injection, but escape them if users must be able to search for them literally. The marker is the driver's dialect: $1 in Postgres, ? in MySQL and SQLite.

SetMaxOpenConns caps the connections (unlimited by default), SetMaxIdleConns keeps some open between bursts (default 2), and SetConnMaxLifetime recycles them. Size the cap from the database's connection budget shared by every pod, then confirm it with db.Stats(). Unlimited is the default: under a spike every goroutine opens its own backend until Postgres answers sorry, too many clients already. The cap protects the database: max_connections is shared by all pods, cron jobs and whoever has psql open. Too low a cap queues inside Go: p99 climbs while the database idles. Stats().WaitCount and WaitDuration show that time; no database metric will. **MaxIdleConns below MaxOpenConns** makes the pool close and re-dial between bursts; a growing Stats().MaxIdleClosed is the tell. Usually set them equal. **SetConnMaxIdleTime must be shorter than any idle timeout a proxy or load balancer enforces, so a connection it already killed is never handed out. SetConnMaxLifetime recycles connections by age: it picks up DNS or failover changes and stays under any maximum connection age the proxy or server enforces. Rule of thumb**: throughput is roughly connections divided by mean query time: ten connections at 5 ms serve about 2,000 queries a second. Far past the database's core count, more connections add contention, not speed.

With QueryContext, cancellation makes database/sql stop waiting and the driver ask Postgres to cancel the query; with plain Query nothing is wired up and the query runs on. Cancellation is best-effort, so statement_timeout is the server-side ceiling that cannot get lost. What the driver does: pgx interrupts the read, sends a cancel request over a separate connection and closes the busy one, so the pool loses that connection. lib/pq also sends a cancel request. The backend stops at its next interruption point, so a query deep inside a sort finishes that step first. Without a cancel, Postgres notices a vanished client only when it tries to send rows, or through client_connection_check_interval (Postgres 14+). Give each query its own budget: context.WithTimeout(ctx, 500*time.Millisecond); waiting for a free pool connection counts against it. **Set statement_timeout** per role or in the DSN, so a query whose cancel was lost, or that came from a tool with no context at all, still dies.

Make repository functions accept a small interface that both *sql.DB and *sql.Tx satisfy, and give the service one InTx(ctx, func(q DBTX) error) that begins, commits and rolls back. The service decides the boundary and never touches *sql.Tx itself. **Why not pass *sql.DB**: a helper that takes *sql.DB inside a transaction runs on a second connection, outside it. It cannot see the transaction's uncommitted rows, and it blocks on rows the transaction has locked. The pool deadlock: every request now needs two connections. With MaxOpenConns(n), n transactions each waiting for a second connection freeze the service, and the database's deadlock detector never sees it. **DBTX**: ExecContext, QueryContext, QueryRowContext. *sql.DB, *sql.Tx and *sql.Conn all implement it, and sqlc generates a DBTX of the same shape. The type system now stops a helper from escaping the transaction. **Tx in context.Context**: some codebases stash the tx in ctx and let repositories pick it up. It works, but the dependency is invisible, and a call that loses the ctx silently runs outside the transaction.

database/sql is the standard pool-and-scan API; pgx is the Postgres driver with a richer native API; sqlx trims Scan boilerplate; sqlc generates typed Go from SQL you write; GORM writes the SQL for you. For Postgres, pgx with sqlc is the common default. **pgx**: use it through database/sql (the stdlib package) or natively with pgxpool. Native adds CopyFrom, batches, LISTEN/NOTIFY, arrays and jsonb without wrappers, and a per-connection statement cache. lib/pq still works, but new code picks pgx. **sqlx**: Get, Select, NamedExec and In over the same *sql.DB. Struct mapping goes by db:"..." tags and fails at runtime, not at compile time. **sqlc**: you write .sql files and it generates methods checked against the schema, so a renamed column fails the build. The cost is a generation step and awkward dynamic queries such as optional filters. GORM: quickest to write for CRUD screens. The cost is queries nobody reviewed: preloads that turn into N+1, soft-delete conditions added to every WHERE. What none of them changes: pool sizing, contexts, transaction boundaries and NULL handling are still yours.

N+1 is one query for a list, then one more query per item inside a loop. Each query is fast; the cost is the round trips: 100 orders mean 101 sequential trips, and at 2 ms each that is 200 ms. The database looks innocent: every call is quick and the plan is good. The time is network and protocol, paid once per iteration, because each query waits for the previous one. Two queries instead of N+1: fetch the parents, collect their ids, run SELECT ... WHERE order_id = ANY($1), scan into a map[int64][]Item and attach the children in one pass. Two queries whatever the page size. **A JOIN is one round trip instead of two, but the parent's columns come back once per child, which is the wrong trade for a wide parent or two child collections. Why it slips through review**: it grows with the page size, so a five-row test fixture never shows it. Watch the query count per request in traces.

One INSERT per row spends its time on 200,000 round trips, not on database work. Use COPY (pgx's CopyFrom) for bulk loads, or chunked multi-row INSERTs, which are capped at 65,535 bind parameters per statement. **COPY streams every row in one statement, with no parse and plan per row. An order of magnitude over single-row inserts is the normal result. It cannot do ON CONFLICT.** The usual shape is COPY into a staging table, then one INSERT ... SELECT ... ON CONFLICT DO UPDATE to merge. The parameter cap: with three columns it allows about 21,845 rows per INSERT, and chunks of 1,000 to 5,000 rows are where the speed-up flattens anyway. **unnest** sidesteps the cap: INSERT ... SELECT * FROM unnest($1::bigint[], $2::text[], $3::timestamptz[]) sends three array parameters for any number of rows.

Pessimistic locking takes the lock up front with SELECT ... FOR UPDATE, so others wait; optimistic locking takes none and detects a concurrent change at write time with a version column. FOR UPDATE SKIP LOCKED turns a table into a job queue: each worker claims rows nobody else holds. Optimistic: UPDATE ... SET version = version + 1 WHERE id = $1 AND version = $2; zero rows affected means someone saved first, so reload or answer 409. Best when conflicts are rare or the edit spans a user's think time, where holding a lock is impossible. Pessimistic: FOR UPDATE inside a short transaction when conflicts are frequent or a retry is expensive, like a balance. NOWAIT fails at once with 55P03 instead of waiting. **SKIP LOCKED skips rows other transactions have locked instead of waiting for them, so N workers claim N different batches with no contention. Claim, then commit**: mark the jobs running in one short statement and process them outside the transaction; a long transaction holds a connection and blocks vacuum. A reaper resets jobs whose locked_at is too old, because a crashed worker leaves them running.

Each transaction locks the rows in transfer order, so A→B and B→A each hold one lock and wait for the other; Postgres finds the cycle after deadlock_timeout (1 s) and aborts one. Take locks in one fixed order (sort the ids) and the cycle cannot form. One statement can order the locks: WHERE id = ANY($1) ORDER BY id FOR UPDATE. When the locks come from separate statements, sort the ids in Go first: the order in the code is the order on the wire. Often no explicit lock is needed: two UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 in id order take the same row locks and check the invariant in SQL; RowsAffected() == 0 means insufficient funds. Keep transactions short: no network calls while holding locks; every extra millisecond widens the window for a cycle. Keep a small bounded retry for 40P01 anyway (foreign keys or another workload can still deadlock), but a retry hides an ordering bug, it does not fix it.

The audit insert goes through s.db, not tx, so it takes a second connection from the pool and commits on its own. Under load every call holds its transaction's connection while waiting for another, the pool starves, and the audit row survives a rollback. Two connections per call: a *sql.Tx is pinned to one connection until Commit or Rollback, and s.db.ExecContext asks the pool for another. With SetMaxOpenConns(N) and N concurrent calls, all N hold a transaction and wait for a free connection: db.Stats().WaitCount climbs, requests die on the context deadline, and Postgres sits idle. Not atomic: the audit statement runs in autocommit and is durable at once. If Commit later fails or the function returns early, the log records an order that does not exist; the second connection cannot even see the uncommitted order row. The fix: every statement of the unit of work goes through tx. Repositories that take a small DBTX interface (ExecContext, QueryContext, QueryRowContext), satisfied by both *sql.DB and *sql.Tx, make the wrong handle harder to pass. Catching it: run integration tests with SetMaxOpenConns(1): the buggy version then hangs on the very first call. In review, any s.db between BeginTx and Commit is a red flag.

A prepared statement lives on one server connection. A *sql.Stmt made from *sql.DB re-prepares itself on each pool connection it lands on, and behind a transaction-mode pooler the connection it prepared on is not the one it executes on. Cost in the pool: 50 connections mean up to 50 server-side copies per *sql.Stmt, and when the connections it knows are busy, a call prepares again on another one. That extra round trip is where the latency comes from. PgBouncer in transaction mode gives each transaction whichever server connection is free, so a named statement prepared on one is missing on the next: prepared statement "..." does not exist. PgBouncer 1.21+ can track protocol-level prepared statements with max_prepared_statements; older setups need pgx in QueryExecModeExec or simple-protocol mode. It was not faster anyway: db.QueryContext(ctx, sql, args...) is already parameterised, and pgx caches prepared statements per connection by default. **When Prepare pays**: inside a *sql.Tx or *sql.Conn, where the connection is fixed, for a statement executed many times. Close it: it is a server-side object.

Postgres never updates a row in place: UPDATE writes a new row version and marks the old one dead, and each transaction sees the versions its snapshot allows. Vacuum removes a dead version only when no open transaction could still see it, so one old transaction stalls cleanup database-wide. Row versions Each version carries xmin, the transaction that created it, and xmax, the one that deleted or replaced it. A snapshot says which transactions count as committed, so readers never block writers and writers never block readers. DELETE only sets xmax. UPDATE is a delete plus an insert, and every index gets a new entry unless it is a HOT update: no indexed column changed and the new version fits on the same page. Cleanup Autovacuum marks dead versions as reusable space and refreshes statistics. The space is reused, not returned to the OS; shrinking a bloated table takes VACUUM FULL (an exclusive lock) or pg_repack. Vacuum also freezes old rows so the 32-bit transaction counter can wrap around safely. A database that cannot vacuum for long enough stops accepting writes to protect itself. One long transaction The cleanup horizon is the oldest snapshot still open anywhere. A two-hour report or a forgotten transaction pins it, and every busy table piles up dead versions meanwhile: indexes grow and scans slow down. From Go, the usual culprit is a *sql.Tx that never reaches Commit or Rollback. It shows as idle in transaction in pg_stat_activity; idle_in_transaction_session_timeout kills it, and backend_xmin with xact_start finds who pins the horizon. Moving reports to a replica helps, but with hot_standby_feedback on, the replica's long queries pin the primary's horizon again; with it off, they get cancelled instead.

Retry the whole transaction, not the failed statement: a serialization failure rolls everything back, so you re-run the function from BeginTx and re-read every value. Anything with an outside effect (an email, an HTTP call, a card charge) must stay out of the loop. Why it happens: Postgres does not block readers at this level. It lets both transactions run and aborts one, often at COMMIT, when their reads and writes could not have come from any serial order. That is expected traffic, not a bug. Bound the loop: three to five attempts with a short randomised backoff, around a function that performs the entire transaction. Reusing a value read during the failed attempt brings back the anomaly you raised the isolation level to prevent. Retry only what is retryable: 40001 and 40P01; never 23505 (unique violation), which will fail the same way every time. Side effects go through an outbox: write the email or event to a table in the same transaction, and let a relay send it after the commit. A call made inside the loop happens once per attempt.

Expand, migrate, contract: during a rolling deploy old and new code share one database, so every schema step must work with both. A rename becomes add column, dual-write, backfill, switch reads and drop over several releases, each applied by the migration tool before its rollout. Expand and contract Release 1 adds a nullable full_name, writes both columns and still reads name. Adding a nullable column without a volatile default does not rewrite the table. Backfill in batches from a job, not inside a migration: a half-hour UPDATE holds its locks, blocks the next deploy and cannot resume. Release 2 reads full_name and still writes both; release 3 stops writing name; only then does a migration drop it. Locks cause the outage Even an instant ALTER TABLE needs an exclusive lock, queues behind a long SELECT, and every query then queues behind it. Run SET LOCAL lock_timeout = '3s' first (a plain SET outlives goose's transaction and leaks into later migrations) and retry. CREATE INDEX CONCURRENTLY does not block writes but cannot run inside a transaction: give it its own file with -- +goose NO TRANSACTION. NOT NULL on a big table: add CHECK (col IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, then SET NOT NULL skips the full scan, then drop the CHECK. The tools goose and golang-migrate apply numbered files once and record the version. Run them as one step before the rollout (a CI job or a Kubernetes Job), not from every pod at startup. Down migrations are for development. In production you roll forward with a new migration, because data written since cannot be un-written.

Replication keeps full copies of the same data on several servers, for failover and read scaling; sharding splits the data by a key so each server holds a part, which scales writes. Synchronous replication makes COMMIT wait for a replica: no lost writes on failover, at the cost of latency. Replication One primary takes writes; replicas replay its WAL and serve reads. It does not scale writes, because every replica applies every write. Asynchronous (the Postgres default): commit returns at once, replicas lag by milliseconds or minutes, and a failover can lose the last acknowledged transactions. Synchronous: synchronous_standby_names = 'ANY 1 (r1, r2)' makes a commit wait for either replica, so one slow replica does not stall writes. Choose it where losing an acknowledged write is unacceptable, such as payments and ledgers. Sharding The shard key decides everything. tenant_id keeps a tenant's data and transactions on one shard; a poor key creates hot shards and cross-shard queries. Cross-shard joins and transactions become expensive or impossible, and resharding is a project. Map many logical shards onto fewer servers, so growth moves whole shards, not rows. Exhaust the cheaper options first: indexes, a bigger server, read replicas, table partitioning, or an existing system such as Citus or Vitess. In Go Keep two pools, primary and replica *sql.DB, and choose per query. Right after a user writes, read their data from the primary, or replica lag shows them stale data. Sharding means a map[shardID]*sql.DB and routing every call by key, so the key must reach every repository method.