DBA: membersihkan catatan klon dari tabel tanpa PK

Ada situasi ketika, dalam sebuah tabel tanpa kunci utama atau indeks unik lainnya, klon penuh dari catatan yang sudah ada jatuh secara tidak sengaja.



Sebagai contoh, nilai-nilai metrik kronologis ditulis ke aliran COPY PostgreSQL, dan kemudian gagal mendadak, dan bagian dari data yang sama persis muncul lagi.

Bagaimana cara membersihkan database dari klon yang tidak perlu?

Ketika PK bukan asisten

Cara termudah adalah mencegah hal ini terjadi sama sekali. Misalnya, gulung KUNCI UTAMA yang sama. Tetapi ini tidak selalu mungkin tanpa menambah jumlah data yang disimpan.

Misalnya, jika akurasi sistem asli lebih tinggi dari keakuratan bidang dalam 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} 

Pernahkah Anda memperhatikan? Hitungan bukannya 00:00:02 ditulis ke database dengan ts sedetik sebelumnya, tetapi tetap cukup valid dari sudut pandang aplikasi (setelah semua, nilai data berbeda!).

Tentu saja, Anda dapat membuat PK (metrik, ts) - tetapi kemudian kami akan mendapatkan konflik penyisipan untuk data yang valid.

Anda dapat membuat PK (metrik, ts, data) - tetapi ini akan sangat meningkatkan volumenya, yang tidak akan kami gunakan.

Oleh karena itu, opsi yang paling benar adalah membuat indeks non-unik reguler (metrik, ts) dan menangani masalah setelah fakta, jika memang muncul.

"Perang klonik telah dimulai"

Beberapa jenis kecelakaan terjadi, dan sekarang kita harus menghancurkan catatan klon dari meja.



Mari kita simulasikan sumber data:

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

Lalu tangan kami gemetar tiga kali, Ctrl + V macet, dan sekarang ...

Pertama, mari kita pahami bahwa tabel kita bisa sangat besar, oleh karena itu, setelah kita menemukan semua klon, disarankan bagi kita untuk benar-benar "menyodok" untuk menghapus catatan tertentu tanpa harus mencari lagi .

Dan ada cara seperti itu - ini ditangani oleh ctid , pengidentifikasi fisik catatan tertentu.

Artinya, pertama-tama, kita perlu mengumpulkan catatan ctid dalam konteks konten penuh dari baris tabel. Opsi termudah adalah dengan memasukkan seluruh baris ke dalam teks:

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

Ya, kami segera melihat bahwa jika ada lebih dari satu rekaman dalam array, itu semua ada klon. Biarkan hanya mereka:

 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) 

Karena nilai string serial itu sendiri tidak menarik bagi kami, kami cukup membuangnya dari kolom subquery yang dikembalikan.

Yang tersisa adalah HAPUS untuk menggunakan set yang kami terima:

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

Periksa diri Anda:


[lihat menjelaskan.tensor.ru]

Ya, itu benar: 3 catatan kami dipilih untuk hanya Pemindaian Seq seluruh tabel, dan simpul Hapus menggunakan satu pass untuk mencari data menggunakan Tid Scan :

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

Jika Anda telah menghapus banyak catatan, jangan lupa untuk mengarahkan VACUUM ANALYZE .

Mari kita periksa tabel yang lebih besar dan dengan banyak hal:

 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; 

[lihat menjelaskan.tensor.ru]

Jadi, metode ini berhasil, tetapi harus diterapkan dengan hati-hati. Karena untuk setiap catatan yang dihapus, ada satu pembacaan halaman data di Tid Scan, dan satu lagi di Delete.