DBA: tổ chức thành thạo việc đồng bộ hóa và nhập khẩu

Để xử lý phức tạp các tập dữ liệu lớn (khác nhau quy trình ETL: nhập, chuyển đổi và đồng bộ hóa với nguồn bên ngoài) thường có nhu cầu tạm thời “ghi nhớ” và xử lý nhanh chóng ngay lập tức thứ gì đó đồ sộ.

Một nhiệm vụ điển hình thuộc loại này thường có âm thanh như thế này: "Ngay chỗ này bộ phận kế toán dỡ hàng từ ngân hàng khách hàng các khoản thanh toán nhận được lần cuối, bạn cần nhanh chóng tải chúng lên trang web và liên kết chúng với tài khoản của mình.”

Nhưng khi dung lượng của “thứ gì đó” này bắt đầu lên tới hàng trăm megabyte và dịch vụ phải tiếp tục hoạt động với cơ sở dữ liệu 24x7, thì nhiều tác dụng phụ sẽ phát sinh sẽ hủy hoại cuộc sống của bạn.

Để xử lý chúng trong PostgreSQL (và không chỉ trong đó), bạn có thể sử dụng một số tối ưu hóa cho phép bạn xử lý mọi thứ nhanh hơn và tiêu thụ ít tài nguyên hơn.

1. Gửi hàng ở đâu?

Trước tiên, hãy quyết định nơi chúng tôi có thể tải lên dữ liệu mà chúng tôi muốn “xử lý”.

1.1. Bảng tạm thời (BẢNG TẠM THỜI)

Về nguyên tắc, các bảng tạm thời của PostgreSQL cũng giống như bất kỳ bảng nào khác. Vì vậy, những mê tín như “Mọi thứ ở đó chỉ được lưu giữ trong ký ức và nó có thể kết thúc”. Nhưng cũng có một số khác biệt đáng kể.

“Không gian tên” của riêng bạn cho mỗi kết nối tới cơ sở dữ liệu

Nếu hai kết nối cố gắng kết nối cùng một lúc CREATE TABLE x, thì chắc chắn sẽ có người nhận được lỗi không duy nhất các đối tượng cơ sở dữ liệu.

Nhưng nếu cả hai cố gắng thực thi CREATE TEMPORARY TABLE x, thì cả hai sẽ thực hiện bình thường và mọi người sẽ nhận được bản sao chép của bạn những cái bàn. Và sẽ không có gì chung giữa họ.

“Tự hủy” khi ngắt kết nối

Khi kết nối bị đóng, tất cả các bảng tạm thời sẽ tự động bị xóa theo cách thủ công DROP TABLE x chẳng có ý nghĩa gì ngoại trừ...

Nếu bạn đang làm việc thông qua pgbouncer trong chế độ giao dịch, thì cơ sở dữ liệu tiếp tục tin rằng kết nối này vẫn đang hoạt động và trong đó bảng tạm thời này vẫn tồn tại.

Do đó, việc cố gắng tạo lại nó từ một kết nối khác tới pgbouncer sẽ dẫn đến lỗi. Nhưng điều này có thể tránh được bằng cách sử dụng CREATE TEMPORARY TABLE IF NOT EXISTS x.

Đúng, tốt hơn hết là bạn không nên làm điều này, vì khi đó bạn có thể “đột nhiên” tìm thấy ở đó dữ liệu còn lại từ “chủ sở hữu trước đó”. Thay vào đó, tốt hơn hết bạn nên đọc hướng dẫn và thấy rằng khi tạo bảng có thể thêm ON COMMIT DROP - tức là khi giao dịch hoàn tất, bảng sẽ tự động bị xóa.

Không sao chép

Vì chúng chỉ thuộc về một kết nối cụ thể nên các bảng tạm thời không được sao chép. Nhưng điều này giúp loại bỏ sự cần thiết phải ghi dữ liệu hai lần trong heap + WAL, vì vậy CHÈN/CẬP NHẬT/XÓA vào đó nhanh hơn nhiều.

Nhưng vì một bảng tạm thời vẫn là một bảng “gần như bình thường” nên nó cũng không thể được tạo trên một bản sao. Ít nhất là ở thời điểm hiện tại, mặc dù bản vá tương ứng đã được lưu hành từ lâu.

1.2. BẢNG KHÔNG ĐƯỢC GHI NHẬP

Nhưng bạn nên làm gì, chẳng hạn, nếu bạn có một loại quy trình ETL rườm rà nào đó không thể thực hiện được trong một giao dịch, nhưng bạn vẫn có pgbouncer trong chế độ giao dịch? ..

Hoặc luồng dữ liệu lớn đến mức Không có đủ băng thông trên một kết nối từ cơ sở dữ liệu (đọc, một tiến trình cho mỗi CPU)?..

Hoặc một số hoạt động đang diễn ra không đồng bộ trong các kết nối khác nhau?..

Ở đây chỉ có một lựa chọn - tạm thời tạo một bảng không tạm thời. Pun, vâng. Đó là:

• đã tạo các bảng “của riêng tôi” với các tên ngẫu nhiên tối đa để không giao nhau với bất kỳ ai
• Trích xuất: điền chúng với dữ liệu từ nguồn bên ngoài
• Chuyển đổi: đã chuyển đổi, điền vào các trường liên kết chính
• Phụ tải: đổ dữ liệu đã sẵn sàng vào các bảng mục tiêu
• đã xóa bảng "của tôi"

Và bây giờ - một con ruồi trong thuốc mỡ. Trong thực tế, tất cả việc ghi vào PostgreSQL xảy ra hai lần - đầu tiên ở WAL, sau đó vào phần thân bảng/chỉ mục. Tất cả điều này được thực hiện để hỗ trợ ACID và hiển thị dữ liệu chính xác giữa COMMIT'hấp dẫn và ROLLBACK'giao dịch vô hiệu.

Nhưng chúng tôi không cần điều này! Chúng tôi có toàn bộ quá trình Hoặc nó đã hoàn toàn thành công hoặc không.. Sẽ có bao nhiêu giao dịch trung gian không quan trọng - chúng tôi không quan tâm đến việc “tiếp tục quá trình từ giữa”, đặc biệt là khi không rõ nó ở đâu.

Để làm điều này, các nhà phát triển PostgreSQL, quay lại phiên bản 9.1, đã giới thiệu một thứ như bảng KHÔNG ĐƯỢC GHI NHẬP:

Với dấu hiệu này, bảng được tạo dưới dạng chưa được ghi lại. Dữ liệu được ghi vào các bảng không được ghi nhật ký không đi qua nhật ký ghi trước (xem Chương 29), khiến các bảng đó bị lỗi. làm việc nhanh hơn nhiều so với bình thường. Tuy nhiên, họ không tránh khỏi thất bại; trong trường hợp máy chủ bị lỗi hoặc tắt khẩn cấp, bảng không được ghi lại tự động cắt ngắn. Ngoài ra, nội dung của bảng chưa được ghi lại không được sao chép đến các máy chủ nô lệ. Bất kỳ chỉ mục nào được tạo trên một bảng chưa được ghi nhật ký sẽ tự động trở thành bảng chưa được ghi nhật ký.

Tóm lại nó sẽ nhanh hơn nhiều, nhưng nếu máy chủ cơ sở dữ liệu “rơi” thì sẽ rất khó chịu. Nhưng điều này xảy ra với tần suất như thế nào và quy trình ETL của bạn có biết cách sửa lỗi này một cách chính xác “từ giữa” sau khi “hồi sinh” cơ sở dữ liệu không?..

Nếu không, và trường hợp trên cũng tương tự như trường hợp của bạn, hãy sử dụng UNLOGGEDnhưng không bao giờ không kích hoạt thuộc tính này trên các bảng thực, dữ liệu mà bạn yêu quý.

1.3. TRÊN CAM KẾT { XÓA HÀNG | LÀM RƠI}

Cấu trúc này cho phép bạn chỉ định hành vi tự động khi giao dịch được hoàn thành khi tạo bảng.

trên ON COMMIT DROP Tôi đã viết ở trên, nó tạo ra DROP TABLE, nhưng vơi ON COMMIT DELETE ROWS tình hình thú vị hơn - nó được tạo ra ở đây TRUNCATE TABLE.

Vì toàn bộ cơ sở hạ tầng để lưu trữ siêu mô tả của bảng tạm thời hoàn toàn giống với cơ sở hạ tầng của bảng thông thường, nên Việc liên tục tạo và xóa các bảng tạm thời dẫn đến bảng hệ thống bị “phình to” nghiêm trọng pg_class, pg_attribute, pg_attrdef, pg_depend,…

Bây giờ hãy tưởng tượng rằng bạn có một nhân viên kết nối trực tiếp với cơ sở dữ liệu, nhân viên này sẽ mở một giao dịch mới mỗi giây, tạo, điền, xử lý và xóa một bảng tạm thời... Sẽ có quá nhiều rác tích lũy trong các bảng hệ thống và điều này sẽ gây ra hiện tượng phanh phụ cho mỗi lần vận hành.

Nói chung, đừng làm điều này! Trong trường hợp này nó hiệu quả hơn nhiều CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS đưa nó ra khỏi chu kỳ giao dịch - khi bắt đầu mỗi giao dịch mới, các bảng đã sẵn sàng sẽ tồn tại (lưu cuộc gọi CREATE), nhưng sẽ trống rỗng, TRUNCATE (chúng tôi cũng đã lưu cuộc gọi của nó) khi hoàn tất giao dịch trước đó.

1.4. THÍCH...BAO GỒM...

Tôi đã đề cập ngay từ đầu rằng một trong những trường hợp sử dụng điển hình cho các bảng tạm thời là các loại nhập khác nhau - và nhà phát triển mệt mỏi sao chép-dán danh sách các trường của bảng mục tiêu vào phần khai báo bảng tạm thời của mình...

Nhưng sự lười biếng là động cơ của sự tiến bộ! Đó là lý do tại sao tạo một bảng mới "dựa trên mẫu" nó có thể đơn giản hơn nhiều:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Vì sau đó bạn có thể tạo nhiều dữ liệu vào bảng này nên việc tìm kiếm qua bảng này sẽ không bao giờ nhanh chóng. Nhưng có một giải pháp truyền thống cho vấn đề này - lập chỉ mục! Và vâng, một bảng tạm thời cũng có thể có chỉ mục.

Vì thông thường, các chỉ mục bắt buộc trùng với các chỉ mục của bảng mục tiêu, nên bạn có thể chỉ cần viết LIKE target_table INCLUDING INDEXES.

Nếu bạn cũng cần DEFAULT-values ​​​​(ví dụ: để điền các giá trị khóa chính), bạn có thể sử dụng LIKE target_table INCLUDING DEFAULTS. Hoặc đơn giản - LIKE target_table INCLUDING ALL — sao chép các giá trị mặc định, chỉ mục, ràng buộc,...

Nhưng ở đây bạn cần hiểu rằng nếu bạn tạo nhập bảng ngay với các chỉ mục, khi đó dữ liệu sẽ mất nhiều thời gian hơn để tảihơn là nếu trước tiên bạn điền mọi thứ và chỉ sau đó tổng hợp các chỉ mục - hãy xem cách nó thực hiện điều này như một ví dụ pg_dump.

Nói chung, RTFM!

2. Viết như thế nào?

Hãy để tôi nói - hãy sử dụng nó COPY-flow thay vì "đóng gói" INSERT, có lúc tăng tốc. Bạn thậm chí có thể trực tiếp từ một tập tin được tạo trước.

3. Xử lý thế nào?

Vì vậy, hãy để phần giới thiệu của chúng ta trông giống như thế này:

• bạn có một bảng chứa dữ liệu khách hàng được lưu trữ trong cơ sở dữ liệu của bạn Bản ghi 1 triệu
• mỗi ngày một khách hàng gửi cho bạn một cái mới đầy đủ "hình ảnh"
• từ kinh nghiệm bạn biết rằng thỉnh thoảng không quá 10K bản ghi được thay đổi

Một ví dụ kinh điển về tình huống như vậy là căn cứ KLADR — tổng cộng có rất nhiều địa chỉ, nhưng trong mỗi lần tải lên hàng tuần có rất ít thay đổi (đổi tên các khu định cư, kết hợp đường phố, xuất hiện những ngôi nhà mới) ngay cả trên quy mô quốc gia.

3.1. Thuật toán đồng bộ hóa đầy đủ

Để đơn giản, giả sử bạn thậm chí không cần phải cơ cấu lại dữ liệu - chỉ cần đưa bảng về dạng mong muốn, đó là:

• loại bỏ mọi thứ không còn tồn tại
• nâng cấp mọi thứ đã tồn tại và cần được cập nhật
• để chèn mọi thứ vẫn chưa xảy ra

Tại sao các hoạt động nên được thực hiện theo thứ tự này? Bởi vì đây là cách kích thước bảng sẽ tăng tối thiểu (hãy nhớ MVCC!).

XÓA TỪ dst

Không, tất nhiên bạn có thể thực hiện được chỉ bằng hai thao tác:

• loại bỏ (DELETE) mọi thứ nói chung
• để chèn tất cả từ hình ảnh mới

Nhưng đồng thời, nhờ có MVCC, Kích thước của bảng sẽ tăng đúng gấp đôi! Nhận được +1M hình ảnh của các bản ghi trong bảng do cập nhật 10K là quá dư thừa ...

CẮT bớt dst

Một nhà phát triển giàu kinh nghiệm hơn biết rằng toàn bộ máy tính bảng có thể được làm sạch khá rẻ:

• để xóa (TRUNCATE) toàn bộ bảng
• để chèn tất cả từ hình ảnh mới

Phương pháp này có hiệu quả, đôi khi khá áp dụng, nhưng có một vấn đề... Chúng tôi sẽ thêm 1 triệu bản ghi trong một thời gian dài, vì vậy chúng tôi không thể để trống bảng trong suốt thời gian này (điều sẽ xảy ra nếu không gói nó trong một giao dịch duy nhất).

Nghĩa là:

• chúng tôi đang bắt đầu giao dịch kéo dài
• TRUNCATE áp đặt Truy cập độc quyền-chặn
• chúng tôi thực hiện việc chèn trong một thời gian dài và mọi người khác tại thời điểm này thậm chí không thể SELECT

Có điều gì đó không ổn...

THAY ĐỔI BẢNG… ĐỔI TÊN… / THẢ BẢNG…

Một cách khác là điền mọi thứ vào một bảng mới riêng biệt, sau đó chỉ cần đổi tên nó thay cho bảng cũ. Một vài điều nhỏ khó chịu:

• vẫn vậy Truy cập độc quyền, mặc dù thời gian ít hơn đáng kể
• tất cả các kế hoạch/thống kê truy vấn cho bảng này được đặt lại, cần chạy PHÂN TÍCH
• tất cả các khóa ngoại đều bị hỏng (FK) lên bàn

Có một bản vá WIP từ Simon Riggs gợi ý thực hiện ALTER-một thao tác để thay thế nội dung bảng ở cấp độ tệp mà không chạm vào số liệu thống kê và FK, nhưng không thu thập số đại biểu.

XÓA, CẬP NHẬT, CHÈN

Vì vậy, chúng tôi giải quyết tùy chọn không chặn của ba thao tác. Gần ba... Làm thế nào để làm điều này một cách hiệu quả nhất?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Xử lý hậu kỳ nhập khẩu

Trong cùng một KLADR, tất cả các bản ghi đã thay đổi phải được chạy bổ sung thông qua quá trình xử lý hậu kỳ - chuẩn hóa, đánh dấu các từ khóa và giảm bớt các cấu trúc được yêu cầu. Nhưng bạn biết nó bằng cách nào - chính xác những gì đã thay đổimà không làm phức tạp mã đồng bộ hóa, lý tưởng nhất là không cần chạm vào nó?

Nếu chỉ quy trình của bạn có quyền ghi tại thời điểm đồng bộ hóa thì bạn có thể sử dụng trình kích hoạt sẽ thu thập tất cả các thay đổi cho chúng tôi:

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

Bây giờ chúng tôi có thể áp dụng trình kích hoạt trước khi bắt đầu đồng bộ hóa (hoặc bật chúng qua 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();

Sau đó, chúng tôi bình tĩnh trích xuất tất cả những thay đổi cần thiết từ bảng nhật ký và chạy chúng thông qua các trình xử lý bổ sung.

3.3. Nhập các bộ liên kết

Ở trên chúng tôi đã xem xét các trường hợp khi cấu trúc dữ liệu của nguồn và đích giống nhau. Nhưng điều gì sẽ xảy ra nếu nội dung tải lên từ hệ thống bên ngoài có định dạng khác với cấu trúc lưu trữ trong cơ sở dữ liệu của chúng ta?

Hãy lấy ví dụ về việc lưu trữ khách hàng và tài khoản của họ, tùy chọn “nhiều-một” cổ điển:

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

Nhưng bản tải xuống từ nguồn bên ngoài đến với chúng tôi dưới dạng “tất cả trong một”:

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

Rõ ràng, dữ liệu khách hàng có thể được sao chép trong phiên bản này và bản ghi chính là “tài khoản”:

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

Đối với mô hình, chúng tôi chỉ cần chèn dữ liệu thử nghiệm của mình, nhưng hãy nhớ - COPY hiệu quả hơn!

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

Trước tiên, hãy làm nổi bật những “điểm cắt” mà “sự thật” của chúng ta đề cập đến. Trong trường hợp của chúng tôi, hóa đơn đề cập đến khách hàng:

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

Để liên kết chính xác các tài khoản với ID khách hàng, trước tiên chúng tôi cần tìm hiểu hoặc tạo các số nhận dạng này. Hãy thêm các trường bên dưới chúng:

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

Hãy sử dụng phương pháp đồng bộ hóa bảng được mô tả ở trên với một sửa đổi nhỏ - chúng tôi sẽ không cập nhật hoặc xóa bất kỳ nội dung nào trong bảng mục tiêu vì chúng tôi nhập ứng dụng khách "chỉ nối thêm":

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

Thực ra mọi thứ đều ở trong invoice_import Bây giờ chúng ta đã điền vào trường liên hệ client_id, mà chúng tôi sẽ chèn hóa đơn.

Nguồn: www.habr.com