Контакт

База данных PostgreSQL: полный обзор, плюсы, минусы и пределы

Для чего нужна база данных PostgreSQL, где она лучший выбор, а где выигрывает другая: плюсы и минусы, сравнение с MySQL, SQLite, MongoDB и ClickHouse, расширения, примеры SQL, пределы и советы.

Стек и технологии Обновлено

Коротко

PostgreSQL — бесплатная база данных с открытым кодом и строгим отношением к данным: транзакции, ограничения и одна из самых полных реализаций SQL. Кроме обычных таблиц она хранит JSON с индексами, геоданные (PostGIS), векторы для ИИ-поиска (pgvector) и временные ряды (TimescaleDB) — одна база часто закрывает то, для чего раньше ставили три. Это выбор по умолчанию для бэкенда веб-сервисов и SaaS, интернет-магазинов и финансов. Слабые места: каждое подключение — отдельный процесс (нужен пулер подключений), обновления оставляют старые версии строк, которые убирает VACUUM, а аналитику по миллиардам строк и горизонтальное масштабирование записи лучше отдать другим системам.

PostgreSQL коротко: паспорт базы

Главные факты одной таблицей: откуда база взялась, как она бережёт данные и как развивается.

Тип
Объектно-реляционная система управления базами данных с открытым кодом
История
Проект POSTGRES в Беркли под руководством Майкла Стоунбрейкера с 1986 года; SQL — с 1995-го; имя PostgreSQL — с 1996-го
Разработка
PostgreSQL Global Development Group — сообщество; у проекта нет компании-владельца
Лицензия
PostgreSQL License — свободная, в том числе для коммерции
Транзакции
ACID и MVCC: чтение не блокирует запись
SQL
Одна из самых полных реализаций: CTE, оконные функции, MERGE, SQL/JSON
JSON
jsonb с 2014 года: двоичный JSON с индексами
Индексы
B-tree, Hash, GIN, GiST, SP-GiST, BRIN; частичные и по выражению
Расширения
PostGIS, pgvector, TimescaleDB, Citus и сотни других
Репликация
Потоковая — с 2010 года, логическая — с 2017-го
Релизы
Мажорная версия раз в год, поддержка пять лет; исправления — не реже раза в квартал
Популярность
С 2023 года — самая используемая база у разработчиков в опросе Stack Overflow

Для чего применяется PostgreSQL: 8 сфер

Основа — классические транзакционные данные, но расширения превратили PostgreSQL в платформу. Под каждой сферой — инструменты и расширения, на которых она держится.

  1. 01

    Бэкенд веб-сервисов и SaaS

    Пользователи, подписки, заказы и права: данные со строгими связями между таблицами. Её поддерживает любой популярный фреймворк.

    DjangoRailsLaravelPrisma

  2. 02

    Интернет-магазины и платежи

    Транзакции гарантируют, что деньги и остатки не разойдутся: операция сохраняется целиком или не сохраняется вовсе.

    ACIDSERIALIZABLECHECK

  3. 03

    Геоданные и карты

    «Ближайшие магазины», зоны доставки, маршруты: PostGIS — стандарт для пространственных данных.

    PostGISGiST

  4. 04

    Поиск по сайту

    Полнотекстовый поиск с учётом словоформ и ранжированием и поиск с опечатками — без отдельного поискового движка.

    tsvectorpg_trgm

  5. 05

    Векторный поиск и ИИ

    Эмбеддинги рядом с данными, которые они описывают: смысловой поиск и RAG в той же базе, где товары и пользователи.

    pgvectorHNSW

  6. 06

    Временные ряды и метрики

    Показания датчиков, события, цены: разбиение по времени и сжатие для многолетней истории.

    TimescaleDBBRIN

  7. 07

    Документы и JSON

    Гибкие атрибуты, настройки и ответы внешних сервисов в jsonb — с индексами, рядом со строгими колонками.

    jsonbGIN

  8. 08

    Очереди задач

    Фоновые задачи, письма и вебхуки без отдельного брокера: воркеры разбирают задачи, не мешая друг другу.

    SKIP LOCKEDRiverObanpgmq

Плюсы и минусы PostgreSQL

PostgreSQL ставит правильность данных на первое место. Отсюда большинство её сильных сторон — и большая часть настройки, которая ей нужна.

Плюсы · 8

  • Надёжные транзакции

    ACID, журнал упреждающей записи и восстановление на любой момент времени: после сбоя база поднимается согласованной.

  • Богатый SQL

    CTE, оконные функции, LATERAL, upsert и MERGE: отчёты и сложная логика пишутся в базе, а не циклами в коде.

  • JSON с индексами

    jsonb даёт гибкость документов, не отнимая транзакций, связей и ограничений.

  • Расширения

    PostGIS, pgvector, TimescaleDB, pg_cron: новые возможности подключаются в ту же базу и тот же SQL.

  • Индекс на любой случай

    Частичные и по выражению, GIN для JSON и текста, BRIN для огромных таблиц, упорядоченных по времени.

  • Строгие данные

    Типы, внешние ключи, ограничения CHECK и UNIQUE не пускают плохие данные на входе, а не в отчёте через месяц.

  • Нет привязки к вендору

    Свободная лицензия и сообщество вместо компании-владельца: ни платы за лицензии, ни риска, что продукт закроют.

  • Есть в любом облаке

    AWS, Google Cloud, Azure, Supabase, Neon и локальные облачные провайдеры дают её как сервис с резервными копиями и репликами из коробки.

Минусы · 8

  • Процесс на подключение

    Каждое подключение стоит памяти, а по умолчанию их всего 100. Сотням веб-процессов нужен пулер вроде PgBouncer.

  • VACUUM и раздувание

    Обновление пишет новую версию строки, старую убирает автовакуум. Если он не успевает, таблицы и индексы разбухают.

  • Масштабирование записи

    Чтение масштабируется репликами, а вся запись идёт на один главный сервер. Шардинг — это Citus или своя логика.

  • Скромные настройки по умолчанию

    Из коробки база настроена под слабую машину: shared_buffers, work_mem и автовакуум нужно выставить под реальную нагрузку.

  • Переход на новую версию — с планом

    Минорные обновления простые, а мажорная версия требует pg_upgrade или логической репликации, и каждое расширение должно её поддерживать.

  • Строчное хранение для аналитики

    Пройтись по миллиардам строк ради пары колонок медленно — для этого созданы колоночные базы вроде ClickHouse.

  • Дорогие частые обновления

    Из-за версий строк таблица, где одни и те же строки меняются тысячи раз в секунду, создаёт много записи и работы по уборке.

  • Нужен присмотр администратора

    Резервные копии, мониторинг, медленные запросы и рост диска требуют регулярного внимания — или управляемого сервиса, который делает это за вас.

PostgreSQL и другие базы: сравнение с MySQL, SQLite, MongoDB и ClickHouse

Качественное сравнение с базами, с которыми PostgreSQL выбирают чаще всего. Точные цифры зависят от данных и запросов, поэтому в таблице — взаимное положение, а не бенчмарки.

КритерийPostgreSQLMySQLSQLiteMongoDBClickHouse
Модель данных таблицы плюс JSON, геоданные, векторы таблицы таблицы в одном файле документы колоночные таблицы
Транзакции полный ACID ACID с InnoDB ACID, один писатель за раз многодокументные с версии 4.0 ограниченные
Язык запросов самый богатый SQL SQL, возможностей меньше SQL свой язык запросов диалект SQL для аналитики
JSON jsonb с индексами GIN тип JSON, индексы по выражениям функции для JSON родной формат тип JSON
Масштабирование реплики на чтение; шардинг через Citus реплики, зрелые инструменты одна машина встроенный шардинг кластеры с шардами
Аналитика на миллиардах строк медленно без расширений медленно не для этого средне лучше всех
Эксплуатация сервер, нужны настройка и пулер сервер, легко начать сервера нет вовсе сервер или кластер сервер или кластер
Лицензия PostgreSQL License, свободная GPL, принадлежит Oracle общественное достояние SSPL, не открытая Apache 2.0
Сильнее всего в веб-сервисах, SaaS, деньгах, смешанных данных классических сайтах и CMS приложениях, прототипах, небольших сайтах документах с меняющейся структурой событиях, логах, аналитике

Когда брать PostgreSQL, а когда нет

Тринадцать типичных задач с вердиктом. Где PostgreSQL не лучший выбор, названа альтернатива.

  • Бэкенд веб-сервиса или SaaS

    Лучший выбор

    Выбор по умолчанию: строгие данные, транзакции и поддержка в любом фреймворке.

  • Интернет-магазин, платежи, учёт

    Лучший выбор

    Транзакции и ограничения держат деньги и остатки согласованными.

  • Геоданные и карты

    Лучший выбор

    PostGIS — отраслевой стандарт пространственных запросов.

  • Таблицы плюс гибкий JSON

    Лучший выбор

    jsonb с индексами избавляет от отдельной документной базы.

  • Векторный поиск для RAG

    Лучший выбор

    pgvector справляется с миллионами векторов рядом с данными, которые они описывают.

  • Поиск по сайту

    Подходит

    Встроенного полнотекстового поиска хватает большинству сайтов; для сложной релевантности — Elasticsearch или Meilisearch.

  • Очередь задач

    Подходит

    SKIP LOCKED хватает до тысяч задач в секунду; дальше — RabbitMQ, NATS или Kafka.

  • Временные ряды

    Подходит

    С TimescaleDB — да; при огромном потоке записи смотрите на ClickHouse.

  • Простой контентный сайт

    Подходит

    Подойдёт, но CMS на MySQL или SQLite проще в эксплуатации.

  • Аналитика по миллиардам событий

    Другой язык

    ClickHouse: колоночное хранение здесь в разы быстрее.

  • Кеш и сессии

    Другой язык

    Redis или Valkey: данные в памяти, доступ за микросекунды.

  • База внутри приложения или устройства

    Другой язык

    SQLite: один файл, без сервера.

  • Хранение файлов и картинок

    Другой язык

    Объектное хранилище вроде S3; в базе — только ссылка и описание.

Экосистема PostgreSQL: инструменты для частых задач

Многое встроено, остальное — расширения и отдельные инструменты. Средняя колонка — то, что идёт вместе с самой PostgreSQL.

ЗадачаВстроеноРасширения и инструменты
Пул подключений — PgBouncer, PgCat
Резервные копии pg_dump, pg_basebackup pgBackRest, Barman, WAL-G
Отказоустойчивость streaming replication Patroni, CloudNativePG
Статистика запросов pg_stat_statements pgBadger, postgres_exporter
Планы запросов EXPLAIN ANALYZE, auto_explain explain.dalibo.com
Геоданные — PostGIS
Векторы — pgvector
Временные ряды partitioning TimescaleDB
Шардинг partitioning, postgres_fdw Citus
Полнотекстовый поиск tsvector, pg_trgm ParadeDB
Задачи по расписанию — pg_cron
Очереди SKIP LOCKED, LISTEN/NOTIFY pgmq, River, Graphile Worker
Миграции схемы — Flyway, Liquibase, Atlas, goose
Администрирование psql pgAdmin, DBeaver, DataGrip

Пределы PostgreSQL: где она упирается

  1. Тысячи прямых подключений

    Если веб-сервер может открыть больше процессов, чем база принимает подключений, пик трафика кладёт все сайты на ней разом. Решается пулером перед базой.

  2. Горячие строки, которые обновляют без конца

    Счётчики, балансы и статусы, которые меняются тысячи раз в секунду, плодят мёртвые версии строк быстрее, чем их убирает автовакуум. Обновляйте пачками или вынесите счётчики в Redis.

  3. Аналитика по миллиардам строк

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

  4. Один сервер на всю запись

    Когда один главный сервер уже не вытягивает запись, следующий шаг — шардинг через Citus или разделение данных по сервисам. Это серьёзный проект.

  5. Файлы внутри базы

    В поле помещается до 1 ГБ, но картинки и документы в таблицах раздувают резервные копии и реплики. Файлы — в объектное хранилище.

  6. Изменение схемы больших таблиц

    Часть операций ALTER TABLE блокирует таблицу или переписывает её целиком. На таблицах в сотни миллионов строк миграции требуют плана и тайм-аута блокировки.

8 советов, как работать с PostgreSQL без шишек

  1. 01

    Пулер подключений с первого дня

    PgBouncer в режиме транзакций позволяет сотням веб-процессов делить несколько десятков настоящих подключений.

  2. 02

    Включите pg_stat_statements

    Он показывает, какие запросы суммарно съедают больше всего времени, — с них и начинать оптимизацию, а не с догадок.

  3. 03

    EXPLAIN перед индексом

    EXPLAIN (ANALYZE, BUFFERS) показывает настоящий план и куда уходит время. Индекс, добавленный наугад, может так и не использоваться.

  4. 04

    Индексы под запросы, а не на всякий случай

    Каждый индекс замедляет запись и занимает диск. Неиспользуемые удаляйте — статистика их показывает.

  5. 05

    Автовакуум настраивать, а не отключать

    Для больших и нагруженных таблиц снижайте пороги, чтобы уборка шла чаще и небольшими порциями.

  6. 06

    Проверяйте копии восстановлением

    pgBackRest с архивом WAL даёт восстановление на любой момент. Копия, которую никто не восстанавливал, — это только надежда.

  7. 07

    Миграции без долгих блокировок

    CREATE INDEX CONCURRENTLY, короткий lock_timeout и большие изменения по шагам — и сайт не встаёт.

  8. 08

    Правильные типы

    timestamptz для времени, numeric для денег, text с CHECK вместо varchar(n), identity-колонки вместо serial.

Как выглядит PostgreSQL: 3 примера SQL

Три примера к главным плюсам PostgreSQL: строгие таблицы с JSON, отчёты прямо в базе и очередь задач без отдельного брокера. Проверены на PostgreSQL 16.

Строгие колонки и JSON в одной таблице

Ограничения охраняют поля с известными правилами, jsonb хранит остальное, а индекс GIN находит заказы по любому ключу внутри JSON.

orders.sql 
-- заказ: строгие колонки там, где правила известны, и JSON для остального
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    total       numeric(12, 2) NOT NULL CHECK (total >= 0),
    status      text NOT NULL DEFAULT 'new',
    details     jsonb NOT NULL DEFAULT '{}',
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- индекс по любым полям внутри JSON
CREATE INDEX orders_details_idx ON orders USING gin (details);

-- заказы с доставкой курьером
SELECT id, total
FROM orders
WHERE details @> '{"delivery": "courier"}';

Отчёт и upsert прямо в базе

Оконная функция ранжирует клиентов внутри каждого месяца одним запросом, а ON CONFLICT вставляет строку или обновляет существующую за одну операцию.

reports.sql 
-- выручка по месяцам и место клиента внутри месяца
SELECT date_trunc('month', created_at) AS month,
       customer_id,
       sum(total) AS revenue,
       rank() OVER (PARTITION BY date_trunc('month', created_at)
                    ORDER BY sum(total) DESC) AS place
FROM orders
GROUP BY 1, 2
ORDER BY month, place;

-- остаток на складе: вставить или прибавить к существующему одной командой
INSERT INTO stock (sku, qty) VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

Очередь задач без брокера

Каждый воркер берёт одну задачу из очереди; благодаря SKIP LOCKED остальные её пропускают, а не ждут, и одну задачу никогда не возьмут дважды.

queue.sql 
-- воркер забирает следующую задачу; соседние воркеры её пропустят, а не будут ждать
WITH next AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET status = 'running', started_at = now()
FROM next
WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;

Вопросы о PostgreSQL

Что такое PostgreSQL простыми словами?

Бесплатная база данных, которая хранит данные приложения в таблицах и гарантирует, что они не потеряются и не испортятся. На ней работают сайты, онлайн-сервисы, магазины и банки — от маленьких проектов до очень больших.

Как правильно произносить PostgreSQL?

«Постгрес-кью-эль». Короткое название Postgres тоже официально принято.

PostgreSQL или MySQL?

Для нового веб-сервиса или SaaS PostgreSQL обычно лучше: богаче SQL, JSON с индексами, расширения и строже данные. MySQL проще на старте и подходит для классических сайтов и CMS, где она уже стандарт.

Можно ли заменить MongoDB на PostgreSQL?

В большинстве проектов — да: jsonb с индексами GIN хранит документы и ищет по ним, сохраняя транзакции и связи. MongoDB остаётся сильнее там, где нужен встроенный шардинг огромных коллекций документов.

PostgreSQL бесплатная?

Да, полностью, в том числе для коммерции. Она распространяется по лицензии PostgreSQL License, похожей на MIT и BSD; платить приходится только за серверы или управляемый сервис.

Сколько данных выдержит PostgreSQL?

Терабайты на одном сервере — обычное дело. С размером блока по умолчанию таблица может вырасти до 32 ТБ, одно поле — до 1 ГБ; за пределами одной машины используют шардинг через Citus.

Нужен ли Redis, если есть PostgreSQL?

На старте часто нет: PostgreSQL справится с очередями, простым кешем и сессиями. Redis или Valkey окупаются для горячих счётчиков, ограничения частоты запросов и кеша с доступом за микросекунды.

Подходит ли PostgreSQL для аналитики?

Для отчётов по миллионам и десяткам миллионов строк — да, благодаря оконным функциям и параллельным запросам. Для миллиардов событий колоночная база вроде ClickHouse намного быстрее.

Форма

Базы данных
на PostgreSQL

Работаю с PostgreSQL в своих проектах: проектирую схему под задачу, ускоряю медленные запросы, настраиваю индексы, резервные копии и подключения. Расскажите о задаче — отвечу в течение рабочего дня.

Или пишите на [email protected]