پاک کردن رکوردهای کلون از یک جدول بدون PK

شرایطی وجود دارد که به یک جدول بدون کلید اصلی یا برخی از شاخص های منحصر به فرد دیگر، به دلیل یک نظارت، کلون های کامل رکوردهای موجود در آن گنجانده شده است.

به عنوان مثال، مقادیر یک متریک زمانی با استفاده از یک جریان COPY در PostgreSQL نوشته می شود و سپس یک شکست ناگهانی رخ می دهد و بخشی از داده های کاملاً یکسان دوباره می رسد.

چگونه پایگاه داده را از کلون های غیر ضروری خلاص کنیم؟

وقتی پی کی کمک کننده نیست

ساده ترین راه جلوگیری از وقوع چنین وضعیتی در وهله اول است. به عنوان مثال، کلید اولیه را رول کنید. اما این همیشه بدون افزایش حجم داده های ذخیره شده امکان پذیر نیست.

به عنوان مثال، اگر دقت سیستم منبع بالاتر از دقت فیلد در پایگاه داده باشد:

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

بیایید خودمان را بررسی کنیم:

[به توضیح.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;

[به توضیح.tensor.ru نگاه کنید]

بنابراین، این روش با موفقیت کار می کند، اما باید با احتیاط از آن استفاده کرد. زیرا برای هر رکوردی که حذف می شود، یک صفحه داده در Tid Scan خوانده می شود و یک صفحه در Delete.

منبع: www.habr.com