Yiyọ awọn igbasilẹ oniye kuro lati tabili laisi PK

Awọn ipo wa nigbati si tabili laisi bọtini akọkọ tabi diẹ ninu awọn atọka alailẹgbẹ miiran, nitori abojuto abojuto, awọn ere ibeji pipe ti awọn igbasilẹ ti o wa tẹlẹ wa pẹlu.

Fun apẹẹrẹ, awọn iye ti metiriki oni-ọjọ ni a kọ sinu PostgreSQL ni lilo ṣiṣan COPY kan, lẹhinna ikuna lojiji wa, ati apakan data kanna ti o jọra yoo de lẹẹkansi.

Bii o ṣe le yọ ibi ipamọ data kuro ti awọn ere ibeji ti ko wulo?

Nigbati PK kii ṣe oluranlọwọ

Ọna to rọọrun ni lati ṣe idiwọ iru ipo bẹẹ lati ṣẹlẹ ni aye akọkọ. Fun apẹẹrẹ, yipo KỌKỌRỌ PATAKI. Ṣugbọn eyi ko ṣee ṣe nigbagbogbo laisi jijẹ iwọn didun ti data ti o fipamọ.

Fun apẹẹrẹ, ti išedede ti eto orisun ba ga ju deede aaye ninu aaye data:

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}

Ṣe o ṣe akiyesi? The kika dipo ti 00:00:02 ti a gba silẹ ninu awọn database pẹlu ts a keji sẹyìn, ṣugbọn wà oyimbo wulo lati ohun elo ojuami ti wo (lẹhin ti gbogbo, awọn data iye yatọ!).

Dajudaju o le ṣe PK (metric, ts) - ṣugbọn lẹhinna a yoo gba awọn ija ifibọ fun data to wulo.

Le ṣe PK (metric, ts, data) - ṣugbọn eyi yoo mu iwọn didun rẹ pọ si, eyiti a kii yoo lo.

Nitorinaa, aṣayan ti o pe julọ ni lati ṣe atọka ti kii ṣe alailẹgbẹ deede (metric, ts) ki o si koju awọn iṣoro lẹhin otitọ ti wọn ba dide.

"Ogun clonic ti bẹrẹ"

Iru ijamba kan ṣẹlẹ, ati nisisiyi a ni lati pa awọn igbasilẹ oniye run lati tabili.

Jẹ ki a ṣe apẹẹrẹ data atilẹba:

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

Nibi ọwọ wa mì ni igba mẹta, Ctrl+V di, ati ni bayi…

Ni akọkọ, jẹ ki a loye pe tabili wa le tobi pupọ, nitorinaa lẹhin ti a rii gbogbo awọn ere ibeji, o ni imọran fun wa lati “fi ika wa” gangan lati paarẹ. awọn igbasilẹ pato lai tun wa wọn.

Ati pe iru ọna kan wa - eyi sọrọ nipa ctid, idanimọ ti ara ti igbasilẹ kan pato.

Iyẹn ni, ni akọkọ, a nilo lati gba ctid ti awọn igbasilẹ ni aaye ti akoonu pipe ti laini tabili. Aṣayan ti o rọrun julọ ni lati sọ gbogbo ila sinu ọrọ:

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

Ṣe o ṣee ṣe lati ma ṣe simẹnti?Ni opo, o ṣee ṣe ni ọpọlọpọ igba. Titi ti o ba bẹrẹ lilo awọn aaye ni tabili yii orisi lai onišẹ Equality:

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

Bẹẹni, a rii lẹsẹkẹsẹ pe ti titẹsi diẹ sii ju ọkan lọ ni titobi, iwọnyi jẹ gbogbo awọn ere ibeji. Jẹ ki a kan fi wọn silẹ:

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)

Fun awọn ti o nifẹ lati kọ kukuruO tun le kọ bi eleyi:

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

Niwọn bi iye ti okun serialized funrararẹ kii ṣe ohun ti o nifẹ si wa, a kan sọ ọ jade kuro ninu awọn ọwọn ti o pada ti abẹlẹ naa.

O ku diẹ diẹ lati ṣe - jẹ ki PELETE lo eto ti a gba:

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

Jẹ ki a ṣayẹwo ara wa:

[wo alaye.tensor.ru]

Bẹẹni, ohun gbogbo ni o tọ: awọn igbasilẹ 3 wa ni a yan fun Scan Seq nikan ti gbogbo tabili, ati pe a lo node Parẹ lati wa data nikan kọja pẹlu Tid Scan:

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

Ti o ba pa ọpọlọpọ awọn igbasilẹ kuro, maṣe gbagbe lati ṣiṣẹ VACUUM ANALYZE.

Jẹ ki a ṣayẹwo fun tabili nla ati pẹlu nọmba nla ti awọn ẹda-ẹda:

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;

[wo alaye.tensor.ru]

Nitorinaa, ọna naa ṣiṣẹ ni aṣeyọri, ṣugbọn o gbọdọ lo pẹlu iṣọra diẹ. Nitoripe fun gbogbo igbasilẹ ti o paarẹ, oju-iwe data kan wa ti a ka ni Tid Scan, ati ọkan ninu Parẹ.

orisun: www.habr.com