Обрезать строки и вставлять новые, не вводя перерыв в обслуживании?

Question

У меня есть около 1 500 000 записей в таблице базы данных PostgreSQL 9.5, и я получаю файл CSV (через запрос HTTP post), который содержит новые ~1 500 000 строк, где некоторые неизменны, некоторые отличаются, а некоторые удалены по сравнению с оригинальные строки.

После, я

1. Обрезать старую таблицу
2. Цикл по строкам файла CSV
3. Вставьте каждую строку в таблицу

Мне нужен способ сделать это без предоставления перерыва в обслуживании моим клиентам, то есть сервис должен продолжать использовать старые данные до тех пор, пока не будут выполнены все три шага. В настоящее время перерыв в обслуживании составляет ~1 час, вот сколько нужно времени, чтобы прочитать CSV и вставить все новые строки. Я в порядке с 5-минутным перерывом, если это требуется.

Как я могу добиться такого поведения?

Вот сокращенная версия моего скрипта Python:

cursor = conn.cursor(cursor_factory=DictCursor)
cursor.execute('TRUNCATE TABLE rows CASCADE')
with open(request.files.csv) as csv_file:
    for line in csv_file:
        row = parse_line(line)
        cursor.execute(
            '''INSERT INTO rows (name, bla, blu)
            VALUES (%(name)s, %(bla)s, %(blu)s)''',
            row,
        )
cursor.commit()

Answer 1

1. использование COPY вместо with open(request.files.csv)потому что 1 500000 строк копируются из CSV в таблицу за секунды
2. если эти секунды (допустим, одну минуту) слишком длинные, простое использование транзакции не поможет, потому что для усечения требуется блокировка таблицы, а не строк

TRUNCATE получает блокировку ACCESS EXCLUSIVE для каждой таблицы, на которой работает

Так что, если вы можете перестроить все зависимые объекты в таблице, лучше всего будет:

create t_table as select * from "rows" where false;
copy t_table from request.files.csv;
--build all needed dependant objects (indexes, constraints,triggers);
begin;
  alter table "rows" rename to "some_name";
  alter table "t_table " rename to "rows";
end;
--here is a miliseconds glitch to swith for users (if you use memcache or so - need to refresh it)
drop table "some_name";

Обновление для копирования столбцов из CSV в несколько столбцов списка столбцов таблицы:

COPY table_name [(column_name [,...])]