Isula amarekhodi e-clone etafuleni ngaphandle kwe-PK

Kukhona izimo lapho etafuleni elingenawo ukhiye oyinhloko noma enye inkomba eyingqayizivele, ngenxa yokwengamela, amarekhodi aphelele akhona kakade afakiwe.

Isibonelo, amanani wemethrikhi yokulandelana kwezikhathi abhalwa ku-PostgreSQL kusetshenziswa ukusakaza kwe-COPY, bese kuba nokwehluleka okungazelelwe, bese ingxenye yedatha efana ngokuphelele ifika futhi.

Ungasusa kanjani i-database yama-clones angadingekile?

Lapho u-PK engeyena umsizi

Indlela elula ukuvimbela isimo esinjalo ukuthi singenzeki kwasekuqaleni. Isibonelo, gingqa PRIMARY KEY. Kodwa lokhu akwenzeki ngaso sonke isikhathi ngaphandle kokwandisa umthamo wedatha egciniwe.

Isibonelo, uma ukunemba kwesistimu yomthombo kuphakeme kunokunemba kwenkambu kusizindalwazi:

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}

Ubonile? I-Countdown esikhundleni sika-00:00:02 yarekhodwa ku-database nge-ts okwesibili ngaphambili, kodwa yahlala ivumelekile ngokubuka kwesicelo (ngemuva kwakho konke, amanani wedatha ahlukile!).

Yebo ungakwenza I-PK(metric, ts) - kodwa-ke sizothola ukungqubuzana kokufakwa kwedatha evumelekile.

Ungakwenza I-PK(imethrikhi, i-ts, idatha) - kodwa lokhu kuzokwandisa kakhulu ivolumu yayo, esingeke siyisebenzise.

Ngakho-ke, inketho elungile kakhulu ukwenza inkomba evamile engeyona eyingqayizivele (imethrikhi, ts) futhi ubhekane nezinkinga ngemuva kweqiniso uma zivela.

"Impi yama-clonic isiqalile"

Uhlobo oluthile lwengozi lwenzekile, futhi manje kufanele sicekele phansi amarekhodi e-clone etafuleni.

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

Lapha isandla sethu saqhaqhazela kathathu, u-Ctrl+V wabambeka, futhi manje...

Okokuqala, masiqonde ukuthi itafula lethu lingaba likhulu kakhulu, ngakho-ke ngemuva kokuthola wonke ama-clones, kuhle ngathi ukuthi "siphuce umunwe wethu" ngokoqobo ukuze sisuse. amarekhodi athile ngaphandle kokusesha kabusha.

Futhi kukhona indlela enjalo - lokhu ekhuluma ngo-ctid, inkomba ebonakalayo yerekhodi elithile.

Okusho ukuthi, okokuqala, sidinga ukuqoqa i-ctid yamarekhodi kumongo wokuqukethwe okuphelele komugqa wethebula. Inketho elula ukuphonsa wonke umugqa embhalweni:

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

Kungenzeka yini ukuthi ungalibali?Empeleni, kungenzeka ezimweni eziningi. Uze uqale ukusebenzisa izinkambu kuleli thebula izinhlobo 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

Yebo, sibona ngokushesha ukuthi uma kukhona okungenayo okungaphezu kokukodwa ohlwini, wonke ama-clones. Asibashiye 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)

Kulabo abathanda ukubhala kafushaneUngakwazi futhi ukuyibhala kanje:

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

Njengoba inani leyunithi yezinhlamvu ye-serialized ngokwayo lingasithakaseli, sivele siliphonse ngaphandle kwamakholomu abuyisiwe wemibuzo engaphansi.

Sekusele kancane ukwenze - yenza okuthi DELETE usebenzise isethi esiyitholile:

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

Ake sizihlole:

[buka kokuthi explain.tensor.ru]

Yebo, konke kulungile: amarekhodi ethu ama-3 akhethelwe i-Seq Scan kuphela yethebula lonke, futhi inodi ethi Susa isetshenziselwe ukucinga idatha. iphasi elilodwa nge-Tid Scan:

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

Uma usule amarekhodi amaningi, ungakhohlwa ukusebenzisa i-VACUUM ANALYZE.

Ake sihlole ithebula elikhudlwana kanye nenani elikhulu lezimpinda:

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;

[buka kokuthi explain.tensor.ru]

Ngakho, indlela isebenza ngempumelelo, kodwa kufanele isetshenziswe ngokuqapha okuthile. Ngoba kuwo wonke amarekhodi asusiwe, kunekhasi elilodwa ledatha elifundwa ku-Tid Scan, nelilodwa kokuthi Susa.

Source: www.habr.com