Условие

Несколько задач для проверки опыта работы с большими БД (PostgreSQL):

Дубликаты: таблица с автоинкрементным id и задвоенными строками. Как корректно их удалить?
Оператор-контроль: 10М+ записей операций. Оператор не может выполнить следующую операцию без контроля предыдущей. Заполнить таблицу контроля (tOper, tControl).
Шардированные счета: Accounts_01, Accounts_02, Accounts_03 (по банкам). View Accounts объединяет, > 1 млрд записей. Написать функцию поиска счетов клиента по банку, ФИО, дате рождения.
Индексы для оптимизации JOIN Accounts_01 ON Accounts_02 by FIO+dBirth WHERE FIO=... AND dBirth=... AND LEFT(account, 3) != '408'.
Объединение DR: Clients_1, Clients_2, ID привязаны к одному клиенту. Сводный запрос с DR из любой не-пустой таблицы.
Большая выборка: 50М клиентов, 200М счетов, 1млрд операций. Запрос: сумма операций конкретного клиента за июнь 2020.
Импорт CSV 20Гб в Postgres из Python.

Решение

1. Удаление дубликатов

DELETE FROM tbl WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY id) AS rn
    FROM tbl
  ) q WHERE rn > 1
);

Альтернатива (быстрее на больших таблицах) через CTID и subselect:

DELETE FROM tbl t1
WHERE EXISTS (
  SELECT 1 FROM tbl t2
  WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2
    AND t1.id < t2.id   -- оставляем строку с max(id)
);

Лучшее решение — INSERT INTO new_tbl SELECT DISTINCT ON (...) + truncate + rename, потому что DELETE 100M строк → reindex.

2. Оператор-контроль

«Оператор не может проводить операцию, если предыдущая требует контроля и не проконтролирована». Используем lag:

WITH ops AS (
  SELECT *,
         LAG(requires_control)  OVER (PARTITION BY operator_id ORDER BY t_oper) AS prev_req,
         LAG(controlled_at)     OVER (PARTITION BY operator_id ORDER BY t_oper) AS prev_ctrl
  FROM operations
)
SELECT operator_id, t_oper, t_control = controlled_at
FROM ops
WHERE requires_control = TRUE
  AND (prev_req IS NULL OR prev_ctrl IS NOT NULL);

3. Функция поиска счетов

CREATE OR REPLACE FUNCTION find_accounts(
  p_bank   INT,
  p_fio    TEXT,
  p_birth  DATE
) RETURNS TABLE(account TEXT, balance NUMERIC) AS $$
BEGIN
  IF p_bank = 1 THEN
    RETURN QUERY SELECT account, balance FROM Accounts_01
                  WHERE FIO = p_fio AND dBirth = p_birth;
  ELSIF p_bank = 2 THEN
    RETURN QUERY SELECT account, balance FROM Accounts_02
                  WHERE FIO = p_fio AND dBirth = p_birth;
  ELSE
    RETURN QUERY SELECT account, balance FROM Accounts_03
                  WHERE FIO = p_fio AND dBirth = p_birth;
  END IF;
END;
$$ LANGUAGE plpgsql;

Важно: запрос идёт на конкретную таблицу, не на view (избегаем UNION ALL для всех 3).

4. Индексы

Для запроса:

SELECT * FROM Accounts_01 a1 JOIN Accounts_02 a2
ON a1.FIO = a2.FIO AND a1.dBirth = a2.dBirth
WHERE a1.FIO = 'Иванов И.И.' AND a1.dBirth = '1990-05-17'
  AND LEFT(a1.account, 3) != '408';

Индексы:

Accounts_01(FIO, dBirth) — для поиска по WHERE.
Accounts_02(FIO, dBirth) — для join.
Дополнительно: partial index Accounts_01(FIO, dBirth) WHERE LEFT(account, 3) != '408' — если запрос частый.
Можно (FIO, dBirth, account) — covering index, чтобы не идти к heap.

LEFT(account, 3) — функция, не sargable. Замените LEFT(account, 3) != '408' на account NOT LIKE '408%' — будет использоваться индекс с prefix.

5. Сводная DR

SELECT c1.client_id,
       COALESCE(c1.dr, c2.dr) AS dr
FROM Clients_1 c1
FULL OUTER JOIN Clients_2 c2 ON c1.client_id = c2.client_id;

Если ID есть только в одной — берётся именно он.

6. Сумма операций клиента за июнь 2020

SELECT SUM(o.amount) AS total
FROM Clients c
JOIN Accounts a ON a.id_client = c.id
JOIN Operations o ON o.id_account = a.id
WHERE c.fio = 'Иванов И.И.'
  AND c.dBirth = '1990-05-17'
  AND o.dt >= '2020-06-01'
  AND o.dt <  '2020-07-01';

Оптимизация:

Index Clients(fio, dBirth).
Index Accounts(id_client).
Index Operations(id_account, dt) — чтобы хорошо обработать и join, и BETWEEN.
Partition Operations by dt (monthly).

7. Импорт CSV 20Гб

import psycopg2
 
conn = psycopg2.connect(...)
cur = conn.cursor()
with open('big.csv', 'r') as f:
    cur.copy_expert("""
      COPY frequencies(letter, frequency, percentage)
      FROM STDIN WITH (FORMAT csv, HEADER true)
    """, f)
conn.commit()

COPY — самый быстрый способ загрузки в PG (в 10–50× быстрее INSERT). Альтернатива — psql \copy от cmd.

Подводные камни

DELETE миллионы строк замедляется из-за WAL и индексов. Лучше INSERT INTO new + DROP old.
LAG на 10M+ записей в одном PARTITION — окно с full sort. Может быть medленно — partitioning по дате обязателен.
View Accounts через UNION ALL: при поиске по конкретному банку оптимизатор не всегда отсекает другие partitions. Используйте функцию.
LEFT(account, 3) не sargable — LIKE '408%' (с text_pattern_ops индексом) может быть гораздо быстрее.
COPY skips constraints: после загрузки сделайте VACUUM ANALYZE.

Эталонный ответ

ROW_NUMBER + DELETE или INSERT new + DROP old.
LAG/window function для контроль-цепочек.
Функция с явным IF на банк, не view.
Индексы (FIO, dBirth), LIKE '408%' вместо LEFT().
FULL OUTER JOIN + COALESCE.
Index Operations(id_account, dt) + partitioning by month.
COPY через psycopg2.copy_expert.

Home Credit Bank — оптимизация SQL-запросов и оператор-контроль