Команда Python for Devs подготовила перевод статьи о том, как можно освободить десятки гигабайт места в PostgreSQL без удаления данных и индексов. TL;DR: удаляем неиспользуемые индексы, чистим bloat, пересобираем таблицы и используем частичные индексы, чтобы хранить только то, что реально нужно.

---

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

Если сразу перейти к результату: нам удалось освободить более 70 ГБ неоптимизированного и неиспользуемого пространства, не удалив ни одного индекса и не потеряв ни одной записи!

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

Вот так выглядел график свободного места в одной из наших баз данных в процессе очистки:

Обычные подозреваемые

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

Неиспользуемые индексы

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

Чтобы найти неиспользуемые индексы, мы используем следующий запрос:

SELECT
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size
FROM
    pg_stat_all_indexes
WHERE
    schemaname = 'public'
    AND indexrelname NOT LIKE 'pg_toast_%'
    AND idx_scan = 0
    AND idx_tup_read = 0
    AND idx_tup_fetch = 0
ORDER BY
    pg_relation_size(indexrelname::regclass) DESC;

Этот запрос ищет индексы, которые ни разу не сканировались и не использовались с момента последнего сброса статистики.

Однако некоторые индексы могут выглядеть «неиспользуемыми», хотя на деле это не так:

• В документации описаны ситуации, когда оптимизатор использует метаданные индекса, но не сам индекс напрямую.

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

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

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

-- Найти oid таблицы по имени
SELECT oid FROM pg_class c WHERE relname = 'table_name';

-- Сбросить счетчики для всех индексов таблицы
SELECT pg_stat_reset_single_table_counters(14662536);

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

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

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

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

Очистка переполнения индексов

Чтобы избавиться от переполнения индекса, его нужно перестроить. Существует несколько способов:

• Создать индекс заново. В этом случае он будет построен оптимальным образом.

• Перестроить индекс. Вместо того чтобы удалять и заново создавать индекс вручную, PostgreSQL позволяет перестроить существующий индекс на месте с помощью команды REINDEX:

REINDEX INDEX index_name;

• Перестроить индекс конкурентно. Первые два способа блокируют таблицу и не дают вносить в неё изменения на время операции, что чаще всего неприемлемо. Чтобы перестроить индекс без блокировки обновлений, можно использовать конкурентное перестроение:

REINDEX INDEX CONCURRENTLY index_name;

При использовании REINDEX CONCURRENTLY PostgreSQL создаёт новый индекс с суффиксом _ccnew в имени и синхронизирует все изменения, внесённые в таблицу за это время. Когда перестроение завершается, система заменяет старый индекс на новый и удаляет прежний.

Если по какой-то причине вам пришлось прервать перестроение индекса на середине, новый индекс не будет удалён. Вместо этого он останется в невалидном состоянии и будет занимать место. Чтобы найти такие невалидные индексы, созданные в процессе REINDEX, мы используем следующий запрос:

-- Поиск невалидных индексов, созданных во время перестроения
SELECT
    c.relname as index_name,
    pg_size_pretty(pg_relation_size(c.oid))
FROM
    pg_index i
    JOIN pg_class c ON i.indexrelid = c.oid
WHERE
    -- Новый индекс, построенный с помощью REINDEX CONCURRENTLY
    c.relname LIKE  '%_ccnew'
    -- Индекс в состоянии INVALID
    AND NOT indisvalid
LIMIT 10;

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

Активация дедупликации B-Tree индексов

В PostgreSQL 13 появилась новая эффективная технология хранения дубликатов в B-Tree индексах — B-Tree Deduplication.

Обычно для каждого индексируемого значения B-Tree хранит в листьях само значение и указатель на строку (TID). Чем больше значения, тем больше размер индекса. До PostgreSQL 12 при наличии множества одинаковых значений все дубликаты записывались в листья индекса. Это было неэффективно и могло занимать очень много места.

Начиная с PostgreSQL 13, при включённой дедупликации B-Tree дублирующиеся значения хранятся только один раз. Это может существенно уменьшить размер индексов с большим количеством повторов.

В PostgreSQL 13 дедупликация индексов включена по умолчанию, если её не отключить:

-- Включение дедупликации для B-Tree индекса (это значение по умолчанию):
CREATE INDEX index_name ON table_name(column_name) WITH (deduplicate_items = ON)

Если вы мигрируете с версий PostgreSQL до 13, чтобы получить все преимущества дедупликации, нужно перестроить индексы командой REINDEX.

Чтобы показать влияние дедупликации B-Tree на размер индекса, создадим таблицу с уникальным столбцом и неуникальным столбцом и заполним её 1 млн строк. Для каждого столбца создадим два B-Tree индекса: один с включённой дедупликацией и один с отключённой:

db=# CREATE test_btree_dedup (n_unique serial, n_not_unique integer);
CREATE TABLE

db=# INSERT INTO test_btree_dedup (n_not_unique)
SELECT (random() * 100)::int FROM generate_series(1, 1000000);
INSERT 0 1000000

db=# CREATE INDEX ix1 ON test_btree_dedup (n_unique)     WITH (deduplicate_items = OFF);
CREATE INDEX

db=# CREATE INDEX ix2 ON test_btree_dedup (n_unique)     WITH (deduplicate_items = ON);
CREATE INDEX

db=# CREATE INDEX ix3 ON test_btree_dedup (n_not_unique) WITH (deduplicate_items = OFF);
CREATE INDEX

db=# CREATE INDEX ix4 ON test_btree_dedup (n_not_unique) WITH (deduplicate_items = ON);
CREATE INDEX

Далее сравним размеры четырёх индексов:

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

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

Очистка переполнения в таблицах

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

Существует несколько способов пересобрать таблицу и уменьшить переполнение:

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

• Вакуумировать таблицу. PostgreSQL предоставляет возможность освободить место, занятое переполнением и «мертвыми» кортежами, с помощью команды VACUUM FULL. Однако такая операция требует блокировки таблицы и не подходит для тех случаев, когда таблица должна оставаться доступной:

-- Таблица будет заблокирована
VACUUM FULL table_name;

Оба варианта требуют либо больших трудозатрат, либо простоя системы.

Использование pg_repack

Оба встроенных варианта пересборки таблиц неидеальны, если вы не можете позволить себе простой. Популярным решением для пересборки таблиц и индексов без простоя является расширение pg_repack.

Это расширение хорошо известно и, скорее всего, доступно через ваш пакетный менеджер или уже установлено вашим облачным провайдером. Чтобы начать работу, нужно сначала создать расширение:

CREATE EXTENSION pg_repack;

Чтобы «упаковать заново» таблицу вместе с её индексами, выполните из консоли следующую команду:

$ pg_repack -k --table table_name db_name

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

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

• Требуется дополнительное место примерно равное размеру таблицы. Так как создаётся копия таблицы и её индексов, для работы нужно дополнительное пространство на диске.

• Может потребоваться ручная очистка. Если процесс «repack» завершился сбоем или был остановлен вручную, могут остаться промежуточные объекты, которые придётся удалять самостоятельно.

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

«Находка»

К этому моменту мы уже перепробовали все привычные методы и освободили немало места. Мы удалили неиспользуемые индексы, почистили переполнение в таблицах и индексах, но… оказалось, что можно освободить ещё немного!

«Озарение»

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

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

В таблице транзакций есть внешние ключи как на покупателя, так и на отменившего пользователя. Для каждого поля определён B-Tree индекс. Поле с покупателем имеет ограничение NOT NULL, поэтому в каждой строке есть значение. А вот поле с отменившим пользователем допускает NULL, и данные там есть лишь в небольшой доле строк. В большинстве случаев в этом поле стоит NULL.

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

Изначально у нас был такой индекс для поля отменившего пользователя:

CREATE INDEX transaction_cancelled_by_ix ON transactions(cancelled_by_user_id);

Чтобы проверить нашу гипотезу, мы заменили его частичным индексом, исключающим NULL-значения:

DROP INDEX transaction_cancelled_by_ix;

CREATE INDEX transaction_cancelled_by_part_ix 
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;

Полный индекс после перестроения занимал 769 МБ, при том что более 99% значений там были NULL. Частичный индекс, исключающий NULL, занял меньше 5 МБ. Это значит, что нам удалось убрать более 99% «мертвого груза»!

Чтобы убедиться, что NULL-значения действительно не нужны, мы сбросили статистику по таблице и немного подождали. Очень скоро стало видно, что новый индекс используется точно так же, как и старый! В итоге мы избавились от более чем 760 МБ неиспользуемых кортежей в индексе — и всё это без потери производительности.

Использование частичных индексов

Получив хороший результат с одним частичным индексом, мы решили, что подобных индексов у нас может быть больше. Чтобы найти кандидатов на частичные индексы, мы написали запрос, который ищет индексы по полям с высоким null_frac — долей значений столбца, которые PostgreSQL оценивает как NULL:

-- Найти индексированные столбцы с высоким null_frac
SELECT
    c.oid,
    c.relname AS index,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    i.indisunique AS unique,
    a.attname AS indexed_column,
    CASE s.null_frac
        WHEN 0 THEN ''
        ELSE to_char(s.null_frac * 100, '999.00%')
    END AS null_frac,
    pg_size_pretty((pg_relation_size(c.oid) * s.null_frac)::bigint) AS expected_saving
    -- Раскомментируйте, чтобы включить определение индекса
    --, ixs.indexdef

FROM
    pg_class c
    JOIN pg_index i ON i.indexrelid = c.oid
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_class c_table ON c_table.oid = i.indrelid
    JOIN pg_indexes ixs ON c.relname = ixs.indexname
    LEFT JOIN pg_stats s ON s.tablename = c_table.relname AND a.attname = s.attname

WHERE
    -- Первичный ключ не может быть частичным
    NOT i.indisprimary

    -- Исключаем уже частичные индексы
    AND i.indpred IS NULL

    -- Исключаем составные индексы
    AND array_length(i.indkey, 1) = 1

    -- Размер больше 10MB
    AND pg_relation_size(c.oid) > 10 * 1024 ^ 2

ORDER BY
    pg_relation_size(c.oid) * s.null_frac DESC;

Результаты этого запроса могут выглядеть так:

   oid   |         index      | index_size | unique | indexed_column | null_frac | expected_saving
---------+--------------------+------------+--------+----------------+-----------+-----------------
  138247 | tx_cancelled_by_ix | 1418 MB    | f      | cancelled_by   |   96.15%  | 1363 MB
   16988 | tx_op1_ix          | 1651 MB    | t      | op1            |    6.11%  | 101 MB
 1473377 | tx_token_ix        | 22 MB      | t      | token          |   11.21%  | 2494 kB
  138529 | tx_op_name_ix      | 1160 MB    | t      | name           |           | 0 bytes

В этой таблице можно выделить несколько типов результатов:

• tx_cancelled_by_ix — большой индекс с большим числом NULL: отличный потенциал!

• tx_op1_ix — большой индекс с небольшой долей NULL: потенциал небольшой.

• tx_token_ix — маленький индекс с небольшой долей NULL: я бы не трогал.

• tx_op_name_ix — большой индекс без NULL: делать нечего.

Из результатов видно, что, превратив tx_cancelled_by_ix в частичный индекс, исключающий NULL, мы можем сэкономить порядка ~1,3 ГБ.

Всегда ли выгодно исключать NULL из индексов?

Нет. NULL — такое же значимое состояние, как и любое другое значение. Если ваши запросы ищут NULL через IS NULL, таким запросам может быть полезен индекс по NULL.

Значит, метод полезен только для NULL?

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

Как же получилось освободить больше 20 ГБ?

Вы могли заметить, что в заголовке фигурирует более 20 ГБ свободного места, а на графиках — лишь половина. Всё просто: индексы удаляются и на репликах! Освободив 10 ГБ на основной базе, вы освобождаете примерно столько же на каждой реплике.

Бонус: миграции с Django ORM

Эта история взята из большого приложения на Django. Чтобы применить описанные выше приёмы в Django, стоит учитывать несколько моментов.

Избегайте неявного создания индексов для внешних ключей

Если явно не указать db_index=False, Django автоматически создаст B-Tree индекс на поле models.ForeignKey. Рассмотрим пример:

from django.db import models
from django.contrib.auth.models import User

class Transaction(models.Model):
    # ...
    cancelled_by_user = models.ForeignKey(
        to=User,
        null=True,
        on_delete=models.CASCADE,
    )

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

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

from django.db import models
from django.contrib.auth.models import User
from django.db.models import Q

class Transaction(models.Model):
    # ...
    cancelled_by_user = models.ForeignKey(
        to=User,
        null=True,
        on_delete=models.CASCADE,
        db_index=False,
    )

    class Meta:
        indexes = (
            models.Index(
                fields=('cancelled_by_user_id', ),
                name='%(class_name)s_cancelled_by_part_ix',
                condition=Q(cancelled_by_user_id__isnull=False),
            ),
        )

Сначала мы указываем Django не создавать индекс на поле внешнего ключа (db_index=False), а затем добавляем частичный индекс через models.Index.

Вывод: Nullable-внешние ключи — отличные кандидаты для частичных индексов!

Миграция существующих полных индексов в частичные

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

Заменяем полные индексы на частичные. Вносим изменения в соответствующие модели Django и заменяем полные индексы на частичные, как показано выше. Сгенерированная миграция сначала отключит ограничение внешнего ключа (если поле — FK), затем удалит существующий полный индекс и создаст новый частичный. Выполнение такой миграции может привести к простою и падению производительности, поэтому напрямую её запускать мы не будем.

Создаём частичные индексы вручную. С помощью ./manage.py sqlmigrate получаем SQL-скрипт миграции, из него берём только операторы CREATE INDEX и правим их на создание индексов CONCURRENTLY. После этого создаём индексы вручную и конкурентно в базе. Поскольку полные индексы ещё не удалены, запросы по-прежнему могут их использовать — производительность на этом этапе не должна страдать. В принципе, индексы можно создавать конкурентно и в миграциях Django, но в этот раз мы решили сделать это вручную.

Сбрасываем счётчики статистики полных индексов. Чтобы убедиться, что можно безопасно удалять полные индексы, нам нужно сначала проверить, что новые частичные индексы реально используются. Для отслеживания их использования мы сбрасываем счётчики для полных индексов с помощью pg_stat_reset_single_table_counters(<full index oid>).

Мониторим использование частичных индексов. После сброса статистики наблюдаем за общей производительностью запросов и за тем, как используются частичные индексы, смотря на значения idx_scan, idx_tup_read и idx_tup_fetch в pg_stat_all_indexes — как для частичных, так и для полных индексов.

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

«Фейкуем» миграцию в Django. Когда состояние базы фактически совпало с состоянием моделей, выполняем «фиктивную» миграцию командой ./manage.py migrate --fake. В этом режиме Django пометит миграцию как выполненную, но реально ничего не запустит — удобно, когда нужен более тонкий контроль процесса. Обратите внимание: в других средах (dev, QA, staging), где простои некритичны, миграции Django будут выполняться обычно, и полные индексы действительно заменятся частичными.

Русскоязычное сообщество про Python

Друзья! Эту статью перевела команда Python for Devs — канала, где каждый день выходят самые свежие и полезные материалы о Python и его экосистеме. Подписывайтесь, чтобы ничего не пропустить!

Заключение

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

Подведём итог шагам, которые помогли нам освободить как можно больше места:

• удалили неиспользуемые индексы;

• пересобрали таблицы и индексы (и включили дедупликацию B-Tree там, где это было возможно);

• использовали частичные индексы, чтобы индексировать только то, что действительно нужно.

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