DBA: mengatur sinkronisasi dan impor secara kompeten
Ketika pemrosesan kompleks kumpulan data besar (berbagai proses ETL: impor, konversi dan sinkronisasi dengan sumber eksternal) seringkali ada kebutuhan untuk sementara "mengingat", dan segera memproses dengan cepat sesuatu yang banyak.
Tugas tipikal semacam ini biasanya terdengar seperti ini: "Disini akuntansi diturunkan dari klien-bank pembayaran terakhir yang diterima, Anda perlu mengunggahnya dengan cepat ke situs dan menautkannya ke akun "
Namun ketika volume "sesuatu" ini mulai diukur dalam ratusan megabyte, dan layanan harus terus bekerja dengan database dalam mode 24x7, ada banyak efek samping yang akan merusak hidup Anda.
Untuk menghadapinya di PostgreSQL (dan tidak hanya di dalamnya), Anda dapat menggunakan beberapa peluang untuk pengoptimalan yang memungkinkan Anda memproses semuanya lebih cepat dan dengan konsumsi sumber daya yang lebih sedikit.
1. Ke mana harus mengirim?
Pertama, mari kita putuskan di mana kita bisa mengisi data yang ingin kita "proses".
1.1. Tabel sementara (TABEL SEMENTARA)
Pada prinsipnya, untuk PostgreSQL, tabel sementara adalah tabel yang sama dengan tabel lainnya. Oleh karena itu, takhayul seperti "semuanya disimpan di sana hanya dalam ingatan, dan itu bisa berakhir". Tetapi ada juga beberapa perbedaan yang signifikan.
Miliki "namespace" untuk setiap koneksi ke database
Jika dua koneksi mencoba untuk terhubung pada waktu yang sama CREATE TABLE x, maka seseorang pasti akan mendapatkannya kesalahan non-keunikan objek basis data.
Tetapi jika keduanya mencoba untuk mengeksekusi CREATE TEMPORARY TABLE x, maka keduanya akan melakukannya secara normal, dan masing-masing akan menerima salinan Anda tabel. Dan tidak akan ada kesamaan di antara mereka.
"Penghancuran diri" saat terputus
Saat koneksi ditutup, semua tabel sementara dihapus secara otomatis, jadi lakukan "secara manual". DROP TABLE x tidak masuk akal kecuali...
Jika Anda bekerja melalui pgbouncer dalam mode transaksi, maka database tetap percaya bahwa koneksi ini masih aktif, dan tabel sementara ini masih ada di dalamnya.
Oleh karena itu, upaya untuk membuatnya lagi, dari koneksi lain ke pgbouncer, akan menghasilkan kesalahan. Tapi ini bisa dilewati dengan menggunakan CREATE TEMPORARY TABLE IF NOT EXISTS x.
Benar, lebih baik tidak melakukan ini sama sekali, karena Anda dapat "tiba-tiba" menemukan data yang tersisa dari "pemilik sebelumnya" di sana. Sebaliknya, jauh lebih baik membaca manual, dan melihat bahwa saat membuat tabel, dimungkinkan untuk menambahkan ON COMMIT DROP - yaitu ketika transaksi selesai, tabel akan terhapus secara otomatis.
Non-replikasi
Berdasarkan milik hanya untuk koneksi tertentu, tabel sementara tidak direplikasi. Tetapi ini menghilangkan kebutuhan untuk menulis data dua kali ke heap + WAL, jadi INSERT/UPDATE/DELETE ke dalamnya secara signifikan lebih cepat.
Tetapi karena tabel sementara masih merupakan tabel "hampir biasa", tabel itu juga tidak dapat dibuat di replika. Setidaknya untuk saat ini, meskipun tambalan yang sesuai sudah ada sejak lama.
1.2. Tabel yang tidak dicatat (UNLOGGED TABLE)
Tetapi apa yang harus dilakukan, misalnya, jika Anda memiliki semacam proses ETL yang rumit yang tidak dapat diterapkan dalam satu transaksi, tetapi Anda tetap pgbouncer dalam mode transaksi? ..
Atau apakah aliran data begitu besar sehingga tidak cukup bandwidth per koneksi dari database (baca, satu proses per CPU)?..
Atau beberapa operasi sedang berlangsung secara asinkron dalam hubungan yang berbeda?..
Hanya ada satu opsi di sini - buat sementara tabel non-temp. Pun, ya. Itu adalah:
membuat tabel "nya" dengan nama acak maksimal agar tidak berpapasan dengan siapa pun
Ekstrak: mengisinya dengan data dari sumber eksternal
Mengubah: dikonversi, mengisi bidang penjilidan kunci
Beban: menuangkan data siap pakai ke tabel target
menghapus tabel "milik sendiri".
Dan sekarang - lalat di salep. Nyatanya, semua tulisan di PostgreSQL terjadi dua kali - pertama di WAL, lalu sudah ada di badan tabel/indeks. Semua ini dilakukan untuk mendukung ACID dan memperbaiki visibilitas data antara COMMIT'cewek dan ROLLBACK'membutuhkan transaksi.
Tapi kita tidak membutuhkan itu! Kami memiliki seluruh proses benar-benar berhasil atau tidak. Tidak masalah berapa banyak transaksi perantara yang akan terjadi - kami tidak tertarik untuk "melanjutkan proses dari tengah", terutama jika tidak jelas di mana itu.
Dengan petunjuk ini, tabel dibuat sebagai non-log. Data yang ditulis ke tabel non-log tidak melalui log tulis-depan (lihat Bab 29), menyebabkan tabel tersebut bekerja lebih cepat dari biasanya. Namun, mereka tidak tahan kesalahan; pada server crash atau shutdown, tabel unloged otomatis terpotong. Selain itu, isi tabel unloged tidak direplikasi ke server budak. Indeks apa pun yang dibuat pada tabel yang tidak dicatat secara otomatis menjadi tidak dicatat.
Singkatnya, akan jauh lebih cepat, tetapi jika server database "jatuh" - itu akan menjadi tidak menyenangkan. Tetapi seberapa sering hal ini terjadi, dan apakah proses ETL Anda dapat menyempurnakan "dari tengah" ini dengan benar setelah "kebangkitan" database?..
Jika tidak, dan kasus di atas mirip dengan kasus Anda, gunakan UNLOGGEDtapi tidak pernah jangan aktifkan atribut ini di tabel nyata, yang datanya sangat Anda sayangi.
1.3. ON COMMIT { HAPUS BARIS | MENJATUHKAN}
Konstruksi ini memungkinkan Anda menyetel perilaku otomatis saat transaksi selesai saat membuat tabel.
Tentang ON COMMIT DROP Saya sudah menulis di atas, itu menghasilkan DROP TABLE, tetapi dengan ON COMMIT DELETE ROWS situasinya lebih menarik - ini dia yang dihasilkan TRUNCATE TABLE.
Karena seluruh infrastruktur untuk menyimpan deskripsi meta dari tabel sementara persis sama dengan tabel biasa, maka pembuatan dan penghapusan tabel sementara yang konstan menyebabkan "pembengkakan" yang kuat pada tabel sistem pg_class, pg_attribute, pg_attrdef, pg_depend,β¦
Sekarang bayangkan Anda memiliki pekerja dengan koneksi langsung ke database, yang membuka transaksi baru setiap detik, membuat, mengisi, memproses, dan menghapus tabel sementara ... Sampah di tabel sistem akan menumpuk secara berlebihan, dan ini tambahan rem untuk setiap operasi.
Secara umum, tidak perlu! Jauh lebih efisien dalam hal ini. CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS keluarkan dari siklus transaksi - kemudian pada awal setiap transaksi baru, tabel sudah akan ada (kami menyimpan panggilan CREATE), tetapi akan kosong, terimakasih untuk TRUNCATE (kami juga menyimpan panggilannya) di akhir transaksi sebelumnya.
1.4. SEPERTIβ¦ TERMASUKβ¦
Saya sebutkan di awal bahwa salah satu kasus penggunaan umum untuk tabel sementara adalah semua jenis impor - dan pengembang dengan lelah menyalin-tempel daftar bidang dari tabel target ke dalam deklarasi ...
Tapi kemalasan adalah mesin kemajuan! Itu sebabnya membuat spreadsheet baru bisa jauh lebih mudah:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);
Karena Anda kemudian dapat menghasilkan banyak data ke dalam tabel ini, pencarian melaluinya tidak akan pernah cepat. Tapi ada solusi tradisional untuk ini - indeks! Dan ya tabel sementara juga dapat memiliki indeks.
Karena seringkali indeks yang diinginkan sama dengan indeks tabel target, Anda cukup menulis LIKE target_table INCLUDING INDEXES.
Jika Anda membutuhkan lebih banyak DEFAULT-nilai (misalnya, untuk mengisi nilai kunci utama), Anda dapat menggunakan LIKE target_table INCLUDING DEFAULTS. Atau hanya - LIKE target_table INCLUDING ALL β akan menyalin default, indeks, batasan,β¦
Tapi di sini Anda sudah perlu memahami bahwa jika Anda membuatnya impor-tabel segera dengan indeks, maka data akan diisi lebih lamadaripada jika Anda mengisi semuanya terlebih dahulu, dan baru kemudian menggulung indeks - lihat sebagai contoh caranya hal_dump.
Sederhananya - gunakan COPY-stream bukan "paket" INSERT, percepatan berkali-kali. Anda bahkan dapat langsung dari file yang dibuat sebelumnya.
3. Bagaimana prosesnya?
Jadi, katakanlah pengantar kita terlihat seperti ini:
Anda memiliki tabel dengan data klien yang disimpan di database Anda 1 juta catatan
setiap hari klien mengirimi Anda yang baru lengkapi "gambar"
Anda tahu dari pengalaman bahwa dari waktu ke waktu tidak lebih dari 10K catatan berubah
Contoh klasik dari situasi seperti itu adalah dasar KLADR - alamatnya banyak, tapi di setiap unggahan mingguan sangat sedikit perubahan (ganti nama pemukiman, penggabungan jalan, rumah baru) bahkan dalam skala nasional.
3.1. Algoritma Sinkronisasi Penuh
Untuk kesederhanaan, katakanlah Anda bahkan tidak perlu menyusun ulang data - cukup bawa tabel ke bentuk yang diinginkan, yaitu:
hapus semua itu tidak ada lagi
upgrade segala sesuatu yang sudah ada dan perlu diperbarui
masukkan semua yang belum terjadi
Mengapa perlu melakukan operasi dalam urutan ini? Karena begitulah ukuran tabel akan bertambah minimal (ingat MVCC!).
HAPUS DARI dst
Tidak, tentu saja Anda dapat melakukannya hanya dengan dua operasi:
hapus (DELETE) secara umum semuanya
masukkan semua dari gambar baru
Namun pada saat yang sama, berkat MVCC, ukuran meja akan bertambah tepat dua kali lipat! Dapatkan +1 juta gambar rekaman tabel karena pembaruan 10K - redundansi biasa-biasa saja...
POTONG dst
Pengembang yang lebih berpengalaman tahu bahwa seluruh tabel dapat dibersihkan dengan cukup murah:
jelas (TRUNCATE) seluruh meja
masukkan semua dari gambar baru
Metode ini efektif terkadang dapat diterapkan, tetapi ada nasib buruk ... Kami akan menuangkan 1 juta catatan untuk waktu yang lama, jadi kami tidak dapat membiarkan tabel kosong selama ini (karena itu akan terjadi tanpa membungkusnya menjadi satu transaksi).
Yang berarti:
kita mulai transaksi berjalan lama
TRUNCATE memaksakan Akses Eksklusif- memblokir
kami membuat sisipan untuk waktu yang lama, dan semua orang saat ini bahkan tidak bisa SELECT
Ada yang salah...
ALTER TABLEβ¦ RENAMEβ¦ / DROP TABLEβ¦
Sebagai opsi, isi semuanya ke dalam tabel baru yang terpisah, lalu cukup ganti namanya ke tempat yang lama. Beberapa hal kecil yang mengganggu:
masih juga Akses Eksklusif, meskipun waktu jauh lebih sedikit
Ada tambalan WIP dari Simon Riggs yang disarankan untuk dilakukan ALTER- operasi untuk mengubah isi tabel pada tingkat file, tanpa menyentuh statistik dan FK, tetapi tidak mengumpulkan kuorum.
HAPUS, PERBARUI, MASUKKAN
Jadi, kami berhenti pada varian non-pemblokiran dari tiga operasi. Hampir tiga... Bagaimana melakukannya dengan paling efektif?
-- Π²ΡΠ΅ Π΄Π΅Π»Π°Π΅ΠΌ Π² ΡΠ°ΠΌΠΊΠ°Ρ ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΈ, ΡΡΠΎΠ±Ρ Π½ΠΈΠΊΡΠΎ Π½Π΅ Π²ΠΈΠ΄Π΅Π» "ΠΏΡΠΎΠΌΠ΅ΠΆΡΡΠΎΡΠ½ΡΡ " ΡΠΎΡΡΠΎΡΠ½ΠΈΠΉ
BEGIN;
-- ΡΠΎΠ·Π΄Π°Π΅ΠΌ Π²ΡΠ΅ΠΌΠ΅Π½Π½ΡΡ ΡΠ°Π±Π»ΠΈΡΡ Ρ ΠΈΠΌΠΏΠΎΡΡΠΈΡΡΠ΅ΠΌΡΠΌΠΈ Π΄Π°Π½Π½ΡΠΌΠΈ
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- ΠΏΠΎ ΠΎΠ±ΡΠ°Π·Ρ ΠΈ ΠΏΠΎΠ΄ΠΎΠ±ΠΈΡ, Π²ΠΌΠ΅ΡΡΠ΅ Ρ ΠΈΠ½Π΄Π΅ΠΊΡΠ°ΠΌΠΈ
) ON COMMIT DROP; -- Π·Π° ΡΠ°ΠΌΠΊΠ°ΠΌΠΈ ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΈ ΠΎΠ½Π° Π½Π°ΠΌ Π½Π΅ Π½ΡΠΆΠ½Π°
-- Π±ΡΡΡΡΠΎ-Π±ΡΡΡΡΠΎ Π²Π»ΠΈΠ²Π°Π΅ΠΌ Π½ΠΎΠ²ΡΠΉ ΠΎΠ±ΡΠ°Π· ΡΠ΅ΡΠ΅Π· COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- ΡΠ΄Π°Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΡΡΡΠΈΠ΅
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- ΠΏΠΎΠ»Ρ ΠΏΠ΅ΡΠ²ΠΈΡΠ½ΠΎΠ³ΠΎ ΠΊΠ»ΡΡΠ°
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "Π°Π½ΡΠΈΠ΄ΠΆΠΎΠΉΠ½"
-- ΠΎΠ±Π½ΠΎΠ²Π»ΡΠ΅ΠΌ ΠΎΡΡΠ°Π²ΡΠΈΠ΅ΡΡ
UPDATE
dst D
SET
(f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
tmp T
WHERE
(D.pk1, D.pk2) = (T.pk1, T.pk2) AND
(D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- Π½Π΅Π·Π°ΡΠ΅ΠΌ ΠΎΠ±Π½ΠΎΠ²Π»ΡΡΡ ΡΠΎΠ²ΠΏΠ°Π΄Π°ΡΡΠΈΠ΅
-- Π²ΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΡΡΡΠΈΠ΅
INSERT INTO
dst
SELECT
T.*
FROM
tmp T
LEFT JOIN
dst D
USING(pk1, pk2)
WHERE
D IS NOT DISTINCT FROM NULL;
COMMIT;
3.2. Impor pasca-pemrosesan
Dalam KLADR yang sama, semua catatan yang diubah harus dijalankan tambahan melalui pasca-pemrosesan - kata kunci yang dinormalisasi dan disorot, dibawa ke struktur yang diinginkan. Tapi bagaimana cara mengetahuinya apa sebenarnya yang berubahtanpa mempersulit kode sinkronisasi, idealnya tanpa menyentuhnya sama sekali?
Jika hanya proses Anda yang memiliki akses tulis pada saat sinkronisasi, Anda dapat menggunakan pemicu yang akan mengumpulkan semua perubahan untuk kami:
-- ΡΠ΅Π»Π΅Π²ΡΠ΅ ΡΠ°Π±Π»ΠΈΡΡ
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- ΡΠ°Π±Π»ΠΈΡΡ Ρ ΠΈΡΡΠΎΡΠΈΠ΅ΠΉ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ
CREATE TABLE kladr$log(
ro kladr, -- ΡΡΡ Π»Π΅ΠΆΠ°Ρ ΡΠ΅Π»ΡΠ΅ ΠΎΠ±ΡΠ°Π·Ρ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΡΡΠ°ΡΠΎΠΉ/Π½ΠΎΠ²ΠΎΠΉ
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- ΠΎΠ±ΡΠ°Ρ ΡΡΠ½ΠΊΡΠΈΡ Π»ΠΎΠ³ΠΈΡΠΎΠ²Π°Π½ΠΈΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- ΠΏΡΠΎΠ²Π΅ΡΡΠ΅ΠΌ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎΡΡΡ Π»ΠΎΠ³Π³ΠΈΡΠΎΠ²Π°Π½ΠΈΡ ΠΏΡΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠΈ Π·Π°ΠΏΠΈΡΠΈ
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- ΡΠΎΠ·Π΄Π°Π΅ΠΌ Π·Π°ΠΏΠΈΡΡ Π»ΠΎΠ³Π°
stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
CASE TG_OP
WHEN 'INSERT' THEN
EXECUTE stmt || 'NULL,$1)' USING NEW;
WHEN 'UPDATE' THEN
EXECUTE stmt || '$1,$2)' USING OLD, NEW;
WHEN 'DELETE' THEN
EXECUTE stmt || '$1,NULL)' USING OLD;
END CASE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Sekarang kita dapat menerapkan pemicu sebelum memulai sinkronisasi (atau mengaktifkannya melalui ALTER TABLE ... ENABLE TRIGGER ...):
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr_house
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
Dan kemudian kami dengan tenang mengekstrak semua perubahan yang kami butuhkan dari tabel log dan menjalankannya melalui prosesor tambahan.
3.3. Mengimpor Set Terkait
Di atas, kami mempertimbangkan kasus di mana struktur data sumber dan tujuan sama. Namun bagaimana jika upload dari sistem eksternal memiliki format yang berbeda dengan struktur penyimpanan di database kita?
Mari kita ambil contoh penyimpanan pelanggan dan faktur untuk mereka, opsi klasik many-to-one:
CREATE TABLE client(
client_id
serial
PRIMARY KEY
, inn
varchar
UNIQUE
, name
varchar
);
CREATE TABLE invoice(
invoice_id
serial
PRIMARY KEY
, client_id
integer
REFERENCES client(client_id)
, number
varchar
, dt
date
, sum
numeric(32,2)
);
Tetapi pembongkaran dari sumber eksternal datang kepada kita dalam bentuk "semua dalam satu":
Pertama-tama mari kita pilih "bagian" yang dirujuk oleh "fakta" kita. Dalam kasus kami, faktur mengacu pada pelanggan:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- ΠΌΠΎΠΆΠ½ΠΎ ΠΏΡΠΎΡΡΠΎ SELECT DISTINCT, Π΅ΡΠ»ΠΈ Π΄Π°Π½Π½ΡΠ΅ Π·Π°Π²Π΅Π΄ΠΎΠΌΠΎ Π½Π΅ΠΏΡΠΎΡΠΈΠ²ΠΎΡΠ΅ΡΠΈΠ²Ρ
client_inn inn
, client_name "name"
FROM
invoice_import;
Agar akun dapat dikaitkan dengan benar dengan ID pelanggan, pertama-tama kami perlu mengetahui atau membuat pengenal ini. Mari tambahkan bidang di bawahnya:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;
Mari gunakan metode sinkronisasi tabel yang dijelaskan di atas dengan sedikit koreksi - kami tidak akan memperbarui atau menghapus apa pun di tabel target, karena kami memiliki impor klien "tambahan saja":
-- ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ ΠΈΠΌΠΏΠΎΡΡΠ° ID ΡΠΆΠ΅ ΡΡΡΠ΅ΡΡΠ²ΡΡΡΠΈΡ Π·Π°ΠΏΠΈΡΠ΅ΠΉ
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- unique key
-- Π²ΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΠΎΠ²Π°Π²ΡΠΈΠ΅ Π·Π°ΠΏΠΈΡΠΈ ΠΈ ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΈΡ ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELECT
inn
, name
FROM
client_import
WHERE
client_id IS NULL -- Π΅ΡΠ»ΠΈ ID Π½Π΅ ΠΏΡΠΎΡΡΠ°Π²ΠΈΠ»ΡΡ
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- unique key
-- ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ ID ΠΊΠ»ΠΈΠ΅Π½ΡΠΎΠ² Ρ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΡΡΠ΅ΡΠΎΠ²
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- ΠΏΡΠΈΠΊΠ»Π°Π΄Π½ΠΎΠΉ ΠΊΠ»ΡΡ
Sebenarnya, semuanya invoice_import sekarang kita memiliki bidang yang terisi client_id, yang dengannya kami akan memasukkan akun.