Pola produksi

Semua sejauh ini tentang mendapatkan jawaban yang benar. Materi ini tentang query yang benar di laptopmu dan menyebabkan insiden pada skala besar.

Upsert

“Sisipkan, atau perbarui kalau sudah ada.” Kedua mesin punya dan tidak ada yang menuliskannya sama.

-- PostgreSQL
INSERT INTO product (sku, name, cents)
VALUES ('A-1', 'Widget', 1200)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name,
      cents = EXCLUDED.cents
WHERE product.cents <> EXCLUDED.cents;      -- lewati penulisan yang tidak mengubah apa pun

-- MySQL
INSERT INTO product (sku, name, cents)
VALUES ('A-1', 'Widget', 1200)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),                      -- atau: name = new.name  (8.0.20+)
  cents = VALUES(cents);

Hal yang penting:

• ON CONFLICT menyebut constraint yang sedang diselesaikannya, jadi kamu tahu aturan keunikan mana yang kamu maksud. ON DUPLICATE KEY bereaksi pada sembarang constraint unik, dan itu jadi ambigu begitu sebuah tabel punya dua.
• WHERE pada DO UPDATE PostgreSQL melewati penulisan baris yang tidak berubah. Itu menghemat satu versi baris, satu pembaruan index, dan satu catatan WAL per baris tak berubah — yang pada sinkronisasi besar adalah sebagian besar pekerjaannya.
• INSERT IGNORE di MySQL dan ON CONFLICT DO NOTHING di PostgreSQL tidak setara: INSERT IGNORE menurunkan setiap error jadi warning, termasuk error tipe dan pemotongan, bukan hanya duplikat yang kamu maksud.

Penyemaian konten situs ini persis pola ini, dengan satu tambahan yang pantas dicontek: WHERE origin = 'seed' pada bagian update-nya, sehingga pengimpor hanya bisa mengubah baris yang dia tulis sendiri dan tidak pernah menimpa sesuatu yang disunting orang.

Keyset pagination

OFFSET makin lambat di setiap halaman, karena mesinnya menghasilkan lalu membuang baris yang dilewati. Keyset pagination — disebut juga cursor atau seek pagination — meminta “20 berikutnya setelah titik ini” dan tetap datar.

-- Halaman 1
SELECT id, placed_at FROM "order"
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- Halaman 2: kirimkan kembali baris terakhir halaman 1
SELECT id, placed_at FROM "order"
WHERE (placed_at, id) < (:last_placed_at, :last_id)     -- perbandingan baris
ORDER BY placed_at DESC, id DESC
LIMIT 20;

Perbandingan baris — (a, b) < (x, y) — didukung kedua mesin dan justru alat yang tepat: dia membandingkan secara leksikografis, jadi artinya “timestamp lebih awal, atau timestamp sama dan id lebih kecil”. Menulisnya sendiri sebagai placed_at < :t OR (placed_at = :t AND id < :i) berarti sama dan lebih mudah salah.

Dengan index pada (placed_at DESC, id DESC) ini membaca 20 baris di halaman 1 dan 20 baris di halaman 5000. Pertukarannya nyata dan pantas dinyatakan: kamu kehilangan kemampuan melompat ke nomor halaman sembarang. Untuk feed atau API itu bukan kerugian; untuk tabel dengan tombol nomor halaman, itu keputusan produk.

Menghapus atau memperbarui banyak baris

Satu DELETE atas sepuluh juta baris menahan lock selama seluruh durasinya, menghasilkan satu transaksi raksasa, dan tidak bisa dihentikan tanpa kehilangan seluruh pekerjaannya. Lakukan dalam kelompok, masing-masing transaksinya sendiri.

-- PostgreSQL: mengembalikan id membuat kemajuan loop-nya bisa diamati
WITH doomed AS (
  SELECT id FROM "order"
   WHERE placed_at < '2020-01-01'
   ORDER BY id
   LIMIT 5000
)
DELETE FROM "order" WHERE id IN (SELECT id FROM doomed);

-- MySQL
DELETE FROM `order` WHERE placed_at < '2020-01-01' ORDER BY id LIMIT 5000;

Ulangi sampai nol baris terpengaruh. Buat kelompoknya cukup kecil sehingga setiap transaksi selesai jauh di bawah satu detik, dan beri jeda antar kelompok supaya replikasi bisa menyusul.

Kalau kamu menghapus sebagian besar isi tabel, menyalin yang selamat ke tabel baru lalu menukar namanya biasanya lebih cepat daripada pengelompokan apa pun, dan dia tidak meninggalkan bloat.

Mengubah skema di bawah lalu lintas hidup

Aturannya: jangan pernah membuat satu perubahan yang sekaligus menulis data dan menghalangi pembaca. Belah dia jadi langkah-langkah yang masing-masing aman untuk dihentikan setelahnya. Ini sering disebut expand / migrate / contract.

Menambahkan kolom NOT NULL dengan nilai bawaan, kalau dilakukan salah, menulis ulang seluruh tabel sambil menahan lock eksklusif. Kalau dilakukan sebagai urutan:

1. Expand — tambahkan kolomnya, boleh NULL, tanpa nilai bawaan. Cepat di kedua mesin.
2. Backfill — isi nilainya dalam kelompok, seperti di atas.
3. Constrain — tambahkan NOT NULL dan nilai bawaannya.
4. Contract — begitu tidak ada kode yang membacanya, hapus kolom lamanya.

Di antara setiap langkah aplikasinya tetap jalan, dengan kode lama dan baru sama-sama bisa berjalan. Itulah yang membuat deploy-nya tidak bergantung pada migrasinya.

Kekhususan mesin:

CREATE INDEX CONCURRENTLY tidak bisa berjalan di dalam transaksi, dan kalau gagal dia meninggalkan index tidak valid yang harus kamu hapus sendiri. Periksa pg_index.indisvalid setelah setiap pembangunan concurrent yang error.

Menanyai JSON

-- PostgreSQL, jsonb
SELECT payload->>'status'                      AS status,   -- ->> menghasilkan teks
       payload->'items'->0->>'sku'             AS first_sku
FROM event
WHERE payload @> '{"status":"paid"}';                        -- containment, bisa di-index GIN

CREATE INDEX ix_event_payload ON event USING gin (payload);

-- MySQL
SELECT payload->>'$.status'                    AS status,
       payload->>'$.items[0].sku'              AS first_sku
FROM event
WHERE payload->>'$.status' = 'paid';

-- Hanya bisa di-index melalui generated column
ALTER TABLE event
  ADD COLUMN status VARCHAR(32) AS (payload->>'$.status') STORED,
  ADD INDEX ix_event_status (status);

@> PostgreSQL dengan index GIN mencari di dalam dokumennya. MySQL tidak punya padanan index seluruh dokumen, jadi setiap field yang kamu saring butuh generated column-nya sendiri — dan itu petunjuk bagus untuk bertanya apakah dia seharusnya sejak awal berupa kolom.

Query N+1

Bukan fitur SQL, tapi bug kinerja paling umum di aplikasi yang memakainya. Mengambil sebuah daftar lalu satu query per baris mengubah 1 permintaan jadi 201.

-- Yang dilakukan ORM tanpa sengaja
SELECT * FROM "order" WHERE customer_id = 7;      -- 200 baris
SELECT * FROM order_item WHERE order_id = 1;      -- lalu 200 query lagi
...

-- Yang seharusnya dia lakukan
SELECT * FROM order_item WHERE order_id = ANY(:ids);   -- PostgreSQL
SELECT * FROM order_item WHERE order_id IN (...);      -- MySQL

Setiap ORM punya perbaikannya — eager loading, JOIN FETCH, includes, select_related. Keahliannya ada pada menyadarinya: catat jumlah query per permintaan, dan pasang peringatan padanya. Jumlah yang membesar seiring ukuran himpunan hasil adalah tanda tangannya.

Yang perlu dibawa pulang

• ON CONFLICT di PostgreSQL, ON DUPLICATE KEY UPDATE di MySQL. Hindari INSERT IGNORE — dia menyembunyikan error yang tidak berkaitan.
• Keyset pagination dengan perbandingan baris tetap datar; OFFSET tidak.
• Kelompokkan penghapusan dan pembaruan besar, satu transaksi masing-masing.
• Expand, backfill, constrain, contract — setiap langkah aman untuk dihentikan setelahnya.
• CREATE INDEX CONCURRENTLY bisa meninggalkan index tidak valid; periksa setelah gagal.
• JSON bisa ditanyai di keduanya, tapi hanya PostgreSQL yang meng-index seluruh dokumen.
• Pantau jumlah query per permintaan, bukan hanya durasi tiap query.