Điểm nghẽn: Tại sao các lệnh INSERT tiêu chuẩn thất bại khi xử lý quy mô lớn
Tôi từng dành cả buổi chiều để theo dõi một script Python chạy ì ạch khi migrate một cơ sở dữ liệu cũ. Tôi đã viết một vòng lặp cơ bản thực thi một câu lệnh INSERT cho mỗi dòng. Sau 30 phút, tôi kiểm tra số lượng: 50.000 bản ghi đã xử lý, còn 9,95 triệu bản ghi nữa.
Với tốc độ đó, quá trình migration sẽ mất gần bốn ngày. Các câu lệnh INSERT tiêu chuẩn rất hoàn hảo cho các giao dịch đơn lẻ, nhưng chúng cực kỳ chậm khi nạp dữ liệu hàng loạt (bulk loading). Mỗi câu lệnh buộc cơ sở dữ liệu phải phân tích cú pháp SQL, lập kế hoạch thực thi, kiểm tra các ràng buộc và ghi vào Write-Ahead Log (WAL).
Khi bạn làm việc với hàng triệu dòng, chi phí tài nguyên này trở thành một rào cản thực sự. Để di chuyển nhanh hơn, bạn phải chuyển từ các giao dịch riêng lẻ sang các thao tác theo lô (batch operations). Mục tiêu của tôi là chỉ cho bạn cách tận dụng tối đa thông lượng phần cứng để di chuyển dữ liệu trong vài phút chứ không phải vài ngày.
Tiêu chuẩn vàng: Sử dụng lệnh COPY
Đối với các tệp phẳng như CSV hoặc TSV, lệnh COPY là công cụ nhanh nhất trong kho vũ khí của PostgreSQL. Đây là một lệnh phía server giúp bỏ qua trình phân tích cú pháp SQL và bộ lập kế hoạch thực thi tiêu chuẩn. Thay vì xử lý từng dòng như một sự kiện riêng biệt, nó truyền dữ liệu trực tiếp vào bảng.
Sử dụng COPY từ psql CLI
Nếu tệp dữ liệu của bạn đã nằm trên database server, bạn có thể kích hoạt việc import trực tiếp từ console psql. Nó trông như thế này:
COPY users(first_name, last_name, email)
FROM '/var/lib/postgres/data/users.csv'
DELIMITER ','
CSV HEADER;Các tệp cục bộ yêu cầu một cách tiếp cận hơi khác. Nếu tệp nằm trên máy tính xách tay của bạn và bạn cần đẩy nó lên một instance RDS hoặc Cloud từ xa, hãy sử dụng meta-command \copy. Lệnh này đọc tệp cục bộ và truyền luồng dữ liệu đến server qua mạng:
psql -h remote-db-instance -U admin -d production_db -c "\copy users FROM 'local_users.csv' WITH (FORMAT csv, HEADER true)"Hiệu năng ở cấp độ ứng dụng: Multi-Row Inserts
Không phải lúc nào bạn cũng có sẵn một tệp CSV sạch sẽ. Đôi khi dữ liệu của bạn đến từ một API trực tiếp hoặc được tạo động bởi mã ứng dụng. Gửi từng lệnh INSERT cho mỗi bản ghi trong một vòng lặp là “kẻ sát nhân” đối với hiệu năng. Thay vào đó, bạn nên nhóm các bản ghi của mình thành các lô (batch).
Một lệnh insert đơn lẻ có thể mất 1ms. Tuy nhiên, một lệnh multi-row insert gồm 1.000 dòng có thể chỉ mất tổng cộng 10ms. Đó là mức tăng tốc 100 lần chỉ bằng cách thay đổi cú pháp của bạn.
Tránh mô hình này:
INSERT INTO orders (id, amount) VALUES (1, 100);
INSERT INTO orders (id, amount) VALUES (2, 200);Hãy áp dụng mô hình này:
INSERT INTO orders (id, amount) VALUES
(1, 100),
(2, 200),
(3, 300);Theo kinh nghiệm của tôi, kích thước lô từ 1.000 đến 5.000 bản ghi là “điểm rơi” lý tưởng. Nó cân bằng giữa việc tiêu thụ bộ nhớ và hiệu quả mạng. Nếu bạn sử dụng Python, thư viện psycopg3 có phương thức copy() nhanh hơn đáng kể so với execute_batch().
Xử lý định dạng dữ liệu trước khi Import
Dữ liệu lộn xộn là lý do phổ biến nhất khiến việc import thất bại. Tôi thường nhận được các tệp có mã hóa lạ hoặc cấu trúc lồng nhau không khớp trực tiếp với các cột. Trước khi xây dựng một pipeline tự động hóa hoàn chỉnh, tôi sử dụng toolcraft.app/vi/tools/data/csv-to-json để nhanh chóng xác minh cấu trúc dữ liệu. Nó chạy hoàn toàn trên trình duyệt, nghĩa là tôi có thể kiểm tra dữ liệu mà không cần tải các bản ghi nhạy cảm lên một server ngẫu nhiên nào đó.
Tối ưu hóa nâng cao: Tinh chỉnh Database Engine
Ngay cả lệnh COPY cũng có thể chạm ngưỡng giới hạn nếu cơ sở dữ liệu của bạn được cấu hình ưu tiên an toàn khi đọc hơn là tốc độ ghi. Khi import hàng trăm triệu dòng, bạn cần tạm thời nới lỏng các quy tắc.
1. Xóa bỏ Index và Ràng buộc
Index là kẻ thù của tốc độ nạp dữ liệu (ingestion speed). Mỗi khi bạn insert một dòng, PostgreSQL phải cập nhật mọi index B-tree hoặc Hash liên quan đến bảng đó. Đối với việc import 100 triệu dòng, việc xóa các index, chạy import, sau đó tạo lại chúng từ đầu thường nhanh hơn gấp 10 lần.
-- Xóa index trước
DROP INDEX idx_user_email;
-- Chạy lệnh COPY của bạn
COPY users FROM 'massive_data.csv' CSV;
-- Tạo lại index (Postgres thực hiện việc này rất hiệu quả khi làm hàng loạt)
CREATE INDEX idx_user_email ON users(email);2. Tinh chỉnh Write-Ahead Log (WAL)
PostgreSQL sử dụng các checkpoint để đảm bảo dữ liệu được ghi an toàn vào đĩa. Trong quá trình nạp dữ liệu lớn, các checkpoint này xảy ra quá thường xuyên, gây ra hiện tượng “checkpoint spikes” làm đình trệ việc import. Bạn có thể cho cơ sở dữ liệu thêm không gian hoạt động bằng cách tăng max_wal_size.
-- Tăng kích thước WAL lên 4GB để giảm tần suất checkpoint
SET max_wal_size = '4GB';
SET checkpoint_timeout = '20min';3. Sử dụng bảng UNLOGGED
Nếu bạn đang import dữ liệu vào một bảng tạm (staging table), hãy sử dụng từ khóa UNLOGGED. Các bảng này không ghi vào WAL. Điều này làm cho chúng nhanh một cách đáng kinh ngạc, mặc dù dữ liệu sẽ không tồn tại nếu cơ sở dữ liệu bị crash. Đối với dữ liệu trung gian mà bạn có thể dễ dàng tạo lại, đây là một lợi thế lớn.
CREATE UNLOGGED TABLE temp_import_staging (
id SERIAL PRIMARY KEY,
payload JSONB
);Danh sách kiểm tra để Import tốc độ cao
Để đạt được kết quả tốt nhất, hãy tuân theo quy trình này cho lần di chuyển dữ liệu lớn tiếp theo của bạn:
- Tắt Autocommit: Luôn bao bọc các lệnh multi-row insert trong một khối transaction duy nhất.
- Loại bỏ các index không thiết yếu: Chỉ tạo lại chúng sau khi dữ liệu đã được nạp xong.
- Tăng maintenance_work_mem: Đặt giá trị này thành 1GB hoặc hơn để tăng tốc độ tạo lại index.
- Sử dụng COPY: Đây là “nhà vô địch” không thể bàn cãi về tốc độ.
- Chạy ANALYZE: Sau khi import xong, hãy chạy
ANALYZE table_name. Điều này đảm bảo bộ lập kế hoạch truy vấn hiểu được sự phân bổ dữ liệu mới.
Bằng cách chuyển từ tư duy xử lý từng dòng sang tư duy truyền dữ liệu (streaming), tôi đã thấy thời gian import giảm từ sáu giờ xuống còn dưới mười lăm phút. PostgreSQL được xây dựng để xử lý thông lượng lớn; bạn chỉ cần ngừng cản trở nó.
