Databases

PostgreSQL

PostgreSQL — свободная объектно-реляционная СУБД с сильной поддержкой SQL, транзакций и расширений вроде PostGIS.

Что такое PostgreSQL

PostgreSQL (произносится «постгрес-кью-эль», в обиходе — Postgres) — свободная объектно-реляционная СУБД. Развивается сообществом с середины 1990-х, распространяется под либеральной лицензией PostgreSQL License: использовать в коммерческих продуктах можно без ограничений, единого владельца у проекта нет.

Репутация Postgres строится на трёх вещах: строгое следование стандарту SQL, надёжность транзакций и расширяемость. Своими типами, функциями, языками и индексами базу дополняют без правки её кода — на этом построены PostGIS, pgvector, TimescaleDB.

Ключевые возможности

  • MVCC — читатели не блокируют писателей, писатели не блокируют читателей.
  • Типы данных: jsonb с индексами по содержимому, массивы, numeric произвольной точности, диапазоны, uuid, геометрия.
  • Индексы: B-tree, GIN, GiST, BRIN, hash — под разные задачи (подробнее).
  • Оконные функции, CTE, рекурсивные запросы — аналитика без выгрузки данных наружу.
  • Транзакционный DDL — CREATE TABLE внутри транзакции откатывается, что делает миграции безопасными.
  • Логическая и потоковая репликация из коробки.

Быстрый старт

sudo apt install postgresql
sudo -u postgres psql

CREATE DATABASE shop;
CREATE USER app WITH PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE shop TO app;

psql -U app -d shop -h 127.0.0.1   # порт по умолчанию 5432

\l          # список баз
\dt         # таблицы
\d+ users   # структура таблицы
\timing on  # показывать время запросов

Доступ управляется файлом pg_hba.conf — правило host all all 0.0.0.0/0 md5 открывает базу всему интернету, чего делать не стоит: 5432 наружу не выставляют, ограничиваясь приватной сетью или SSH-туннелем.

Базовый тюнинг

Postgres из коробки настроен консервативно и не знает, сколько на сервере памяти:

# postgresql.conf, ориентиры для сервера с 16 ГБ RAM
shared_buffers = 4GB              # ~25% RAM
effective_cache_size = 12GB       # ~75% RAM, подсказка планировщику
work_mem = 32MB                   # на операцию сортировки, не на сессию
maintenance_work_mem = 512MB      # VACUUM, CREATE INDEX
random_page_cost = 1.1            # для NVMe SSD, по умолчанию 4.0 (под HDD)
max_connections = 100

random_page_cost — частая находка: значение по умолчанию рассчитано на диски со шпинделем, и на NVMe планировщик из-за него недооценивает индексы и уходит в Seq Scan.

pgBouncer

pgBouncer — легковесный пулер соединений для PostgreSQL. Нужен потому, что Postgres на каждое соединение порождает отдельный процесс: тысяча соединений от приложения — тысяча процессов и гигабайты RAM. pgBouncer держит небольшой пул реальных соединений к базе и мультиплексирует в него клиентов.

# pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_port = 6432
pool_mode = transaction     # соединение возвращается в пул после COMMIT
max_client_conn = 1000      # клиентов принимаем
default_pool_size = 20      # реальных соединений к Postgres

pool_mode = transaction — обычный выбор для веб-приложений: он даёт лучшее переиспользование, но ломает то, что живёт дольше транзакции (SET, session-level advisory locks, подготовленные выражения без учёта пулера).

PostGIS

PostGIS — расширение, превращающее Postgres в полноценную геоинформационную базу: типы geometry/geography, пространственные индексы на GiST и сотни функций.

CREATE EXTENSION postgis;

-- 10 ближайших точек к координате
SELECT name, ST_Distance(geom, ST_MakePoint(37.62, 55.75)::geography) AS m
FROM places
ORDER BY geom <-> ST_MakePoint(37.62, 55.75)::geography
LIMIT 10;

Stack Builder

Stack Builder — вспомогательная утилита из установщика PostgreSQL для Windows (сборка EDB). После установки базы она предлагает докачать дополнительные компоненты: PostGIS, pgAgent, драйверы ODBC/JDBC, pgJDBC. Отдельной ценности не имеет и на Linux не используется — там всё ставится пакетным менеджером. Если Stack Builder запустился после установки и вам ничего из списка не нужно, окно можно просто закрыть.

Связанные термины

Готовы запустить GPU-задачу?

Запустить GPU-сервер