Ho hlakola lirekoto tsa clone tafoleng ntle le PK

Ho na le maemo ha ho ya tafoleng ntle le senotlolo sa mantlha kapa index e 'ngoe e ikhethang, ka lebaka la tlhokomeliso, li-clones tse felletseng tsa lirekoto tse seng li ntse li le teng li kenyelelitsoe.

Mohlala, boleng ba metric ea tatellano ea liketsahalo bo ngotsoe ho PostgreSQL ho sebelisoa molatsoana oa COPY, ebe ho hlaha ho hloleha ka tšohanyetso, 'me karolo ea data e ts'oanang e fihla hape.

Joang ho tlosa database ea clones e sa hlokahaleng?

Ha PK e se mothusi

Tsela e bonolo ka ho fetisisa ke ho thibela boemo bo joalo hore bo se ke ba etsahala pele. Ka mohlala, rola PRIMARY KEY. Empa sena ha se kamehla se ka khonehang ntle le ho eketsa bongata ba data e bolokiloeng.

Ka mohlala, haeba ho nepahala ha tsamaiso ea mohloli ho phahametse ho nepahala ha sebaka se polokelong ea boitsebiso:

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}

Na u hlokometse? The countdown sebakeng sa 00:00:02 e ngotsoe ka har'a database ka ts'ebetso ea bobeli pejana, empa e ile ea lula e sebetsa ho tloha ponong ea kopo (ka mor'a moo, litekanyetso tsa data li fapane!).

Ehlile u ka e etsa PK(metric, ts) - empa joale re tla fumana likhohlano tsa ho kenya bakeng sa data e nepahetseng.

E ka etsa PK(metric, ts, data) - empa sena se tla eketsa haholo molumo oa eona, oo re ke keng ra o sebelisa.

Ka hona, khetho e nepahetseng ka ho fetesisa ke ho etsa index ea kamehla e sa ikhethang (metric, ts) le ho sebetsana le mathata kamora 'nete haeba a hlaha.

"Ntoa ea clonic e qalile"

Ho etsahetse kotsi ea mofuta o mong, 'me joale re tlameha ho senya lirekoto tsa clone tse tsoang tafoleng.

Ha re etseng mohlala oa data ea mantlha:

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

Mona letsoho la rona le ile la thothomela ka makhetlo a mararo, Ctrl+V e ile ea khomarela, 'me joale ...

Taba ea pele, a re utloisiseng hore tafole ea rona e ka ba kholo haholo, ka hona, ka mor'a hore re fumane li-clones tsohle, ho bohlokoa hore re "nyobe monoana oa rona" ho hlakola. direkoto tse itseng ntle le ho di batlisisa hape.

'Me ho na le mokhoa o joalo - sena ho bua ka ctid, sesupo sa sebele sa rekoto e itseng.

Ke hore, pele ho tsohle, re hloka ho bokella ctid ea lirekoto molemong oa litaba tse felletseng tsa lethathamo la tafole. Khetho e bonolo ka ho fetisisa ke ho beha mola oohle mongolong:

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

Na hoa khoneha ho se lahle?Ha e le hantle, hoa khoneha maemong a mangata. Ho fihlela o qala ho sebelisa masimo a tafoleng ena mefuta e se nang mokhanni oa tekano:

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

E, hang-hang rea bona hore haeba ho na le batho ba fetang bonngoe ka har'a sehlopha, tsena kaofela ke li-clones. Ha re li tloheleng feela:

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)

Bakeng sa ba ratang ho ngola ka bokhutšoaneU ka boela ua e ngola tjena:

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

Kaha boleng ba khoele ea serialized ka boeona ha bo thahasellise ho rona, re e lahlile feela ho tsoa ho litšiea tse khutlisitsoeng tsa subquery.

Ho na le ho se hokae ho etsa - etsa hore DELETE e sebelise sete eo re e fumaneng:

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

Ha re itlhahlobe:

[sheba ho explain.tensor.ru]

E, ntho e 'ngoe le e' ngoe e nepahetse: lirekoto tsa rona tsa 3 li khethiloe bakeng sa Seq Scan feela ea tafole eohle, 'me node ea Delete e ne e sebelisetsoa ho batla lintlha. Phase e le 'ngoe e nang le Tid Scan:

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

Haeba u hlakotse lirekoto tse ngata, u seke oa lebala ho matha VACUUM ANALYZE.

Ha re hlahlobeng tafole e kholoanyane le palo e kholoanyane ea likopi:

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;

[sheba ho explain.tensor.ru]

Kahoo, mokhoa ona o sebetsa ka katleho, empa o tlameha ho sebelisoa ka hloko. Hobane bakeng sa rekoto e 'ngoe le e' ngoe e hlakotsoeng, ho na le leqephe le le leng la data le baloang ho Tid Scan, le le leng ho Delete.

Source: www.habr.com