Prestasi PostgreSQL & Pengoptimuman Pertanyaan · Pelajaran

Throughput COPY berbanding INSERT Berbilang Baris

Tanda aras dan pilih kaedah pengingesan yang memaksimumkan baris sesaat dalam kekangan sebenar.

Pelajaran 1 daripada 413 langkah

Throughput COPY berbanding INSERT Berbilang Baris ialah pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Sebanyak 3 pelajaran dalam laluan pembelajaran ini boleh dibaca sepenuhnya secara percuma — selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan praktikal dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Prestasi PostgreSQL & Pengoptimuman Pertanyaan merangkumi sejumlah 4 pelajaran.

Mengapa Kelajuan Penyerapan Penting

Apabila anda memuatkan berjuta-juta baris ke dalam PostgreSQL, kaedah yang dipilih menentukan sama ada kerja itu mengambil masa saat atau jam. Pelajaran ini menanda aras dua laluan penyerapan utama: COPY dan INSERT berbilang baris.

COPY menstrim baris melalui satu laluan pukal yang dioptimumkan.
INSERT berbilang baris mengumpulkan banyak tupel dalam satu pernyataan untuk mengurangkan kos perjalanan pergi balik.

Pilihan yang tepat bergantung pada sumber data, kos perjalanan pergi balik dan cara baris tiba.

Garis Dasar Naif: INSERT Satu Baris

Pola paling perlahan ialah satu baris bagi setiap pernyataan. Setiap pernyataan menanggung kos penghuraian, perancangan, perjalanan pergi balik rangkaian dan (tanpa pengumpulan) pengesahan berasingan.

Apabila terdapat ribuan baris, overhed setiap pernyataan menjadi faktor utama dan daya pemprosesan merosot. Inilah garis dasar yang ditewaskan oleh setiap kaedah lain.

-- Slow: one round-trip and (by default) one commit per row
INSERT INTO events (user_id, kind, payload) VALUES (1, 'click', '{}');
INSERT INTO events (user_id, kind, payload) VALUES (2, 'view',  '{}');
INSERT INTO events (user_id, kind, payload) VALUES (3, 'click', '{}');
-- ... repeated 1,000,000 times

INSERT Berbilang Baris: Mengurangkan Perjalanan Pergi Balik

INSERT berbilang baris menyenaraikan banyak tupel dalam satu pernyataan. Kos penghuraian dan perancangan dibayar sekali, hanya satu perjalanan pergi balik rangkaian dibuat dan seluruh kelompok disahkan bersama.

Saiz kelompok yang baik biasanya 500 hingga 5,000 baris bagi setiap pernyataan.
Nilai yang jauh lebih tinggi memberikan pulangan yang semakin berkurang dan membesarkan pernyataan yang dihuraikan.

-- One statement, one round-trip, many rows
INSERT INTO events (user_id, kind, payload) VALUES
  (1, 'click', '{}'),
  (2, 'view',  '{}'),
  (3, 'click', '{}'),
  (4, 'view',  '{}');
-- typically 500-5000 tuples per statement

COPY: Laluan Pukal

COPY ialah pemuat pukal yang dibina khusus untuk PostgreSQL. Ia memintas penghuraian pernyataan bagi setiap baris sepenuhnya dan menstrim baris melalui gelung yang cekap, menjadikannya biasanya cara paling pantas untuk menyerap jumlah data yang besar.

COPY ... FROM memuatkan data ke dalam jadual.
Ia membaca format teks, CSV atau binari.

COPY FROM 'file' pada bahagian pelayan memerlukan pengguna super atau peranan pg_read_server_files; klien biasanya menggunakan \copy sebagai gantinya.

-- Server-side COPY from a CSV file (needs file access privileges)
COPY events (user_id, kind, payload)
FROM '/data/events.csv'
WITH (FORMAT csv, HEADER true);

\copy pada Bahagian Klien dan COPY FROM STDIN

Apabila file berada pada klien (bukan pelayan), gunakan perintah meta \copy psql atau COPY ... FROM STDIN. Kedua-duanya menstrim data melalui sambungan klien sedia ada, jadi keistimewaan khas untuk file pelayan tidak diperlukan.

Kebanyakan pemacu ETL (psycopg, JDBC, libpq) menyediakan API COPY FROM STDIN penstriman, iaitu laluan pemuatan terprogram yang paling pantas.

-- psql meta-command: file is read on the CLIENT machine
\copy events (user_id, kind, payload) FROM 'events.csv' WITH (FORMAT csv, HEADER true)

-- Equivalent SQL that streams from the client connection
COPY events (user_id, kind, payload) FROM STDIN WITH (FORMAT csv);

Mengapa COPY Menang: Kurang Kerja bagi Setiap Baris

Jurang daya pemprosesan berpunca daripada kos setiap baris:

INSERT satu baris: huraikan + rancang + laksanakan + perjalanan pergi balik + sahkan bagi setiap baris.
INSERT berbilang baris: huraikan + rancang sekali bagi setiap kelompok; masih membina pepohon penghuraian penuh bagi setiap tupel.
COPY: tiada penghuraian SQL bagi setiap baris; nilai dinyahkod terus menjadi tupel.

COPY juga menjana rekod WAL yang lebih sedikit bagi setiap unit kerja baris, yang menjadi sebahagian besar kelebihan kelajuannya.

Menanda Aras dengan Adil

Untuk membandingkan kaedah secara jujur, kekalkan semua perkara lain sama dan ukur masa sebenar serta bilangan baris sesaat. Gunakan \timing dalam psql atau balut pemuatan dalam alat ujian bermasa.

Muatkan set data yang sama bagi setiap larian.
Gunakan TRUNCATE antara larian supaya anda bermula daripada keadaan kosong yang boleh dibandingkan.
Jalankan setiap kaedah beberapa kali dan ambil median untuk mengurangkan kesan hingar.

\timing on

TRUNCATE events;
-- run method A (multi-row INSERT batches), note the time

TRUNCATE events;
-- run method B (COPY FROM), note the time

-- rows_per_second = row_count / elapsed_seconds

Urus Niaga dan Kos Pengesahan

Satu sebab lazim INSERT satu baris perlahan ialah satu pengesahan bagi setiap baris. Setiap pengesahan memaksa pembilasan WAL (iaitu fsync) ke cakera. Membungkus banyak sisipan dalam satu urus niaga mengurangkan ribuan fsync menjadi satu.

COPY dan INSERT berbilang baris kedua-duanya sudah melakukan pengesahan bagi setiap pernyataan, tetapi jika anda menjalankan banyak pernyataan melalui skrip, bungkus semuanya dalam satu urus niaga yang dinyatakan secara jelas.

BEGIN;
INSERT INTO events (user_id, kind) VALUES (1, 'click');
INSERT INTO events (user_id, kind) VALUES (2, 'view');
-- ... many statements, ONE fsync at the end
COMMIT;

Indeks, Pencetus dan Kekangan Memperlahankan Pemuatan

Kaedah penyerapan yang paling pantas pun akan merangkak jika setiap baris yang disisipkan perlu mengemas kini lima indeks dan mengaktifkan pencetus. Taktik pemuatan pukal klasik ialah memuatkan data dahulu, kemudian membina indeks.

Buang atau nyahdayakan indeks yang tidak penting, kemudian cipta semula selepas pemuatan.
Membina indeks sekali untuk seluruh jadual jauh lebih murah daripada menyelenggarakannya baris demi baris.
Nyahdayakan pencetus yang mahal semasa pemuatan apabila data dipercayai.

-- Bulk-load pattern: strip overhead, load, then rebuild
DROP INDEX IF EXISTS idx_events_user_id;

COPY events (user_id, kind, payload)
FROM '/data/events.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_events_user_id ON events (user_id);

Jadual UNLOGGED dan Pementasan

Untuk data pementasan sementara, jadual UNLOGGED melangkau penulisan WAL sepenuhnya, yang boleh mempercepatkan pemuatan dengan ketara. Pertukarannya: jadual tanpa log tidak selamat daripada kerosakan dan akan dipotong selepas kerosakan.

Pola ETL yang kukuh memuatkan data ke dalam jadual pementasan tanpa log atau sementara menggunakan COPY, mengubahnya, kemudian menyisipkan hasil yang bersih ke dalam sasaran yang tahan lama.

-- Fast, non-durable staging area for ETL
CREATE UNLOGGED TABLE events_staging (
  user_id integer,
  kind    text,
  payload jsonb
);

COPY events_staging FROM '/data/events.csv' WITH (FORMAT csv, HEADER true);

INSERT INTO events SELECT * FROM events_staging WHERE kind IS NOT NULL;

Memilih Mengikut Kekangan Sebenar

Pilih kaedah yang sepadan dengan cara data tiba:

Adakah file atau strim pukal tersedia? Gunakan COPY / \copy — kaedah ini unggul dari segi daya pemprosesan.
Adakah baris tiba secara terprogram dalam kod? Utamakan COPY FROM STDIN pemacu anda; jika tidak tersedia, gunakan INSERT berbilang baris secara kelompok 500-5,000 sebagai pilihan sandaran.
Perlukan pengendalian konflik bagi setiap baris (ON CONFLICT)? COPY tidak dapat melakukannya — gunakan INSERT berbilang baris atau COPY ke jadual pementasan kemudian UPSERT.

-- COPY has no ON CONFLICT; stage then upsert when you need it
COPY events_staging FROM STDIN WITH (FORMAT csv);

INSERT INTO events AS e (user_id, kind, payload)
SELECT user_id, kind, payload FROM events_staging
ON CONFLICT (user_id, kind) DO UPDATE
  SET payload = EXCLUDED.payload;

Semakan Ringkas: Maksimumkan Daya Pemprosesan

Uji pemahaman anda tentang keputusan teras penyerapan.

Jawab dalam pelajaran interaktif

Rumusan

Kini anda boleh menanda aras dan memilih kaedah penyerapan dengan sengaja:

COPY / \copy unggul dari segi daya pemprosesan untuk pemuatan file pukal dan strim; kaedah ini melangkau penghuraian bagi setiap baris dan meminimumkan WAL.
INSERT berbilang baris (500-5,000 baris bagi setiap pernyataan) mengatasi sisipan satu baris dan menjadi pilihan sandaran apabila anda memerlukan ON CONFLICT.
Bungkus pemuatan dalam satu urus niaga untuk mengelakkan kos fsync bagi setiap baris.
Buang indeks, nyahdayakan pencetus dan gunakan jadual pementasan UNLOGGED untuk menghapuskan overhed bagi setiap baris, kemudian bina semula.
Tanda aras dengan adil: data yang sama, TRUNCATE antara larian, \timing, dan ambil median baris/saat.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 22
Pelajaran: 88

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Throughput COPY berbanding INSERT Berbilang Baris” percuma?

Ya — sebanyak 3 pelajaran dalam laluan pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan, termasuk “Throughput COPY berbanding INSERT Berbilang Baris”, boleh dibaca sepenuhnya secara percuma di web ini. Selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan interaktif dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Kursus Prestasi PostgreSQL & Pengoptimuman Pertanyaan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Throughput COPY berbanding INSERT Berbilang Baris”?

Tanda aras dan pilih kaedah pengingesan yang memaksimumkan baris sesaat dalam kekangan sebenar. Anda berlatih Prestasi PostgreSQL & Pengoptimuman Pertanyaan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Prestasi PostgreSQL & Pengoptimuman Pertanyaan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Throughput COPY berbanding INSERT Berbilang Baris” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan ini?

Ya. Setiap pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Throughput COPY berbanding INSERT Berbilang Baris
Menangguhkan Indeks dan Kekangan Semasa Pemuatan
Menala WAL dan Titik Semak untuk Pengingesan
Upsert pada Skala Besar dengan ON CONFLICT

← Kembali ke Prestasi PostgreSQL & Pengoptimuman Pertanyaan