Удаление многих строк без их блокировки
В PostgreSQL у меня есть запрос, подобный следующему, который удаляет 250 тыс. Строк из таблицы строк длиной 1 м:
DELETE FROM table WHERE key = 'needle';
Для выполнения запроса требуется более часа и в течение этого времени затронутые строки блокируются для записи. Это нехорошо, потому что это означает, что многие запросы на обновление должны ждать завершения большого запроса на удаление (а затем они потерпят неудачу, потому что строки исчезли из-под них, но это нормально). Мне нужен способ разбить этот большой запрос на несколько частей, чтобы они вызывали как можно меньше помех для запросов на обновление. Например, если запрос на удаление может быть разбит на куски, каждый из которых содержит 1000 строк, то другим запросам на обновление самое большее придется ждать запроса на удаление, включающего 1000 строк.
DELETE FROM table WHERE key = 'needle' LIMIT 10000;
Этот запрос будет работать хорошо, но, увы, его нет в postgres.
3 ответа
Попробуйте использовать подвыборку и уникальное условие:
DELETE FROM
table
WHERE
id IN (SELECT id FROM table WHERE key = 'needle' LIMIT 10000);
устанавливает уровень блокировки для удаления и обновления до более детального режима блокировки. обратите внимание, что ваши транзакции теперь будут выполняться медленнее.
http://www.postgresql.org/docs/current/static/sql-lock.html
http://www.postgresql.org/docs/current/static/explicit-locking.html
Ответ Frak'а хорош, но это может быть быстрее, но требует 8.4 из-за поддержки оконных функций (псевдокод):
result = query('select
id from (
select id, row_number(*) over (order by id) as row_number
from mytable where key=?
) as _
where row_number%8192=0 order by id', 'needle');
// result contains ids of every 8192nd row which key='needle'
last_id = 0;
result.append(MAX_INT); // guard
for (row in result) {
query('delete from mytable
where id<=? and id>? and key=?', row.id, last_id, 'needle');
// last_id is used to hint query planner,
// that there will be no rows with smaller id
// so it is less likely to use full table scan
last_id = row.id;
}
Это преждевременная оптимизация - злая штука. Остерегайтесь.