კლონის ჩანაწერების გასუფთავება ცხრილიდან 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-ით წამით ადრე, მაგრამ აპლიკაციის თვალსაზრისით საკმაოდ ძალაში დარჩა (ბოლოს და ბოლოს, მონაცემთა მნიშვნელობები განსხვავებულია!).

რა თქმა უნდა, შეგიძლიათ ამის გაკეთება 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[]);

მოდით გადავამოწმოთ საკუთარი თავი:

[ნახეთ description.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;

[ნახეთ description.tensor.ru]

ასე რომ, მეთოდი წარმატებით მუშაობს, მაგრამ ის უნდა იქნას გამოყენებული გარკვეული სიფრთხილით. რადგან ყველა წაშლილი ჩანაწერისთვის არის ერთი მონაცემთა გვერდი წაკითხული Tid Scan-ში და ერთი წაშლაში.

წყარო: www.habr.com