PostgreSQL — практическая работа и API

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Инженеру

Другие СУБД в этом цикле: SQLite · MySQL · Microsoft SQL Server · SQL — о разделе · Практикум demo — авиакомпания · Практикум PostgreSQL по JSONB · Практикум PostgreSQL 8.11 · phpPgAdmin.

PostgreSQL — серверная объектно-реляционная СУБД. В отличие от SQLite, она работает как отдельный процесс сервера, поддерживает роли, сетевые подключения, сложные типы данных, расширения и масштабируемые продакшен-сценарии.

Маршрут для новичка по PostgreSQL — в SQL — о разделе: Первые шаги с SQL (установка и psql) → эта глава → демобаза авиакомпании → справочник администратора. На PHP-стеке параллельно удобен phpPgAdmin. Для production — практикум 8.11.

Обзор PostgreSQL

PostgreSQL ведёт происхождение от академического проекта POSTGRES (Беркли, 1980‑е), затем Postgres95 и с 1996 года — имя PostgreSQL (связь с POSTGRES и переход на SQL). Произносят "постгрес-ку-эль" или просто "постгрес".

Развитием руководит PostgreSQL Global Development Group; релиз major-версии выходит примерно раз в год (сейчас актуальна 16). Среди ключевых механизмов ядра — MVCC, WAL и vacuum; к экосистеме относятся полнотекстовый поиск, типы JSON/JSONB и семейства индексов GiST/GIN. Подробнее про MVCC, VACUUM и эксплуатацию — практикум 8.11.

Документация на русском

Полный комплект документации PostgreSQL 16 на русском — postgrespro.ru/docs/postgresql/16. Заметки к выпуску 16 — release-16.

Как работает PostgreSQL

Кластер PostgreSQL — это процессы ОС, разделяемая память и файлы на диске. Ниже — краткая напоминалка по слоям; подробнее про путь SQL-запроса и компоненты — в Принципах работы SQL-движка, про WAL при сбое — в Восстановлении после сбоя.

Подключения и процессы

Главный процесс postmaster слушает порт (обычно 5432), принимает подключения и порождает дочерний backend-процесс на каждую сессию клиента (модель "одно подключение — один процесс").
Backend-процесс разбирает SQL, строит план, читает и пишет страницы через общие буферы, возвращает результат приложению (psql, JDBC, драйвер в коде).
Тяжёлые запросы могут задействовать parallel workers — отдельные фоновые процессы, которые помогают одному backend'у (сортировка, сканирование, агрегация).

Разделяемая память

Все backend'ы и служебные процессы работают с одним блоком shared memory:

Область	Назначение
Shared buffers	Кэш страниц таблиц и индексов в ОЗУ
WAL buffers	Буфер записей журнала до сброса на диск
CLOG buffers	Статусы транзакций (committed / aborted / in progress)
Temp buffers	Временные таблицы сессии
Прочие буферы	Служебные структуры ядра

Параметр shared_buffers задаёт размер кэша данных; work_mem — память на операцию внутри одного запроса (сортировка, хеш), а не на всё подключение.

Фоновые (auxiliary) процессы

Они обслуживают кластер, пока приложения шлют SQL:

Процесс	Роль
WAL Writer	Сбрасывает WAL buffers в файлы `pg_wal/`
Background Writer (BG Writer)	Дозаписывает "грязные" страницы из shared buffers в файлы данных
Checkpointer	Периодические checkpoint — сокращают объём WAL при recovery
Autovacuum	Убирает "мёртвые" строки (MVCC), обновляет статистику
Stats Collector	Собирает метрики для планировщика (`pg_stat_*`)
Sys Logger	Пишет текстовые логи сервера
Archiver	Копирует сегменты WAL в архив (бэкап, PITR)
Replication Launcher	Запускает процессы потоковой репликации

Физическое хранение и WAL

Data files — таблицы и индексы в каталоге кластера (base/, tablespace).
WAL files — журнал опережающей записи — любое изменение, которое должно пережить сбой, сначала попадает в WAL, затем (с задержкой) отражается в файлах данных через буферы и BG Writer / checkpoint.
Archive files — архивированные сегменты WAL для восстановления на момент времени (Резервное копирование и восстановление PostgreSQL).
Log files — текстовые логи PostgreSQL (отдельно от WAL).

Транзакции и MVCC дают параллельное чтение и запись без блокировки каждой строки на чтение; согласованность при падении питания обеспечивает цепочка WAL → checkpoint → crash recovery (глава 8).

SHOW server_version;
SHOW data_directory;
SHOW wal_level;
SHOW shared_buffers;

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Связь с ОС

Пример "один клиент — один процесс" у PostgreSQL — в Процесс и поток. Интерактивный разбор слоёв СУБД — в Принципах работы SQL-движка (демо DbmsArchitecturePlay).

Подключение и запуск

psql

psql -h localhost -p 5432 -U app_user -d app_db

В интерактивном режиме приглашение подсказывает контекст:

Приглашение	Значение
`postgres=#`	БД `postgres`, роль с правами суперпользователя (`#`)
`appdb=>`	БД `appdb`, обычная роль (`>`)

Команды, начинающиеся с \, понимает только psql (не SQL). Шпаргалка мета-команд — в Первые шаги с SQL.

Перечитать конфигурацию без перезапуска

После правок postgresql.conf или pg_hba.conf (для параметров с sighup):

SELECT pg_reload_conf();

Проверка значения — SHOW parameter_name;. Параметры уровня postmaster (например, shared_buffers) требуют restart службы.

Альтернатива правке файла вручную — ALTER SYSTEM SET work_mem = '128MB'; (значения попадают в postgresql.auto.conf).

Строка подключения (URI)

postgresql://app_user:secret@localhost:5432/app_db
postgresql://app_user@localhost/app_db?sslmode=require

Базовая настройка

CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE DATABASE app_db OWNER app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;

Типы данных и синтаксис

Ключевые типы PostgreSQL:

Числовые — smallint, integer, bigint, numeric, real, double precision
Строки — text, varchar, char
Дата/время — date, timestamp, timestamptz, interval
Булев: boolean
Бинарные: bytea
Расширенные — json, jsonb, uuid, array, hstore, tsvector, tsquery

Теория типа json / jsonb и сравнение с текстовым JSON — JSONB. Пошаговая практика (операторы, функции, индексы, EXPLAIN) — Практикум PostgreSQL по JSONB.

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    profile JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Возможности PostgreSQL

Полный SQL + богатый диалект (CTE, оконные функции, RETURNING, UPSERT)
Сложные типы (JSONB, массивы, UUID, геоданные через PostGIS)
Материализованные представления
Расширения (pg_trgm, uuid-ossp, postgis, pg_stat_statements)
Полнотекстовый поиск
Роли и права (GRANT/REVOKE)
Репликация и резервное копирование
Партиционирование
Server-side функции и процедуры (PL/pgSQL, и др.)

Доступные и недоступные команды

Категория	Команда / конструкция	Статус в PostgreSQL	Комментарий
DML	`SELECT`, `INSERT`, `UPDATE`, `DELETE`	Доступно	Полноценная поддержка
DDL	`CREATE/ALTER/DROP`	Доступно	Широкий набор возможностей
Транзакции	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`	Доступно	Полная поддержка ACID
Права	`CREATE ROLE`, `GRANT`, `REVOKE`	Доступно	Ролевая модель доступа
Серверные объекты	`FUNCTION`, `PROCEDURE`, `TRIGGER`	Доступно	PL/pgSQL и расширения
Репликация/WAL	streaming replication, logical replication	Доступно	Продакшен-масштабирование
Материализованные view	`CREATE MATERIALIZED VIEW`	Доступно	Поддерживается
Подсказки оптимизатору как `/+ hint /`	Частично/ограничено	Нет стандартных Oracle-style hints
`MERGE`	Доступно (современные версии)	Проверять версию кластера
Горизонтальный шардинг "из коробки"	Частично	Реализуется через архитектуру/расширения

Практика SQL в PostgreSQL

На учебной схеме интернет-магазина (JOIN, GROUP BY, выручка, склад, DML) с построчным разбором — SQL — реальные кейсы. Тот же смысл в PostgreSQL с префиксом shop_data. — практикум shop_data.

CRUD

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO tasks(title) VALUES ('Пройти PostgreSQL');
SELECT id, title, done FROM tasks ORDER BY id;
UPDATE tasks SET done = TRUE WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

UPSERT

INSERT INTO tasks(id, title, done)
VALUES (1, 'Обновлённая задача', FALSE)
ON CONFLICT(id) DO UPDATE SET
  title = EXCLUDED.title,
  done = EXCLUDED.done;

Транзакция

BEGIN;
INSERT INTO tasks(title) VALUES ('A');
INSERT INTO tasks(title) VALUES ('B');
COMMIT;

Возврат данных сразу после изменения

INSERT INTO tasks(title) VALUES ('C') RETURNING id, title;
UPDATE tasks SET done = TRUE WHERE id = 2 RETURNING id, done;
DELETE FROM tasks WHERE id = 3 RETURNING id;

Клиенты и инструменты

psql (CLI)
pgAdmin — установка и первые шаги
phpPgAdmin — веб-клиент на PHP (OpenServer, LAMP; см. подключение)
DBeaver
DataGrip
TablePlus
Beekeeper Studio
VS Code + PostgreSQL extensions

API по языкам — подключение, CRUD, библиотеки, классы и функции

Как читать блоки API

В каждом блоке указаны библиотека, ключевые классы/типы, основные свойства и методы, затем минимальный рабочий пример подключения и CRUD.

C# — Npgsql

Библиотека

Npgsql (NuGet)

Ключевые классы

NpgsqlConnection
NpgsqlCommand
NpgsqlTransaction
NpgsqlDataReader
NpgsqlParameter
NpgsqlDataSource

Свойства и методы

Open(), BeginTransaction()
CommandText, Parameters.AddWithValue()
ExecuteNonQuery(), ExecuteScalar(), ExecuteReader()
Read(), GetFieldValue<T>()

Код ITЗагрузка примера кода…

Python — psycopg

Библиотека

psycopg (v3)

Ключевые классы

Connection
Cursor
AsyncConnection/AsyncCursor

Методы

psycopg.connect()
cursor(), execute(), executemany()
fetchone(), fetchall()
commit(), rollback()

Код ITЗагрузка примера кода…

Java — JDBC PostgreSQL Driver

Библиотека

org.postgresql:postgresql

Ключевые классы

Connection
PreparedStatement
ResultSet
DriverManager

Методы

DriverManager.getConnection(...)
prepareStatement(), setAutoCommit(false), commit(), rollback()
setString(), executeUpdate(), executeQuery()
next(), getLong(), getString()

Код ITЗагрузка примера кода…

C — libpq

Библиотека

libpq

Ключевые типы

PGconn
PGresult

Ключевые функции

PQconnectdb(), PQfinish()
PQexec(), PQexecParams()
PQresultStatus(), PQntuples(), PQgetvalue()
PQclear(), PQerrorMessage()

Код ITЗагрузка примера кода…

C++ — libpqxx

Библиотека

libpqxx

Ключевые классы

pqxx::connection
pqxx::work
pqxx::result
pqxx::row

Методы

connection(conninfo)
work txn(conn);
txn.exec(), txn.exec_params(), txn.commit()
row["col"].as<T>()

Код ITЗагрузка примера кода…

Go — pgx или database/sql + pq

Библиотеки

github.com/jackc/pgx/v5 (предпочтительно)
database/sql + github.com/lib/pq (классический вариант)

Ключевые типы pgx

*pgx.Conn
pgx.Rows
pgx.Tx

Методы

pgx.Connect(), Close()
Exec(), Query(), QueryRow()
Begin(), Commit(), Rollback()

Код ITЗагрузка примера кода…

PHP — PDO_PGSQL

Библиотека

PDO с драйвером pgsql

Ключевые классы

PDO
PDOStatement

Методы

new PDO("pgsql:host=...;port=...;dbname=...")
prepare(), execute(), fetchAll()
транзакции — beginTransaction(), commit(), rollBack()

<?php
$db = new PDO("pgsql:host=localhost;port=5432;dbname=app_db", "app_user", "secret");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)");

$stmt = $db->prepare("INSERT INTO users(name) VALUES (:name)");
$stmt->execute([':name' => 'Anna']);

$rows = $db->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row['id'] . " " . $row['name'] . PHP_EOL;
}

Kotlin — JDBC (PostgreSQL)

Библиотека

JDBC + org.postgresql:postgresql

Ключевые классы

Connection
PreparedStatement
ResultSet

Методы

DriverManager.getConnection()
prepareStatement(), executeUpdate(), executeQuery()
setString(), next(), getLong(), getString()

Код ITЗагрузка примера кода…

Rust — tokio-postgres и sqlx

Библиотеки

tokio-postgres (низкоуровневый async)
sqlx (высокоуровневый async, compile-time checks)

Ключевые типы tokio-postgres

Client
Row
Transaction

Методы

tokio_postgres::connect()
execute(), query(), query_one()
transaction(), commit(), rollback()

Код ITЗагрузка примера кода…

JavaScript — node-postgres (pg)

Библиотека

pg

Ключевые классы

Client
Pool
QueryResult

Методы

connect(), end()
query(text, params)
транзакции через BEGIN/COMMIT/ROLLBACK

Код ITЗагрузка примера кода…

Минимальный шаблон безопасной работы

Используйте параметризованные запросы ($1, $2, именованные параметры в ORM).
Включайте TLS для внешних соединений.
Ограничивайте права ролей по принципу minimum privilege.
Используйте миграции с откатом.
Мониторьте медленные запросы и планы выполнения.

SET statement_timeout = '5s';
SET lock_timeout = '3s';
SHOW transaction_isolation;

Когда PostgreSQL подходит и когда выбрать другой путь

PostgreSQL подходит:

многопользовательские серверные системы;
аналитические и транзакционные сценарии в одном кластере;
сложные типы данных и поиск;
строгие требования к целостности и правам доступа.

Другой путь выбирают:

для сверхпростого локального файла в приложении удобнее SQLite;
для экстремального горизонтального масштаба нужен отдельный архитектурный слой с шардингом;
для узкоспециализированного OLAP-хранилища используют колоночные СУБД.

Обзор PostgreSQL​

Как работает PostgreSQL​

Подключения и процессы​

Разделяемая память​

Фоновые (auxiliary) процессы​

Физическое хранение и WAL​

Подключение и запуск​

psql​

Перечитать конфигурацию без перезапуска​

Строка подключения (URI)​

Базовая настройка​

Типы данных и синтаксис​

Возможности PostgreSQL​

Доступные и недоступные команды​

Практика SQL в PostgreSQL​

CRUD​

UPSERT​

Транзакция​

Возврат данных сразу после изменения​

Клиенты и инструменты​

API по языкам — подключение, CRUD, библиотеки, классы и функции​

C# — Npgsql​

Python — psycopg​

Java — JDBC PostgreSQL Driver​

C — libpq​

C++ — libpqxx​

Go — pgx или database/sql + pq​

PHP — PDO_PGSQL​

Kotlin — JDBC (PostgreSQL)​

Rust — tokio-postgres и sqlx​

JavaScript — node-postgres (pg)​

Минимальный шаблон безопасной работы​

Когда PostgreSQL подходит и когда выбрать другой путь​

См. также

SQL - язык структурированных запросов

Первые шаги с SQL

Эволюция систем хранения данных

Реляционная модель данных

Нормализация данных

Словарь данных и системные каталоги

Резервное копирование и восстановление PostgreSQL

Оператор SELECT — синтаксис и стиль

Подзапросы, EXISTS и IN

Фильтрация и трёхзначная логика

Блокировки и конкурентный доступ в PostgreSQL

Практикум shop_data

Обзор PostgreSQL

Как работает PostgreSQL

Подключения и процессы

Разделяемая память

Фоновые (auxiliary) процессы

Физическое хранение и WAL

Подключение и запуск

psql

Перечитать конфигурацию без перезапуска

Строка подключения (URI)

Базовая настройка

Типы данных и синтаксис

Возможности PostgreSQL

Доступные и недоступные команды

Практика SQL в PostgreSQL

CRUD

UPSERT

Транзакция

Возврат данных сразу после изменения

Клиенты и инструменты

API по языкам — подключение, CRUD, библиотеки, классы и функции

C# — Npgsql

Python — psycopg

Java — JDBC PostgreSQL Driver

C — libpq

C++ — libpqxx

Go — pgx или database/sql + pq

PHP — PDO_PGSQL

Kotlin — JDBC (PostgreSQL)

Rust — tokio-postgres и sqlx

JavaScript — node-postgres (pg)

Минимальный шаблон безопасной работы

Когда PostgreSQL подходит и когда выбрать другой путь