Неочевидные для начинающих тонкости Postgres

Добрую часть десятилетия я профессионально занимаюсь веб-приложениями, и за этот срок научилась пользоваться множеством разных систем и инструментов.

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

За исключением… Postgres.

Не потому, что официальная документация несовершенна (она прекрасна!), просто её очень много. Если распечатать её как стандартный PDF на бумаге формата Letter, то получится 3200 страниц (для текущей версии 17)¹. Разработчик-джун вряд ли сможет сесть и прочитать её с начала до конца.

^{1. Если печатать на бумаге A4, то получится 3024 страницы; наверно, это ещё один аргумент в пользу Letter.}

Поэтому мне захотелось каталогизировать ту информацию, которая бы очень пригодилась мне, когда я только начинала работать с базой данных Postgres.

Надеюсь, это упростит процесс освоения для читателей моей статьи.

Стоит отметить, что многое из описанного может быть применимо и к другим системам управления базами данных (СУБД) SQL и вообще к базам данных в целом, но я слабо знакома с ними, так что не буду утверждать с уверенностью.

Нормализуйте данные, если только нет веских причин не делать этого

Нормализация баз данных — это процесс удаления дублирующихся или избыточных данных из схемы базы данных. Например, допустим, у вас есть сайт, на который пользователи могут загружать документы и подписываться на уведомления по электронной почте о том, что их документы просматривают другие. Если в базе данных есть таблица documents, то не следует добавлять в неё столбец user_email: если пользователь захочет поменять электронную почту, то вы не должны обновлять сотни строк для всех загруженных им документов. Вместо этого каждая строка в documents должна обозначать строку в другой таблице (например, users) при помощи внешнего ключа (например, user_id).

Если поискать онлайн информацию по запросу «нормализация баз данных» («database normalization»), то можно найти кучу результатов о «первой нормальной форме» («1st normal form») и тому подобном. Вам необязательно знать все эти «нормальные формы», но неплохо в общих чертах знать процесс в целом, потому что это позволит вам создать схему базы данных, которую будет удобно поддерживать.

Иногда наличие избыточных данных (то есть денормализация схемы) может иметь смысл: обычно оно используется для ускорения чтения определённых данных, чтобы их не нужно быть вычислять заново при каждом новом запросе. Допустим, у вас есть приложение для управления сменами сотрудников в пекарне. Пользователю может потребоваться узнать, сколько часов он проработал в этом году.

Чтобы вычислить это, нам нужно, например, определить длительность его смен, а затем суммировать все эти часы. Логично будет вместо этого вычислять эту сумму периодически или при изменении количества отработанных часов. Эти данные можно денормализовать внутри базы данных Postgres или снаружи (например, в слое кэширования наподобие Redis). Следует учитывать, что за денормализацию данных всегда приходится расплачиваться, например, возможной несогласованностью данных или повышением сложности записи.

Прислушивайтесь к советам разработчиков Postgres

В официальной Postgres wiki есть длинный список с говорящим названием «Don’t do this» («Не делай этого»). Возможно, вы поймёте не все пункты этого списка. Это нормально! Если вы не понимаете, то, вероятно, и не совершите этой ошибки. Вот некоторые рекомендации, которые стоят упоминания:

1. Просто используйте тип text для хранения всего текста.
2. Просто используйте тип timestampz/time with time zone для хранения всех меток времени.
3. Давайте имена таблицам в snake_case.

Изучите особенности SQL в целом

▍ Пожалейте свои мизинцы: необязательно писать все ключевые слова SQL заглавными буквами

В большинстве документации и туториалов SQL пишут вот так:

SELECT * FROM my_table WHERE x = 1 AND y > 2 LIMIT 10;

На самом деле, регистр для ключевых слов SQL не важен. Это значит, что показанный выше пример эквивалентен такому:

select * from my_table where x = 1 and y > 2 limit 10;

или такому:

SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;

Насколько я знаю, это справедливо не только для Postgres. Ваши мизинцы будут вам благодарны.

▍ NULL — это странная штука

Вероятно, по другим языкам программирования вы знакомы со значением null или nil. NULL из SQL непохож на них. На самом деле, NULL ближе к «неизвестно». Например, NULL = NULL возвращает NULL (потому что неизвестно, равно ли одно неизвестное другому неизвестному!). Это справедливо практически для всех операторов, а не только для = (чуть позже мы рассмотрим некоторые исключения): если одна часть сравнения имеет значение NULL, то результат будет NULL.

Есть несколько операторов, применяемые для сравнения с NULL, результатом которых не является NULL:

Оператор WHERE выполняет сопоставление, только если условие равно true. Это значит, что запрос вида SELECT * FROM users WHERE title != 'manager' не вернёт строки, в которых title равен NULL, потому что NULL != 'manager' — это NULL.

Ещё одна полезная функция для обуздания NULL — это COALESCE: COALESCE получает любое количество аргументов и возвращает первый, не являющийся NULL:

COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

Можно сделать psql более полезным

▍ Исправление нечитаемого вывода

Вам когда-нибудь доводилось выполнять запрос к таблице со множеством столбцов или длинными значениями в этих столбцах, из-за чего вывод оказывался практически нечитаемым? Вероятно, так было потому, что у вас не включён пейджер. Терминальный пейджер позволяет просматривать файл (или таблицу, в случае psql) текста скроллингом окна просмотра по холсту большего размера. Без пейджера вывод просто сбрасывает текст в терминал, выполняя перенос каждый раз, когда заканчивается пространство.

less — это удобный пейджер для любой Unix-подобной системы. Можно настроить его в качестве своего пейджера, установив переменную окружения в ~/.bashrc/~/.zshrc/etc.:

# Используем опцию "-S", чтобы обрезать длинные строки для скроллинга вместо их переноса
export PAGER='less -S'

Иногда даже просмотр в виде удобно отформатированной таблицы не очень полезен, особенно в случае таблиц из кучи столбцов. В таком случае можно переключиться в «расширенный» режим при помощи \pset expanded (или \x) в сессии psql. Чтобы использовать его по умолчанию, можно добавить в исходный каталог (например, ~/.psqlrc) файл .psqlrc и ввести в него \x. При каждом запуске сессии psql он сначала будет выполнять все команды из этого файла.

▍ Уточнение двусмысленных null

Очень важно знать, когда в выводе значение равно NULL, но при настройках по умолчанию это не особо понятно. Можно передать psql строку для вывода, когда он обращается к NULL. Я настроила её как [NULL], выполнив

\pset null '[NULL]'

Подойдёт любая строка в Unicode!

Если вы хотите использовать её по умолчанию, то тоже можно создать в исходном каталоге файл .psqlrc (например, ~/.psqlrc) и добавить в него \pset null '[NULL]'.

▍ Использование автозаполнения

psql, как и многие интерактивные консоли, имеет возможность автозаполнения. Она полезна, ведь SQL — достаточно строгий и структурированный язык. Можно просто начать вводить большинство ключевых слов или имён таблиц, а затем нажать Tab, чтобы psql заполнил остальное:

-- начинаем вводить "SEL"
SEL
-- ^ нажимаем `Tab`
SELECT

▍ Использование кратких команд с обратной косой чертой

В psql есть целая куча полезных кратких команд для поиска, редактирования командной строки и тому подобного.

Таких команд очень много, в таблице представлена лишь малая доля.

▍ Копирование в CSV

Иногда нужно передать вывод команды кому-то для ввода его в Excel или другую программу. В Postgres можно очень легко копировать вывод любого запроса в CSV на локальной машине:

\copy (select * from some_table) to 'my_file.csv' CSV

^{2. Это позволяет избежать использования более стандартного выражения COPY: к сожалению, оно часто требует повышенных привилегий, которых у вас может и не быть.}

Если вы хотите, чтобы была добавлена начальная строка со всеми именами столбцов, то можно указать опцию HEADER:

\copy (select * from some_table) to 'my_file.csv' CSV HEADER

Гораздо более полную информацию (в том числе и о том, как выполнить обратную операцию: вставить строки из данных в CSV!) можно найти в документации по этой команде.

▍ Использование сокращений и псевдонимов столбцов

При выполнении оператора SELECT в psql можно переименовать каждый выводимый столбец (дать ему «псевдоним») командой AS:

SELECT vendor, COUNT(*) AS number_of_backpacks FROM backpacks GROUP BY vendor ORDER BY number_of_backpacks DESC;

Она также переименовывает столбец в выводе.

Более того, GROUP BY и ORDER BY тоже имеют свои удобные сокращения: после SELECT можно ссылаться на столбцы в выводе по их порядковому номеру. То есть предыдущий запрос можно переписать так:

SELECT vendor, COUNT(*) AS number_of_backpacks FROM backpacks GROUP BY 1 ORDER BY 2 DESC;

Хоть это и полезно, не оставляйте это в запросах, которые вы выпускаете в продакшен — в будущем вы будете себе благодарны за это!

Может оказаться, что добавление индекса ничего не делает (особенно если он неправильно сконфигурирован)

▍ Что такое индекс?

Индекс — это структура данных, которая должна помогать в поиске данных, позволяя Postgres хранить «каталог ярлыков» строк таблицы.

Самый распространённый вид — это индекс «B-дерево», который представляет собой разновидность дерева поиска, работающее и для условий точного равенства (например, WHERE a = 3), и для условий-диапазонов (например, WHERE a > 5).

Однако нельзя попросить Postgres использовать конкретный индекс. Она должна предсказывать (при помощи статистики, которая ведётся для каждой таблицы), что это будет быстрее, чем просто прочитать таблицу сверху вниз для поиска релевантных данных (это называется «sequential scan» или «seq. scan» — произносится как «seek scan»). Вы можете увидеть, как Postgres планирует исполнить запрос, добавив EXPLAIN перед SELECT ... FROM .... Так вы получите «план запроса» («query plan»): план того, как Postgres будет искать данные, и её оценка того, сколько работы потребует каждая задача.

Существует множество хороших руководств по тому, как читать вывод этих планов запросов, например, статья thoughtbot или пост pganalyze. Хорошим источником может быть и официальная документация (хотя для новичка там может быть чересчур много информации). Я считаю, что для анализа планов запросов часто бывает полезен этот инструмент.

▍ Индекс не особо полезен для таблицы, в которой почти нет строк

Это в особенности важно при разработке собственной локальной базы данных.
Есть вероятность, что в локальной базе данных у вас не миллионы строк.
Postgres может определить, что быстрее просто выполнить seq. scan и вообще не использовать индекс, если там всего сто строк.

▍ При индексировании множества столбцов важен порядок

Postgres поддерживает многостолбцовые индексы, выполняющие именно то, что и можно от них ожидать: при создании индекса столбцов a и b подобным образом:

CREATE INDEX CONCURRENTLY ON tbl (a, b);

оператор WHERE вида

SELECT * FROM tbl WHERE a = 1 AND b = 2;

будет быстрее, чем если для a и b были созданы два отдельных индекса. Это вызвано тем, что при одном многостолбцовом индексе Postgres достаточно обойти одно B-дерево, что позволяет эффективно комбинировать ограничения поискового запроса.

Этот индекс ускоряет запросы, выполняющие фильтрацию a, так же, как индекс для одного a.

Но как насчёт запроса вида SELECT * FROM tbl WHERE b = 5;? Будет ли он тоже быстрее? Возможно, но не настолько быстрым, насколько мог бы быть. Оказывается, этот индекс не избавляет от необходимости отдельного индекса b. Это вызвано тем, что B-дерево в индексе сначала использует ключи a, а затем уже ключи b. То есть нам придётся обойти все значения a в индексе, чтобы найти все значения b в индексе. Часто при необходимости использования в запросах комбинаций столбцов стоит иметь индексы для (a, b), а также отдельно для b. Тем не менее, при необходимости можно будет использовать индексы a и b по отдельности.

▍ При сопоставлении префиксов используйте text_pattern_ops

Допустим, вы храните в своей базе данных иерархическую систему каталогов при помощи техники materialized path (при которой в каждой строке хранится список всех id предков строк) и в какой-то части приложения вам нужно получить все каталоги-потомки. То есть вам нужен запрос к таблице для нахождения всех строк, для которых столбец соответствует какому-то общему префиксу:

-- % - это подстановочный символ: оператор "WHERE" здесь запрашивает "directories", в которых "path" начинается с "/1/2/3/"
SELECT * FROM directories WHERE path LIKE '/1/2/3/%'

Чтобы ускорить работу, вы добавляете индекс столбца path таблицы directories:

CREATE INDEX CONCURRENTLY ON directories (path);

К сожалению, он может и не использоваться: большинство типов индексов (в том числе и индекс «B-дерево», косвенно создаваемый в выражении CREATE INDEX выше) для своей работы требует упорядочивания значений. Чтобы Postgres могла выполнить простую посимвольную сортировку, необходимую для подобного сопоставления префиксов и для сопоставления паттерна в целом, вам нужно при определении индекса указать другой «класс оператора»:

CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

Долго удерживаемые блокировки могут поломать ваше приложение (даже ACCESS SHARE)

▍ Что такое блокировка?

Блокировка («lock») или мьютекс («mutex», сокращение от «mutual exclusion», «взаимное исключение») гарантирует, что одновременно опасные действия может выполнять только один клиент. Эта концепция встречается в разных областях, но особенно она важна в Postgres, как и в любой другой базе данных, потому что обновление отдельной сущности (будь то строка, таблица, представление и так далее) должно завершиться однозначным успехом или неудачей. Операции могут выполниться частично успешно, если их пытаются одновременно выполнить два разных клиента/процесса, поэтому операция должна получить блокировку соответствующей сущности.

▍ Как блокировки работают в Postgres

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

А вот, как они конфликтуют (X означает конфликт):

Рассмотрим пример для одной таблицы: