Bulk insert Postgres: COPY vs UNNEST vs multi-VALUES — chọn cái nào cho tốc độ

Import job cuối tuần: 500K dòng từ file CSV vào một bảng Postgres, chạy trong worker. Version 1 dùng ORM session.add() trong vòng lặp — 42 phút. Version 2 chuyển sang executemany() — 11 phút. Version 3 dùng COPY — 28 giây. Chênh nhau gần 90 lần chỉ vì cách gửi dữ liệu, không phải index, không phải schema.

Đây là chỗ đa số backend engineer bỏ qua. Bulk insert Postgres có ít nhất 3 cách rất khác nhau về hiệu năng, và chọn sai không phải “chậm chút” — chậm cả một order of magnitude. Bài này mổ xẻ ba cách, benchmark thô, và khi nào dùng cái nào.

Ba cách chính

Cùng nhiệm vụ: insert 100.000 dòng vào bảng events(id BIGSERIAL, user_id BIGINT, event_type TEXT, payload JSONB, created_at TIMESTAMPTZ), không có index phụ ngoài PK.

Cách 1: multi-row VALUES

sql = "INSERT INTO events (user_id, event_type, payload, created_at) VALUES " + \
      ", ".join(["(%s, %s, %s, %s)"] * len(rows))
params = [v for row in rows for v in row]
cur.execute(sql, params)

Gộp N tuple vào một câu INSERT ... VALUES (...), (...), .... Postgres parse một lần, plan một lần, execute một lần — nhanh hơn executemany (mà psycopg2 mặc định chạy N round-trip riêng). Nhược điểm: SQL text có thể phình rất to, và Postgres giới hạn số parameter placeholder ở 65535 cho một prepared statement. 100K dòng × 4 cột = 400K placeholder → vượt trần. Phải chia thành batch nhỏ (~10K dòng).

Cách 2: INSERT với unnest

sql = """
INSERT INTO events (user_id, event_type, payload, created_at)
SELECT * FROM unnest(%s::bigint[], %s::text[], %s::jsonb[], %s::timestamptz[])
"""
cur.execute(sql, (user_ids, event_types, payloads, timestamps))

Truyền mỗi cột như một mảng, dùng unnest để “unroll” chúng thành rows. Chỉ có 4 parameter thôi — bất kể bao nhiêu dòng. Không đụng giới hạn 65535. Postgres nhận payload nhị phân gọn hơn hẳn text SQL phình to.

Trick này ít người biết. Nó thực sự đẹp — SQL vẫn là INSERT ... SELECT chuẩn, dễ gắn thêm ON CONFLICT, RETURNING, join thêm bảng khác. Là điểm ngọt giữa “gõ SQL bằng tay” và “chạy full COPY”.

Cách 3: COPY

with cur.copy("COPY events (user_id, event_type, payload, created_at) FROM STDIN") as copy:
    for row in rows:
        copy.write_row(row)

Đây là con đường ngắn nhất từ client vào storage. Postgres bypass hầu như toàn bộ SQL parser, executor, planner — chỉ còn parse từng dòng theo format COPY, ghi thẳng vào heap. Với psycopg v3, API cur.copy() gọn gàng và hỗ trợ binary format nhanh hơn nữa.

Benchmark thô

Chạy trên Postgres 16 local, bảng rỗng, 100K dòng, mỗi payload JSONB ~200 bytes:

Cách	Thời gian	Ghi chú
session.add() từng dòng (SQLAlchemy)	~65s	ORM overhead + flush
executemany (psycopg2 mặc định)	~14s	N round-trip riêng, mỗi cái 1 parse
Multi-row VALUES (batch 5000)	~5.8s	Parse 1 lần / batch
INSERT ... SELECT unnest (batch 20000)	~4.1s	Ít parameter, protocol nhẹ
COPY FROM STDIN (text)	~1.9s	Bypass SQL
COPY FROM STDIN (binary)	~1.4s	+ không parse text

Số cụ thể sẽ khác trên máy khác, nhưng tỉ lệ giữa chúng khá ổn định: COPY nhanh hơn multi-row VALUES 3-4x, và VALUES nhanh hơn executemany mặc định 2-3x, còn ORM per-row là thảm hoạ.

Vì sao COPY nhanh khủng khiếp

Ba lý do chính:

1. Bypass SQL parser/planner. Mỗi INSERT ... VALUES (kể cả multi-row) đều phải parse SQL text, resolve tên bảng/cột, kiểm tra type. COPY chỉ ăn stream data theo format cố định, không có “câu lệnh” để parse cho từng dòng.

2. Ít round-trip mạng. Client stream data liên tục qua một protocol frame duy nhất. Không có “gửi N câu execute, chờ N response ACK”.

3. WAL viết gọn hơn. Nếu bảng được tạo hoặc truncate trong cùng transaction với COPY, Postgres có thể dùng optimization wal_level = minimal để giảm WAL. Không phải chỗ nào cũng ăn được optimization này, nhưng khi ăn được thì tiết kiệm rất nhiều I/O.

Vì sao unnest cũng nhanh (và khi nào chọn nó)

unnest không nhanh bằng COPY, nhưng có tính năng COPY không có: nó vẫn là một câu INSERT bình thường. Có nghĩa là:

• ON CONFLICT (uuid) DO UPDATE SET ... — upsert hàng loạt. COPY không hỗ trợ trực tiếp.
• RETURNING id, created_at — lấy về ID vừa insert. COPY (via psycopg) không returning được.
• Join với dữ liệu khác trong DB: SELECT u.id, e.event_type FROM unnest(...) e(user_email, event_type) JOIN users u ON u.email = e.user_email — biến bulk insert thành bulk lookup + insert trong một round-trip.

Khi tôi cần bulk upsert vào bảng đã có dữ liệu — như sync danh mục từ vendor, hay ingest kèm dedupe theo unique key — unnest gần như luôn là lựa chọn đúng.

sql = """
INSERT INTO products (sku, name, price, updated_at)
SELECT * FROM unnest(%s::text[], %s::text[], %s::numeric[], %s::timestamptz[])
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at
"""
cur.execute(sql, (skus, names, prices, timestamps))

Một câu, 20K dòng, upsert xong.

Multi-row VALUES: đơn giản nhất, đủ tốt cho phần lớn case

Không phải case nào cũng cần đến unnest hay COPY. Nếu batch chỉ 500-2000 dòng, chạy vài lần / phút — multi-row VALUES đủ tốt và ai đọc code cũng hiểu ngay. Nhớ:

• Chia batch theo giới hạn 65535 placeholder: batch_size = 60000 // len(columns) cho an toàn.
• Nếu dùng SQLAlchemy Core: session.execute(insert(Model).values([...])) sẽ tự sinh multi-row VALUES thay vì per-row.
• Nếu dùng psycopg2, bật execute_values từ psycopg2.extras — nó tự gộp thành multi-row VALUES đúng chuẩn.

Mấy pitfall dễ dính

Pitfall 1: psycopg2 executemany mặc định chậm khủng khiếp.

cur.executemany(sql, list_of_params) trong psycopg2 KHÔNG batch. Nó chạy N câu INSERT riêng, N round-trip. Chậm bằng loop for. Đổi sang execute_values (psycopg2) hoặc executemany của psycopg3 (đã tự batch bằng pipeline mode) — nhanh gấp 5-10 lần chỉ đổi hàm.

Pitfall 2: COPY trong transaction cùng với các query khác.

COPY FROM STDIN chiếm connection cho stream. Nếu bạn cố chạy query khác trong cùng transaction giữa lúc đang stream, sẽ dính lỗi protocol. Pattern an toàn: mở transaction → chạy setup query (truncate, create temp table) → chạy COPY → commit. Đừng xen kẽ.

Pitfall 3: unnest sai type cast.

unnest(%s::text[], %s::int[]) — nếu chỉ truyền %s mà không cast, psycopg đôi khi gửi array sai loại và Postgres sẽ khóc “cannot determine type of empty array”. Cast rõ ràng cả khi dữ liệu không rỗng để tránh bug lúc batch rỗng edge case.

Pitfall 4: JSONB serialize.

Với unnest(%s::jsonb[]), psycopg cần biết dịch dict Python → JSONB. psycopg v3 xử lý dict tự động; psycopg2 cần Json(...) wrapper hoặc json.dumps trước rồi cast ::jsonb. Sai chỗ này sẽ ra lỗi “invalid input syntax for type json” khó truy.

Pitfall 5: Index và trigger ăn hết thời gian tiết kiệm được.

Bạn dùng COPY để insert nhanh 5M dòng vào bảng có 4 index và 2 trigger. Insert xong không phải 30 giây mà 8 phút — vì mỗi row phải maintain 4 index và fire 2 trigger. Với ETL initial load, pattern quen tay là drop index / disable trigger → COPY → tạo lại index (CREATE INDEX CONCURRENTLY). Với ingest incremental thì đành chịu.

Quy tắc tôi đang dùng

• < 100 dòng, insert lẻ tẻ: INSERT bình thường, không cần optimize.
• 100 - 5000 dòng, batch ổn định: multi-row VALUES (hoặc execute_values / SQLAlchemy insert().values([...])).
• Cần upsert / RETURNING / join thêm: INSERT ... SELECT unnest(...).
• > 100K dòng, initial load, không cần upsert: COPY FROM STDIN (binary nếu client hỗ trợ).
• ETL định kỳ vào bảng nhiều index: drop index → COPY → tạo lại CONCURRENTLY.

Đừng bắt đầu bằng “tôi cần COPY”. Bắt đầu bằng câu hỏi kích cỡ batch trung bình bao nhiêu. Với 90% code path ứng dụng, batch <1000 dòng và multi-row VALUES là đủ. Chỉ khi bạn viết ingest job hoặc migration mới cần chạm tới COPY.

Nhưng khi cần — biết nó tồn tại là khác biệt giữa job 30 giây và job 30 phút.