Что бы я хотел знать о Postgres, когда начал его изучать

За исключением… Postgres. Дело не в том, что официальная документация не является выдающейся (она такая и есть!) — она просто огромна. Для текущей версии (17 на момент написания статьи), если распечатать её в виде стандартного PDF на бумаге формата A4, она занимает 3,024 страницы. Это не то, что может просто сесть и прочитать от начала до конца любой начинающий инженер.

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

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

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

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

Если задать в Интернете запрос нормализация базы данных, вы найдёте множество результатов о 1-й нормальной форме и подобного. Не обязательно знать, что представляет собой каждая из этих нормальных форм, но общий процесс полезно знать, поскольку он может привести к созданию более удобной схемы базы данных.

Бывают случаи, когда избыточные данные (то есть денормализация схемы) могут иметь смысл: обычно это делается для ускорения чтения определённых данных, чтобы их не приходилось вычислять заново каждый раз, когда они запрашиваются. Например, приложение управляет сменами сотрудников в пекарне. Пользователь может захотеть узнать, сколько часов он отработал в этом году. Чтобы вычислить это, нужно определить продолжительность каждой смены, а затем суммировать их. Возможно, вместо этого имеет смысл рассчитывать эту сумму через регулярный интервал или при изменении количества отработанных часов. Эти данные могут быть денормализованы как в базе данных Postgres, так и вне её (например, в кэширующем слое, таком как Redis). Обратите внимание, что денормализованные данные почти всегда имеют определённую стоимость, будь то возможная несогласованность данных или повышенная сложность записи.

Следуйте всем советам разработчиков Postgres.

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

1. Просто используйте этот тип text для хранения всего текста.
2. Просто используйте тип timestampz/timestamp 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 из других языков программирования. В SQL NULL не такой. 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/и т.д.:

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

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

Уточнение неоднозначных null

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

\pset null '[NULL]'

Подойдёт любая строка Юникода! Сейчас уже не сезон страшилок, но можно поступить как мой друг Steven Harman и установить для него "👻".

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

Используйте автозавершение

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

-- начните набирать "SEL"
SEL
-- ^ нажмите `Tab`
SELECT

Используйте сокращения с обратным слешем

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

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

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

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

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

Если необходимо, чтобы он включал начальную строку с именами всех столбцов, можно добавить параметр 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, чтобы он использовал конкретный индекс. Он должен предсказать (используя статистику, ведущуюся для каждой таблицы), что поиск нужных данных будет быстрее, чем простое чтение таблицы сверху вниз (известное как последовательное сканирование или "seq. scan" — произноситься, как "seek scan"). Можно увидеть, как Postgres планирует выполнить запрос, добавив EXPLAIN перед SELECT ... FROM ..... Это покажет "план запроса": план того, как Postgres собирается найти данные, и его оценку, сколько работы займёт каждая задача. Есть много хороших руководств по анализу результатов этих планов запросов, например, вот это от thoughtbot или вот это от pganalyze. Официальная документация также представляет хороший справочник (хотя и немного перегруженный для новичков). Для анализа планов запросов я нашёл этот инструмент.

Индекс не особо нужен таблице, в которой почти нет строк.

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

При индексировании нескольких столбцов порядок имеет значение

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

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

-- % - это подстановочный символ: выражение `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)

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

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

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

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

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

Например, рассмотрим следующее для отдельной таблицы:

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

Как это может стать причиной проблем

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

К распространённым рецептам медленного выполнения операторов ALTER TABLE относятся

• Добавление столбца с непостоянным значением по умолчанию^*
  • По моему опыту, это самая распространённая причина замедления работы
• Изменение типа столбца
• Добавление ограничения уникальности

Допустим, вы добавляете новый столбец в активно используемую таблицу. При этом не делаете ничего глупого с помощью оператора 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, у него всё ещё есть блокировка.^* Если бы другой клиент захотел удалить эту строку, он бы выполнил следующее:

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

Но всё просто зависнет. Он ничего не удалит, пока клиент 1 не вернётся и не завершит транзакцию.

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

JSONB — это острый нож

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

Однако при неправильном использовании у него есть свои недостатки.

JSONB может работать медленнее, чем обычные колонки

Хотя JSONB довольно гибок, Postgres не отслеживает статистику столбцов JSONB, что может означать, что эквивалентный запрос к одному столбцу JSONB может быть значительно медленнее, чем к набору "обычных" столбцов. Вот отличная статья с демонстрацией того, как это происходит в 2000 раз медленнее!

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

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

Тип JSONB Postgres немного неудобен в работе

Допустим, у вас есть таблица backpacks с JSONB столбцом data, в котором есть поле 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

Что происходит? Postgres ожидает, что тип правой части сравнения будет соответствовать типу левой части; то есть, чтобы это был правильно отформатированный JSON-документ — следовательно, он должен быть JSON-объектом, массивом, строкой, числом, логическим значением или null. Имейте в виду, что ни один из этих типов не имеет ничего общего с такими типами Postgres, как boolean или integer. И NULL в SQL работает совсем не так, как null в JSONB, ведущий себя скорее как обычный тип.^* Чтобы правильно написать этот запрос, необходимо включить в Postgres возможность выполнения некоторого принуждения.

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

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

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

В общем…

Надеюсь, статья была полезной. Спасибо Lillie Chilen, Monica Cowan, Steven Harman и KWu за поддержку и отзывы об этой статье. Если у вас есть исправления, отзывы или комментарии, меня можно найти практически на всех сайтах как hibachrach.

Комментарии