Balun.Courses
Бесплатные уроки
Корпоративное обучение
Полезные статьи
Стать преподавателем
Каталог курсов
    •  
    •  
    •  
    •  
    •  
  • website icon
Каталог курсов
Содержание

С вас вопросы, с нас ответы. Часть 14

Отвечаем на популярные вопросы про PostgreSQL
Консультирует:
Олег Мифле,
Team Lead разработки в Т-банке

Содержание

1. Какие бывают индексы...
2. Как проверить, использ..
3. Что такое составной...
4. В каком порядке доб...
5. Что такое MVCC в...
6. Как PostgreSQL предот...

1. Какие бывают индексы в PostgreSQL?

В PostgreSQL есть шесть основных методов доступа к индексам:
  • B-tree — индекс по умолчанию. Подходит для сравнений =, <, >, <=, >=, диапазонов, BETWEEN, IN, а также для сортировки. В большинстве прикладных задач сначала стоит рассматривать именно B-tree.
  • Hash — предназначен только для проверки равенства через оператор =. На практике применяется значительно реже B-tree.
  • GiST — инфраструктура для специализированных индексов. Используется для геометрических типов, диапазонов, полнотекстового поиска, поиска ближайших объектов и других задач, определяемых классом операторов.
  • SP-GiST — подходит для структур, которые естественно разбиваются на непересекающиеся области: деревьев префиксов, quad tree, k-d tree и некоторых геометрических данных.
  • GIN — инвертированный индекс. Обычно применяется для поиска по составным значениям: массивам, jsonb и полнотекстовым документам.
  • BRIN — компактный индекс для очень больших таблиц, в которых значение колонки коррелирует с физическим порядком строк. Типичный пример — таблица событий, добавляемых по времени, с индексом по created_at. PostgreSQL также поддерживает расширение bloom и позволяет реализовывать собственные методы доступа.
Метод доступа не следует путать с формой индекса. Индекс также может быть:
  • уникальным;
  • составным;
  • частичным;
  • построенным по выражению;
  • покрывающим, с дополнительными колонками в INCLUDE.
Например:
CREATE UNIQUE INDEX users_email_idx
    ON users (lower(email));

CREATE INDEX active_orders_idx
    ON orders (created_at)
    WHERE status = 'active';

CREATE INDEX orders_customer_idx
    ON orders (customer_id)
    INCLUDE (status, total);
Индексы по выражениям ускоряют запросы с тем же выражением, частичные индексы содержат только строки, удовлетворяющие условию, а уникальные индексы дополнительно контролируют отсутствие дубликатов.

2. Как проверить, используется ли индекс?

Для этого используется EXPLAIN:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;
Если индекс используется, в плане можно увидеть один из узлов:
  • Index Scan;
  • Index Only Scan;
  • Bitmap Index Scan.
Например:
Index Scan using orders_customer_id_idx on orders
  Index Cond: (customer_id = 42)
Для получения фактического времени выполнения, количества обработанных строк и информации о чтении данных используется:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;
Index Cond показывает условие, которое PostgreSQL применил непосредственно при обходе индекса. Filter означает, что строки сначала были получены, а затем дополнительно отфильтрованы.

Отсутствие индексного сканирования не обязательно является проблемой. PostgreSQL может выбрать Seq Scan, если таблица небольшая, запрос возвращает значительную часть строк или последовательное чтение оценивается дешевле случайных обращений через индекс.

3. Что такое составной индекс?

Составной, или многоколоночный, индекс включает несколько колонок:
CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at);
Такой индекс может быть полезен для запроса:
SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= DATE '2026-01-01';
Для B-tree особенно важны ведущие, то есть расположенные слева, колонки. Индекс (customer_id, created_at) хорошо подходит для условий:
WHERE customer_id = 42
и
WHERE customer_id = 42
  AND created_at >= DATE '2026-01-01'
Но обычно значительно хуже подходит для запроса только по второй колонке:
WHERE created_at >= DATE '2026-01-01'
В современных версиях PostgreSQL планировщик в некоторых случаях может применить оптимизацию skip scan, но проектировать индексы в расчёте только на неё не следует.

4. В каком порядке добавлять поля в составной индекс?

Универсального правила «ставить самую селективную колонку первой» нет. Порядок должен определяться реальными запросами.

Для B-tree обычно используется следующая логика:

1. Сначала идут колонки с условиями равенства.
2. Затем колонка с диапазонным условием: <, >, BETWEEN.
3. Затем учитываются колонки из ORDER BY.
4. Колонки, которые нужны только для возврата результата, можно поместить в INCLUDE.

Например, для запроса:
SELECT id, total
FROM orders
WHERE customer_id = $1
  AND status = 'paid'
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;
может подойти индекс:
CREATE INDEX orders_customer_status_created_idx
    ON orders (customer_id, status, created_at DESC)
    INCLUDE (total);
Если две колонки всегда проверяются на равенство, их порядок для конкретного запроса часто не имеет большого значения. Но он влияет на то, какие другие запросы смогут использовать левый префикс индекса.

Например, индекс:
(customer_id, status, created_at)
может обслуживать запросы по customer_id, но индекс:
(status, customer_id, created_at)
не так удобен для запросов только по customer_id.

Поэтому среди колонок с равенством первой обычно ставят ту, которая чаще используется самостоятельно или является обязательным ограничителем, например tenant_id. Равенства на ведущих колонках и диапазон на первой колонке без равенства позволяют максимально ограничить просматриваемую часть B-tree.

5. Что такое MVCC в PostgreSQL?

MVCC расшифровывается как Multiversion Concurrency Control — многоверсионное управление конкурентным доступом.

PostgreSQL может одновременно хранить несколько версий одной логической строки. При изменении данных старая версия не удаляется немедленно: создаётся новая версия, а предыдущая некоторое время сохраняется для транзакций, которые ещё могут её видеть.

Каждый SQL-запрос работает со снимком данных — snapshot. Поэтому он видит согласованное состояние базы, соответствующее правилам текущего уровня изоляции, а не случайную смесь изменений разных транзакций.

Основное преимущество MVCC заключается в том, что обычное чтение не конфликтует с обычной записью:

  • SELECT может читать старую видимую версию строки;
  • UPDATE создаёт новую версию;
  • незавершённые изменения другой транзакции не становятся видимыми раньше времени.

Таким образом PostgreSQL уменьшает количество конфликтующих блокировок между читателями и писателями. При этом конкурирующие изменения одной и той же строки по-прежнему могут блокировать друг друга.

6. Как PostgreSQL предотвращает блокировки?

PostgreSQL не предотвращает блокировки полностью. Он уменьшает их количество с помощью MVCC.

В системах, основанных преимущественно на блокировках, читателю может потребоваться ждать писателя, а писателю — читателя. В PostgreSQL обычный читатель получает подходящую версию строки из своего snapshot, поэтому чтение обычно не блокирует запись, а запись — чтение.
Однако блокировки всё равно возникают:

  • два UPDATE одной строки конфликтуют;
  • SELECT FOR UPDATE блокирует выбранные строки;
  • некоторые DDL-команды блокируют таблицу;
  • TRUNCATE, DROP TABLE, VACUUM FULL и ряд вариантов ALTER TABLE получают ACCESS EXCLUSIVE;
  • длительные транзакции долго удерживают уже полученные блокировки.

Даже обычный SELECT получает табличную блокировку ACCESS SHARE, но она совместима почти со всеми другими режимами. Обычное чтение таблицы блокирует только ACCESS EXCLUSIVE.

задай вопрос, а мы ответим

другие статьи


    Здесь ты найдешь подборку материалов для изучения PostgreSQL. А если ты хочешь изучить все тонкости за 1,5 месяца, приходи на наш курс по PostgreSQL: изучаем все тонкости на реально встречающихся кейсах — от тормозящих запросов и масштабирования БД до проблем
с индексами и транзакциями.

    Вот некоторые кейсы наших студентов, которые учились в нашей школе:

    Рекомендуем также изучить и другие полезные материалы: