Введение в PostgreSQL: история, архитектура, отличия от других СУБД
Введение
PostgreSQL — это мощная объектно-реляционная система управления базами данных (ОРСУБД) с открытым исходным кодом, которая завоевала репутацию одной из самых надежных, функциональных и стандартизированных СУБД в мире. В этом уроке мы погрузимся в историю PostgreSQL, изучим её архитектуру и разберем ключевые отличия от других популярных систем управления базами данных.
История PostgreSQL
Происхождение: проект POSTGRES
История PostgreSQL начинается в 1986 году в Университете Калифорнии в Беркли под руководством профессора Майкла Стоунбрейкера (Michael Stonebraker), который ранее создал известную СУБД Ingres.
Ключевые этапы развития:
1986-1994: POSTGRES
- Проект начался как исследование в области баз данных
- Основная цель: преодоление ограничений реляционных СУБД того времени
- Введены революционные концепции:
- Поддержка пользовательских типов данных
- Объектно-ориентированные возможности
- Правила (Rules) и триггеры
- Временные данные и версионность
1994-1995: Postgres95
- Добавлена поддержка SQL вместо собственного языка запросов POSTQUEL
- Значительное улучшение производительности (на 30-50%)
- Код был полностью переписан на C
- Проект получил название Postgres95
1996-настоящее время: PostgreSQL
- В 1996 году проект переименован в PostgreSQL
- Название отражает поддержку SQL и наследие POSTGRES
- Формирование активного сообщества разработчиков
- Создание PostgreSQL Global Development Group (PGDG)
Важные вехи
| Год | Версия | Ключевые нововведения |
|---|---|---|
| 1996 | 6.0 | Первый релиз как PostgreSQL |
| 2000 | 7.0 | Внешние ключи, подзапросы |
| 2005 | 8.0 | PITR, tablespaces, Windows поддержка |
| 2010 | 9.0 | Репликация, Hot Standby |
| 2013 | 9.3 | JSON, материализованные представления |
| 2014 | 9.4 | JSONB, логическая репликация |
| 2017 | 10 | Логическая репликация, партиционирование |
| 2020 | 13 | Улучшенная индексация, партиционирование |
| 2023 | 16 | Логическая репликация из standby, SQL/JSON |
| 2024 | 17 | Инкрементальные бэкапы, улучшенный MERGE |
Философия проекта
PostgreSQL придерживается следующих принципов:
- Надежность и целостность данных — приоритет на сохранность данных
- Соответствие стандартам SQL — максимальная совместимость со стандартом
- Расширяемость — возможность добавления новых функций без изменения ядра
- Открытый исходный код — лицензия PostgreSQL (BSD-подобная)
- Сообщество — развитие усилиями глобального сообщества
Интересный факт
Майкл Стоунбрейкер, создатель PostgreSQL, получил престижную премию Тьюринга в 2014 году за фундаментальный вклад в концепции и практику современных систем управления базами данных.
Архитектура PostgreSQL
Общая архитектура
PostgreSQL использует клиент-серверную архитектуру с многопроцессной моделью (процесс на соединение).
┌─────────────────────────────────────────────────────────┐
│ Клиентские приложения │
│ (psql, pgAdmin, приложения на Python/Java/PHP и т.д.) │
└────────────────────┬────────────────────────────────────┘
│ TCP/IP, Unix Socket
▼
┌─────────────────────────────────────────────────────────┐
│ PostgreSQL Server │
│ │
│ ┌────────────────────────────────────────────────┐ │
│ │ Postmaster (главный процесс) │ │
│ └──────────────────┬─────────────────────────────┘ │
│ │ │
│ ┌──────────────────┴─────────────────────────────┐ │
│ │ Backend Processes (процессы) │ │
│ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │
│ │ │ Backend │ │ Backend │ │ Backend │ ... │ │
│ │ │ Process │ │ Process │ │ Process │ │ │
│ │ └─────────┘ └─────────┘ └─────────┘ │ │
│ └────────────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────────────┐ │
│ │ Фоновые процессы (Background) │ │
│ │ • WAL Writer (запись журнала) │ │
│ │ • Checkpointer (контрольные точки) │ │
│ │ • Autovacuum (очистка) │ │
│ │ • Stats Collector (статистика) │ │
│ │ • Logical Replication Worker │ │
│ └────────────────────────────────────────────────┘ │
│ │
│ ┌────────────────────────────────────────────────┐ │
│ │ Shared Memory (общая память) │ │
│ │ • Shared Buffer Cache (кэш страниц) │ │
│ │ • WAL Buffers (буферы журнала) │ │
│ │ • Lock Tables (таблицы блокировок) │ │
│ └────────────────────────────────────────────────┘ │
└───────────────────────┬─────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────┐
│ Файловая система │
│ • Data Directory (PGDATA) │
│ • WAL (журнал транзакций) │
│ • Конфигурационные файлы │
└─────────────────────────────────────────────────────────┘
Ключевые компоненты
Postmaster (главный процесс)
Postmaster — это основной серверный процесс PostgreSQL, который:
- Слушает входящие соединения (обычно на порту 5432)
- Аутентифицирует клиентов
- Создает новый backend-процесс для каждого соединения
- Управляет фоновыми процессами
- Обрабатывает аварийные ситуации и восстановление
# Просмотр процессов PostgreSQL
ps aux | grep postgres
# Пример вывода:
postgres 1234 ... /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main
postgres 1235 ... postgres: checkpointer
postgres 1236 ... postgres: background writer
postgres 1237 ... postgres: walwriter
postgres 1238 ... postgres: autovacuum launcher
postgres 1239 ... postgres: user mydb 127.0.0.1(52618) idle
Backend процессы
Каждое клиентское подключение получает отдельный серверный процесс (backend), который:
- Парсит и планирует SQL запросы
- Выполняет запросы
- Взаимодействует с разделяемой памятью
- Возвращает результаты клиенту
- Управляет транзакциями для своего соединения
Жизненный цикл backend-процесса:
-- 1. Клиент подключается
-- 2. Postmaster создает новый backend-процесс
-- 3. Backend выполняет запросы клиента
SELECT * FROM users WHERE id = 1;
-- 4. При закрытии соединения процесс завершается
-- 5. Ресурсы освобождаются
Почему процессы, а не потоки?
PostgreSQL использует процессы вместо потоков для изоляции и стабильности. Если один backend-процесс падает, это не влияет на другие соединения. Это обеспечивает высокую надежность, хотя и требует больше ресурсов.
Разделяемая память (Shared Memory)
Shared Memory — это область памяти, общая для всех процессов PostgreSQL:
Основные компоненты:
- Shared Buffer Cache (разделяемый буферный кэш)
- Хранит страницы данных из таблиц и индексов
- Размер настраивается параметром
shared_buffers - Обычно 25% от RAM сервера
- Реализует алгоритм вытеснения страниц
-- Просмотр размера буферного кэша
SHOW shared_buffers;
-- Результат: 128MB (пример)
-- Статистика попаданий в кэш
SELECT
sum(heap_blks_read) as disk_reads,
sum(heap_blks_hit) as cache_hits,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_ratio
FROM pg_stattuple;
-
WAL Buffers (буферы журнала опережающей записи)
- Временное хранилище для WAL записей
- Размер:
wal_buffers(обычно 16MB)
-
Lock Tables (таблицы блокировок)
- Управление блокировками для конкурентного доступа
- Хранит информацию о текущих блокировках
Фоновые процессы
WAL Writer (процесс записи журнала)
- Записывает WAL буферы на диск
- Обеспечивает долговечность транзакций
- Работает асинхронно для производительности
Checkpointer (процесс контрольных точек)
- Периодически записывает "грязные" страницы из буферного кэша на диск
- Создает контрольные точки для ускорения восстановления
- Частота: параметры
checkpoint_timeoutиcheckpoint_completion_target
Background Writer (фоновый писатель)
- Записывает грязные страницы в фоне
- Снижает нагрузку на checkpointer
- Улучшает производительность записи
Autovacuum Launcher (запускатель автоочистки)
- Автоматически запускает процессы VACUUM
- Очищает мертвые строки (dead tuples)
- Обновляет статистику планировщика
- Предотвращает wraparound transaction ID
Stats Collector (сборщик статистики)
- Собирает статистику использования БД
- Данные для планировщика запросов
- Мониторинг производительности
-- Просмотр активности autovacuum
SELECT * FROM pg_stat_progress_vacuum;
-- Настройка autovacuum
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.1);
Структура хранения данных
Директория данных (PGDATA)
Все данные PostgreSQL хранятся в PGDATA директории:
PGDATA/
├── base/ # Файлы баз данных
│ ├── 1/ # Системная база (template1)
│ ├── 13000/ # Пользовательская БД (OID)
│ │ ├── 16384 # Файл таблицы
│ │ ├── 16384.1 # Продолжение файла (если > 1GB)
│ │ └── ...
│ └── ...
├── global/ # Общие системные таблицы
├── pg_wal/ # Журнал транзакций (WAL)
│ ├── 000000010000000000000001
│ ├── 000000010000000000000002
│ └── ...
├── pg_xact/ # Статусы транзакций
├── pg_multixact/ # Мультитранзакционные данные
├── pg_commit_ts/ # Временные метки коммитов
├── pg_logical/ # Логическая репликация
├── pg_stat/ # Статистика
├── pg_tblspc/ # Символические ссылки на tablespaces
├── postgresql.conf # Главный конфигурационный файл
├── pg_hba.conf # Аутентификация
├── pg_ident.conf # Маппинг пользователей
└── postmaster.pid # PID главного процесса
Организация таблиц и индексов
PostgreSQL хранит данные в страницах (pages) размером 8KB (по умолчанию):
┌─────────────────────────────────────────────┐
│ Страница (8KB) │
├─────────────────────────────────────────────┤
│ Page Header (24 байта) │
│ • pd_lsn (WAL позиция) │
│ • pd_checksum (контрольная сумма) │
│ • pd_flags, pd_lower, pd_upper │
├─────────────────────────────────────────────┤
│ Item Pointers (массив указателей) │
│ • Указывают на строки (tuples) │
├─────────────────────────────────────────────┤
│ Free Space (свободное место) │
├─────────────────────────────────────────────┤
│ Tuples (строки данных) │
│ • Tuple 1 │
│ • Tuple 2 │
│ • ... │
├─────────────────────────────────────────────┤
│ Special Space (для индексов) │
└─────────────────────────────────────────────┘
Структура строки (tuple):
┌─────────────────────────────────────────────┐
│ Tuple Header │
│ • t_xmin (ID транзакции создания) │
│ • t_xmax (ID транзакции удаления) │
│ • t_cid (command ID) │
│ • t_ctid (физическая позиция) │
├─────────────────────────────────────────────┤
│ NULL Bitmap (битовая маска NULL значений) │
├─────────────────────────────────────────────┤
│ Data (фактические данные колонок) │
│ • column1_value │
│ • column2_value │
│ • ... │
└─────────────────────────────────────────────┘
-- Просмотр физического размещения
SELECT ctid, id, name FROM users;
-- ctid показывает (page_number, item_number)
-- Результат: (0,1), (0,2), (1,1) ...
-- Размер таблицы на диске
SELECT
pg_size_pretty(pg_total_relation_size('users')) AS total_size,
pg_size_pretty(pg_relation_size('users')) AS table_size,
pg_size_pretty(pg_indexes_size('users')) AS indexes_size;
MVCC (Multi-Version Concurrency Control)
MVCC — это ключевая особенность архитектуры PostgreSQL для управления конкурентным доступом.
Принцип работы:
- При обновлении строка не перезаписывается, а создается новая версия
- Старая версия помечается как удаленная (устанавливается
t_xmax) - Читатели видят версию, актуальную на момент начала их транзакции
- Записывающие не блокируют читателей
- VACUUM удаляет устаревшие версии
-- Пример MVCC в действии
CREATE TABLE account (id INT, balance NUMERIC);
INSERT INTO account VALUES (1, 1000);
-- Транзакция 1 (начата в момент T1)
BEGIN;
SELECT balance FROM account WHERE id = 1; -- Видит 1000
-- ... транзакция не завершена
-- Транзакция 2 (начата в момент T2, изменяет данные)
BEGIN;
UPDATE account SET balance = 1500 WHERE id = 1;
COMMIT;
-- Транзакция 1 продолжается
SELECT balance FROM account WHERE id = 1; -- Все еще видит 1000!
COMMIT;
-- Теперь все видят новое значение
SELECT balance FROM account WHERE id = 1; -- 1500
Преимущества MVCC:
- ✅ Читатели не блокируют писателей
- ✅ Писатели не блокируют читателей
- ✅ Высокая степень параллелизма
- ✅ Согласованное чтение
Недостатки:
- ❌ Накопление "мертвых" строк
- ❌ Необходимость регулярного VACUUM
- ❌ Дополнительное использование дискового пространства
-- Просмотр мертвых строк
SELECT
schemaname,
relname,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_percentage
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;
-- Ручной запуск VACUUM
VACUUM ANALYZE users;
WAL (Write-Ahead Logging)
WAL — журнал опережающей записи, обеспечивающий долговечность и восстановление.
Принцип работы:
- Перед изменением данных изменение записывается в WAL
- WAL гарантирует, что изменения не потеряются
- При сбое PostgreSQL восстанавливает данные из WAL
- Контрольные точки ограничивают объем восстановления
Изменение данных:
1. Записать в WAL буфер
2. Записать WAL на диск (fsync)
3. Изменить страницу в shared buffers
4. (Позже) Записать страницу на диск
Восстановление после сбоя:
1. Найти последнюю контрольную точку
2. Воспроизвести все записи WAL после нее
3. Восстановить согласованное состояние
-- Настройки WAL
SHOW wal_level; -- minimal, replica, logical
SHOW fsync; -- on (критично для надежности!)
SHOW synchronous_commit; -- on, off, local, remote_write, remote_apply
SHOW wal_buffers;
-- Мониторинг WAL
SELECT * FROM pg_stat_wal;
-- Текущая позиция WAL
SELECT pg_current_wal_lsn();
-- Размер WAL с последней контрольной точки
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), checkpoint_lsn) / 1024 / 1024 AS mb
FROM pg_control_checkpoint();
Важно для надежности
Никогда не отключайте fsync в продакшене! Это единственная гарантия, что данные действительно записались на диск. Отключение fsync допустимо только при начальной загрузке больших объемов данных.
Отличия PostgreSQL от других СУБД
PostgreSQL vs MySQL/MariaDB
| Характеристика | PostgreSQL | MySQL/MariaDB |
|---|---|---|
| Лицензия | PostgreSQL License (BSD-подобная) | GPL (MySQL), GPL v2 (MariaDB) |
| Архитектура | Процесс на соединение | Потоки (threads) |
| MVCC | На уровне строк с версионностью | На уровне строк (InnoDB) |
| Репликация | Синхронная/асинхронная, логическая | Асинхронная, полусинхронная |
| Полнотекстовый поиск | Встроенный, мощный | Базовый (FULLTEXT) |
| JSON | JSON и JSONB с индексами | JSON (без индексации) |
| Транзакции | Полная поддержка ACID | ACID (InnoDB), не все движки |
| Подзапросы | Отличная оптимизация | Исторически слабее |
| Window Functions | Полная поддержка | Добавлены в 8.0 |
| CTE (WITH) | С рекурсией | Поддержка есть |
| Расширения | Богатая экосистема | Ограниченные |
| GIS | PostGIS (лучший в классе) | Spatial Extensions |
| Типы данных | Очень богатые (массивы, диапазоны, геометрия) | Стандартные |
Примеры различий:
-- PostgreSQL: массивы встроены
CREATE TABLE tags (
post_id INT,
tags TEXT[] -- массив строк
);
INSERT INTO tags VALUES (1, ARRAY['postgresql', 'database']);
-- MySQL: нужна отдельная таблица или JSON
CREATE TABLE tags (
post_id INT,
tags JSON
);
INSERT INTO tags VALUES (1, '["postgresql", "database"]');
-- PostgreSQL: оконные функции (с ранних версий)
SELECT
name,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
-- MySQL: поддержка добавлена позже (8.0+)
Когда выбрать PostgreSQL:
- Сложные запросы и аналитика
- Требуется строгая согласованность данных
- Работа с JSON/геоданными/массивами
- Необходимы расширения (PostGIS, TimescaleDB)
- Enterprise-функции без платных версий
Когда выбрать MySQL:
- Простые веб-приложения (LAMP/LEMP стек)
- Большое количество операций чтения
- Уже есть экспертиза команды в MySQL
- Интеграция с продуктами Oracle/MySQL
PostgreSQL vs Microsoft SQL Server
| Характеристика | PostgreSQL | MS SQL Server |
|---|---|---|
| Платформа | Кроссплатформенная | Windows (основная), Linux |
| Стоимость | Бесплатная | Платная (кроме Express) |
| Открытый код | Да | Нет |
| T-SQL vs PL/pgSQL | PL/pgSQL (похож на PL/SQL) | T-SQL (Transact-SQL) |
| Индексы | Множество типов (B-tree, GiST, GIN, BRIN) | B-tree, Columnstore |
| JSON | Отличная поддержка | Хорошая поддержка (с 2016) |
| Расширяемость | Высокая (расширения) | Ограниченная |
| Интеграция | Стандарты открытые | Экосистема Microsoft |
| Производительность | Отличная | Отличная |
| Инструменты | psql, pgAdmin, множество OSS | SSMS (отличный), платные решения |
-- PostgreSQL: RETURNING clause
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com')
RETURNING id, created_at;
-- SQL Server: OUTPUT clause
INSERT INTO users (name, email)
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('John', 'john@example.com');
Когда выбрать PostgreSQL:
- Нужна open-source СУБД enterprise-уровня
- Linux/Mac среда разработки
- Стоимость лицензий критична
- Гибкость и расширяемость важны
Когда выбрать SQL Server:
- Инфраструктура полностью на Windows
- Интеграция с .NET/Azure
- Нужны специфичные функции (SSIS, SSRS, SSAS)
- Есть бюджет на лицензии и поддержку Microsoft
PostgreSQL vs Oracle Database
| Характеристика | PostgreSQL | Oracle Database |
|---|---|---|
| Стоимость | Бесплатная | Очень дорогая |
| Лицензирование | Свободная | Per CPU/Named User |
| Совместимость с Oracle | Хорошая (с расширениями) | Полная (это Oracle) |
| PL/SQL | PL/pgSQL (похож) | PL/SQL (оригинал) |
| Партиционирование | Декларативное (встроено) | Очень мощное (платная опция) |
| RAC | Нет встроенного | Real Application Clusters |
| Экосистема | Open Source | Проприетарная, огромная |
| Поддержка | Сообщество + коммерческие компании | Oracle Corporation |
| Масштабирование | Горизонтальное (расширения) | Вертикальное + RAC |
-- PostgreSQL синтаксис близок к Oracle
CREATE OR REPLACE FUNCTION calculate_bonus(emp_id INT)
RETURNS NUMERIC AS $$
DECLARE
salary NUMERIC;
bonus NUMERIC;
BEGIN
SELECT emp_salary INTO salary FROM employees WHERE id = emp_id;
bonus := salary * 0.1;
RETURN bonus;
END;
$$ LANGUAGE plpgsql;
-- Очень похоже на Oracle PL/SQL
Миграция с Oracle на PostgreSQL:
PostgreSQL предлагает несколько решений для совместимости с Oracle:
- orafce — расширение с Oracle-совместимыми функциями
- EDB Postgres Advanced Server — коммерческая версия с высокой совместимостью
- Инструменты миграции: ora2pg, AWS SCT
Когда выбрать PostgreSQL:
- Хотите снизить TCO (Total Cost of Ownership)
- Достаточно функциональности open-source СУБД
- Готовы адаптировать Oracle-специфичный код
- Новый проект без legacy-кода
Когда остаться на Oracle:
- Критические enterprise-приложения
- Использование специфичных Oracle-функций (RAC, Data Guard)
- Огромные инвестиции в Oracle-экосистему
- Требования к сертифицированным решениям