Вичищаємо клон-записи з таблиці без PK

Трапляються ситуації, коли у таблицю без первинного ключа або якогось іншого унікального індексу недогляду потрапляють повні клони вже існуючих записів.

Наприклад, пишуться в PostgreSQL COPY-потоком значення хронологічної метрики, а потім раптовий збій, і частина повністю ідентичних даних надходить повторно.

Як позбавити основу від непотрібних клонів?

Коли PK не помічник

Найпростіший спосіб взагалі не допустити виникнення такої ситуації. Наприклад, накотити-таки PRIMARY KEY. Але це можливо не завжди без збільшення обсягу даних, що зберігаються.

Наприклад, якщо точність вихідної системи вища, ніж точність поля в БД:

metric   | ts                  | data
--------------------------------------------------
cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 10}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2}
cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}

Помітили? Відлік замість 00:00:02 записався в базу з ts на секунду раніше, але залишився цілком валідним з прикладної точки зору (адже значення data різні!).

Звісно, ​​можна зробити PK(metric, ts) — але тоді ми отримуватимемо конфлікти вставки для валідних даних.

Можна зробити PK(metric, ts, data) — але це сильно збільшить його обсяг, яким ми й користуватися не будемо.

Тому найправильніший варіант — зробити звичайний неунікальний індекс (Metric, ts) і розбиратися з проблемами постфактум, якщо вони таки виникнуть.

«Війна клонічна почалася»

Сталася якась аварія, і тепер нам належить знищити клон-записи з таблиці.

Давайте змоделюємо вихідні дані:

CREATE TABLE tbl(k text, v integer);

INSERT INTO tbl
VALUES
  ('a', 1)
, ('a', 3)
, ('b', 2)
, ('b', 2) -- oops!
, ('c', 3)
, ('c', 3) -- oops!!
, ('c', 3) -- oops!!
, ('d', 4)
, ('e', 5)
;

Тут у нас тричі здригнулася рука, залип Ctrl+V, і ось…

Спочатку зрозуміємо, що таблиця у нас може бути дуже немаленькою, тому після того, як ми знайдемо всі клони, нам бажано буквально «тикати пальцем», щоб видаляти конкретні записи без повторного їх пошуку.

І такий спосіб є – це адресація по ctid, фізичному ідентифікатору конкретного запису

Тобто передусім нам треба зібрати ctid записів у розрізі повного контенту рядка таблиці. Найпростіший варіант — скастувати весь рядок у text:

SELECT
  T::text
, array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  1;

t     | ctids
---------------------------------
(e,5) | {"(0,9)"}
(d,4) | {"(0,8)"}
(c,3) | {"(0,5)","(0,6)","(0,7)"}
(b,2) | {"(0,3)","(0,4)"}
(a,3) | {"(0,2)"}
(a,1) | {"(0,1)"}

А чи можна кастувати?У принципі — можна здебільшого. Поки ви не почнете використовувати в цій таблиці поля типів без оператора рівності:

CREATE TABLE tbl(k text, v integer, x point);
SELECT
  array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  T;
-- ERROR:  could not identify an equality operator for type tbl

Ага, одразу бачимо, що якщо в масиві виявилося більше одного запису, то це все і є клони. Давайте залишимо тільки їх:

SELECT
  unnest(ctids[2:])
FROM
  (
    SELECT
      array_agg(ctid) ctids
    FROM
      tbl T
    GROUP BY
      T::text
  ) T;

unnest
------
(0,6)
(0,7)
(0,4)

Любителям писати коротшеМожна написати і ось так:

SELECT
  unnest((array_agg(ctid))[2:])
FROM
  tbl T
GROUP BY
  T::text;

Оскільки саме значення серіалізованого рядка нам нецікаве, то ми його просто викинули з стовпців підзапиту, що повертаються.

Залишилося небагато — змусити DELETE використовувати отриманий нами набір:

DELETE FROM
  tbl
WHERE
  ctid = ANY(ARRAY(
    SELECT
      unnest(ctids[2:])
    FROM
      (
        SELECT
          array_agg(ctid) ctids
        FROM
          tbl T
        GROUP BY
          T::text
      ) T
  )::tid[]);

Перевіримо себе:

[Подивитися на explain.tensor.ru]

Так, все правильно: наші 3 записи відібралися за єдиний Seq Scan всієї таблиці, а Delete-вузол використовував для пошуку даних одноразовий прохід за допомогою Tid Scan:

->  Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
      TID Cond: (ctid = ANY ($0))

Якщо зачистили багато записів, не забуваємо прогнати VACUUM ANALYZE.

Перевіримо для таблиці більше і з великою кількістю дублів:

TRUNCATE TABLE tbl;

INSERT INTO tbl
SELECT
  chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z
, (random() * 100)::integer v -- 0..99
FROM
  generate_series(1, 10000) i;

[Подивитися на explain.tensor.ru]

Отже, спосіб успішно працює, але застосовувати треба з певною обережністю. Тому що на кожен запис, що видаляється, припадає одне читання сторінки даних в Tid Scan, і одне - в Delete.

Джерело: habr.com