Kuchotsa zolemba za clone patebulo popanda PK

Pali zochitika pamene ku tebulo popanda kiyi yoyamba kapena mlozera wina wapadera, chifukwa cha kuyang'anira, zolemba zonse zomwe zilipo kale zikuphatikizidwa.

Mwachitsanzo, zikhulupiriro zamakina amalembedwa mu PostgreSQL pogwiritsa ntchito mtsinje wa COPY, ndiyeno pamakhala kulephera kwadzidzidzi, ndipo gawo limodzi lazofanana limabweranso.

Momwe mungachotsere database ya ma clones osafunikira?

Pamene PK si wothandizira

Njira yosavuta ndiyo kupewa kuti zinthu zoterezi zisachitike poyambirira. Mwachitsanzo, tsegulani PRIMARY KEY. Koma izi sizingatheke nthawi zonse popanda kuwonjezera kuchuluka kwa deta yosungidwa.

Mwachitsanzo, ngati kulondola kwa magwero ndikokwera kwambiri kuposa kulondola kwa gawo la database:

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}

Kodi munazindikira? The countdown m'malo mwa 00:00:02 idalembedwa m'dawunilodi ndi ts kachiwiri koyambirira, koma idakhalabe yovomerezeka kuchokera pamawonekedwe ogwiritsira ntchito (pambuyo pake, ma data ndi osiyana!).

Inde mukhoza kuchita PK (metric, ts) - koma ndiye tipeza mikangano yoyika pazovomerezeka.

Ndikhoza kuchita PK (metric, ts, data) - koma izi zidzakulitsa kwambiri voliyumu yake, yomwe sitidzagwiritsa ntchito.

Chifukwa chake, njira yolondola kwambiri ndikupanga index yosakhala yapadera (metric, ts) ndi kuthana ndi mavuto pambuyo pake ngati abuka.

"Nkhondo ya clonic yayamba"

Ngozi yamtundu wina inachitika, ndipo tsopano tiyenera kuwononga zolemba za clone patebulo.

Tiyeni tichite chitsanzo choyambirira:

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

Apa dzanja lathu linanjenjemera katatu, Ctrl + V idakakamira, ndipo tsopano ...

Choyamba, tiyeni timvetsetse kuti tebulo lathu likhoza kukhala lalikulu kwambiri, chifukwa chake titatha kupeza ma clones onse, ndi bwino kuti "tigwire chala chathu" kuti tichotse. zolemba zenizeni popanda kuzifufuzanso.

Ndipo pali njira yotere - iyi kuyankhula ndi ctid, chizindikiritso chenicheni cha mbiri inayake.

Ndiko kuti, choyamba, tifunika kusonkhanitsa cid of records molingana ndi zomwe zili mumzere wa tebulo. Njira yosavuta ndikuyika mzere wonse kukhala mawu:

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

Kodi sizingatheke kuponya?Mfundo n'zotheka nthawi zambiri. Mpaka mutayamba kugwiritsa ntchito minda yomwe ili patebuloli mitundu yopanda opareshoni yofanana:

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

Inde, nthawi yomweyo tikuwona kuti ngati pali zolowera zochulukirapo, zonsezi ndi ma clones. Tiyeni tingowasiya:

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)

Kwa omwe amakonda kulemba mwachiduleMukhozanso kulemba motere:

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

Popeza mtengo wa zingwe zotsatiridwa pawokha sizosangalatsa kwa ife, tidangochiponya pamizere yobwereranso ya subquery.

Kwatsala pang'ono kuchita - pangani DELETE kugwiritsa ntchito zomwe talandira:

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

Tiyeni tidziyese tokha:

[onani pa explain.tensor.ru]

Inde, zonse ndi zolondola: zolemba zathu za 3 zidasankhidwa ku Seq Scan yokha ya tebulo lonse, ndipo Delete node idagwiritsidwa ntchito kufufuza deta. pass single yokhala ndi Tid Scan:

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

Ngati mwachotsa zolemba zambiri, osayiwala kuthamanga VACUUM ANALYZE.

Tiyeni tiwone tebulo lalikulu ndi kuchuluka kwa zobwereza:

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;

[onani pa explain.tensor.ru]

Choncho, njirayi imagwira ntchito bwino, koma iyenera kugwiritsidwa ntchito mosamala. Chifukwa pa mbiri iliyonse yomwe yachotsedwa, pali tsamba limodzi la data lomwe limawerengedwa mu Tid Scan, ndi limodzi mu Chotsani.

Source: www.habr.com