Каковы последствия различных уровней изоляции в PostgreSQL для производительности?

Я пишу архивный скрипт (на Python с использованием psycopg2), который должен извлечь очень большой объем данных из базы данных PostgreSQL (9.4), обработать, загрузить и затем удалить их из базы данных.

Я запускаю транзакцию, выполняю оператор select для создания именованного курсора, извлекаю N строк за раз из курсора и выполняю обработку и загрузку частей (используя многокомпонентную загрузку S3). Как только курсор исчерпан и ошибок не возникло, я завершаю загрузку и выполняю оператор удаления, используя те же условия, что и при выборе. Если удаление завершается успешно, я фиксирую транзакцию.

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

Итак вопросы у меня такие:

Какой уровень изоляции следует использовать, чтобы обеспечить архивирование и удаление одних и тех же строк?

Какое влияние эти операции окажут на возможность чтения/записи базы данных? Блокируется ли что-либо на запись или чтение в описанном выше процессе?


postgresql sql transactions archiving
person Mad Wombat    schedule 22.02.2019    source источник

Ответы (1)


arrow_upward
2
arrow_downward

У вас есть два хороших варианта:

  1. Получить данные с

    SELECT ... FOR UPDATE

    чтобы ряды были заблокированы. Тогда они гарантированно будут там, когда вы их удалите.

  2. Использовать

    DELETE FROM ... RETURNING *

    Затем вставьте возвращенные строки в свой архив.

Второе решение лучше, потому что вам нужен только один оператор.

Ничего плохого не может случиться. Если по какой-либо причине транзакция не удалась, ни одна строка не будет удалена.

Вы можете использовать уровень изоляции по умолчанию READ COMMITTED для обоих решений.

person Laurenz Albe    schedule 22.02.2019
comment
Могу ли я использовать именованный курсор со вторым подходом? Объемы данных, с которыми я работаю, огромны, я не могу спокойно скачать все и потом заархивировать, это приходится делать частями. Первый подход, похоже, не гарантирует, что строки, вставленные во время обработки, не изменят результаты более позднего запроса. - person Mad Wombat; 22.02.2019
comment
Тогда лучше всего использовать курсор с DELETE FROM tab WHERE CURRENT OF c, где c — курсор. Это можно использовать с первым подходом. К сожалению, второй подход нельзя использовать с курсором. - person Laurenz Albe; 22.02.2019
comment
В настоящее время я извлекаю данные кусками по сто тысяч строк за раз. Есть ли способ адаптировать подход WHERE CURRENT к выборке/удалению нескольких строк за раз? - person Mad Wombat; 22.02.2019
comment
Похоже, мне придется делать это программно. Я могу принудительно упорядочить по первичному ключу при выборе и использовать автоматически увеличивающийся первичный ключ, чтобы отметить определенное место в таблице как точку отсечки для удаления. - person Mad Wombat; 22.02.2019
comment
Есть FETCH NEXT n FROM c. - person Laurenz Albe; 22.02.2019
comment
Да, это то, что я использую прямо сейчас. Я просто не уверен, как использовать его в сочетании с DELETE FROM t WHERE CURRENT OF c - person Mad Wombat; 22.02.2019
comment
Эм, я не думал. Что ж, либо вы выбираете курсор построчно, либо вы можете найти способ использовать подход DELETE ... RETURNING * в пакетах, добавляя дополнительные условия, чтобы каждый пакет был достаточно маленьким; например диапазон дат. - person Laurenz Albe; 22.02.2019