Эффективное удаление старых строк с секциони­ро­ва­нием

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

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

MySQL

CREATE TABLE audits (
  -- more columns...
  created_at timestamp NOT NULL
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
  PARTITION audits_2023_01 VALUES
    LESS THAN (UNIX_TIMESTAMP('2023-02-01 00:00:00')),
  PARTITION audits_2023_02 VALUES
    LESS THAN (UNIX_TIMESTAMP('2023-03-01 00:00:00')),
  PARTITION audits_2023_03 VALUES
    LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')),
  PARTITION audits_2023_04 VALUES
    LESS THAN (UNIX_TIMESTAMP('2023-05-01 00:00:00'))
);

ALTER TABLE audits DROP PARTITION audits_2023_01;

PostgreSQL

CREATE TABLE audits (
  -- more columns...
  created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE audits_2023_01 PARTITION OF audits FOR VALUES
  FROM ('2023-01-01 00:00:00+00') TO ('2023-01-31 23:59:59+00');
CREATE TABLE audits_2023_02 PARTITION OF audits FOR VALUES
  FROM ('2023-02-01 00:00:00+00') TO ('2023-02-28 23:59:59+00');
CREATE TABLE audits_2023_03 PARTITION OF audits FOR VALUES
  FROM ('2023-03-01 00:00:00+00') TO ('2023-03-31 23:59:59+00');
CREATE TABLE audits_2023_04 PARTITION OF audits FOR VALUES
  FROM ('2023-04-01 00:00:00+00') TO ('2023-04-30 23:59:59+00');

ALTER TABLE audits DETACH PARTITION audits_2023_01 CONCURRENTLY;

DROP TABLE audits_2023_01;

Детальное объяснение

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

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

• Их удаление займёт очень много времени и может серьёзно повлиять на производительность вашей системы.
• Все эти изменения необходимо перенести и применить к вашим репликациям базы данных, что может привести к значительным задержкам репликации. Возможно, вам придётся реализовать более сложное поведение фрагментации, чтобы распределить нагрузку на более длительный период времени.
• Размер диска, занятый удалёнными строками, не будет освобождён. Вы должны выполнить VACUUM FULL в PostgreSQL, или OPTIMIZE TABLE в MySQL, чтобы полностью перестроить таблицу и уменьшить её. Но монопольная блокировка этими операциями заблокирует любой доступ к большим таблицам на очень долгое время.

Эти недостатки неприемлемы для большинства приложений. Таким образом, базы данных добавили решение многих распространённых проблем, включая заявленные. При секционировании таблиц одна большая таблица прозрачно разбивается на множество меньших секций. В нашем примере мы построили месячные разделы с января по апрель 2023 года. Все запросы по-прежнему выполняются в родительской таблице, но база данных будет прозрачно перенаправлять их в затронутые разделы без каких либо усилий. С точки зрения SQL нет никакой разницы между одной большой таблицей и секционированной. Вы всё ещё выполняете запросы select и insert в основной таблице.

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