Мы удачно спроектировали структуру нашей PostgreSQL-базы для хранения переписки, прошел год, пользователи активно ее наполняют, вот в ней уже миллионы записей, и… что-то все начало подтормаживать.

• Часть 1: проектируем каркас базы
• Часть 2: секционируем «наживую»

Дело в том, что с ростом объема таблицы растет и «глубина» индексов — хоть и логарифмически. Но со временем это заставляет сервер для выполнения тех же задач чтения/записи обрабатывать в разы больше страниц данных, чем в начале.

Вот тут на помощь и приходит секционирование.

Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования «с нуля» базы для мессенджера.

• Часть 1: проектируем каркас базы
• Часть 2: секционируем «наживую»

Наша база будет не такой масштабной и распределенной, как у ВКонтакте или Badoo, а «чтобы было», но было хорошо — функционально, быстро и умещалось на одном сервере PostgreSQL — чтобы можно было развернуть отдельный экземпляр сервиса где-то на стороне, например.

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

Немного SQL-магии под катом: математика, рекурсия, псевдографика.

Заодно вспоминаем под Новый год формулу угла между векторами:

VACUUM может «зачистить» из таблицы в PostgreSQL только то, что никто не может увидеть — то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.

А если такой неприятный тип (продолжительная OLAP-нагрузка на OLTP-базе) все же есть? Как почистить активно меняющуюся таблицу в окружении длинных запросов и не наступить на грабли?

Как стоит поступить (а как точно не надо), если в «многомиллионной» активно используемой таблице PostgreSQL нужно обновить большое количество записей — проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.

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



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

Как избавить базу от ненужных клонов?

Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку «на вход». Иногда попадаются очень странные решения этой задачи.

Пойдем «от обратного» и посмотрим, как делать не стоит, почему, и как можно сделать лучше.

Иногда возникает задача «склеить» внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными «по столбцам».

Для выбора наиболее эффективного плана выполнения запроса PostgreSQL пользуется накопленной статистикой о распределении значений данных в целевых таблицах.

Она обновляется с помощью явного запуска команд ANALYZE и VACUUM ANALYZE или в фоновом режиме процессом autovacuum/autoanalyze. Но если статистика не успеет актуализироваться — может произойти беда.

Как такую проблему обнаружить и исправить?

Бойтесь операций, buffers приносящих…
На примере небольшого запроса рассмотрим некоторые универсальные подходы к оптимизации запросов на PostgreSQL. Пользоваться ими или нет — выбирать вам, но знать о них стоит.

По роду деятельности приходится сталкиваться с ситуациями, когда разработчик пишет запрос и думает "база умная, сама со всем справится!"

В некоторых случаях (частично от незнания возможностей БД, частично от преждевременных оптимизаций) такой подход приводит к появлению «франкенштейнов».

Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса — к консультанту по PostgreSQL, почти всегда звучит одинаково: «Почему запросы выполняются на базе так долго?»

Традиционный набор причин:

• неэффективный алгоритм
  когда вы решили сделать JOIN нескольких CTE по паре десятков тысяч записей
• неактуальная статистика
  если фактическое распределение данных в таблице уже сильно отличается от собранной ANALYZE'ом в последний раз
• «затык» по ресурсам
  и уже не хватает выделенных вычислительных мощностей CPU, постоянно прокачиваются гигабайты памяти или диск не успевает за всеми «хотелками» БД
• блокировки от конкурирующих процессов

И если блокировки достаточно сложны в поимке и анализе, то для всего остального нам достаточно плана запроса, который можно получить с помощью оператора EXPLAIN (лучше, конечно, сразу EXPLAIN (ANALYZE, BUFFERS) ...) или модуля auto_explain.

Но, как сказано в той же документации,

«Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, …»

Но можно обойтись и без него, если воспользоваться подходящим инструментом!