Prestasi PostgreSQL & Pengoptimuman Pertanyaan · Pelajaran

Upsert pada Skala Besar dengan ON CONFLICT

Laksanakan logik gabungan yang cekap untuk kelompok besar sambil mengelakkan perebutan kunci dan penggembungan.

Pelajaran 4 daripada 413 langkah

Upsert pada Skala Besar dengan ON CONFLICT ialah pelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan percuma di CoddyKit. Ini ialah pelajaran 4 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 Upsert Perlu Dikendalikan dengan Cermat pada Skala Besar

Operasi kemas kini atau sisipan akan menyisipkan satu baris, tetapi jika tindakan itu bertembung dengan kunci sedia ada, baris sedia ada akan dikemas kini. PostgreSQL menulisnya sebagai INSERT ... ON CONFLICT.

Untuk beban kerja kecil, perkara ini mudah. Namun, bagi kelompok bersaiz ETL (daripada puluhan ribu hingga jutaan baris), pendekatan naif menimbulkan tiga masalah:

Perebutan kunci — penulis serentak berebut baris atau halaman indeks yang sama.
Penggelembungan jadual — setiap UPDATE menulis versi baris baharu (tuple mati) yang kemudiannya perlu dituntut semula oleh VACUUM.
Overhed WAL dan perjalanan pergi balik — operasi kemas kini atau sisipan baris demi baris menggandakan kos rangkaian dan transaksi.

Dalam pelajaran ini, anda akan membina operasi cantum yang betul dan cekap dari segi kadar pemprosesan.

Bentuk ON CONFLICT

ON CONFLICT memerlukan sasaran konflik: lajur atau kekangan yang menentukan pendua. PostgreSQL memerlukan kekangan unik atau pengecualian pada sasaran itu untuk membuat keputusan.

Dua tindakannya ialah DO NOTHING (melangkau baris yang bertembung) dan DO UPDATE (menggabungkan nilai baharu).

Di dalam DO UPDATE, baris yang masuk didedahkan melalui jadual pseudo khas EXCLUDED.

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Satu Pernyataan, Banyak Baris

Peraturan kadar pemprosesan yang paling penting: kelompokkan baris dalam satu pernyataan. Senarai VALUES berbilang baris (atau SELECT yang membekalkan data) dihuraikan, dirancang dan dilakukan sekali, bukannya N kali.

Ini menggabungkan N perjalanan pergi balik rangkaian dan N pengesahan transaksi menjadi satu, yang sering memberikan peningkatan kelajuan 10 hingga 100 kali ganda berbanding operasi kemas kini atau sisipan baris demi baris.

INSERT INTO products (sku, name, price)
VALUES
  ('A-100', 'Widget', 9.99),
  ('A-101', 'Gadget', 14.50),
  ('A-102', 'Gizmo', 7.25)
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Jadual Pementasan + INSERT...SELECT

Untuk ETL sebenar, muatkan data mentah ke dalam jadual pementasan tanpa log terlebih dahulu (biasanya melalui COPY, laluan pukal terpantas), kemudian cantumkan data daripada pementasan ke sasaran dengan satu INSERT ... SELECT ... ON CONFLICT.

Faedah:

COPY mengelakkan overhed INSERT bagi setiap baris.
Jadual pementasan UNLOGGED melangkau WAL semasa fasa pemuatan.
Anda boleh membuang pendua dan mengubah data dalam SELECT sebelum mencantumkannya.

CREATE UNLOGGED TABLE products_stage (LIKE products);

-- bulk load: COPY products_stage FROM '/data/products.csv' CSV;

INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM products_stage
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Buang Pendua Kelompok Terlebih Dahulu

Ralat halus tetapi membawa padah: satu pernyataan INSERT tidak boleh mengemas kini baris sasaran yang sama dua kali. Jika kelompok anda mengandungi dua baris dengan kunci konflik yang sama, PostgreSQL akan menghasilkan:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Baikinya dengan menggabungkan pendua dalam sumber sebelum mencantumkannya. DISTINCT ON mengekalkan satu baris bagi setiap kunci — biasanya baris yang paling baharu.

INSERT INTO products (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM products_stage
ORDER BY sku, updated_at DESC
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Langkau Kemas Kini Tanpa Perubahan untuk Mengurangkan Penggelembungan

Setiap DO UPDATE menulis versi baris baharu, walaupun nilai baharu sama dengan nilai lama. Tuple mati ini menggelembungkan jadual dan menambah kerja untuk VACUUM.

Tambahkan klausa WHERE pada DO UPDATE supaya ia hanya dijalankan apabila sesuatu benar-benar berubah. Gunakan IS DISTINCT FROM supaya perbandingan NULL dilakukan dengan betul.

INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM products_stage
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price
WHERE products.name  IS DISTINCT FROM EXCLUDED.name
   OR products.price IS DISTINCT FROM EXCLUDED.price;

Susun Kelompok untuk Menangani Perebutan Kunci

Apabila beberapa pekerja ETL berjalan serentak, kebuntuan berlaku jika mereka menyentuh kunci yang sama dalam susunan berbeza. Pekerja 1 mengunci kunci X kemudian Y; pekerja 2 mengunci Y kemudian X — kedua-duanya tersekat sehingga PostgreSQL menghentikan salah satu.

Langkah perlindungan:

Isih setiap kelompok mengikut kunci konflik supaya semua pekerja memperoleh kunci dalam susunan yang sama.
Bahagikan kerja mengikut julat kunci supaya tiada dua pekerja berkongsi kunci.
Pastikan transaksi singkat — kunci baris yang dipegang lama memburukkan perebutan.

INSERT INTO products (sku, name, price)
SELECT sku, name, price
FROM products_stage
ORDER BY sku            -- consistent lock acquisition order
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Pecahkan Cantuman Gergasi kepada Kelompok

Satu transaksi amat besar yang mencantumkan jutaan baris memegang kunci untuk tempoh yang lama, membesarkan WAL dan menghalang autovacuum daripada melakukan pembersihan. Pecahkan kerja kepada kelompok (contohnya 10k–50k baris) dan lakukan pengesahan antara kelompok.

Transaksi yang lebih kecil melepaskan kunci lebih awal, membolehkan autovacuum mengejar kerja pembersihan dan menjadikan percubaan semula selepas kegagalan lebih murah. Pertukarannya ialah sedikit pertambahan overhed pengesahan — laraskan saiz kelompok mengikut perkakasan anda.

-- Merge one bounded slice; loop over key ranges from the app side.
INSERT INTO products (sku, name, price)
SELECT sku, name, price
FROM products_stage
WHERE sku >= 'A-0000' AND sku < 'A-5000'
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Memilih Sasaran Konflik yang Tepat

Sasaran konflik mesti sepadan dengan kekangan unik/kunci utama atau pengecualian yang sebenar. Anda boleh menyasarkan melalui senarai lajur ON CONFLICT (sku) atau melalui nama kekangan ON CONSTRAINT products_sku_key.

Untuk indeks unik separa, ulangi predikat indeks supaya PostgreSQL boleh memilih indeks yang betul:

-- Unique only among active rows
CREATE UNIQUE INDEX products_active_sku
  ON products (sku) WHERE is_active;

INSERT INTO products (sku, name, is_active)
VALUES ('A-100', 'Widget', true)
ON CONFLICT (sku) WHERE is_active DO UPDATE
  SET name = EXCLUDED.name;

ON CONFLICT berbanding MERGE

PostgreSQL 15+ menambah MERGE yang mematuhi piawaian SQL dan boleh melakukan INSERT, UPDATE serta DELETE dalam satu laluan. Untuk operasi kemas kini atau sisipan dengan kadar pemprosesan tinggi, INSERT ... ON CONFLICT biasanya masih lebih digemari:

ON CONFLICT adalah atomik terhadap sisipan serentak — ia menangani perlumbaan apabila transaksi lain menyisipkan kunci yang sama, dengan mencuba semula secara dalaman.
MERGE klasik boleh menghasilkan pelanggaran unik di bawah keserentakan tinggi kerana ia tidak mempunyai pengantaraan konflik terbina dalam itu.

Gunakan MERGE apabila anda memerlukan cabang DELETE atau logik bersyarat yang kompleks; gunakan ON CONFLICT untuk operasi kemas kini atau sisipan biasa yang selamat terhadap keserentakan.

Penyelenggaraan: VACUUM dan Indeks

Walaupun cantuman dioptimumkan, tuple mati tetap terhasil pada baris yang dikemas kini. Kekalkan prestasi dengan disiplin penyelenggaraan:

Pastikan vakum automatik dapat mengikut kadar perubahan; untuk jadual ETL yang kerap berubah, rendahkan autovacuum_vacuum_scale_factor supaya ia dicetuskan dengan lebih kerap.
Selepas pengisian semula besar-besaran yang dilakukan sekali, jalankan VACUUM (ANALYZE) untuk menuntut semula ruang dan menyegarkan statistik perancang.
Setiap indeks tambahan pada sasaran memperlahankan cantuman (setiap sisipan/kemas kini menyelenggara semuanya) — simpan hanya indeks yang benar-benar diperlukan.

VACUUM (ANALYZE) products;

Semakan Ringkas

Uji pemahaman anda tentang cantuman yang selamat dan mempunyai kadar pemprosesan tinggi.

Jawab dalam pelajaran interaktif

Ringkasan

Operasi kemas kini atau sisipan yang cekap pada skala besar terhasil daripada beberapa amalan yang digabungkan:

Kelompokkan baris dalam satu pernyataan; untuk ETL, buat pementasan dengan COPY kemudian gunakan INSERT ... SELECT ... ON CONFLICT.
Buang pendua daripada kelompok (DISTINCT ON) supaya tiada kunci muncul dua kali.
Langkau kemas kini tanpa perubahan dengan klausa WHERE ... IS DISTINCT FROM untuk mengurangkan tuple mati dan penggelembungan.
Susun mengikut kunci konflik dan bahagikan kerja untuk mengelakkan kebuntuan; pecahkan cantuman besar kepada transaksi singkat.
Utamakan ON CONFLICT untuk operasi kemas kini atau sisipan yang selamat terhadap keserentakan; pastikan vakum automatik sihat dan indeks tidak berlebihan.

Secara bersama, amalan ini menukar cantuman rapuh baris demi baris menjadi pemuatan ETL yang pantas dan kurang perebutan.

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 “Upsert pada Skala Besar dengan ON CONFLICT” percuma?

Ya — sebanyak 3 pelajaran dalam laluan pembelajaran Prestasi PostgreSQL & Pengoptimuman Pertanyaan, termasuk “Upsert pada Skala Besar dengan ON CONFLICT”, 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 “Upsert pada Skala Besar dengan ON CONFLICT”?

Laksanakan logik gabungan yang cekap untuk kelompok besar sambil mengelakkan perebutan kunci dan penggembungan. 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 4 daripada 4.

Berapa lamakah pelajaran “Upsert pada Skala Besar dengan ON CONFLICT” 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