🥇DBA: kanthi cekap ngatur sinkronisasi lan impor

Kanggo pangolahan kompleks set data gedhe (beda pangolahan ETL: impor, konversi lan sinkronisasi karo sumber external) asring ana perlu sementara "eling" lan langsung cepet proses soko voluminous.

Tugas khas kaya iki biasane kaya mangkene: "Neng kene departemen accounting unloaded saka bank klien pembayaran pungkasan sing ditampa, sampeyan kudu ngunggah kanthi cepet menyang situs web lan nyambung menyang akun sampeyan"

Nanging nalika volume "soko" iki wiwit ngukur ing atusan megabyte, lan layanan kudu terus bisa karo database 24x7, akeh efek sisih njedhul sing bakal ngrusak urip.

Kanggo menehi hasil karo wong-wong mau ing PostgreSQL (lan ora mung ing), sampeyan bisa nggunakake sawetara optimizations sing bakal ngijini sampeyan kanggo proses kabeh luwih cepet lan karo konsumsi sumber kurang.

1. Ngendi ngirim?

Pisanan, ayo mutusake ing ngendi kita bisa ngunggah data sing pengin "diproses."

1.1. Tabel sementara (TEMPORARY TABLE)

Ing asas, kanggo PostgreSQL tabel sauntara padha karo liyane. Mulane, superstitions kaya "Kabeh sing ana mung disimpen ing memori, lan bisa rampung". Nanging ana uga sawetara beda sing signifikan.

"Ruang jeneng" dhewe kanggo saben sambungan menyang database

Yen loro sambungan nyoba kanggo nyambung bebarengan CREATE TABLE x, banjur wong mesthi bakal entuk kesalahan non-uniqueness obyek database.

Nanging yen loro nyoba kanggo nglakokaké CREATE TEMPORARY TABLE x, banjur loro bakal nindakake iku biasane, lan saben wong bakal njaluk salinan sampeyan tabel. Lan bakal ana apa-apa ing umum antarane wong-wong mau.

"Ngrusak dhewe" nalika medhot

Nalika sambungan ditutup, kabeh tabel sauntara otomatis dibusak, supaya manual DROP TABLE x ora ana gunane kajaba ...

Yen sampeyan nggarap pgbouncer ing mode transaksi, banjur database terus pracaya sing sambungan iki isih aktif, lan ing tabel sauntara iki isih ana.

Mulane, nyoba kanggo nggawe maneh, saka sambungan beda kanggo pgbouncer, bakal nyebabake kesalahan. Nanging iki bisa circumvented dening nggunakake CREATE TEMPORARY TABLE IF NOT EXISTS x.

Bener, luwih becik ora nindakake iki, amarga sampeyan bisa "tiba-tiba" nemokake data sing isih ana saka "pemilik sadurunge". Nanging, luwih apik kanggo maca manual lan ndeleng manawa nggawe tabel bisa ditambahake ON COMMIT DROP - yaiku, nalika transaksi rampung, tabel bakal dibusak kanthi otomatis.

Non-replikasi

Amarga padha mung kanggo sambungan tartamtu, tabel sauntara ora replicated. Nanging iki ngilangake perlu kanggo ngrekam pindho data ing numpuk + WAL, supaya INSERT / UPDATE / DELETE menyang iku Ngartekno luwih cepet.

Nanging wiwit tabel sak wentoro isih "meh biasa" Tabel, iku ora bisa digawe ing tiron uga. Paling ora kanggo saiki, sanajan tembelan sing cocog wis suwe saya nyebar.

1.2. TABEL UNLOGGED

Nanging apa sing kudu sampeyan lakoni, umpamane, yen sampeyan duwe sawetara proses ETL sing rumit sing ora bisa ditindakake sajrone siji transaksi, nanging sampeyan isih duwe pgbouncer ing mode transaksi? ..

Utawa aliran data dadi gedhe sing Ora ana bandwidth sing cukup ing siji sambungan saka database (maca, siji proses saben CPU)?..

Utawa sawetara operasi sing arep ing asynchronously ing sambungan sing beda? ..

Mung ana siji pilihan ing kene - sementara nggawe tabel non-temporer. Pun, yeah. Iku:

nggawe tabel "dhewe" kanthi jeneng acak kanthi maksimal supaya ora intersect karo sapa wae
extract: diisi karo data saka sumber eksternal
Ngowahi: diowahi, kapenuhan kolom ngubungake tombol
load: diwutahake data siap menyang tabel target
mbusak tabel "ku".

Lan saiki - fly ing ointment. Nyatane, kabeh nulis ing PostgreSQL kelakon kaping pindho - pisanan ing WAL, banjur menyang tabel / badan indeks. Kabeh iki rampung kanggo ndhukung ACID lan visibilitas data sing bener antarane COMMIT'nut lan ROLLBACKtransaksi null.

Nanging kita ora butuh iki! Kita duwe kabeh proses Salah siji iku rampung sukses utawa ora.. Ora ketompo carane akeh transaksi penengah bakal ana - kita ora kasengsem ing "nerusake proses saka tengah," utamané yen ora cetha ngendi iku.

Kanggo nindakake iki, pangembang PostgreSQL, bali ing versi 9.1, ngenalaken bab kayata tabel UNLOGGED:

Kanthi indikasi iki, tabel digawe minangka ora dicathet. Data sing ditulis ing tabel sing ora dicathet ora mlebu log nulis ing ngarep (pirsani Bab 29), nyebabake tabel kasebut dadi bisa luwih cepet saka biasanipun. Nanging, dheweke ora kebal marang kegagalan; ing cilik saka Gagal server utawa mati darurat, tabel unloggged otomatis dipotong. Kajaba iku, isi tabel sing ora dicathet ora ditiru kanggo server budak. Sembarang indeks sing digawe ing tabel sing ora mlebu log kanthi otomatis dadi ora mlebu log.

Ing cendhak bakal luwih cepet, nanging yen server database "tiba", bakal ora nyenengake. Nanging sepira kerepe kedadeyan iki, lan apa proses ETL sampeyan ngerti carane mbenerake iki kanthi bener "saka tengah" sawise "revitalizing" database?..

Yen ora, lan kasus ing ndhuwur padha karo sampeyan, gunakake UNLOGGEDnanging ora tau ora ngaktifake atribut iki ing tabel nyata, data saka kang dear kanggo sampeyan.

1.3. ON COMMIT { Mbusak baris | DROP}

Konstruksi iki ngidini sampeyan nemtokake prilaku otomatis nalika transaksi rampung nalika nggawe tabel.

ing ON COMMIT DROP Aku wis wrote ndhuwur, iku njedulake DROP TABLE, nanging karo ON COMMIT DELETE ROWS kahanan luwih menarik - iku kui kene TRUNCATE TABLE.

Amarga kabeh prasarana kanggo nyimpen meta-deskripsi saka tabel sauntara persis padha karo tabel biasa, banjur Nggawe lan mbusak tabel sauntara terus-terusan nyebabake "pembengkakan" tabel sistem sing abot pg_class, pg_attribute, pg_attrdef, pg_depend,…

Saiki mbayangno yen sampeyan duwe pekerja ing sambungan langsung menyang database, sing mbukak transaksi anyar saben detik, nggawe, ngisi, ngolah lan mbusak tabel sementara ... Bakal ana keluwihan sampah sing dikumpulake ing tabel sistem, lan iki bakal nimbulaké rem ekstra kanggo saben operasi.

Umumé, aja nindakake iki! Ing kasus iki, iku luwih efektif CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS njupuk metu saka siklus transaksi - banjur ing awal saben transaksi anyar tabel wis bakal ana (simpen telpon CREATE), nanging bakal kosong, matur nuwun kanggo TRUNCATE (kita uga nyimpen telpon) nalika ngrampungake transaksi sadurunge.

1.4. SEPERTI...kalebu...

Aku kasebut ing wiwitan yen salah sawijining kasus panggunaan khas kanggo tabel sementara yaiku macem-macem jinis impor - lan pangembang kesel nyalin-tempel dhaptar kolom tabel target menyang deklarasi sementara ...

Nanging kesed minangka mesin kemajuan! Mulane nggawe tabel anyar "adhedhasar sampel" iku bisa dadi luwih prasaja:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Amarga sampeyan bisa ngasilake akeh data menyang tabel iki, nggoleki ora bakal cepet. Nanging ana solusi tradisional kanggo iki - indeks! Lan, ya, Tabel sauntara uga bisa duwe indeks.

Wiwit, asring, indeks sing dibutuhake pas karo indeks tabel target, sampeyan mung bisa nulis LIKE target_table INCLUDING INDEXES.

Yen sampeyan uga perlu DEFAULT-nilai (contone, kanggo ngisi nilai kunci utama), sampeyan bisa nggunakake LIKE target_table INCLUDING DEFAULTS. Utawa mung - LIKE target_table INCLUDING ALL - salinan standar, indeks, kendala,...

Nanging ing kene sampeyan kudu ngerti yen sampeyan nggawe ngimpor tabel langsung karo indeks, banjur data bakal njupuk maneh kanggo mbukaktinimbang yen sampeyan ngisi kabeh, banjur gulung indeks - deleng carane nindakake iki minangka conto pg_dump.

Umumé RTFM!

2. Kepiye carane nulis?

Ayo kula ngomong - gunakake COPY-alur tinimbang "paket" INSERT, percepatan ing kaping. Sampeyan bisa malah langsung saka file sing wis digawe.

3. Carane ngolah?

Dadi, ayo intro kita katon kaya iki:

sampeyan duwe tabel karo data klien sing disimpen ing database 1M cathetan
saben dina klien ngirim sing anyar lengkap "gambar"
saka pengalaman sampeyan ngerti sing saka wektu kanggo wektu ora luwih saka 10K cathetan diganti

Conto klasik saka kahanan kasebut yaiku pangkalan KLADR - ana akeh alamat ing total, nanging ing saben upload saben minggu ana sawetara banget owah-owahan (jeneng saka pamukiman, nggabungke dalan, katon saka omah anyar) malah ing skala nasional.

3.1. Algoritma sinkronisasi lengkap

Kanggo kesederhanaan, ayo ngomong yen sampeyan ora perlu nyusun ulang data - mung nggawa tabel menyang wangun sing dikarepake, yaiku:

mbusak kabeh sing wis ora ana maneh
nganyari kabeh sing wis ana lan kudu dianyari
masang kabeh sing durung kelakon

Yagene operasi kudu ditindakake kanthi urutan iki? Amarga iki carane ukuran meja bakal tuwuh minimal (inget MVCC!).

Mbusak saka dst

Ora, mesthine sampeyan bisa entuk kanthi mung rong operasi:

mbusak (DELETE) kabeh umume
masang kabeh saka gambar anyar

Nanging ing wektu sing padha, thanks kanggo MVCC, Ukuran meja bakal nambah persis kaping pindho! Entuk +1M gambar rekaman ing tabel amarga nganyari 10K pancen redundansi banget...

TRUNCATE dst

Pangembang sing luwih berpengalaman ngerti manawa kabeh tablet bisa diresiki kanthi murah:

cetha (TRUNCATE) meja kabeh
masang kabeh saka gambar anyar

Cara kasebut efektif, kadhangkala cukup ditrapake, nanging ana masalah ... Kita bakal nambah cathetan 1M kanggo dangu, supaya kita ora bisa saged ninggalake meja kosong kanggo kabeh wektu iki (minangka bakal kelakon tanpa mbungkus ing siji transaksi).

Kang tegese:

kita miwiti transaksi long-run
TRUNCATE nemtokke Akses Eksklusif- pamblokiran
kita nindakake selipan kanggo dangu, lan wong liya ing wektu iki ora bisa malah SELECT

Ana sing ora apik...

ALTER TABLE… GANTI NAMA… / DROP TABLE…

Alternatif kanggo ngisi kabeh menyang tabel anyar sing kapisah, banjur ganti jeneng ing papan sing lawas. Sawetara perkara cilik sing ora becik:

isih uga Akses Eksklusif, sanajan wektu sing luwih sithik
kabeh rencana/statistik pitakon kanggo tabel iki direset, kudu mbukak ANALYZE
kabeh kunci manca rusak (FK) menyang meja

Ana tembelan WIP saka Simon Riggs sing disaranake nggawe ALTER-operasi kanggo ngganti awak Tabel ing tingkat file, tanpa ndemek statistik lan FK, nanging ora ngumpulake quorum.

Mbusak, UPDATE, INSERT

Dadi, kita milih opsi non-blocking saka telung operasi. Meh telung ... Carane nindakake iki 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. Ngimpor post-processing

Ing KLADR sing padha, kabeh rekaman sing diganti kudu ditambahake liwat proses pasca - dinormalisasi, kata kunci disorot, lan dikurangi dadi struktur sing dibutuhake. Nanging kepiye sampeyan ngerti - apa persis digantitanpa complicating kode sinkronisasi, saenipun tanpa ndemek ing kabeh?

Yen mung proses sampeyan duwe akses nulis nalika sinkronisasi, sampeyan bisa nggunakake pemicu sing bakal ngumpulake kabeh owah-owahan kanggo kita:

-- целевые таблицы
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;

Saiki kita bisa ngetrapake pemicu sadurunge miwiti sinkronisasi (utawa ngaktifake liwat 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();

Banjur kita kanthi tenang ngekstrak kabeh owah-owahan sing dibutuhake saka tabel log lan mbukak liwat panangan tambahan.

3.3. Ngimpor Linked Sets

Ing ndhuwur kita nimbang kasus nalika struktur data sumber lan tujuan padha. Nanging kepiye yen unggahan saka sistem eksternal duwe format sing beda karo struktur panyimpenan ing database kita?

Ayo dadi conto panyimpenan klien lan akun, pilihan "akeh-kanggo-siji" klasik:

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

Nanging download saka sumber eksternal teka ing wangun "kabeh ing siji":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Temenan, data pelanggan bisa diduplikasi ing versi iki, lan rekaman utama yaiku "akun":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Kanggo model, kita mung bakal nglebokake data tes, nanging elinga - COPY luwih efisien!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Pisanan, ayo nyorot "potongan" sing diarani "fakta" kita. Ing kasus kita, invoice ngrujuk marang pelanggan:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Kanggo nggandhengake akun kanthi bener karo ID pelanggan, luwih dhisik kita kudu ngerteni utawa ngasilake pengenal kasebut. Ayo ditambahake kolom ing ngisor iki:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Ayo nggunakake metode sinkronisasi tabel sing diterangake ing ndhuwur kanthi amandemen cilik - kita ora bakal nganyari utawa mbusak apa wae ing tabel target, amarga kita ngimpor klien "mung nambah":

-- проставляем в таблице импорта 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; -- прикладной ключ

Bener, kabeh ana ing invoice_import Saiki kita wis diisi kolom kontak client_id, karo kita bakal masang invoice.

Source: www.habr.com

DBA: cekap ngatur sinkronisasi lan impor