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

Өзімізді тексеріп көрейік:

[express.tensor.ru сайтынан қарау]

Иә, бәрі дұрыс: біздің 3 жазбамыз бүкіл кестенің жалғыз реттік сканерлеуі үшін таңдалды және деректерді іздеу үшін Жою түйіні пайдаланылды. 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