PKsiz jadvaldan klon yozuvlarini tozalash

Bunday holatlar mavjud asosiy kalitsiz jadvalga yoki boshqa noyob indeks, nazorat tufayli, allaqachon mavjud yozuvlarning to'liq klonlari kiritilgan.

Masalan, xronologik metrikaning qiymatlari COPY oqimi yordamida PostgreSQL-ga yoziladi, keyin to'satdan nosozlik yuz beradi va butunlay bir xil ma'lumotlarning bir qismi yana keladi.

Ma'lumotlar bazasini keraksiz klonlardan qanday tozalash mumkin?

PK yordamchi bo'lmasa

Eng oson yo'li, birinchi navbatda, bunday vaziyatning paydo bo'lishining oldini olishdir. Masalan, PRIMARY KEY-ni aylantiring. Lekin bu saqlangan ma'lumotlar hajmini oshirmasdan har doim ham mumkin emas.

Masalan, agar manba tizimining aniqligi ma'lumotlar bazasidagi maydonning aniqligidan yuqori bo'lsa:

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}

Siz sezdingizmi? 00:00:02 o'rniga ortga hisoblash ma'lumotlar bazasida bir soniya oldin ts bilan qayd etilgan, ammo dastur nuqtai nazaridan juda haqiqiy bo'lib qoldi (axir, ma'lumotlar qiymatlari boshqacha!).

Albatta qila olasiz PK(metrik, ts) - lekin keyin biz haqiqiy ma'lumotlar uchun kiritish ziddiyatlarini olamiz.

Qilishingiz mumkin PK(metrik, ts, ma'lumotlar) - lekin bu uning hajmini sezilarli darajada oshiradi, biz undan foydalanmaymiz.

Shuning uchun, eng to'g'ri variant odatiy bo'lmagan indeksni yaratishdir (metrik, ts) va agar ular paydo bo'lsa, haqiqatdan keyin muammolarni hal qiling.

"Klonik urush boshlandi"

Qandaydir baxtsiz hodisa yuz berdi va endi biz jadvaldagi klon yozuvlarini yo'q qilishimiz kerak.

Keling, asl ma'lumotlarni modellashtiramiz:

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)
;

Bu erda qo'limiz uch marta titradi, Ctrl+V tiqilib qoldi, endi esa...

Birinchidan, bizning stolimiz juda katta bo'lishi mumkinligini tushunamiz, shuning uchun barcha klonlarni topganimizdan so'ng, o'chirish uchun tom ma'noda "barmog'imizni urishimiz" tavsiya etiladi. maxsus yozuvlarni ularni qayta izlamasdan.

Va shunday yo'l bor - bu ctid tomonidan murojaat qilish, ma'lum bir yozuvning jismoniy identifikatori.

Ya'ni, birinchi navbatda, jadval qatorining to'liq mazmuni kontekstida yozuvlar ctidini to'plashimiz kerak. Eng oddiy variant butun qatorni matnga aylantirishdir:

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)"}

Chiqib ketmaslik mumkinmi?Asos sifatida, ko'p hollarda mumkin. Ushbu jadvaldagi maydonlardan foydalanishni boshlamaguningizcha tenglik operatorisiz turlar:

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

Ha, biz darhol ko'ramizki, agar massivda bir nechta yozuv bo'lsa, bularning barchasi klonlardir. Keling, ularni qoldiramiz:

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)

Qisqaroq yozishni yaxshi ko'radiganlar uchunSiz ham shunday yozishingiz mumkin:

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

Seriyalashtirilgan satrning o'zi biz uchun qiziq emasligi sababli, biz uni pastki so'rovning qaytarilgan ustunlaridan chiqarib tashladik.

Ozgina ish qoldi – biz olgan to‘plamdan DELETE-ni ishlating:

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[]);

Keling, o'zimizni tekshiramiz:

[express.tensor.ru saytiga qarang]

Ha, hamma narsa to'g'ri: bizning 3 ta yozuvimiz butun jadvalning yagona Seq Scan uchun tanlangan va ma'lumotlarni qidirish uchun "O'chirish" tugunidan foydalanilgan. Tid Scan bilan bitta o'tish:

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

Agar siz ko'p yozuvlarni tozalagan bo'lsangiz, VACUUM ANALYZE-ni ishga tushirishni unutmang.

Keling, kattaroq jadvalni va ko'proq dublikatlarni tekshiramiz:

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;

[express.tensor.ru saytiga qarang]

Shunday qilib, usul muvaffaqiyatli ishlaydi, lekin uni biroz ehtiyotkorlik bilan ishlatish kerak. Chunki o'chirilgan har bir yozuv uchun Tid Scan-da o'qiladigan bitta ma'lumotlar sahifasi va O'chirishda bitta sahifa mavjud.

Manba: www.habr.com