PK を使用せずにテーブルからクローン レコードをクリアする

という状況があります。 主キーのないテーブルへ またはその他の一意のインデックス、見落としにより、既存のレコードの完全なクローンが含まれています。

たとえば、時系列メトリクスの値が COPY ストリームを使用して PostgreSQL に書き込まれた後、突然障害が発生し、完全に同一のデータの一部が再び到着します。

データベースから不要なクローンを削除するにはどうすればよいですか?

PKが助っ人ではない場合

最も簡単な方法は、そのような状況の発生を最初から防ぐことです。 たとえば、PRIMARY KEY をロールします。 ただし、保存されるデータの量を増やさずにこれが常に可能であるとは限りません。

たとえば、ソース システムの精度がデータベース内のフィールドの精度より高い場合は、次のようになります。

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

ここで手が XNUMX 回震え、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 Scan に対して 3 つのレコードが選択され、データの検索には削除ノードが使用されました。 Tid Scan によるシングルパス:

->  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 で読み取られるデータ ページが XNUMX つと、削除で読み取られるデータ ページが XNUMX つあるためです。

出所： habr.com