Lúc đóng gói chuyển nhà, không ai vừa lôi từng cuốn sách khỏi kệ vừa xếp theo bảng chữ cái rồi mới bỏ vào thùng. Bạn dồn hết vào thùng thật nhanh, sang nhà mới mới sắp lại một lượt. Sắp-vừa-lấy nghĩa là mỗi cuốn mới lại phải xáo lại chồng đang lớn; sắp-ở-cuối chỉ tốn một lần. Nạp dữ liệu vào cơ sở dữ liệu vấp đúng chỗ đó, và không biết điều này là lý do người ta để COPY bò trên một bảng đầy chỉ mục. 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, 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:

Cách Thời gian Dòng/giây
COPY từ tệp 1,23 s 813.008
INSERT ... SELECT generate_series 1,54 s 649.351
Gộp 1.000 dòng mỗi lệnh INSERT — 294.118
INSERT từng dòng, tất cả trong một giao dịch — 21.368
INSERT từng dòng, mỗi dòng một giao dịch — 7.278

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

Bảng có gì COPY 1 triệu dòng
Trống, không ràng buộc 1,23 s
Thêm khoá chính 1,66 s
Thêm 2 chỉ mục nữa 3,05 s
Thêm một check 3,05 s

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

Thời gian WAL
Bảng thường 1,14 s 72 MB
Bảng UNLOGGED 1,05 s 40 byte

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:

Ngay sau khi nạp, chưa VACUUM relallvisible
COPY thường 0
COPY FREEZE 12.346 trên 12.376 trang

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.

Mẫu số chung

Chuyện "dựng chỉ mục sau nhanh hơn 36%" chỉ ra một nguyên tắc lớn hơn nạp dữ liệu: bảo trì một cấu trúc từng-món khi dữ liệu nhỏ giọt vào phải trả một lần tổ chức-lại cho mỗi món; nếu đã có sẵn cả tập, hoãn việc tổ chức tới một lượt gộp duy nhất ở cuối chỉ tốn một lần. Chèn từng khoá vào cây B-tree đang lớn là xáo lại cái chồng mỗi bước; dựng chỉ mục sau khi nạp là một lần sắp xếp. Cùng dữ liệu, cùng kết quả, khác ở chỗ trả phí tổ chức bao nhiêu lần.

Đúng "gộp-ở-cuối thắng bảo-trì-từng-món" này ở khắp nơi:

  • Trong Java, dựng một HashMap mà khai sẵn dung lượng đầu thắng để nó tự lớn và rehash nhiều lần; đổ hết vào một mảng rồi Arrays.sort một lần thắng việc giữ một TreeSet sắp lại sau mỗi lần chèn; StringBuilder (nối rồi toString một lần) thắng nối chuỗi lặp đi lặp lại.
  • Trong Go, make([]T, 0, n) cấp sẵn sức chứa thắng append để slice liên tục cấp lại vùng nhớ và sao chép; gom hết rồi sort một lần thắng chèn-có-thứ-tự từng phần.
  • Ở tầng giải thuật, heapify biến một mảng thành heap trong O(n) một lượt, rẻ hơn hẳn chèn n phần tử vào heap O(n log n); dựng chỉ mục tìm kiếm ngoại tuyến rẻ hơn cập nhật theo từng tài liệu.

Nên khi phải nạp hay dựng một tập dữ liệu đã biết trước, câu hỏi đúng không phải "cách chèn nào nhanh nhất" mà là "tôi có đang bắt cấu trúc làm việc sổ sách theo từng món mà lẽ ra để dồn tới cuối không". Đưa dữ liệu vào thô và rẻ trước, rồi dựng thứ tự/chỉ mục/thống kê trong một nhát — và hoãn mọi thứ hoãn được (WAL, VACUUM, ràng buộc) ra khỏi vòng nạp nóng. Tự soát ngay xem ứng dụng đang 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.