эффективно обрезать таблицы postgresql

У меня есть около 10 таблиц с более чем 2 миллионами записей и одна с 30 миллионами. Я хотел бы эффективно удалить старые данные из каждой из этих таблиц.

Мой общий алгоритм:

  • создайте временную таблицу для каждой большой таблицы и заполните ее новыми данными
  • обрезать исходные таблицы
  • скопируйте данные tmp обратно в исходные таблицы, используя: «вставить в исходную таблицу (выбрать * из tmp_table)»

Однако последний шаг обратного копирования данных занимает больше времени, чем хотелось бы. Я думал об удалении исходных таблиц и создании временных таблиц «постоянными», но я теряю информацию об ограничении/внешнем ключе.

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

Спасибо.


postgresql performance foreign-keys
person agilefall    schedule 19.04.2010    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Самый быстрый процесс, вероятно, будет именно таким, как вы описали:

  1. Скопируйте новые данные во временную таблицу
  2. Удаление индексов и внешних ключей
  3. Скиньте старую таблицу
  4. Скопируйте временную таблицу обратно к старому имени таблицы
  5. Перестроить индексы и внешние ключи.

В руководстве по Postgres также есть некоторые рекомендации по производительности, которые могут или может не применяться. Откровенно говоря, значительно быстрее удалить таблицу, чем миллионы строк (поскольку каждое удаление выполняется кортеж за кортежем), и значительно быстрее вставить миллионы строк в таблицу без ограничений или индексов (поскольку каждое ограничение должны быть проверены, и каждый индекс должен быть обновлен для каждой вставки записи; удаляя все ограничения, вы ограничиваете это одним построением индекса и одной проверкой ограничения).

person ig0774    schedule 20.04.2010

arrow_upward
1
arrow_downward

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

Однако разделение в PostgreSQL не так просто, как в некоторых других базах данных — вам нужно перемещать данные вручную с помощью триггеров, и есть оговорки (например, отсутствие глобальных первичных ключей).

См. руководство PostgreSQL по разделам.

person intgr    schedule 21.04.2010