Klonen erregistroak PK gabe taula batetik garbitzea

Badaude egoerak noiz gako nagusirik gabeko mahai batera edo beste indize bereziren bat, gainbegiratu bat dela eta, lehendik dauden erregistroen klon osoak sartzen dira.

Esate baterako, metrika kronologiko baten balioak PostgreSQL-n idazten dira COPY korronte baten bidez, eta gero bat-bateko hutsegite bat gertatzen da eta datu guztiz berdinak berriro iristen dira.

Nola kendu datu-basea alferrikako klonetatik?

PK laguntzailea ez denean

Modurik errazena horrelako egoerarik ez gertatzea da lehenik eta behin. Adibidez, jaurti PRIMARY KEY. Baina hori ez da beti posible gordetako datuen bolumena handitu gabe.

Adibidez, iturri-sistemaren zehaztasuna datu-baseko eremuaren zehaztasuna baino handiagoa bada:

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}

Konturatu al zara? 00:00:02-ren ordez atzerako kontaketa datu-basean ts-ekin grabatu zen segundo bat lehenago, baina nahiko balio izan zuen aplikazioaren ikuspuntutik (azken finean, datuen balioak desberdinak dira!).

Noski egin dezakezula PK(metrikoa, ts) - baina gero datu baliozkoetarako txertatzeko gatazkak lortuko ditugu.

Egin dezake PK(metrikoa, ts, datuak) - baina honek bere bolumena asko handituko du, guk erabiliko ez duguna.

Hori dela eta, aukerarik zuzenena indize ez-bakarra arrunta egitea da (metrikoa, ts) eta arazoei aurre egin ondoren sortzen badira.

"Gerra klonikoa hasi da"

Nolabaiteko istripu bat gertatu zen, eta orain mahaitik klonen erregistroak suntsitu behar ditugu.

Eredu ditzagun jatorrizko datuak:

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

Hemen gure eskua hiru aldiz dardar egin zen, Ctrl+V trabatu egin zen, eta orain...

Lehenik eta behin, uler dezagun gure taula oso handia izan daitekeela, beraz, klon guztiak aurkitu ondoren, gomendagarria da literalki "hatza zartatzea" ezabatzeko. erregistro zehatzak berriro bilatu gabe.

Eta bada modu bat - hau ctid bidez zuzentzea, erregistro zehatz baten identifikatzaile fisikoa.

Hau da, lehenik eta behin, erregistroen ctid bildu behar dugu taulako errenkadaren eduki osoaren testuinguruan. Aukerarik errazena lerro osoa testura botatzea da:

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

Posible al da ez botatzea?Printzipioz, kasu gehienetan posible da. Taula honetako eremuak erabiltzen hasi arte berdintasun-operadorerik gabeko motak:

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

Bai, berehala ikusten dugu matrizean sarrera bat baino gehiago baldin badago, hauek guztiak klonak direla. Utz ditzagun:

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)

Laburrago idaztea gustuko dutenentzatHonela ere idatzi dezakezu:

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

Serializatutako katearen balioa bera ez zaigunez interesgarria denez, besterik gabe, azpikontsultaren itzuliko zutabeetatik bota dugu.

Pixka bat falta da egiteko - egin DELETE-ri jaso dugun multzoa erabiltzea:

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

Azter dezagun geure burua:

[ikusi explain.tensor.ru helbidean]

Bai, dena zuzena da: gure 3 erregistroak taula osoko Seq Scan bakarrerako hautatu ziren, eta Ezabatu nodoa datuak bilatzeko erabili zen. pase bakarra Tid Scan-ekin:

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

Erregistro asko garbitu badituzu, ez ahaztu VACUUM ANALYZE exekutatzen.

Ikus dezagun taula handiagoa eta bikoiztu kopuru handiagoa duen:

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;

[ikusi explain.tensor.ru helbidean]

Beraz, metodoak arrakastaz funtzionatzen du, baina kontu handiz erabili behar da. Ezabatzen den erregistro bakoitzeko, Tid Scan-en datu-orri bat irakurtzen baita eta Ezabatu-n.

Iturria: www.habr.com