Основы PostgreSQL
Основы PostgreSQL¶
PostgreSQL — это классическая реляционная СУБД с богатым SQL и предсказуемым поведением. Если коротко: надежно хранит данные, честно исполняет запросы и не пытается угадывать за вас.
Где живет Postgres и как он устроен¶
Postgres — это один серверный процесс (postmaster), который принимает подключения и под каждое соединение поднимает отдельный backend-процесс.
- Shared Buffers — общий кэш страниц таблиц и индексов.
- WAL — журнал, в который сначала пишутся изменения, а уже потом попадают в данные на диске.
- Data files — сами файлы таблиц и индексов.
Запомни простую схему
Сначала запись в WAL, затем изменения попадают на диск. Это и есть гарантия надежности.
База данных, схема и объекты¶
Уровни, которые чаще всего путают:
- Cluster — физическая инстанция на диске (один каталог данных).
- Database — логическая база данных внутри инстанса.
- Schema — пространство имен для объектов внутри базы.
Основные объекты:
- Tables — хранение данных.
- Views — сохраненные SELECT.
- Indexes — ускорение поиска.
- Sequences — генерация идентификаторов.
- Functions — серверная логика.
Типы данных, которые реально используют¶
integer,bigint— для ключей и счетчиков.text,varchar— текст.numeric— деньги и точные значения.timestamp,date— время.jsonb— гибкие структуры.array— массивы (быстро и удобно для простых списков).
Транзакции и MVCC¶
PostgreSQL реализует MVCC (многоверсионность). Это значит, что чтение не блокирует запись и наоборот: каждый запрос видит свою версию данных.
Базовый ритуал транзакции:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;
Важный нюанс
Старые версии строк никуда не исчезают мгновенно. Их потом убирает VACUUM.
Индексы: коротко и по делу¶
- B-Tree — индекс по умолчанию. Подходит почти всегда.
- GIN — для
jsonb, массивов и полнотекста. - GiST — гео, похожие значения, ранжирование.
- BRIN — большие таблицы с упорядоченными данными по времени.
Индекс не ускоряет всё подряд. Он помогает, когда запрос выбирает небольшую часть таблицы.
VACUUM и ANALYZE¶
Postgres не перезаписывает строки на месте. Он создает новые версии, а старые чистит фоновый процесс.
- VACUUM — удаляет "мертвые" версии строк.
- ANALYZE — обновляет статистику для планировщика.
Автовакуум включен по умолчанию, но на больших таблицах его стоит контролировать.
Минимальный SQL, чтобы стартовать¶
CREATE TABLE analytics.events (
event_id bigserial PRIMARY KEY,
user_id bigint,
event_name text,
created_at timestamp default now()
);
INSERT INTO analytics.events (user_id, event_name)
VALUES (101, 'login'), (102, 'purchase');
SELECT user_id, count(*)
FROM analytics.events
GROUP BY user_id
ORDER BY count(*) DESC;