KAL
Menu

Pola ingestion

COPY, insert terbatch, dan upsert untuk data yang datang terlambat.

Daftar isi

Pola ingestion menentukan berapa round-trip klien-server yang dibutuhkan untuk menuliskan sekumpulan titik, dan bagaimana data terlambat ditangani tanpa menolak baris. Topik ini membandingkan tiga pola, yaitu single-row INSERT, multi-row INSERT, dan COPY, lalu menjelaskan upsert dengan klausa ON CONFLICT untuk data yang datang terlambat. Setelah membaca topik ini, pilihan pola ingestion tidak lagi diambil begitu saja, dan biaya upsert dibandingkan insert biasa dapat diperhitungkan secara eksplisit.

Tiga varian INSERT dan COPY#

Pola penulisan ke hypertable pada dasarnya sama dengan penulisan ke tabel Postgres biasa, sebab hypertable tetap menerima SQL standar. Yang membedakan adalah volume dan laju kedatangan, sehingga pemilihan pola menentukan throughput yang dapat dicapai. Pola pertama, single-row INSERT, mengirim satu perintah per baris, sehingga lima baris berarti lima round-trip klien-server. Pola kedua, multi-row INSERT, mengemas beberapa baris ke dalam satu perintah dengan daftar VALUES, sehingga lima baris cukup satu round-trip. Pola ketiga, COPY, mengirim aliran data tanpa membungkus tiap baris dalam sintaksis SQL, sehingga satu round-trip dapat membawa ribuan baris dengan protokol biner yang ringkas.

Perintah berikut tidak dijalankan di sandbox karena memerlukan Postgres dengan TimescaleDB aktif, sehingga hanya menjadi referensi sintaksis. Ketiga pola menghasilkan data yang sama pada tabel suhu_sensor, tetapi throughput dan biaya parsing SQL-nya berbeda.

postgres Tiga pola penulisan ke hypertable 
-- pola 1: single-row insert, satu perintah per baris
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c)
VALUES ('2026-11-01 00:00:00+00', 'S01', 24.1);
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c)
VALUES ('2026-11-01 00:01:00+00', 'S01', 24.2);

-- pola 2: multi-row insert, beberapa baris per perintah
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c)
VALUES ('2026-11-01 00:00:00+00', 'S01', 24.1),
       ('2026-11-01 00:01:00+00', 'S01', 24.2);

-- pola 3: COPY dari STDIN dengan format CSV
-- diikuti baris data: '2026-11-01 00:00:00+00,S01,24.1'
COPY suhu_sensor (ts, sensor_id, suhu_c)
FROM STDIN WITH (FORMAT csv);
text Keluaran

Membandingkan tiga pola dalam satu keluaran#

Untuk melihat perbedaan ketiga pola secara konkret, skrip Node.js berikut membangkitkan ketiga varian SQL dari deret lima titik yang sama, lalu mencatat jumlah round-trip yang dibutuhkan tiap pola. Skrip tidak menjalankan SQL, melainkan mencetak string SQL yang dihasilkan agar perbedaan format dan panjangnya terlihat. Skrip berjalan pada Node.js 24 yang tersedia di sandbox, sehingga keluarannya asli dan dapat diverifikasi.

javascript bangun_insert.js 
// Membangkitkan tiga varian SQL untuk ingestion telemetri.
//
// Skrip ini TIDAK menjalankan SQL. Tujuannya membandingkan tiga pola
// penulisan: single-row INSERT, multi-row INSERT, dan COPY dari STDIN.
const titik = [
  { ts: "2026-11-01 00:00:00+00", sensor_id: "S01", suhu_c: 24.1 },
  { ts: "2026-11-01 00:01:00+00", sensor_id: "S01", suhu_c: 24.2 },
  { ts: "2026-11-01 00:02:00+00", sensor_id: "S01", suhu_c: 24.3 },
  { ts: "2026-11-01 00:03:00+00", sensor_id: "S01", suhu_c: 24.2 },
  { ts: "2026-11-01 00:04:00+00", sensor_id: "S01", suhu_c: 24.4 },
];

function singleRow() {
  return titik
    .map(t => `INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('${t.ts}', '${t.sensor_id}', ${t.suhu_c});`)
    .join("\n");
}

function multiRow() {
  const values = titik
    .map(t => `('${t.ts}', '${t.sensor_id}', ${t.suhu_c})`)
    .join(", ");
  return `INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES\n  ${values};`;
}

function copyCsv() {
  const rows = titik
    .map(t => `${t.ts},${t.sensor_id},${t.suhu_c}`)
    .join("\n");
  return `COPY suhu_sensor (ts, sensor_id, suhu_c) FROM STDIN WITH (FORMAT csv);\n${rows}\n\\.`;
}

console.log("=== single-row insert (5 perintah terpisah) ===");
console.log(singleRow());
console.log();
console.log("=== multi-row insert (1 perintah, 5 baris VALUES) ===");
console.log(multiRow());
console.log();
console.log("=== COPY dari STDIN (1 perintah, 5 baris CSV) ===");
console.log(copyCsv());
console.log();
console.log(`jumlah baris data: ${titik.length}`);
console.log("single-row: 5 round-trip klien-server");
console.log("multi-row: 1 round-trip klien-server");
console.log("COPY: 1 round-trip, protokol biner streaming");
text Keluaran 
=== single-row insert (5 perintah terpisah) ===
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('2026-11-01 00:00:00+00', 'S01', 24.1);
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('2026-11-01 00:01:00+00', 'S01', 24.2);
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('2026-11-01 00:02:00+00', 'S01', 24.3);
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('2026-11-01 00:03:00+00', 'S01', 24.2);
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES ('2026-11-01 00:04:00+00', 'S01', 24.4);

=== multi-row insert (1 perintah, 5 baris VALUES) ===
INSERT INTO suhu_sensor (ts, sensor_id, suhu_c) VALUES
  ('2026-11-01 00:00:00+00', 'S01', 24.1), ('2026-11-01 00:01:00+00', 'S01', 24.2), ('2026-11-01 00:02:00+00', 'S01', 24.3), ('2026-11-01 00:03:00+00', 'S01', 24.2), ('2026-11-01 00:04:00+00', 'S01', 24.4);

=== COPY dari STDIN (1 perintah, 5 baris CSV) ===
COPY suhu_sensor (ts, sensor_id, suhu_c) FROM STDIN WITH (FORMAT csv);
2026-11-01 00:00:00+00,S01,24.1
2026-11-01 00:01:00+00,S01,24.2
2026-11-01 00:02:00+00,S01,24.3
2026-11-01 00:03:00+00,S01,24.2
2026-11-01 00:04:00+00,S01,24.4
\.

jumlah baris data: 5
single-row: 5 round-trip klien-server
multi-row: 1 round-trip klien-server
COPY: 1 round-trip, protokol biner streaming

Keluaran di atas memperlihatkan dua hal. Pertama, single-row INSERT menghasilkan lima perintah terpisah, sehingga lima round-trip terjadi antara klien dan server. Kedua, multi-row INSERT dan COPY masing-masing hanya membutuhkan satu round-trip, sehingga latensi jaringan tidak dikali jumlah baris. COPY lebih unggul dari multi-row INSERT pada volume besar, sebab protokolnya tidak membungkus tiap baris dengan tanda kurung dan tanda kutip, sehingga ukuran paket lebih kecil dan parsing SQL di sisi server menjadi minimal.

Multi-row INSERT dan ukuran batch#

Ukuran batch pada multi-row INSERT tidak boleh diambil sembarang tanpa batas. Perintah dengan daftar VALUES yang terlalu panjang menyebabkan parser Postgres menanggung biaya besar, dan pada beberapa konfigurasi, ukuran paket dapat melebihi batas max_allowed_packet atau batas memori transaksi. Praktik lazim adalah memilih batch antara 100 hingga 1.000 baris per perintah, lalu membagi data menjadi beberapa perintah berturut-turut. Driver seperti psycopg menyediakan execute_values yang mengotomatiskan pembagian itu, dan JDBC menyediakan rewriteBatchedStatements untuk tujuan yang sama.

Pemilihan batch juga bergantung pada ukuran baris. Bila tiap baris membawa banyak kolom dengan nilai panjang, misalnya JSONB besar, batch sebaiknya diperkecil agar satu perintah tidak melebihi beberapa megabita. Bila tiap baris hanya berisi cap waktu dan satu angka, batch dapat diperbesar hingga ribuan baris. Aturan praktis yang sering digunakan adalah memulai dari 500 baris, mengukur throughput, lalu menaikkan atau menurunkan hingga titik manis ditemukan. Pemilihan tanpa pengukuran sering kali berakhir pada batch yang terlalu besar atau terlalu kecil.

COPY: jalur paling cepat#

COPY adalah jalur penulisan paling cepat yang disediakan Postgres, dan TimescaleDB mewarisinya tanpa modifikasi. Perintah COPY ... FROM STDIN memungkinkan klien mengirim aliran data dalam format teks CSV atau biner, tanpa membungkus tiap baris dalam sintaksis SQL. Karena itu, parsing SQL di sisi server menjadi minimal, dan throughput dapat mencapai jutaan baris per detik pada konfigurasi yang sesuai. Bila data berasal dari berkas CSV, COPY ... FROM '/path/ke/berkas.csv' membaca berkas langsung di sisi server, sehingga klien tidak ikut mentransfer data.

COPY cocok untuk backfill historis dan bulk load awal, sedangkan multi-row INSERT cocok untuk ingestion real-time dengan batch kecil. Kombinasi keduanya sering digunakan pada pipeline produksi, yaitu COPY untuk memuat data awal dan multi-row INSERT untuk ingestion harian. Driver psycopg menyediakan copy_expert dan cursor.copy(), sedangkan JDBC menyediakan CopyManager, sehingga COPY dapat dipanggil dari kode aplikasi biasa.

Upsert untuk data terlambat#

Data terlambat adalah kenyataan pada sistem IoT dan pemantauan, sebab jaringan dapat putus, sensor dapat kehilangan koneksi, atau batch dapat tertahan di agregator. Bila sebuah titik dengan pasangan (sensor_id, ts) yang sudah pernah ditulis datang kembali dengan nilai yang diperbaiki, insert biasa akan menimbulkan constraint violation bila ada unique constraint pada pasangan itu. Klausa ON CONFLICT DO UPDATE pada Postgres menangani kasus ini dengan upsert, yaitu menyisipkan baris baru bila pasangan belum ada, atau memperbarui baris yang sudah ada bila pasangan sudah ada.

Perintah COPY tidak mendukung ON CONFLICT, sehingga upsert harus dilakukan dengan INSERT ... ON CONFLICT DO UPDATE pada tabel target, atau dengan pola staging table: data dimuat ke tabel sementara dengan COPY, lalu disisipkan ke tabel utama dengan INSERT ... ON CONFLICT. Skrip Python berikut tidak menjalankan SQL, tetapi menggambarkan logika upsert dengan kamus Python sebagai padanan tabel, sehingga perilaku ON CONFLICT DO UPDATE terlihat secara konkret.

python simulasi_upsert.py 
"""Simulasi logika INSERT ... ON CONFLICT pada ingestion terlambat.

Skrip ini tidak menjalankan SQL. Tujuannya menggambarkan bagaimana klausa
ON CONFLICT DO UPDATE pada Postgres bekerja untuk data yang datang terlambat,
yakni: bila pasangan (sensor_id, ts) sudah ada, nilai suhu_c diperbarui;
bila belum ada, baris baru disisipkan. Logika ini mensimulasikan apa yang
dilakukan planner Postgres pada hypertable TimescaleDB.
"""
from __future__ import annotations

from dataclasses import dataclass


@dataclass
class Titik:
    ts: str
    sensor_id: str
    suhu_c: float


def main() -> None:
    # tabel hipotetis: pasangan (sensor_id, ts) sebagai kunci unik
    tabel: dict[tuple[str, str], Titik] = {
        ("S01", "2026-11-01 00:00:00+00"): Titik("2026-11-01 00:00:00+00", "S01", 24.1),
        ("S01", "2026-11-01 00:01:00+00"): Titik("2026-11-01 00:01:00+00", "S01", 24.2),
    }
    print("isi tabel sebelum batch upsert:")
    for k, v in tabel.items():
        print(f"  {k} -> suhu_c={v.suhu_c}")
    print()

    # batch yang datang terlambat: 2 baris baru + 1 baris duplikat
    batch = [
        Titik("2026-11-01 00:02:00+00", "S01", 24.3),
        Titik("2026-11-01 00:03:00+00", "S01", 24.2),
        Titik("2026-11-01 00:01:00+00", "S01", 99.9),  # duplikat, koreksi
    ]
    print("batch upsert (3 titik, 1 di antaranya duplikat):")
    for t in batch:
        print(f"  ({t.sensor_id}, {t.ts}) suhu_c={t.suhu_c}")
    print()

    aksi = {"insert": 0, "update": 0}
    for t in batch:
        kunci = (t.sensor_id, t.ts)
        if kunci in tabel:
            tabel[kunci] = t
            aksi["update"] += 1
        else:
            tabel[kunci] = t
            aksi["insert"] += 1

    print("isi tabel setelah batch upsert:")
    for k, v in tabel.items():
        print(f"  {k} -> suhu_c={v.suhu_c}")
    print()
    print(f"ringkasan: {aksi['insert']} baris disisipkan, {aksi['update']} baris diperbarui")
    print("duplikat (S01, 2026-11-01 00:01:00+00) suhu_c berubah 24.2 -> 99.9")


if __name__ == "__main__":
    main()
text Keluaran 
isi tabel sebelum batch upsert:
  ('S01', '2026-11-01 00:00:00+00') -> suhu_c=24.1
  ('S01', '2026-11-01 00:01:00+00') -> suhu_c=24.2

batch upsert (3 titik, 1 di antaranya duplikat):
  (S01, 2026-11-01 00:02:00+00) suhu_c=24.3
  (S01, 2026-11-01 00:03:00+00) suhu_c=24.2
  (S01, 2026-11-01 00:01:00+00) suhu_c=99.9

isi tabel setelah batch upsert:
  ('S01', '2026-11-01 00:00:00+00') -> suhu_c=24.1
  ('S01', '2026-11-01 00:01:00+00') -> suhu_c=99.9
  ('S01', '2026-11-01 00:02:00+00') -> suhu_c=24.3
  ('S01', '2026-11-01 00:03:00+00') -> suhu_c=24.2

ringkasan: 2 baris disisipkan, 1 baris diperbarui
duplikat (S01, 2026-11-01 00:01:00+00) suhu_c berubah 24.2 -> 99.9

Keluaran di atas memperlihatkan dua hal. Pertama, dari tiga titik pada batch, dua disisipkan sebagai baris baru dan satu memperbarui baris yang sudah ada. Kedua, nilai suhu_c pada baris duplikat berubah dari 24,2 menjadi 99,9, yang merupakan koreksi nilai pengukuran. Pada Postgres, perilaku ini diperoleh dengan klausa ON CONFLICT (sensor_id, ts) DO UPDATE SET suhu_c = EXCLUDED.suhu_c, dan klausa itu bekerja pada hypertable tanpa modifikasi.

Biaya upsert vs insert biasa#

Upsert tidak gratis. Untuk setiap baris, Postgres harus memeriksa apakah pasangan kunci sudah ada, dan pemeriksaan ini memerlukan akses ke indeks unique pada kunci itu. Bila batch berisi banyak baris baru, pemeriksaan menjadi overhead tanpa manfaat, sebab sebagian besar baris akhirnya disisipkan, bukan diperbarui. Karena itu, upsert sebaiknya digunakan hanya bila duplikasi memang mungkin terjadi, misalnya pada late arrival yang sudah diketahui polanya, dan bukan pada ingestion normal yang didominasi baris baru.

Pada hypertable, biaya upsert sedikit lebih tinggi daripada pada tabel biasa, sebab setiap baris masih harus diperiksa terhadap indeks unique yang tersebar pada chunk. Bila pasangan kunci sudah ada pada chunk lama yang sudah dikompresi, upsert memicu dekompresi sebagian, yang merupakan operasi mahal. Karena itu, kebijakan kompresi dan kebijakan upsert sebaiknya dirancang bersama, sehingga upsert terjadi pada rentang waktu yang belum dikompresi.

Kesalahan umum#

  • Menggunakan single-row INSERT untuk batch besar. Akibatnya throughput jatuh karena round-trip jaringan dikalikan jumlah baris.
  • Mengaktifkan upsert pada setiap ingestion tanpa memverifikasi apakah duplikasi memang mungkin. Biaya pemeriksaan indeks unique pada baris baru yang dominan menjadi overhead tanpa manfaat.
  • Mencoba menggunakan COPY dengan ON CONFLICT. COPY tidak mendukung upsert, sehingga duplikasi menyebabkan seluruh batch gagal. Pola staging table menjadi solusi untuk upsert skala besar.

Ringkasan#

  • Tiga pola ingestion utama adalah single-row INSERT, multi-row INSERT, dan COPY, dengan jumlah round-trip yang menurun dari pola pertama ke pola ketiga.
  • COPY adalah jalur paling cepat untuk bulk load, sedangkan multi-row INSERT cocok untuk ingestion real-time dengan batch 100 hingga 1.000 baris.
  • Upsert dengan ON CONFLICT DO UPDATE menangani data terlambat, tetapi biayanya tidak gratis, sehingga sebaiknya digunakan hanya bila duplikasi memang mungkin terjadi.

Referensi#

  • COPY — PostgreSQL — Dokumentasi resmi perintah COPY untuk pemuatan data berkecepatan tinggi dari berkas atau STDIN.
  • INSERT — PostgreSQL — Dokumentasi resmi perintah INSERT, termasuk klausa ON CONFLICT yang menjadi dasar mekanisme upsert.
  • Insert data — Timescale — Panduan resmi TimescaleDB untuk menuliskan data ke hypertable, termasuk pola multi-row INSERT dan COPY.