Ответ 1

Столбец/Строка

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

Любой UPDATE в Модель PostgreSQL MVCC создает новую версию всей строки. Если одновременные транзакции меняют любой столбец той же строки, возникают трудоемкие проблемы concurrency. Подробности в руководстве. Знание того же столбца не будет затронуто параллельными транзакциями, избегает некоторых возможных осложнений, но не других.

Индекс

Чтобы избежать переадресации в оффтопическую дискуссию, допустим, что все значения статуса для 35 миллионов столбцов в настоящее время установлены к тому же (ненулевому) значению, что делает индекс бесполезным.

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

Производительность

Например, скажем, у меня есть таблица под названием "заказы" с 35 миллионами строки, и я хочу сделать это:

UPDATE orders SET status = null;

Я понимаю, что вы нацелены на более общее решение (см. ниже). Но для решения заданного вопроса: это можно решить в миллисекундах, независимо от размера таблицы:

ALTER TABLE orders DROP column status
                 , ADD  column status text;

В документации:

Когда столбец добавлен с ADD COLUMN, все существующие строки в таблице инициализируются значением столбца по умолчанию (NULL, если нет DEFAULTзадается условие). Если не существует предложения DEFAULT, это просто изменение метаданных...

и

Форма DROP COLUMN физически не удаляет столбец, а просто делает его невидимым для операций SQL. Последующая вставка и обновление операции в таблице будут хранить нулевое значение для столбца. Таким образом, удаление столбца происходит быстро, но это не сразу уменьшит размер диска на вашем диске, так как пространство, занятое сброшенным колонка не исправлена. Пространство будет исправлено с течением времени, поскольку существующие строки обновляются. (Эти утверждения не применяются, когда удаление столбца системы oid; это делается с немедленным переписыванием.)

Убедитесь, что у вас нет объектов в зависимости от столбца (ограничения внешнего ключа, индексы, представления,...). Вам нужно будет отбросить/воссоздать их. Запрет на то, что крошечные операции в таблице системного каталога pg_attribute выполняют эту работу. Требуется эксклюзивная блокировка в таблице, которая может быть проблемой для большой одновременной нагрузки. Поскольку это занимает всего несколько миллисекунд, вы все равно должны быть в порядке.

Если у вас есть значение по умолчанию, которое вы хотите сохранить, добавьте его в отдельную команду. Выполнение этой же команды немедленно применило бы ее ко всем строкам, освободив эффект. Затем вы можете обновить существующие столбцы в партиях. Следуйте ссылке на документацию и прочитайте примечания в руководстве.

Общее решение

dblink упоминается в другом ответе. Он позволяет получить доступ к "remote" базам данных Postgres в неявных отдельных соединениях. База данных "remote" может быть текущей, тем самым достигая "автономных транзакций": то, что функция записывает в "remote" db, совершается и ее нельзя отменить.

Это позволяет запускать одну функцию, которая обновляет большую таблицу в меньших частях, и каждая часть выполняется отдельно. Избегает наращивания транзакционных издержек для очень большого количества строк и, что более важно, выпускает блокировки после каждой части. Это позволяет проводить параллельные операции без большой задержки и делает блокировку менее вероятной.

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

Отказ

Прежде всего, многие небольшие транзакции на самом деле дороже. Этот имеет смысл только для больших таблиц. Сладкое пятно зависит от многих факторов.

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

Пошаговые инструкции

Сначала необходимо установить дополнительный dblink модуля:

• Как использовать (установить) dblink в PostgreSQL?

Настройка соединения с dblink очень сильно зависит от настройки вашего кластера БД и политик безопасности на месте. Это может быть сложно. Связанный позже ответ с более , как подключиться с dblink:

• Постоянные вставки в UDF, даже если функция прервана

Создайте FOREIGN SERVER и USER MAPPING, как описано там, чтобы упростить и оптимизировать соединение (если только у вас его нет).
Предполагая a serial PRIMARY KEY с некоторыми пробелами или без них.

CREATE OR REPLACE FUNCTION f_update_in_steps()
  RETURNS void AS
$func$
DECLARE
   _step int;   -- size of step
   _cur  int;   -- current ID (starting with minimum)
   _max  int;   -- maximum ID
BEGIN
   SELECT INTO _cur, _max  min(order_id), max(order_id) FROM orders;
                                        -- 100 slices (steps) hard coded
   _step := ((_max - _cur) / 100) + 1;  -- rounded, possibly a bit too small
                                        -- +1 to avoid endless loop for 0
   PERFORM dblink_connect('myserver');  -- your foreign server as instructed above

   FOR i IN 0..200 LOOP                 -- 200 >> 100 to make sure we exceed _max
      PERFORM dblink_exec(
       $$UPDATE public.orders
         SET    status = 'foo'
         WHERE  order_id >= $$ || _cur || $$
         AND    order_id <  $$ || _cur + _step || $$
         AND    status IS DISTINCT FROM 'foo'$$);  -- avoid empty update

      _cur := _cur + _step;

      EXIT WHEN _cur > _max;            -- stop when done (never loop till 200)
   END LOOP;

   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

Вызов:

SELECT f_update_in_steps();

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

• Имя таблицы как параметр функции PostgreSQL

Об избежании пустого UPDATE:

• Как мне (или я могу) выбрать SELECT DISTINCT в нескольких столбцах?

Ответ 2

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

Возможно, некоторые из строк уже имеют status NULL?

Если да, то:

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

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

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

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

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

Ответ 3

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

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

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

truncate order_status;

Ответ 4

Я бы использовал CTAS:

begin;
create table T as select col1, col2, ..., <new value>, colN from orders;
drop table orders;
alter table T rename to orders;
commit;

Ответ 5

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

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

Ответ 6

Я ни в коем случае не администратор базы данных, но дизайн базы данных, где вам часто приходится обновлять 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 находится там, чтобы отслеживать, насколько далеко продвинулся script. Если вы не отслеживаете уведомления или не заботитесь, было бы лучше оставить это.

Ответ 7

Вы уверены, что это из-за блокировки? Я так не думаю, и есть много других возможных причин. Чтобы узнать, вы всегда можете попытаться сделать только блокировку. Попробуй это: НАЧАТЬ; SELECT NOW(); SELECT * FROM order FOR UPDATE; SELECT NOW(); ROLLBACK;

Чтобы понять, что происходит на самом деле, вы должны сначала запустить EXPLAIN (EXPLAIN UPDATE заказывает статус SET...) и/или EXPLAIN ANALYZE. Возможно, вы обнаружите, что недостаточно памяти для эффективного выполнения UPDATE. Если это так, SET work_mem TO 'xxxMB'; может быть простым решением.

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

Ответ 8

Некоторые параметры, которые не были упомянуты:

Используйте тэг new table. Вероятно, что вам нужно было сделать в вашем случае, написать несколько триггеров для его обработки, чтобы изменения в исходной таблице также распространялись на вашу копию таблицы, что-то вроде этого... (percona является примером того, что делает это триггером). Другим вариантом может быть "создать новый столбец, а затем заменить старый" трюк, чтобы избежать блокировок (неясно, помогает ли скорость).

Возможно, вычислите max ID, затем сгенерируйте "все необходимые вам запросы" и передайте их в виде одного запроса, такого как update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ..., тогда он может не делать столько блокировки и все равно быть всем SQL, хотя у вас есть дополнительные логику перед этим: (