Pira cara-carane ngunggah data kanthi efisien nganggo COPY?

Question

Accepted Answer

**`COPY`** minangka printah PostgreSQL sing performane dhuwur kanggo **ngunggah data kanthi bulk lan ngeksport** — minangka cara kanggo mindhah data kanthi banyak antarane file (utawa stream) lan tabel nang luwih cepet tinimbang statement `INSERT` individu. Iku alat standar kanggo import/export dataset gedhe.

## COPY kanggo ngunggah bulk nang cepet

```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` maca/nulis file lengkap ing siji operasi — nang malah luwih cepet tinimbang njalanake ribuan INSERT individu.

## Apa COPY luwih cepet banget tinimbang 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 vs \copy (server-side vs client-side)

```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.
```

## Tips kanggo ngunggah bulk dataset gedhe

```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
```

## Apa iki penting

`COPY` minangka alat PostgreSQL sing penting kanggo **ngunggah lan ngeksport data kanthi efisien**, lan ngerti carane iku narik kanggo skenario apa wae sing melibatkan dataset gedhe — migrasi data, ETL, import/export, seeding, lan analytics, sing kerap nang jaman saiki.

Nilai utama yaiku **performane**: ngunggah data kanthi banyak nganggo statement `INSERT` individu iku alon banget (per-statement overhead, round-trips — bisa 10-100x luwih alon), nanging `COPY` njalanake ngunggah minangka siji operasi bulk sing dioptimisasi, dadi iku pilihan utama kanggo import utawa export dataset gedhe (rong siji nganti jutaan baris).

Ngerti *apa* COPY luwih cepet (operasi bulk vs per-row overhead) ngewangi kanggo ngerti kapan kudu nganggo (data gedhe) versus INSERT (baris sithik/individu).

Ngerti bedane praktis antarane **`COPY`** (printah SQL server-side, mbutuhake akses file/privilege) lan **`\copy`** (printah psql client-side, ora mbutuhake privilege khusus — bisa lawan database jarak saka mesin sampeyan) iku penting kanggo nganggo ing konteks beda.

Tipsan **bulk-loading** (nganggo COPY tinimbang INSERT, dhulung nyusut/ngruwat maneh indeks ing sekitar ngunggah gedhe amarga mbanguN indeks sepisan luwih apik tinimbang njaga saben baris, ngunggah ing transaksi) iku ilmu optimisasi narik kanggo import gedhe.

Karana operasi data bulk (migrasi, import, export, ETL) kerap, lan karana COPY minangka cara standar, sing performane dhuwur kanggo nangani ing PostgreSQL (jauh luwih apik tinimbang INSERT kanggo data gedhe), ngerti COPY — kauntungan performanenya, bedane COPY vs \copy, lan praktek-praktek terbaik bulk-loading — iku ilmu narik, relevan kanthi praktik kanggo mindhah dataset gedhe menyang lan metu saka Postgres kanthi efisien, kebutuhan operasional sing kerap nanging nganggo alat sing tepat (COPY) nggawe beda gedhe.