Ukucoca iirekhodi ze-clone kwitafile ngaphandle kwe-PK

Kukho iimeko xa kwitafile engenasitshixo sokuqala okanye esinye isalathiso esisodwa, ngenxa yokongamela, iikopi ezipheleleyo zeerekhodi esele zikho zibandakanyiwe.

Ngokomzekelo, amaxabiso e-metric yokulandelelana abhalwa kwi-PostgreSQL usebenzisa umjelo we-COPY, kwaye emva koko kukho ukusilela ngequbuliso, kwaye inxalenye yedatha efana ngokupheleleyo ifika kwakhona.

Indlela yokususa i-database yee-clones ezingafunekiyo?

Xa i-PK ingengomncedi

Eyona ndlela ilula kukuthintela ukuba imeko enjalo ingenzeki kwasekuqaleni. Umzekelo, roll PRIMARY KEY. Kodwa oku akusoloko kunokwenzeka ngaphandle kokunyusa umthamo wedatha egciniweyo.

Umzekelo, ukuba ukuchaneka kwenkqubo yomthombo kuphezulu kunokuchaneka kwendawo ekwisiseko sedatha:

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}

Ngaba uqaphele? I-countdown endaweni ye-00:00:02 yarekhodwa kwisiseko sedatha kunye ne-ts okwesibini ngaphambili, kodwa yahlala isemthethweni ukusuka kwindawo yokujonga isicelo (emva koko, amaxabiso edatha ahlukile!).

Ewe ungayenza PK(metric, ts) - kodwa ke siya kufumana ukungqubana kokufaka kwidatha esebenzayo.

Ungenza PK(metric, ts, data) - kodwa oku kuya kwandisa kakhulu umthamo wayo, esingayi kuyisebenzisa.

Ngoko ke, olona khetho luchanekileyo kukwenza isalathisi esingaqhelekanga (imitha, ts) kwaye ujongane neengxaki emva kwenyani ukuba zivela.

"Imfazwe yeclonic iqalile"

Olunye uhlobo lwengozi lwenzeke, kwaye ngoku kufuneka sitshabalalise iirekhodi ze-clone ezivela etafileni.

Masenze umzekelo wedatha yoqobo:

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

Apha isandla sethu sangcangcazela kathathu, Ctrl+V waxinga, kwaye ngoku...

Okokuqala, masiqonde ukuba itafile yethu ingaba nkulu kakhulu, ngoko emva kokuba sifumene zonke iiclones, kuyacetyiswa ukuba "sibambe umnwe wethu" ngokoqobo ukucima. iirekhodi ezithile ngaphandle kokuphinda kuziphendle.

Kwaye kukho indlela enjalo - oku intetho ngoctid, isazisi esibonakalayo serekhodi ethile.

Oko kukuthi, okokuqala, kufuneka siqokelele i-ctid yeerekhodi kumxholo womxholo opheleleyo woluhlu lwetheyibhile. Olona khetho lulula kukuphosa umgca wonke kumbhalo:

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

Ngaba kunokwenzeka ukuba ungalibali?Ngokomgaqo, kunokwenzeka kwiimeko ezininzi. Ude uqale ukusebenzisa imihlaba kule theyibhile iintlobo ngaphandle komqhubi wokulingana:

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

Ewe, sibona kwangoko ukuba kukho ngaphezulu kokungeniswa kwesinye kuluhlu, ezi ziiclones zonke. Masibashiye nje:

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)

Kwabo bathanda ukubhala ngokufutshaneUnako kwakhona ukuyibhala ngolu hlobo:

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

Kuba ixabiso lomtya wolandelelwano ngokwalo alinamdla kuthi, sivele salijula ​​ngaphandle kweekholamu ezibuyisiweyo ze-subquery.

Kushiyeke kancinci ukwenza - yenza DELETE usebenzise iseti esiyifumeneyo:

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

Masizijonge:

[Jonga apha explain.tensor.ru]

Ewe, yonke into ichanekile: iirekhodi zethu ezi-3 zikhethwe kuphela kweSeq Scan yetafile yonke, kwaye i-node yokuCima isetyenziselwa ukukhangela idatha. ipasile enye ngeTid Scan:

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

Ukuba ucime iirekhodi ezininzi, ungalibali ukuqhuba iVACUUM ANALYZE.

Makhe sijonge itafile enkulu kunye nenani elikhulu lokuphinda:

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;

[Jonga apha explain.tensor.ru]

Ngoko ke, indlela isebenza ngempumelelo, kodwa kufuneka isetyenziswe ngononophelo oluthile. Kuba kuyo yonke irekhodi ecinyiweyo, kukho iphepha elinye ledatha elifundwe kwiTid Scan, kunye nelinye kuCima.

umthombo: www.habr.com