COPY so với INSERT

Nạp dữ liệu là việc ai cũng phải làm và ít ai đo. Phần này nạp cùng một triệu dòng bằng năm cách khác nhau, và tìm ra rằng cách nhanh nhất không phải là cách đáng khuyên nhất.

Năm cách, chênh nhau 112 lần

1.000.000 dòng, tệp CSV 81 MB, bảng trống không có chỉ mục:

COPY nhanh hơn cách chậm nhất 112 lần.

Nhưng nhìn kỹ hơn thì bước nhảy lớn nhất không nằm ở COPY:

7.278  ->  21.368     gộp vào một giao dịch          x 2,9
21.368 -> 294.118     gộp 1.000 dòng mỗi câu lệnh    x 13,8
294.118 -> 813.008    dùng COPY                      x 2,8

Từ 7.278 lên 294.118 là 40 lần, và làm được bằng cách gộp nhiều dòng vào một câu INSERT. Điều này quan trọng vì COPY không phải lúc nào cũng dùng được: nó cần quyền ghi tệp trên máy chủ hoặc một thư viện hỗ trợ COPY FROM STDIN, còn INSERT gộp thì chạy được ở mọi nơi.

Hầu hết ORM cho phép gộp — saveAll với batch_size trong Hibernate, executemany trong psycopg. Nếu ứng dụng của bạn đang chèn từng dòng một, 40 lần tốc độ nằm ở một dòng cấu hình.

Còn "mỗi dòng một giao dịch" là cách chậm nhất vì mỗi COMMIT buộc ghi WAL xuống đĩa và đợi. Đó là 1.000.000 lần fsync.

Chỉ mục làm chậm COPY

Ba chỉ mục làm COPY chậm 2,5 lần. Ràng buộc check thì gần như miễn phí — nó chỉ là một phép so sánh trên giá trị đã có trong bộ nhớ.

Thứ tự đúng là nạp trước, dựng chỉ mục sau:

COPY vào bảng đã có 3 chỉ mục            3,05 s
COPY vào bảng trống rồi dựng 3 chỉ mục   1,95 s      nhanh hơn 36%

Lý do: dựng chỉ mục trên dữ liệu đã có là một lần sắp xếp lớn, hiệu quả hơn nhiều so với chèn từng khoá một vào cây B-tree đang lớn dần.

Với bảng đã có dữ liệu và đang được dùng thì không xoá chỉ mục được. Nhưng với lần nạp đầu tiên, hoặc khi nạp vào bảng trung gian, đây là 36% miễn phí.

UNLOGGED: bỏ hẳn WAL

WAL từ 72 MB xuống 40 byte — gần như bằng không.

Thời gian thì gần như không đổi (1,14 so với 1,05 s) vì ở phép đo này đĩa không phải nút thắt. Trên máy chủ thật có sao lưu liên tục và máy dự phòng, 72 MB WAL cho một lần nạp là con số đáng kể — nó phải được truyền đi và lưu lại.

Cái giá thì rõ ràng và tuyệt đối. Tôi giết tiến trình chính bằng SIGKILL rồi khởi động lại:

bảng thường     1.000.000 dòng
bảng UNLOGGED           0 dòng

Không phải mất một phần — mất sạch. PostgreSQL cắt trắng mọi bảng UNLOGGED khi khởi động sau một lần dừng không sạch, vì nó không có cách nào biết chúng còn nguyên vẹn hay không.

Cách dùng đúng là cho bảng trung gian: nạp vào bảng UNLOGGED, xử lý, rồi chuyển kết quả sang bảng thường. Hoặc đổi chính bảng đó sau khi nạp xong:

alter table ul set logged;

Câu này ghi toàn bộ nội dung bảng vào WAL một lần — nên tổng WAL không giảm, nhưng bạn tránh được việc ghi WAL trong lúc nạp, khi tốc độ quan trọng nhất.

COPY FREEZE: bỏ được lần VACUUM đầu

begin;
truncate fz;
copy fz from '/tmp/d.csv' with (format csv, freeze);
commit;

Cả truncate và copy phải nằm trong cùng một giao dịch — đó là điều kiện để FREEZE được chấp nhận.

Phép đo cho thấy nó không nhanh hơn (1,34 so với 1,14 s) và không ít WAL hơn (cùng 72 MB). Lợi ích nằm chỗ khác:

relallvisible là số trang được đánh dấu "mọi dòng đều hiển thị với mọi giao dịch". Phần 15 đã đo rằng chính con số này quyết định Index Only Scan có chạy được hay không.

Với COPY thường, bảng vừa nạp xong không dùng được Index Only Scan cho tới khi có một lần VACUUM. Với COPY FREEZE, nó dùng được ngay.

Trên bảng lịch sử nạp một lần rồi chỉ đọc — đúng kiểu bảng người ta hay nạp bằng COPY — đây là khác biệt thật.

Thêm một lợi ích nữa: age(relfrozenxid) của bảng FREEZE là 3 so với 10 của bảng thường. Các dòng đã được đóng băng sẵn nên chúng không góp phần vào bài toán tràn số hiệu giao dịch ở phần 24.

Thứ tự đúng cho một lần nạp lớn

-- 1. bảng trống, chưa có chỉ mục và ràng buộc
create table dich(...);

-- 2. nạp, trong một giao dịch, có freeze
begin;
copy dich from '/duong/dan/du-lieu.csv' with (format csv, freeze);
commit;

-- 3-5. dựng chỉ mục và ràng buộc sau
alter table dich add primary key (id);
create index on dich(ma);
alter table dich add constraint c1 check (gia >= 0);

-- 6. thống kê
analyze dich;

Bước 6 hay bị quên nhất, và bỏ nó là rơi vào đúng cái bẫy đã đo ở phần 29: không có thống kê, bộ lập lịch dùng độ chọn lọc mặc định 1/3 và chọn kế hoạch chậm hơn 800 lần. Truy vấn vẫn chạy đúng, chỉ chậm, và triệu chứng tự biến mất khi autovacuum chạy — nên rất khó chẩn đoán.

Vài lưu ý về COPY

COPY FROM STDIN không cần quyền ghi tệp trên máy chủ. Đây là dạng mà thư viện client dùng, và nó nhanh ngang COPY từ tệp.

\copy của psql khác COPY của SQL. \copy đọc tệp trên máy client, COPY đọc trên máy chủ và cần quyền superuser hoặc vai pg_read_server_files.

Một dòng lỗi huỷ cả lệnh. COPY không có chế độ bỏ qua dòng hỏng. Nếu dữ liệu nguồn không sạch, nạp vào bảng trung gian toàn cột text trước rồi mới chuyển đổi.

COPY không kích hoạt trigger BEFORE INSERT theo dòng ở chế độ mặc định — thực ra nó có, nhưng đó là một trong những lý do khiến COPY chậm đi đáng kể trên bảng có trigger. Nếu bảng đích có trigger, con số 813.008 dòng/giây không áp dụng.

Thử ba mươi giây

Nếu ứng dụng của bạn đang chèn nhiều dòng, kiểm xem nó chèn thế nào:

select query, calls, rows, round(mean_exec_time::numeric, 3) as tb_ms
from pg_stat_statements
where query ilike 'insert%'
order by calls desc limit 10;

Nếu cột calls rất lớn mà rows chia cho calls bằng đúng 1, ứng dụng đang chèn từng dòng một. Đổi sang gộp lô là 40 lần tốc độ, và thường chỉ cần một dòng cấu hình.

Phần sau đo UPSERT và ON CONFLICT: so với cách kiểm tra rồi ghi, và cái bẫy đua tranh mà cách kiểm-tra-rồi-ghi luôn mắc phải.