Kubvisa marekodhi eclone kubva patafura isina PK

Pane mamiriro ezvinhu apo kutafura isina kiyi yekutanga kana imwe indekisi yakasarudzika, nekuda kwetarisiro, ma clones akazara emarekodhi agara aripo anosanganisirwa.

Semuenzaniso, kukosha kweiyo metric yenguva inonyorwa muPostgreSQL uchishandisa COPY rukova, uye ipapo pane kutadza kamwe kamwe, uye chikamu che data rakafanana rinosvika zvakare.

Nzira yekubvisa sei database yezvisingakoshi clones?

Apo PK isiri mubatsiri

Nzira iri nyore ndeyekudzivirira mamiriro ezvinhu akadaro kubva pakutanga. Semuenzaniso, roll PRIMARY KEY. Asi izvi hazvigoneke nguva dzose pasina kuwedzera huwandu hwe data rakachengetwa.

Semuenzaniso, kana iko kurongeka kweiyo sosi system kwakakwira kupfuura kurongeka kwemunda mune dhatabhesi:

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}

Wazviona here? The countdown pachinzvimbo che 00:00:02 yakanyorwa mudhatabhesi ne ts sekondi yapfuura, asi yakaramba yakanyatsoshanda kubva pakuona kwekushandisa (mushure mezvose, data data yakasiyana!).

Chokwadi unogona kuzviita PK(metric, ts) - asi ipapo isu tinozowana yekuisa kupokana kune inoshanda data.

Anogona kuita PK (metric, ts, data) - asi izvi zvichawedzera zvakanyanya kuwanda kwayo, iyo yatisingazoshandisi.

Naizvozvo, iyo yakanyanya kunaka sarudzo ndeye kuita yenguva dzose isiri-yakasarudzika index (metric, ts) uye kugadzirisa matambudziko mushure mechokwadi kana amuka.

"Hondo yeclonic yakatanga"

Imwe mhando yetsaona yakaitika, uye zvino tinofanira kuparadza zvinyorwa zveclone kubva patafura.

Ngatienzanisirei data rekutanga:

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

Pano ruoko rwedu rwakadedera katatu, Ctrl + V yakanamatira, uye zvino ...

Kutanga, ngatinzwisisei kuti tafura yedu inogona kunge yakakura kwazvo, saka mushure mekunge tawana ma clones, zvinokurudzirwa kuti isu tinyatso "kukwenya munwe wedu" kudzima. marekodhi chaiwo pasina kuatsvaga zvakare.

Uye kune nzira yakadaro - iyi kutaura nectid, chiziviso chemuviri cherekodhi chaiyo.

Ndiko kuti, chekutanga pane zvese, isu tinofanirwa kuunganidza iyo cid yemarekodhi mumamiriro ezvakakwana zvemukati wetafura mutsara. Iri nyore sarudzo ndeyekukanda mutsara wese muzvinyorwa:

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

Zvinoita here kusakanda?Muchidimbu, zvinogoneka muzviitiko zvakawanda. Kusvikira watanga kushandisa minda iri patafura iyi mhando dzisina kuenzana opareta:

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

Ehe, isu tinobva tangoona kuti kana paine zvinopfuura imwe yekupinda muhurongwa, aya ese ma clones. Ngativasiyei.

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)

Kune vanoda kunyora zvipfupiIwe unogona zvakare kuinyora seizvi:

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

Sezvo kukosha kweiyo serialized tambo pachayo kusingafadzi kwatiri, isu takangoikanda kunze kwemakoramu akadzoserwa eiyo subquery.

Kwangosara zvishoma kuita - ita DELETE kushandisa seti yatakagamuchira:

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

Ngatizviongororei:

[tarisa ku explain.tensor.ru]

Hongu, zvese ndezvechokwadi: edu 3 marekodhi akasarudzwa kune chete Seq Scan yetafura yese, uye Delete node yakashandiswa kutsvaga data. pass imwe chete neTid Scan:

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

Kana iwe wakadzima zvinyorwa zvakawanda, usakanganwe kumhanya VACUUM ANALYZE.

Ngatitarisei tafura hombe uye ine nhamba huru yezvakapetwa:

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;

[tarisa ku explain.tensor.ru]

Saka, nzira inoshanda zvinobudirira, asi inofanira kushandiswa nekuchenjerera. Nekuti kune rekodhi rega rega rinodzimwa, pane peji re data rakaverengwa muTid Scan, uye imwe muDelete.

Source: www.habr.com