state_groups_state

state_groups_state


У меня случилась ситуация, в базе весом 25 гиг одна таблица занимала 20 гиг.

Это печальноизвестная

state_groups_state

.

Вот так я узнал что 99% веса этой таблицы это всего один чатик:

SELECT g.room_id, 
       COUNT(DISTINCT s.state_group) AS groups,
       COUNT(*) AS state_entries
FROM state_groups g
JOIN state_groups_state s ON g.id = s.state_group
GROUP BY g.room_id
ORDER BY state_entries DESC
LIMIT 10;

Ну и бонусом я узнал что почти весь вес это постоянное изменение m.room.member:

SELECT s.type, 
       COUNT(*) AS entries,
       pg_size_pretty(COUNT(*) * 100) AS estimated_size
FROM state_groups g
JOIN state_groups_state s ON g.id = s.state_group
WHERE g.room_id = '!mefQhZzgTaxNCNzAeK:kde.org'
GROUP BY s.type
ORDER BY entries DESC
LIMIT 5;

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

  1. участник входит
  2. бот понижает ему права (чтобы не могу писать)
  3. бот повышает ему права (после проверки, чтобы мог писать)

тоесть каждый раз при входе участника это 3 события вместо одного.

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

  1. выходим всеми участниками сервера с этого чатика
  2. удаляем этот чат с сервера

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

Так что дальше на свой страх и риск:

  1. через ketesa я попробовал удалить чатик, и примерно через час ketesa перестал видеть этот чат, но в бд он остался
  2. много попыток курловать api сервера меняя кучу параметров в результате чего я пришёл к выводу что нужно менять код сервера или лезть своими руками в бд.
  3. лезу в бд:

BEGIN;

-- 1. Убираем из чёрного списка (если есть)
DELETE FROM blocked_rooms WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 2. Удаляем текущее состояние комнаты
DELETE FROM current_state_events WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 3. Удаляем информацию о членстве
DELETE FROM room_memberships WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 4. Находим все state_group ID этой комнаты (для каскадного удаления)
-- Создаём временную таблицу с ID групп
CREATE TEMP TABLE temp_groups_to_delete AS
SELECT id FROM state_groups WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 5. Удаляем записи состояний по этим группам (самая тяжёлая часть - 42 млн строк)
DELETE FROM state_groups_state 
WHERE state_group IN (SELECT id FROM temp_groups_to_delete);

-- 6. Удаляем сами группы состояний
DELETE FROM state_groups WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 7. Удаляем события комнаты (может быть много, но обычно меньше чем state)
DELETE FROM events WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 8. Очищаем связанные таблицы событий (опционально, но рекомендуется)
DELETE FROM event_json WHERE event_id IN (
    SELECT event_id FROM events WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org'
);
DELETE FROM event_edges WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';
DELETE FROM event_forward_extremities WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';
DELETE FROM event_backward_extremities WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';
DELETE FROM event_push_actions WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';
DELETE FROM event_search WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 9. Удаляем медиа, привязанные к комнате (опционально)
DELETE FROM local_media_repository WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';
DELETE FROM remote_media_cache WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 10. Чистим временную таблицу
DROP TABLE temp_groups_to_delete;

COMMIT;

-- Пересоздайте индексы (они могли фрагментироваться)
REINDEX TABLE state_groups_state;
REINDEX TABLE events;

-- Физически сожмите таблицы (вернёт место ОС)
VACUUM FULL state_groups_state;
VACUUM FULL events;
VACUUM FULL current_state_events;

-- Обновите статистику для планировщика запросов
ANALYZE state_groups_state;
ANALYZE events;

-- 1. Проверить, остались ли события этой комнаты
SELECT COUNT(*) AS events_left FROM events WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 2. Проверить current_state_events
SELECT COUNT(*) AS state_left FROM current_state_events WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 3. Проверить room_memberships
SELECT COUNT(*) AS memberships_left FROM room_memberships WHERE room_id = '!mefQhZzgTaxNCNzAeK:kde.org';

-- 4. Общая статистика по таблице состояний
SELECT 
    COUNT(*) AS total_entries,
    COUNT(DISTINCT state_group) AS active_groups
FROM state_groups_state;

Результат: таблица ужалась с 20 гигов до полугига, при этом синапсу в целом норм, ошибками не сыпет, работает нормально.

Содержание