PK-гүй хүснэгтээс клон бичлэгүүдийг цэвэрлэж байна

Ийм нөхцөл байдал бий үндсэн түлхүүргүй хүснэгт рүү эсвэл бусад өвөрмөц индекс, хяналт шалгалтын улмаас аль хэдийн байгаа бүртгэлүүдийн бүрэн хуулбарыг оруулсан болно.

Жишээлбэл, он цагийн хэмжүүрийн утгыг COPY урсгалыг ашиглан PostgreSQL-д бичсэн бөгөөд дараа нь гэнэт алдаа гарч, бүрэн ижил өгөгдлийн хэсэг дахин ирдэг.

Шаардлагагүй клонуудын мэдээллийн санг хэрхэн арилгах вэ?

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-ээр тэмдэглэсэн боловч хэрэглээний үүднээс нэлээд хүчинтэй хэвээр байна (эцсийн эцэст өгөгдлийн утга өөр байна!).

Мэдээжийн хэрэг та үүнийг хийж чадна PK(метр, ts) - гэхдээ дараа нь бид хүчинтэй өгөгдөлд оруулах зөрчилтэй болно.

Чадуулж чадна PK(метр, ts, өгөгдөл) - гэхдээ энэ нь түүний эзлэхүүнийг ихээхэн нэмэгдүүлэх болно, бид үүнийг ашиглахгүй.

Тиймээс хамгийн зөв сонголт бол ердийн өвөрмөц бус индекс хийх явдал юм (метр, 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-ийг цуглуулах хэрэгтэй. Хамгийн энгийн сонголт бол мөрийг бүхэлд нь текст болгон оруулах явдал юм:

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

Өөрийгөө шалгацгаая:

[express.tensor.ru-г үзнэ үү]

Тийм ээ, бүх зүйл зөв байна: манай 3 бичлэгийг бүх хүснэгтийн цорын ганц Seq Scan-д сонгосон бөгөөд Устгах цэгийг өгөгдөл хайхад ашигласан. 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;

[express.tensor.ru-г үзнэ үү]

Тиймээс, энэ арга нь амжилттай ажилладаг, гэхдээ үүнийг болгоомжтой ашиглах хэрэгтэй. Учир нь устгасан бичлэг бүрийг Tid Scan-д уншсан мэдээллийн нэг хуудас, Устгахад нэг өгөгдлийн хуудас байна.

Эх сурвалж: www.habr.com