Эффективно обрезать таблицы postgresql
У меня около 10 таблиц с более чем 2 миллионами записей и одна с 30 миллионами. Я хотел бы эффективно удалить более старые данные из каждой из этих таблиц.
Мой общий алгоритм:
- создайте временную таблицу для каждой большой таблицы и заполните ее новыми данными
- обрезать исходные таблицы
- скопировать данные tmp обратно в исходные таблицы, используя: "вставить в исходную таблицу (выбрать * из tmp_table)"
Однако последний шаг копирования данных занимает больше времени, чем хотелось бы. Я думал об удалении исходных таблиц и создании временных таблиц "постоянными", но я теряю информацию об ограничениях / внешних ключах.
Если я удалю из таблиц напрямую, это займет гораздо больше времени. Учитывая, что мне нужно сохранить все внешние ключи и ограничения, существуют ли более быстрые способы удаления старых данных?
Благодарю.
2 ответа
Самый быстрый процесс, вероятно, будет именно таким, как вы описали:
- Скопируйте новые данные во временную таблицу
- Удалить индексы и внешние ключи
- Брось старый стол
- Скопируйте временную таблицу обратно в старое имя таблицы
- Перестройте индексы и внешние ключи.
В руководстве Postgres также есть некоторые предложения по производительности, которые могут или не могут применяться. Честно говоря, однако, значительно быстрее удалить таблицу, чем отбросить миллионы строк (поскольку каждое удаление выполняется кортеж за кортежем), и значительно быстрее вставить миллионы строк в таблицу без ограничений или индексов (как каждое ограничение должен быть проверен, и каждый индекс должен быть обновлен для каждой вставки записи; удаляя все ограничения, вы ограничиваете это одной сборкой индекса и одной проверкой для ограничения).
"Стандартное" решение этих проблем обычно включает разбиение таблиц на соответствующий ключ, так что, когда вам нужно удалить старые данные, вы можете просто удалить целый раздел - безусловно, самое быстрое удаление, которое вы когда-либо получите.
Однако разбиение в PostgreSQL не так просто, как в некоторых других базах данных - вам нужно перемещать данные вручную с помощью триггеров, и есть предостережения (например, нет глобальных первичных ключей)