Как я делаю большие обновления неблокирования в PostgreSQL?

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

Так что, если это действительно занимает 5 часов, это должно быть по другой причине (например, что вы делаете одновременно пишет, вопреки вашему утверждению, что вы этого не делаете).

Прежде всего - вы уверены, что вам нужно обновить все строки?

Возможно, в некоторых строках уже есть status NULL?

Если да, то:

UPDATE orders SET status = null WHERE status is not null;

Что касается разделения изменений - это невозможно в чистом sql. Все обновления выполняются в одной транзакции.

Один из возможных способов сделать это в "чистом sql" - это установить dblink, подключиться к той же базе данных с помощью dblink, а затем выпустить множество обновлений через dblink, но это кажется излишним для такой простой задачи.

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

perl -e '
    for (my $i = 0; $i <= 3500000; $i += 1000) {
        printf "UPDATE orders SET status = null WHERE status is not null
                and order_id between %u and %u;\n",
        $i, $i+999
    }
'

Я заключил здесь строки для удобства чтения, обычно это одна строка. Вывод вышеуказанной команды может быть передан в psql напрямую:

perl -e '...' | psql -U ... -d ...

Или сначала в файл, а затем в psql (на случай, если он понадобится позже):

perl -e '...' > updates.partitioned.sql
psql -U ... -d ... -f updates.partitioned.sql

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

Простой WHERE status IS NOT NULL может немного ускорить процесс (при условии, что у вас есть индекс состояния) - не зная фактического варианта использования, я предполагаю, что если он выполняется часто, большая часть из 35 миллионов строк может уже иметь нулевой статус.

Однако вы можете создавать циклы в запросе с помощью оператора LOOP . Я просто приготовлю небольшой пример:

CREATE OR REPLACE FUNCTION nullstatus(count INTEGER) RETURNS integer AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    FOR i IN 0..(count/1000 + 1) LOOP
        UPDATE orders SET status = null WHERE (order_id > (i*1000) and order_id <((i+1)*1000));
        RAISE NOTICE 'Count: % and i: %', count,i;
    END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Затем его можно запустить, выполнив что-то вроде:

SELECT nullstatus(35000000);

Вы можете выбрать количество строк, но учтите, что точное количество строк может занять много времени. В вики PostgreSQL есть статья о медленном подсчете и о том, как его избежать .

Кроме того, часть RAISE NOTICE предназначена только для того, чтобы отслеживать, насколько далеко продвинулся сценарий. Если вы не следите за уведомлениями или вам все равно, лучше не указывать их.

Вы должны делегировать этот столбец другой таблице следующим образом:

create table order_status (
  order_id int not null references orders(order_id) primary key,
  status int not null
);

Тогда ваша операция установки status = NULL будет мгновенной:

truncate order_status;

Вы уверены, что это из-за блокировки? Я так не думаю, и есть много других возможных причин. Чтобы выяснить это, вы всегда можете попробовать сделать просто блокировку. Попробуй это: НАЧАТЬ; ВЫБЕРИТЕ СЕЙЧАС (); ВЫБРАТЬ * ИЗ ЗАКАЗА ДЛЯ ОБНОВЛЕНИЯ; ВЫБЕРИТЕ СЕЙЧАС (); ROLLBACK;

Чтобы понять, что на самом деле происходит, вы должны сначала запустить EXPLAIN (EXPLAIN UPDATE заказывает статус SET ...) и / или EXPLAIN ANALYZE. Возможно, вы обнаружите, что у вас недостаточно памяти для эффективного выполнения ОБНОВЛЕНИЯ. Если это так, УСТАНОВИТЕ work_mem в «xxxMB»; может быть простым решением.

Также следите за журналом PostgreSQL, чтобы увидеть, не возникают ли какие-либо проблемы, связанные с производительностью.