Скорость усечения Postgresql

Это появилось несколько раз в последнее время, как в SO, так и в почтовых списках PostgreSQL.

TL; DR для двух последних пунктов:

(a) Большие shared_buffers могут быть причиной того, что TRUNCATE медленнее на сервере CI. Также может быть виновата другая конфигурация fsync или использование ротационных носителей вместо SSD.

(b) TRUNCATE имеет фиксированную стоимость, но не обязательно более медленную, чем DELETE , плюс она делает больше работы. См. Подробное объяснение, которое следует ниже.

ОБНОВЛЕНИЕ: из этой публикации возникла значительная дискуссия о производительности pgsql . Смотрите эту тему .

ОБНОВЛЕНИЕ 2: Усовершенствования добавлены к 9.2beta3, которые должны помочь в этом, см. Этот пост .

Подробное объяснение TRUNCATE vs DELETE FROM :

Я не знаю эксперта по этой теме, я понимаю, что TRUNCATE имеет почти фиксированную стоимость за таблицу, а DELETE – не менее O (n) для n строк; хуже, если есть foreign keys, ссылающиеся на удаляемую таблицу.

Я всегда предполагал, что фиксированная стоимость TRUNCATE была ниже, чем стоимость DELETE на почти пустой таблице, но это совсем не так.

TRUNCATE table; делает больше, чем DELETE FROM table;

Состояние базы данных после TRUNCATE table выглядит так же, как если бы вы запускали:

• DELETE FROM table;
• VACCUUM (FULL, ANALYZE) table; (Только 9.0+, см. Сноску)

… хотя, конечно, TRUNCATE фактически не достигает своих эффектов с помощью DELETE и VACUUM .

Дело в том, что DELETE и TRUNCATE делают разные вещи, поэтому вы не просто сравниваете две команды с одинаковыми результатами.

DELETE FROM table; позволяет сохранять мертвые ряды и раздуваться, позволяет индексировать несущие записи, не обновляет статистику таблицы, используемую планировщиком запросов, и т. д.

TRUNCATE дает вам совершенно новую таблицу и индексы, как если бы они были только CREATE ed. Это похоже на то, что вы удалили все записи, переиндексировали таблицу и сделали VACUUM FULL .

Если вам не все равно, если в таблице остался crud, потому что вы собираетесь пойти и заполнить его снова, вам может быть лучше использовать DELETE FROM table; ,

Поскольку вы не используете VACUUM вы обнаружите, что мертвые строки и индексные записи накапливаются как раздувание, которое необходимо отсканировать, а затем игнорировать; это замедляет все ваши запросы. Если ваши тесты фактически не создают и не удаляют все те данные, которые вы не заметите или не заботитесь, и вы всегда можете сделать VACUUM или два VACUUM пути через ваш тестовый прогон, если вы это сделаете. Лучше, пусть агрессивные настройки автовакуума гарантируют, что автовакуум сделает это для вас на заднем плане.

Вы все еще можете TRUNCATE всех своих таблиц после TRUNCATE всего тестового набора, чтобы убедиться, что во многих прогонах нет эффектов. На 9.0 и новее, VACUUM (FULL, ANALYZE); глобально на столе, по крайней мере, так хорошо, если не лучше, и это намного проще.

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

DELETE FROM table; очень дешево для небольших столов без f / k refs

Чтобы DELETE все записи из таблицы без ссылок на foreign keys, все Pg должны выполнить последовательное сканирование таблицы и установить xmax встречающихся кортежей. Это очень дешевая операция – в основном линейное чтение и полулинейная запись. AFAIK ему не нужно касаться индексов; они продолжают указывать на мертвые кортежи, пока они не будут очищены более поздним VACUUM который также отмечает, что блоки в таблице содержат только мертвые кортежи как свободные.

DELETE становится дороже, если есть много записей, если есть много ссылок на foreign keys, которые необходимо проверить, или если вы подсчитаете последующую VACUUM (FULL, ANALYZE) table; чтобы соответствовать эффектам TRUNCATE пределах стоимости вашего DELETE .

В моих тестах здесь DELETE FROM table; как правило, в 4 раза быстрее, чем TRUNCATE на 0,5 мс против 2 мс. Это тестовая БД на SSD, работающая с fsync=off потому что мне все равно, потеряю ли я все эти данные. Конечно, DELETE FROM table; не делает все той же работы, и если я буду следить за VACUUM (FULL, ANALYZE) table; это намного дороже 21 мс, поэтому DELETE – это только победа, если мне не нужна настоящая таблица.

TRUNCATE table; делает намного более фиксированную стоимость работы и домашнего хозяйства, чем DELETE

Напротив, TRUNCATE должен выполнять большую работу. Он должен выделять новые файлы для таблицы, ее таблицу TOAST, если таковые имеются, и каждый индекс, который имеет таблица. Заголовки должны быть записаны в эти файлы, и системные каталоги могут также нуждаться в обновлении (не уверен в этом, не проверял). Затем он должен заменить старые файлы на новые или удалить старые, и должен обеспечить, чтобы файловая система догнала изменения с помощью операции синхронизации – fsync () или аналогичной – обычно сбрасывает все буферы на диск , Я не уверен, пропускается ли синхронизация, если вы работаете с опцией (data-eating) fsync=off .

Недавно я узнал, что TRUNCATE должен также очистить все буферы PostgreSQL, связанные со старой таблицей. Это может потребовать нетривиального количества времени с огромными shared_buffers . Я подозреваю, что на сервере CI он медленнее.

Баланс

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

Следовательно, вам может быть лучше делать DELETE FROM table; ,

–

Примечание: в БД до 9.0 CLUSTER table_id_seq ON table; ANALYZE table; CLUSTER table_id_seq ON table; ANALYZE table; или VACUUM FULL ANALYZE table; REINDEX table; VACUUM FULL ANALYZE table; REINDEX table; будет более близким к TRUNCATE . VACUUM FULL impl изменился на гораздо лучший в 9.0.

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

Связанный вопрос: 30 таблиц с несколькими строками – TRUNCATE – самый быстрый способ их опорожнения и сбросить прикрепленные последовательности?

Также рассмотрите эту проблему и этот запрос:

https://github.com/bmabey/database_cleaner/issues/126

https://github.com/bmabey/database_cleaner/pull/127

Также этот stream: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

Я сожалею, что написал это как ответ, но я не нашел ссылок на комментарии, может быть, потому, что там уже слишком много комментариев.

Несколько альтернативных подходов к рассмотрению:

• Создайте пустую базу данных со статическими данными «fixture» в ней и запустите в ней тесты. Когда вы закончите, просто снимите базу данных, которая должна быть быстрой.
• Создайте новую таблицу под названием «test_ids_to_delete», которая содержит столбцы для имен таблиц и идентификаторов первичного ключа. Обновите логику удаления, чтобы вместо этого вставить имена идентификаторов / таблиц в эту таблицу, что будет намного быстрее, чем запуск удалений. Затем напишите сценарий, чтобы запустить «офлайн», чтобы фактически удалить данные, либо после завершения всего тестового прогона, либо в одночасье.

Первый – подход «чистой комнаты», а последний означает, что некоторые тестовые данные будут сохраняться в базе данных дольше. «Грязный» подход с автономными удалениями – это то, что я использую для тестового набора с примерно 20 000 тестов. Да, иногда возникают проблемы из-за наличия «дополнительных» тестовых данных в базе данных dev, но иногда. Но иногда эта «грязность» помогла нам найти и устранить ошибку, потому что «беспорядок» лучше имитировал ситуацию в реальном мире, так что подход чистой комнаты никогда не будет.

В последнее время я столкнулся с подобной проблемой, то есть:

1. Время запуска набора тестов, в котором используется DatabaseCleaner, широко варьировалось между различными системами с сопоставимым оборудованием,
2. Изменение страtagsи DatabaseCleaner для :deletion при условии улучшения 10x.

Коренной причиной медленности была файловая система с журналированием (ext4), используемая для хранения базы данных. Во время операции TRUNCATE демона журналирования (jbd2) использовала ~ 90% емкости дискового ввода-вывода. Я не уверен, является ли это ошибкой, краевым случаем или нормальным поведением в этих обстоятельствах. Это объясняет, однако, почему TRUNCATE был намного медленнее DELETE – он генерировал намного больше записей на диске. Поскольку я не хотел использовать DELETE, я прибегал к установке fsync=off и этого было достаточно, чтобы смягчить эту проблему (безопасность данных в этом случае не была важна).