คุณทำ bulk-load ข้อมูลอย่างมีประสิทธิภาพด้วย COPY อย่างไร?

Question

Accepted Answer

**`COPY`** เป็นคำสั่งประสิทธิภาพสูงของ PostgreSQL สำหรับ **bulk loading และ exporting** ข้อมูล โดยย้ายข้อมูลปริมาณมากระหว่างไฟล์ (หรือ stream) กับตารางได้เร็วกว่าคำสั่ง `INSERT` ทีละรายการมาก เป็นเครื่องมือมาตรฐานสำหรับการ import/export ชุดข้อมูลขนาดใหญ่

## COPY สำหรับ bulk loading ที่รวดเร็ว

```sql
-- load data from a CSV file into a table (server-side; needs file access + privileges)
COPY users (name, email, age) FROM '/path/data.csv' WITH (FORMAT csv, HEADER true);

-- export a table to a CSV file
COPY users TO '/path/export.csv' WITH (FORMAT csv, HEADER true);

-- from psql (client-side — works without server file access): use \copy
\copy users FROM 'data.csv' WITH (FORMAT csv, HEADER true)
```

`COPY` อ่าน/เขียนทั้งไฟล์ในการดำเนินการเดียว ซึ่งเร็วกว่าการรัน INSERT ทีละรายการนับพันครั้งอย่างมาก

## ทำไม COPY จึงเร็วกว่า INSERT มาก

```text
❌ thousands of individual INSERTs:
   each is a separate statement → per-statement overhead, possibly per-row transactions,
   network round-trips → SLOW for large data (can be 10-100x slower)

✅ COPY:
   one bulk operation, minimal overhead, optimized data path → FAST
→ For loading large datasets, COPY is the go-to; INSERTs are for small/individual rows.
```

## COPY กับ \copy (ฝั่ง server กับฝั่ง client)

```text
COPY (SQL command) → reads/writes files on the SERVER, needs superuser/file privileges
\copy (psql command) → reads/writes files on the CLIENT, no special privileges
→ Use \copy when running from your machine against a remote database.
```

## เคล็ดลับสำหรับ bulk loading ชุดข้อมูลขนาดใหญ่

```text
✓ Use COPY (not INSERTs) for large data
✓ Consider dropping/disabling indexes and constraints during a huge load, then
  recreating them after (building an index once is faster than maintaining it per-row)
✓ Load within a single transaction; increase maintenance_work_mem for index rebuilds
✓ For programmatic bulk inserts, use multi-row INSERT or the driver's COPY support
```

## ทำไมจึงสำคัญ

`COPY` เป็นเครื่องมือสำคัญของ PostgreSQL สำหรับ **การ load และ export ข้อมูลปริมาณมากอย่างมีประสิทธิภาพ** และการเข้าใจมันมีคุณค่าต่อทุกสถานการณ์ที่เกี่ยวข้องกับชุดข้อมูลขนาดใหญ่ เช่น data migration, ETL, import/export, การ seed และ analytics ซึ่งเป็นความต้องการในโลกจริงที่พบบ่อย

คุณค่าสำคัญคือ **ประสิทธิภาพ**: การ load ข้อมูลปริมาณมากด้วยคำสั่ง `INSERT` ทีละรายการช้าอย่างมาก (มี overhead ต่อ statement, round-trip อาจช้ากว่า 10-100 เท่า) ในขณะที่ `COPY` ทำการ load เป็นการดำเนินการแบบ bulk ที่ถูก optimize เดียว ทำให้เป็นตัวเลือกหลักสำหรับการ import หรือ export ชุดข้อมูลขนาดใหญ่ (หลายพันถึงหลายล้านแถว)

การเข้าใจ *ว่าทำไม* COPY จึงเร็วกว่ามาก (การดำเนินการแบบ bulk เทียบกับ overhead ต่อแถว) ช่วยให้ชัดเจนว่าเมื่อใดควรเลือกใช้มัน (ข้อมูลขนาดใหญ่) เทียบกับ INSERT (แถวเล็ก/ทีละรายการ)

การรู้ความแตกต่างเชิงปฏิบัติระหว่าง **`COPY`** (คำสั่ง SQL ฝั่ง server ต้องการสิทธิ์เข้าถึงไฟล์) และ **`\copy`** (คำสั่ง psql ฝั่ง client ไม่ต้องการสิทธิ์พิเศษ ทำงานกับฐานข้อมูลระยะไกลจากเครื่องของคุณได้) เป็นสิ่งสำคัญต่อการใช้งานจริงในบริบทต่าง ๆ

**เคล็ดลับการ bulk-load** (ใช้ COPY แทน INSERT, การ drop/สร้าง index ใหม่รอบ ๆ การ load ขนาดใหญ่ เนื่องจากการสร้าง index ครั้งเดียวดีกว่าการบำรุงรักษาทีละแถว, การ load ใน transaction) เป็นความรู้ด้านการ optimize ที่มีคุณค่าสำหรับการ import ขนาดใหญ่

เนื่องจากการดำเนินการกับข้อมูลปริมาณมาก (migration, import, export, ETL) พบได้บ่อย และเนื่องจาก COPY เป็นวิธีมาตรฐานที่มีประสิทธิภาพสูงในการจัดการสิ่งเหล่านี้ใน PostgreSQL (มีประสิทธิภาพเหนือกว่า INSERT อย่างมากสำหรับข้อมูลขนาดใหญ่) การเข้าใจ COPY ทั้งประโยชน์ด้านประสิทธิภาพ ความแตกต่างระหว่าง COPY กับ \copy และแนวปฏิบัติที่ดีในการ bulk-load จึงเป็นความรู้ที่มีคุณค่าและเกี่ยวข้องเชิงปฏิบัติสำหรับการย้ายข้อมูลขนาดใหญ่เข้าและออกจาก Postgres อย่างมีประสิทธิภาพ ซึ่งเป็นความต้องการเชิงปฏิบัติการที่พบบ่อยที่การใช้เครื่องมือที่ถูกต้อง (COPY) สร้างความแตกต่างอย่างมาก