⚡ Индексы и производительность

Короткий ответ Индекс — это дополнительная структура данных (обычно B-tree), хранящая упорядоченные значения колонки(ок) со ссылками на строки, что позволяет находить нужные строки за O(log n) вместо полного перебора таблицы (Seq Scan). Как это работает подробнее Без индекса СУБД читает все строки таблицы (Sequential Scan), проверяя условие у каждой. Индекс позволяет быстро найти диапазон или точное значение через обход дерева и затем обратиться только к нужным строкам таблицы. Пример кода Что использовать на практике Практика. Индексы ускоряют SELECT с фильтрацией/сортировкой/джойнами по индексируемой колонке, но замедляют INSERT/UPDATE/DELETE, так как индекс нужно поддерживать в актуальном состоянии; план выполнения проверяют через EXPLAIN. Подводные камни Ошибка. Создают индекс «на всякий случай» на каждую колонку, не оценивая рост времени записи и объёма на диске; забывают, что индекс не всегда используется планировщиком при низкой избирательности условия.

Читать полностьюindexes

Короткий ответ Составной B-tree индекс упорядочен лексикографически — сначала по первой колонке, затем по второй внутри одинаковых значений первой, поэтому он эффективно используется для условий по первой колонке (или по обеим), но обычно бесполезен для запроса, фильтрующего только по второй колонке без первой. Как это работает подробнее Структура похожа на отсортированный список пар (a, b) — найти диапазон по a быстро, а внутри найденного диапазона дополнительно быстро сузить по b; без фиксации a СУБД не может перейти сразу к нужным b, они «разбросаны» по дереву. Пример кода Что использовать на практике Практика. Общее правило — ставить первой колонку с более высокой избирательностью или ту, по которой чаще фильтруют в равенстве (=), а колонку с диапазонным условием (<, >, BETWEEN) обычно ставят последней. Подводные камни Ошибка. Создают отдельные индексы на a и на b по отдельности, думая, что это эквивалентно составному индексу (a, b) для запросов с условием на оба поля; меняют порядок колонок «для красоты»

Читать полностьюindexes

Короткий ответ Нужно собрать факты о реальном поведении в production — найти конкретный медленный запрос через pg_stat_statements или log_min_duration_statement, снять его реальный план через EXPLAIN (ANALYZE, BUFFERS) на сопоставимых по объёму данных и сравнить с локальной средой по объёму данных, статистике планировщика и настройкам. Как это работает подробнее pg_stat_statements агрегирует статистику по нормализованным запросам (общее время, число вызовов, среднее время), что помогает найти «тяжёлые» по суммарному вкладу запросы; log_min_duration_statement логирует отдельные запросы, превысившие порог. Пример кода Что использовать на практике Практика. Частая причина разницы между локальной средой и production — объём данных, устаревшая статистика (ANALYZE не выполнялся после массовой загрузки), отсутствующий в локальной БД индекс, конкуренция за ресурсы под реальной нагрузкой. Подводные камни Ошибка. Тестируют гипотезу только через EXPLAIN без ANALYZE — он показывает лишь оценку; делают выводы на локальной

Читать полностьюindexes

Короткий ответ Шифрование на уровне диска/тома защищает данные только «в покое» и полностью прозрачно для SQL-слоя — индексы и запросы работают как обычно, но не защищает от доступа через скомпрометированную учётную запись БД; column-level шифрование через pgcrypto защищает конкретное поле даже от доступа с правами на таблицу, если ключ хранится отдельно от БД, но делает обычный поиск/сравнение/сортировку по этому полю в зашифрованном виде невозможными без расшифровки. Как это работает подробнее pgcrypto предоставляет функции pgp_sym_encrypt/pgp_sym_decrypt, которые шифруют значение перед записью и расшифровывают при чтении с передачей ключа как параметра запроса; хранимое значение — бинарные зашифрованные байты, поэтому обычный индекс на этой колонке бесполезен для поиска по исходному значению. Сравнение Компромисс. TDE/шифрование диска проще внедрить и не влияет на производительность и индексацию, но защищает от более узкого набора угроз; column-level шифрование защищает от более широкого класса угроз, но т

Читать полностьюencryption

Короткий ответ Партиционирование по диапазону дат обычно лучший выбор для таблиц событий/логов, так как совпадает с паттерном доступа (чаще читают/пишут свежие данные) и позволяет дешёво удалять старые данные целыми партициями (DROP PARTITION) вместо построчного DELETE; партиционирование по хешу даёт равномерное распределение без «горячего» диапазона, но не даёт дешёвого способа избавляться от старых данных и не ускоряет диапазонные запросы по времени. Как это работает подробнее PostgreSQL Declarative Partitioning создаёт родительскую таблицу и набор физических партиций-наследников; планировщик использует partition pruning, чтобы при запросе с условием на партиционирующий ключ сканировать только нужные партиции, что также уменьшает объём данных для VACUUM/ANALYZE. Сравнение Компромисс. Range-партиционирование по дате даёт удобное устаревание данных, но требует дисциплины по созданию новых партиций вовремя и миграции существующей большой таблицы на партиционированную структуру (обычно через создание новой табл

Читать полностьюindexes

Короткий ответ Функциональный индекс строится не по значению колонки напрямую, а по результату выражения/функции от неё (например, lower(email)); он нужен, когда запросы фильтруют по результату такого выражения, а не по исходному значению — обычный индекс на email в этом случае не используется планировщиком для условия с lower(email). Как это работает подробнее PostgreSQL может строить B-tree индекс по любому детерминированному выражению, вычисляя и храня в индексе результат для каждой строки; планировщик использует индекс только при точном совпадении формы выражения в условии WHERE. Пример кода Что использовать на практике Практика. Типичный пример — регистронезависимый поиск по email: вместо LOWER(email) = LOWER(:input) с полным сканированием строят CREATE INDEX ON users (lower(email)) и фильтруют по WHERE lower(email) = lower(:input); альтернатива — тип citext. Подводные камни Ошибка. Создают обычный индекс на email, а в запросах используют LOWER(email), из-за чего индекс не применяется; рассинхронизируют

Читать полностьюindexes

Короткий ответ Partial index — это индекс, построенный только для строк, удовлетворяющих условию WHERE, указанному при его создании; его применяют, когда запросы почти всегда фильтруют по одному и тому же подмножеству строк (например, только активные записи), чтобы индекс был меньше и быстрее полного. Как это работает подробнее При создании CREATE INDEX ... WHERE condition PostgreSQL включает в индекс только строки, удовлетворяющие condition, и поддерживает это условие при последующих INSERT/UPDATE; планировщик использует такой индекс только для запросов, чьё условие WHERE логически покрывается условием индекса. Пример кода Что использовать на практике Практика. Типичный кейс — таблица задач с большинством завершённых записей: CREATE INDEX ... WHERE status != 'done' резко уменьшает размер индекса по сравнению с полным индексом на всю таблицу. Подводные камни Ошибка. Пишут запрос с условием, которое СУБД не может сопоставить с условием partial-индекса буквально (например, индекс WHERE status = 'active', а запр

Читать полностьюindexes