Неочевидные для начинающих тонкости 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 означает конфликт):

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

^{3. Вернее… обычно ждать. Некоторые формы ALTER TABLE (например, добавление ограничений) требуют менее жёсткой блокировки. Дополнительную информацию см. на странице этого выражения в документации.}

Полный список всей этой информации можно найти в официальной документации. Также есть хорошее руководство по конфликтам разных операций (именно о них, а не об уровнях блокировок мы думаем чаще всего).

▍ Как это может вызывать проблемы

В предыдущем разделе мы отметили, что если один клиент выполняет выражение ALTER TABLE, то это может помешать выполнению SELECT. Это может быть очень плохо, если выполнение выражения ALTER TABLE занимает много времени. Если вы обновляете основную таблицу (например, users, на которую должны ссылаться все запросы вашего веб-приложения), все операции чтения SELECT из этой таблицы просто будут ожидать завершения. А потом, разумеется, завершатся по таймауту, заставив приложение возвращать 503.

Обычно медленные ALTER TABLE получаются из-за следующих причин:

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

• Изменение типа столбца.
• Добавление ограничения уникальности.

^{4. Когда-то любое значение по умолчанию могло сделать столбец медленным, но это было исправлено в Postgres 11, которая кажется мне совсем новой, но была выпущена целых шесть лет назад.}

Допустим, вы добавляете новый столбец в активно используемую таблицу. Вы не делаете ничего глупого со своими выражениями ALTER TABLE. Да, вы добавляете новый столбец, но он не имеет переменного значения по умолчанию. Но это всё равно может поломать ваше приложение.

Дело в том, что выражение ALTER TABLE будет быстрым… когда получит блокировку. Но допустим, много лет назад вы создали какой-то внутренний дэшборд, выполняющий периодические запросы к этой таблице. Со временем этот запрос становился всё медленнее и медленнее. То, что раньше занимало миллисекунды, теперь занимает минуты. Это абсолютно нормально, ведь, в конце концов, это всего лишь выражение SELECT, но если пока оно выполняется, начнёт исполняться выражение ALTER TABLE, ему придётся подождать.

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

Отличную статью именно о таком сценарии можно прочитать здесь.

▍ Длительные транзакции тоже могут вести себя столь же плохо

Транзакции — это способ группировки серии выражений баз данных, чтобы они действовали по принципу «всё или ничего» (иными словами, они «атомарные»). После запуска транзакции (например, при помощи BEGIN), вы скрываетесь от всех. Ни один другой клиент не сможет увидеть вносимые вами изменения. Вы можете завершить транзакцию (при помощи COMMIT), что затем «опубликует» их для остальной части базы данных. В абстрактном смысле транзакции схожи с блокировками: они позволяют избегать проблем с другими клиентами, пытающимися вмешаться в ваши действия.

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

Однако можно запросто «выстрелить себе в ногу», при написании транзакций, если они будут выполняться слишком долго. Это вызвано тем, что когда транзакция получает блокировку, она удерживает её, пока не будут внесены изменения. Например, допустим, Клиент 1 открыл psql и написал следующее:

BEGIN;
SELECT * FROM backpacks WHERE id = 2;
UPDATE backpacks SET content_count = 3 WHERE id = 2;
SELECT count(*) FROM backpacks;
-- ...

Но тут Клиента 1 внезапно отвлекли. Несмотря на то, что Клиент 1, по сути, «закончил» обновлять строку с id = 2, он всё равно имеет блокировку⁵.

^{5. В этом случае проблемная блокировка — это row lock, то есть (как можно было догадаться), блокировка строки. Блокировки строк работают достаточно похоже на блокировки на уровне таблиц, о которых мы говорили выше, но немного проще и имеют всего четыре уровня.}

Если другой клиент захочет удалить эту строку, то выполнит следующее:

DELETE FROM backpacks WHERE id = 2;
-- ...
-- ?

Но эта операция просто зависнет. Она не сможет ничего удалить, пока Клиент 1 не вернётся и не выполнит коммит транзакции.

Можно представить, как это может привести к всевозможным сценариям, в которых клиенты удерживают блокировку гораздо дольше необходимого, не позволяя другим успешно выполнять запросы к базе данных или её обновления.

JSONB — это обоюдоострый меч

У Postgres есть очень мощная возможность: можно хранить эффективно сериализированный JSON с возможностью запросов к нему как значение в строке. Во многих смыслах это даёт Postgres все сильные стороны документоориентированной СУБД (как, например, MongoDB) без необходимости запуска нового сервиса или координации между двумя разными хранилищами данных.

^{6. В Postgres есть и значения JSON (в которых текст хранится как текст), и JSONB, в которых JSON преобразуются в эффективный двоичный формат. JSONB обладает множеством преимуществ (например, его можно индексировать!), поэтому в некоторых случаях можно оставить использование формата JSON только для особых случаев (по крайней мере, по моему опыту).}

Однако эта возможность при неправильном использовании обладает недостатками.

▍ JSONB может быть медленнее, чем обычные столбцы

Хотя JSONB достаточно гибок, Postgres не позволяет отслеживать статистику столбцов JSONB; это может привести к тому, что эквивалентный запрос к одному столбцу JSONB окажется существенно медленнее, чем к нескольким «обычным» столбцам. Есть замечательный пост с демонстрацией того, что можно сделать выполнение в две тысячи раз медленнее!

▍ JSONB не такой самодокументируемый, как стандартная схема таблицы

Столбец JSONB, по сути, может содержать в себе что угодно — это одна из основных причин его мощи! Но в то же время это означает, что у нас практически нет гарантий того, как он будет структурирован. В случае обычной таблицы мы можем взглянуть на схему и увидеть, что вернёт запрос. Будет ли ключ записан в camelCase? Или в snake_case? Будут ли состояния описаны булевыми true/false? Или при помощи перечислений типа yes/maybe/no?

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

▍ С JSONB-типами Postgres достаточно неудобно работать

Допустим, у вас есть таблица backpacks со столбцом JSONBdata, в котором есть поле brand. Вы хотите найти backpacks JanSport, потому что любите эстетику начала 90-х. Поэтому вы пишете запрос⁷:

-- ВНИМАНИЕ: не работает!
select * from backpacks where data['brand'] = 'JanSport';

и получаете в ответ ошибку

ERROR:  invalid input syntax for type json
LINE 1: select * from backpacks where data['brand'] = 'JanSport';
                                                      ^
DETAIL:  Token "JanSport" is invalid.
CONTEXT:  JSON data, line 1: JanSport

^{7. Здесь используется относительно новый синтаксис subscripting, появившийся в Postgres 14 (на мой взгляд, он лучше, чем первоначальный синтаксис ->, который кажется менее знакомым по сравнению с тем, как выполняется обход JSON в других языках).}

Как же так? Postgres ожидает, что тип правой части будет соответствовать типу левой части; поскольку это корректно форматированный документ JSON, он должен быть объектом JSON, массивом, строкой, числом, boolean или null. Помните, что ни один из этих типов не имеет ничего общего с типами Postgres наподобие boolean или integer. А NULL в SQL работает сильно иначе, чем null JSONB, который больше похож на обычный тип⁸.

^{8. Самое примечательная разница: 'null'::jsonb = 'null'::jsonb равно true, а NULL = NULL равно NULL}

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

select * from backpacks where data['brand'] = '"JanSport"';
-- ^ На самом деле это эквивалент следующего (потому что Postgres знает, что левая часть - это "jsonb")
select * from backpacks where data['brand'] = '"JanSport"'::jsonb;
-- Или же можно преобразовать левую часть в "text" Postgres:
select * from backpacks where data->>'brand' = 'JanSport';

Обратите внимание на двойные кавычки внутри одинарных. Сам по себе JanSport не является валидным JSON.

Более того, существует целая куча специфичных для JSONB операторов и функций, которую сложно запомнить за раз.

Заключение

Надеюсь, пост окажется для вас полезным. Благодарю Лилли Чилен, Монику Коуэн, Стивена Хармана и KWu за мотивацию и отзывы. Если у вас есть исправления, отзывы или комментарии, можете найти меня практически на всех сайтах под ником hibachrach.