Как удалить много строк из PostgreSQL?

#postgresql #amazon-web-services #query-optimization #amazon-rds

#postgresql #amazon-веб-сервисы #оптимизация запросов #amazon-rds

Вопрос:

У нас есть таблица с большим количеством строк (100 м), размещенная в AWS RDS.

  1. Как мы можем эффективно удалить 50% строк, если в таблице есть постоянные запросы на чтение, запись и обновление?
  2. Как ежедневно удалять 1% данных?

Структура таблицы:

  • созданный_ат
  • идентификатор пользователя
  • другие данные

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

Комментарии:

1. Разделите таблицу и удалите «самый старый» раздел, когда это необходимо.

2. @PanagiotisKanavos Я добавил структуру таблицы к вопросу. Мне нужно запросить все данные для пользователя за все время. Могу ли я добиться этого с помощью разделения? Кроме того, у меня есть эта таблица прямо сейчас, и я не могу добавить разделение для текущих данных, а только для новых данных.

3. Если ваша система не может обрабатывать автовакуум, значит, она либо неправильно настроена, либо недостаточно подготовлена. Независимо от того, что вызвало автовакуум.

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

5. Объявление AWS для Postgres 11 о разделении Postgres RDS — это краткое руководство по разделению и его преимуществам в целом. В анонсе Postgres 12 объясняется, как в последней версии было улучшено удаление разделов (избегание ненужных разделов при выполнении запроса).

Ответ №1:

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

Что вы делаете, так это удаляете пакет с ограниченным количеством строк за раз, например, так.

 DELETE FROM yourtable
 WHERE id IN (
     SELECT id
       FROM yourtable
      WHERE created_at < '2020-11-01'
      LIMIT 1000
    )
 

(В моем примере мы удаляем все строки в таблице, созданной в любое время до конца октября 2020 года.)

При этом удаляется пакет из 1000 строк. Вы продолжаете выполнять этот запрос до тех пор, пока он не удалит ни одной строки.

Это работает, потому что удаление каждого пакета не занимает много времени, и каждый пакет не будет слишком сильно мешать вашей рабочей нагрузке или обслуживанию вакуума. Это будет особенно эффективно, если у вас есть индекс в created_at столбце.

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

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

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

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

Комментарии:

1. Это зависит от наличия индекса created_at , чтобы избежать полного сканирования таблицы. Существует несколько похожих методов для решения этой проблемы — например, скопируйте PKS для удаления во временную таблицу и используйте ее с помощью USING . Это все еще дорого, особенно на RDS