ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK

Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK

Muaj cov xwm txheej thaum mus rau lub rooj uas tsis muaj tus yuam sij tseem ceeb los yog qee qhov kev ntsuas tshwj xeeb, vim muaj kev saib xyuas, ua tiav clones ntawm cov ntaub ntawv uas twb muaj lawm tau suav nrog.

Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK

Piv txwv li, qhov tseem ceeb ntawm ib qho kev ntsuas chronological tau sau rau hauv PostgreSQL siv lub COPY kwj, thiab tom qab ntawd muaj qhov tsis ua tiav tam sim ntawd, thiab ib feem ntawm cov ntaub ntawv zoo ib yam tuaj txog dua.

Yuav ua li cas tshem tawm cov database tsis tsim nyog clones?

Thaum PK tsis yog tus pab

Txoj kev yooj yim tshaj plaws yog los tiv thaiv qhov xwm txheej zoo li no tshwm sim thawj zaug. Piv txwv li, yob PRIMARY KEY. Tab sis qhov no tsis yog ib txwm ua tau yam tsis tau nce qhov ntim ntawm cov ntaub ntawv khaws cia.

Piv txwv li, yog tias qhov tseeb ntawm qhov system yog siab dua qhov tseeb ntawm daim teb hauv cov ntaub ntawv:

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}

Koj puas tau pom? Lub countdown es tsis txhob ntawm 00:00:02 tau kaw nyob rau hauv lub database nrog ts ib tug thib ob ua ntej, tab sis tseem siv tau heev los ntawm ib daim ntawv thov point of view (tom qab tag nrho, cov ntaub ntawv muaj nuj nqis yog txawv!).

Tau kawg koj ua tau PK (metric, ts) - tab sis tom qab ntawd peb yuav tau txais kev tsis sib haum xeeb rau cov ntaub ntawv siv tau.

Ua tau PK(metric, ts, data) - tab sis qhov no yuav ua rau nws qhov ntim ntau, uas peb yuav tsis siv.

Yog li, qhov kev xaiv raug tshaj plaws yog los ua ib qho kev ntsuas tsis tu ncua (metric, ts) thiab daws teeb meem tom qab qhov tseeb yog tias lawv tshwm sim.

"Kev tsov rog clonic tau pib"

Qee qhov xwm txheej tshwm sim, thiab tam sim no peb yuav tsum rhuav tshem cov ntaub ntawv clone los ntawm lub rooj.

Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK

Cia peb ua qauv cov ntaub ntawv qub:

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

Ntawm no peb txhais tes tshee peb zaug, Ctrl + V tau daig, thiab tam sim no ...

Ua ntej, cia peb nkag siab tias peb lub rooj tuaj yeem loj heev, yog li tom qab peb pom tag nrho cov clones, nws raug nquahu rau peb kom "poke peb tus ntiv tes" kom tshem tawm. cov ntaub ntawv tshwj xeeb yam tsis tau rov tshawb xyuas lawv.

Thiab muaj xws li ib txoj kev - qhov no hais los ntawm ctid, tus cim lub cev ntawm ib cov ntaub ntawv tshwj xeeb.

Ntawd yog, ua ntej ntawm tag nrho cov, peb yuav tsum sau cov ctid ntawm cov ntaub ntawv nyob rau hauv cov ntsiab lus ntawm tag nrho cov ntsiab lus ntawm kab lus. Qhov kev xaiv yooj yim tshaj plaws yog pov tag nrho kab rau hauv cov ntawv:

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

Puas muaj peev xwm tsis muab pov tseg?Nyob rau hauv txoj cai, nws yog ua tau nyob rau hauv feem ntau. Txog thaum koj pib siv cov teb hauv cov lus no hom tsis muaj vaj huam sib luag tus neeg teb xov tooj:

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

Yog lawm, peb pom tam sim ntawd yog tias muaj ntau tshaj ib qho kev nkag rau hauv cov array, cov no yog txhua tus clones. Cia wb cia li tso lawv tseg:

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)

Rau cov uas nyiam sau luvKoj tuaj yeem sau tau zoo li no:

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

Txij li thaum tus nqi ntawm txoj hlua serialized nws tus kheej tsis nthuav rau peb, peb tsuas yog pov nws tawm ntawm cov kab rov qab ntawm cov lus nug.

Tsuas muaj me ntsis ntxiv ua - ua DELETE siv cov txheej peb tau txais:

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

Cia peb kuaj peb tus kheej:

Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK
[saib ntawm piav qhia.tensor.ru]

Yog lawm, txhua yam yog qhov tseeb: peb 3 cov ntaub ntawv raug xaiv rau Seq Scan ntawm tag nrho cov lus, thiab rho tawm node tau siv los tshawb nrhiav cov ntaub ntawv ib zaug dhau nrog Tid Scan:

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

Yog tias koj tshem ntau cov ntaub ntawv, tsis txhob hnov ​​​​qab khiav VACUUM ANALYZE.

Cia peb kuaj xyuas lub rooj loj dua thiab nrog cov lej ntau dua:

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;

Clearing clone cov ntaub ntawv los ntawm lub rooj tsis muaj PK
[saib ntawm piav qhia.tensor.ru]

Yog li, txoj kev ua haujlwm tau zoo, tab sis nws yuav tsum tau siv nrog qee qhov ceev faj. Vim tias rau txhua cov ntaub ntawv uas tau muab tshem tawm, muaj ib nplooj ntawv cov ntaub ntawv nyeem hauv Tid Scan, thiab ib qho hauv Delete.

Tau qhov twg los: www.hab.com

Ntxiv ib saib