PK 없이 테이블에서 클론 레코드 지우기

다음과 같은 상황이 있습니다. 기본 키가 없는 테이블에 또는 일부 다른 고유 인덱스는 감독으로 인해 기존 레코드의 완전한 복제가 포함됩니다.

예를 들어 COPY 스트림을 사용하여 연대순 메트릭 값이 PostgreSQL에 기록된 후 갑작스러운 오류가 발생하여 완전히 동일한 데이터 중 일부가 다시 도착합니다.

불필요한 클론의 데이터베이스를 제거하는 방법은 무엇입니까?

PK가 도우미가 아닌 경우

가장 쉬운 방법은 애초에 그러한 상황이 발생하지 않도록 방지하는 것입니다. 예를 들어 기본 키를 굴립니다. 그러나 저장된 데이터의 양을 늘리지 않고는 이것이 항상 가능한 것은 아닙니다.

예를 들어 소스 시스템의 정확도가 데이터베이스 필드의 정확도보다 높은 경우:

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}

눈치채셨나요? 00:00:02 대신 카운트다운은 XNUMX초 전에 ts로 데이터베이스에 기록되었지만 애플리케이션 관점에서는 꽤 유효한 상태로 유지되었습니다(결국 데이터 값이 다릅니다!).

물론 당신은 그것을 할 수 있습니다 PK(미터법, TS) - 하지만 유효한 데이터에 대한 삽입 충돌이 발생합니다.

할 수있다 PK(미터법, TS, 데이터) -그러나 이렇게 하면 볼륨이 크게 증가하므로 사용하지 않을 것입니다.

따라서 가장 올바른 옵션은 일반 고유하지 않은 인덱스를 만드는 것입니다. (미터법, TS) 문제가 발생하면 사후에 처리합니다.

"클론 전쟁이 시작되었습니다"

어떤 사고가 발생하여 이제 테이블에서 복제 기록을 삭제해야 합니다.

원본 데이터를 모델링해 보겠습니다.

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

여기서 손이 세번 떨리고 Ctrl+V가 멈췄는데 지금은...

먼저 테이블이 매우 클 수 있으므로 모든 클론을 찾은 후에 문자 그대로 "손가락을 찔러" 삭제하는 것이 좋습니다. 특정 기록을 재검색하지 않고.

그리고 그런 방법이 있습니다 - 이것은 ctid로 주소 지정, 특정 레코드의 물리적 식별자입니다.

즉, 우선 테이블 행의 전체 내용에 대한 맥락에서 레코드의 ctid를 수집해야 합니다. 가장 간단한 옵션은 전체 줄을 텍스트로 변환하는 것입니다.

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

안 캐스팅이 가능한가요?원칙적으로 대부분의 경우 가능합니다. 이 테이블의 필드를 사용하기 시작할 때까지 항등 연산자가 없는 유형:

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

예, 배열에 항목이 두 개 이상 있으면 모두 클론이라는 것을 즉시 알 수 있습니다. 그냥 두자:

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)

짧게 쓰고 싶은 분들은다음과 같이 작성할 수도 있습니다.

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

직렬화된 문자열의 값 자체는 우리에게 흥미롭지 않기 때문에 하위 쿼리의 반환된 열에서 해당 값을 삭제했습니다.

해야 할 일이 조금 남았습니다. DELETE가 우리가 받은 세트를 사용하도록 하세요.

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

스스로 확인해 봅시다:

[explain.tensor.ru 참조]

예, 모든 것이 정확합니다. 전체 테이블의 유일한 Seq 스캔을 위해 3개의 레코드가 선택되었으며 데이터 검색에는 삭제 노드가 사용되었습니다. Tid 스캔을 사용한 단일 패스:

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

많은 기록을 지웠다면, VACUUM ANALYZE를 실행하는 것을 잊지 마십시오.

더 큰 테이블과 더 많은 중복 항목이 있는지 확인해 보겠습니다.

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;

[explain.tensor.ru 참조]

따라서 이 방법은 성공적으로 작동하지만 주의해서 사용해야 합니다. 삭제되는 모든 기록에 대해 Tid Scan에서 읽은 데이터 페이지가 하나, 삭제에서 하나의 데이터 페이지가 있기 때문입니다.

출처 : habr.com