30 таблиц с несколькими строками - TRUNCATE самый быстрый способ очистить их и сбросить присоединенные последовательности?

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

Я нашел себе такое решение для MySQL, оно работает намного быстрее, чем просто усечение таблиц одна за другой. Но в любом случае меня интересуют и самые быстрые решения для MySQL. Смотрите мой результат здесь, конечно, это только для MySQL: https://github.com/bmabey/database_cleaner/issues/126

У меня есть следующие предположения:

I have 30-100 tables. Let them be 30.

Half of the tables are empty.

Each non-empty table has, say, no more than 100 rows. By this I mean, tables are NOT large.

I need an optional possibility to exclude 2 or 5 or N tables from this procedure.

I cannot! use transactions.

Мне нужна самая быстрая стратегия очистки для такого случая, работающая на PostgreSQL как 8, так и 9.

Я вижу следующие подходы:

1) Усекать каждую таблицу. Я думаю, это слишком медленно, особенно для пустых столов.

2) Проверьте каждую таблицу на пустоту более быстрым методом, а затем, если она пуста, сбросьте столбец уникального идентификатора (аналог AUTO_INCREMENT в MySQL) в исходное состояние (1), то есть восстановите ее last_value из последовательности обратно в 1, в противном случае запустить усечение на нем.

Я использую код Ruby для перебора всех таблиц, вызывая приведенный ниже код для каждой из них, я пытался настроить SQL-код, работающий с каждой таблицей, например:

DO $$DECLARE r record;
BEGIN
  somehow_captured = SELECT last_value from #{table}_id_seq
  IF (somehow_captured == 1) THEN
    == restore initial unique identifier column value here ==
  END

  IF (somehow_captured > 1) THEN
    TRUNCATE TABLE #{table};
  END IF;
END$$;

Управляя этим кодом в различных аспектах, я не смог заставить его работать, потому что я незнаком с функциями и блоками PostgreSQL (и переменными).

Также я предположил, что EXISTS(ВЫБРАТЬ что-то из TABLE) может как-то использоваться для хорошей работы в качестве одного из элементов "процедуры проверки", из которого должна состоять процедура очистки, но также не выполнила ее.

Я был бы признателен за любые советы о том, как эта процедура может быть выполнена в PostgreSQL нативным способом.

ОБНОВИТЬ:

Мне нужно все это для запуска модульных и интеграционных тестов для проектов Ruby или Ruby on Rails. Каждый тест должен иметь чистую БД перед выполнением или выполнять очистку после себя (так называемый демонтаж). Транзакции очень хороши, но они становятся непригодными для запуска тестов с определенными веб-драйверами, в моем случае необходим переход на стратегию усечения. Как только я обновлю это со ссылкой на RoR, пожалуйста, не публикуйте здесь ответы о "Очевидно, вам нужен DatabaseCleaner для PG" и так далее, и так далее.

ОБНОВЛЕНИЕ 2:

Стратегия, описанная здесь недавно, была объединена с DatabaseCleaner, https://github.com/bmabey/database_cleaner как опция:pre_count (см. README там).

Источник

5 ответов

Решение

Если кого-то интересует текущая стратегия, я использую для этого, посмотрите это репо на основе Ruby https://github.com/stanislaw/truncate-vs-count для MySQL и PostgreSQL.

Мои результаты:

MySQL: самая быстрая стратегия очистки баз данных - это усечение со следующими модификациями:

if table is not empty
  truncate. 
else 
  if AUTO_INCREMENT is not 0
    truncate.
  end
end
  • Для MySQL только усечение намного быстрее, чем просто удаление. Единственный случай, когда DELETE выигрывает у TRUNCATE, это делает это на пустом столе.
  • Для MySQL усечение с пустыми проверками намного быстрее, чем просто многократное усечение.
  • Для MySQL удаление с пустыми проверками намного быстрее, чем просто УДАЛЕНИЕ на каждой таблице.

PostgreSQL: Самая быстрая стратегия очистки баз данных - это удаление с теми же пустыми проверками, что и для MySQL, но вместо этого с использованием currval:

if table is not empty
  delete table
else 
  if currval is not 0
    delete table
  end
end
  • Для PostgreSQL просто удаление происходит намного быстрее, чем просто TRUNCATION(даже несколько).
  • Для PostgreSQL несколько TRUNCATE выполняют пустые проверки раньше, чем несколько раз TRUNCATE
  • Для PostgreSQL удаление с пустыми чеками происходит немного быстрее, чем просто удаление PostgreSQL.

Вот откуда это началось: https://github.com/bmabey/database_cleaner/issues/126

Это код результата и долгое обсуждение: https://github.com/bmabey/database_cleaner/pull/127

Это обсуждение списка рассылки pgsql-performance: http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php

Мы начали собирать отзывы пользователей, подтверждающие мою идею, сначала проверив правильность пустых таблиц.

Источник

PostgreSQL может обрезать множество таблиц в одном TRUNCATE TABLE заявление. Не заморачивайся, просто делай

TRUNCATE TABLE table1,table2,table3,...,table30;
Источник

Смотрите также:

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

для обсуждения того, почему усечение может быть медленнее на Pg, и почему DELETE - это не одно и то же.

Источник

В соответствии с просьбой в комментарии
(хотя я не чувствую, что это правильный ответ - но это слишком долго для комментария)

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

Как указано в руководстве (http://www.postgresql.org/docs/current/static/sql-truncate.html), "оно фактически не сканирует таблицы"

Поэтому, если вы сначала проверите, есть ли какие-либо строки в таблице, вы будете сканировать таблицу. То, что не произойдет, если вы просто выпустите truncate не беспокоясь ли

Источник

[Я не знаю RoR]

Хороший способ начать с чистого листа - создать и использовать временную SCHEMA:

DROP SCHEMA fuzz CASCADE;
CREATE SCHEMA fuzz;
SET search_path='fuzz';

(это то, что я использую для проверки фрагментов SQL). Но это создаст пустую схему, и вы не сможете скопировать схемы, IFAIK.

Другой способ - создать базу данных (включая пустые таблицы) и использовать ее в качестве шаблона для построения тестовой установки:

DROP DATABASE testdb;
CREATE DATABASE testdb TEMPLATE my_spcial_template;

Проблема в том, что вы не можете удалить базу данных, если к ней все еще есть подключения (например, сам процесс удаления). Поэтому ваш интерфейс должен сначала отключиться, чем временно подключиться к какой-либо другой БД (такой как my_spcial_template), чем dropdb + createb, чем подключить testdb. Я не знаю о производительности, но, по крайней мере, это надежная схема.

Источник
Другие вопросы по тегам