SQL и Postgres — справочник

1. database/sql: пул и жизненный цикл

sql.Open не открывает соединение: он лишь проверяет аргументы и создаёт пул. Реальное соединение появляется при первом запросе или PingContext. *sql.DB безопасен для одновременного использования и переиспользует соединения.

db, err := sql.Open("pgx", dsn)
if err != nil {
    return err
}
defer db.Close()

db.SetMaxOpenConns(20)
db.SetMaxIdleConns(20)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

if err := db.PingContext(ctx); err != nil {
    return err
}

Лимит соединений должен соответствовать возможностям Postgres: у сервера есть max_connections, а каждая реплика приложения открывает свой пул. Если пул меньше, чем нужно, запросы ждут в очереди; если больше — сервер тратит память на соединения.

2. Запросы и сканирование

Rows нужно закрывать; после цикла проверяют rows.Err(), иначе ошибка чтения потеряется. QueryRowContext без строк возвращает sql.ErrNoRows из Scan.

rows, err := db.QueryContext(ctx,
    `select id, name from users where active = $1`, true)
if err != nil {
    return err
}
defer rows.Close()

var users []User
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil {
        return err
    }
    users = append(users, u)
}
if err := rows.Err(); err != nil {
    return err
}

var u User
err := db.QueryRowContext(ctx,
    `select id, name from users where id = $1`, id).
    Scan(&u.ID, &u.Name)

if errors.Is(err, sql.ErrNoRows) {
    return ErrNotFound
}
if err != nil {
    return err
}

Postgres использует позиционные параметры $1, $2. LastInsertId у драйвера Postgres не работает: идентификатор возвращают через returning.

var id int64
err := db.QueryRowContext(ctx,
    `insert into users (name) values ($1) returning id`, name).
    Scan(&id)

3. NULL

NULL — не zero value. Сканировать NULL в string или int нельзя: Scan вернёт ошибку. Варианты: sql.NullString, sql.NullInt64, sql.NullFloat64, sql.NullBool, sql.NullTime, обобщённый sql.Null[T] [1.22] или указатель.

var name sql.NullString
if err := row.Scan(&name); err != nil {
    return err
}
if name.Valid {
    fmt.Println(name.String)
}

var age sql.Null[int]
if err := row.Scan(&age); err != nil {
    return err
}

Для JSON-полей «нет значения» и «пустая строка» — разные вещи. Если API должен их различать, указатель или Null[T] честнее, чем COALESCE.

4. pgx

pgx — драйвер и клиент Postgres с нативным протоколом. Он умеет то, чего нет в database/sql: batch, COPY, нативные типы, CollectRows, структуры по именам колонок. Для совместимости есть адаптер stdlib, который регистрирует драйвер "pgx" для database/sql.

pool, err := pgxpool.New(ctx, dsn)
if err != nil {
    return err
}
defer pool.Close()

rows, err := pool.Query(ctx,
    `select id, name from users where active = $1`, true)
if err != nil {
    return err
}

users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])
if err != nil {
    return err
}

type User struct {
    ID   int64  `db:"id"`
    Name string `db:"name"`
}

tag, err := pool.Exec(ctx,
    `update users set name = $1 where id = $2`, name, id)
if err != nil {
    return err
}
fmt.Println(tag.RowsAffected())

RowToStructByName требует, чтобы число публичных полей структуры совпадало с числом колонок; Lax допускает больше полей, чем колонок. Имена сопоставляются без учёта регистра, тег db переопределяет имя, db:"-" исключает поле.

5. Транзакции и изоляция

Транзакция начинается с BeginTx и заканчивается Commit или Rollback. defer tx.Rollback() безопасен: после успешного Commit он вернёт sql.ErrTxDone, и этот результат игнорируют.

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
})
if err != nil {
    return err
}
defer tx.Rollback() // безопасно и после Commit

if _, err := tx.ExecContext(ctx,
    `update accounts set balance = balance - $1 where id = $2`,
    amount, from); err != nil {
    return err
}
if _, err := tx.ExecContext(ctx,
    `update accounts set balance = balance + $1 where id = $2`,
    amount, to); err != nil {
    return err
}

return tx.Commit()

На Serializable Postgres может вернуть 40001 (serialization_failure) — транзакцию нужно повторить целиком. Savepoint задаётся обычным SQL: SAVEPOINT name, ROLLBACK TO SAVEPOINT name.

6. Ошибки и SQLSTATE

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
    switch pgErr.Code {
    case "23505":
        return ErrConflict
    case "40001", "40P01":
        return ErrRetry
    }
}
return fmt.Errorf("query: %w", err)

Код SQLSTATE — стабильный контракт; текст ошибки может меняться между версиями и локалями. Сравнивать нужно код, а не сообщение. Повторять имеет смысл только транзакцию целиком и только для 40001 и 40P01.

7. Индексы

B-tree — индекс по умолчанию и подходит для равенства, диапазонов и сортировки. Порядок колонок в составном индексе важен: (user_id, created_at) помогает фильтру по user_id и сортировке по created_at, но не наоборот.

create index orders_user_created_idx
    on orders (user_id, created_at desc);

create index orders_pending_idx
    on orders (created_at)
    where status = 'pending';

create index orders_covering_idx
    on orders (user_id)
    include (status, total);

create index events_payload_idx
    on events using gin (payload jsonb_path_ops);

Индекс не бесплатен: он замедляет вставку и обновление и занимает место. Индекс на колонку с низкой селективностью (например, status с двумя значениями) чаще вредит, чем помогает; partial-индекс решает это, индексируя только нужное подмножество.

8. EXPLAIN и планы

EXPLAIN показывает план, EXPLAIN ANALYZE — ещё и реальное выполнение. BUFFERS добавляет чтение страниц из кэша и с диска. Без ANALYZE числа — оценки планировщика, а не факты.

explain (analyze, buffers, format text)
select id, name
from users
where email = 'a@b.c';

Что смотреть в строке плана: cost=startup..total — оценка стоимости, rows — оценка числа строк, width — средняя ширина, actual time=startup..total rows=N loops=N — факт, Buffers: shared hit/read — попадания в кэш и чтения с диска.

Limit  (cost=0.42..8.44 rows=1 width=40) (actual time=0.020..0.021 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using users_email_idx on users
        (cost=0.42..8.44 rows=1 width=40) (actual time=0.019..0.020 rows=1 loops=1)
        Index Cond: (email = 'a@b.c'::text)

Если rows в оценке и actual rows расходятся на порядки, планировщику не хватает статистики: помогает ANALYZE table. Index Only Scan требует visibility map, поэтому после массовых изменений нужен VACUUM.

9. Миграции

Схема версионируется файлами, а не правками вручную. В Postgres DDL транзакционный: create table, alter table и create index можно завернуть в транзакцию вместе с записью в таблицу версий.

create table schema_migrations (
    version    bigint primary key,
    applied_at timestamptz not null default now()
);

begin;

alter table users add column email text;

insert into schema_migrations (version) values (20261104_01);

commit;

select pg_advisory_lock(42);
-- применить миграции
select pg_advisory_unlock(42);

Инструменты: golang-migrate, goose, atlas. Официального инструмента в Go нет; формат файлов и таблицу версий выбирает проект.

10. Тестирование

Репозиторий проверяют на настоящем Postgres: моки скрывают блокировки, типы, NULL и ограничения. Варианты — локальный сервер, Docker через testcontainers-go или общий тестовый экземпляр в CI.

Быстрый приём — транзакция на тест с откатом: каждый тест видит чистое состояние и не мешает другим.

func TestUserRepository(t *testing.T) {
    ctx := context.Background()

    pool, err := pgxpool.New(ctx, os.Getenv("TEST_DATABASE_URL"))
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(pool.Close)

    tx, err := pool.Begin(ctx)
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(func() { _ = tx.Rollback(ctx) })

    repo := NewUserRepository(tx)
    if err := repo.Create(ctx, User{Name: "Аня"}); err != nil {
        t.Fatal(err)
    }
}

11. Первоисточники

• pkg.go.dev/database/sql — DB, Tx, Null[T], уровни изоляции
• pkg.go.dev/github.com/jackc/pgx/v5 — соединение, запросы, CollectRows
• pkg.go.dev/github.com/jackc/pgx/v5/pgxpool — пул соединений
• pkg.go.dev/github.com/jackc/pgx/v5/pgconn — PgError и SQLSTATE
• PostgreSQL: Using EXPLAIN — чтение планов
• PostgreSQL: EXPLAIN — параметры ANALYZE, BUFFERS, FORMAT
• PostgreSQL: Indexes — виды индексов и когда их применять
• PostgreSQL: Error Codes — SQLSTATE
• PostgreSQL: Transaction Isolation — уровни изоляции