เมื่อ Bulk UPDATE แพงกว่า Read–Transform

WHEN THE COST FLIPS

กรณีที่ rebuild เริ่มได้เปรียบ

ไม่มี threshold สากล ต้อง benchmark workload และ engine จริง แต่ pattern เหล่านี้ทำให้ mutation แพงขึ้น

60–100% rows

เปลี่ยนแถวจำนวนมาก

UPDATE สูญเสียข้อดีจากการแตะข้อมูลน้อย แต่ยังแบกต้นทุน version และ log ต่อแถว

secondary indexes

มี Index หลายชุด

ค่าที่เปลี่ยนทำให้ต้องลบและเพิ่ม index entry หลายชุด

PostgreSQL · InnoDB

MVCC และ Dead Rows

snapshot เก่าต้องอ่าน row version เดิม และระบบต้อง cleanup ภายหลัง

primary + replicas

WAL, Redo และ Replication

ทุก mutation ต้อง recover และส่งต่อ replica

parts · compression

Columnar Storage

การแก้ row กระจัดกระจายอาจกลายเป็นการ rewrite compressed part

partition · sort · encoding

เปลี่ยน Physical Layout

สร้างตารางใหม่ทำ transformation และจัด layout ให้จบในรอบเดียว

poor locality

Random I/O

UPDATE กระโดดหลาย page แต่ CTAS มักอ่านและเขียนต่อเนื่อง

vacuum · purge · merge

Table Bloat

ต้นทุนต่อเนื่องไปยัง scan, cache, backup และ cleanup

join · cast · normalize

Transform ครั้งใหญ่

Set-oriented pipeline สร้างปลายทางโดยตรงได้

PHYSICAL WORK

SQL หนึ่งบรรทัด อาจหมายถึงงานทางกายภาพคนละโลก

เทียบ bytes, logs, index work, locks, cleanup และ replica impact ไม่ใช่เวลาอย่างเดียว

Bulk UPDATE

Locate qualifying rows
Create/rewrite row versions
Write undo/redo or WAL
Maintain indexes
Replicate mutations
Vacuum, purge or merge later

changed_rows × (row_write + log + index_work + cleanup)

CTAS / Rebuild

Sequential source scan
Set-oriented transformation
Compact destination write
Bulk index build
Validate constraints and counts
Swap table or partition

all_rows × (scan + transform + sequential_write) + cutover

INTERACTIVE COST MODEL

สำรวจจุดที่ต้นทุนโดยประมาณเริ่มพลิก

ใช้สร้าง intuition ไม่ใช่แทน benchmark จริง

Rows Rows changed Secondary indexes Average row width

—UPDATE proxy

—Rebuild proxy

—UPDATE / rebuild

—ยืนยันด้วย benchmark จริง

LIVE MARIADB LAB · MEASURE ONE BOUNDARY AT A TIME

แยกเวลา Copy ออกจาก UPDATE แล้วเทียบเฉพาะงาน Transform

ตารางต้นทางถูกสร้างเพียงเพื่อจัดฉากการทดลอง เวลานี้รายงานแยกและไม่รวมในคำตัดสินหลัก

SETUP · ไม่นำมาตัดสิน

สร้าง source table และ index ตั้งต้น แล้วตรวจ checksum ก่อนเริ่มทั้งสองเส้นทาง

PATH A · UPDATE ONLY

คัดลอก source เป็น working copy ก่อน แล้วเริ่มจับเวลาเฉพาะคำสั่ง UPDATE

PATH B · TRANSFORM → NEW

เริ่มจับเวลาตั้งแต่ scan source, transform ด้วย CASE และเขียน new table

Rowsแถวที่เปลี่ยนSecondary indexes

CONNECTING TO MARIADB…
เลือกขนาดแล้วทดลองซ้ำหลายรอบ ค่าแรกอาจรวมผลของ cold cache และการจัดสรร page

PATH A · COPY PREPARED, THEN UPDATE

UPDATE copy_table
SET amount = amount * 2 + 7,
    segment = MOD(segment + 1, 12)
WHERE MOD(id, 100) < :changed_percent;

PATH B · TRANSFORM → NEW TABLE

CREATE TEMPORARY TABLE new_table AS
SELECT id,
       CASE WHEN changed THEN amount * 2 + 7 ELSE amount END amount,
       CASE WHEN changed THEN MOD(segment + 1, 12) ELSE segment END segment
FROM source_table;

PATH A · MUTATION

UPDATE existing copy

—เวลา UPDATE เท่านั้น

เตรียม working copy: —
สร้าง index บน copy: —
รวมทั้งเส้นทาง: —

PATH B · IMMUTABLE TRANSFORM

Transform → new table

—scan + transform + write table

สร้าง index บนตารางใหม่: —
รวมทั้งเส้นทาง: —
ผลลัพธ์เท่ากัน: —

การเปรียบเทียบหลัก—

UPDATE only ÷ transform → new table เวลาคัดลอกและสร้าง index แสดงไว้เพื่อความโปร่งใส แต่ไม่ปนใน ratio นี้

SETUP—

ยังไม่ได้ทดลอง

อ่านผลอย่างไรโดยไม่สรุปเกินข้อมูล

1 · ถ้า UPDATE only ช้ากว่า
mutation work ต่อแถว—รวม index maintenance และ logging—แพงกว่า scan-transform-write ใน workload นี้ แม้ไม่นับเวลา copy

2 · ถ้า UPDATE ยังเร็วกว่า
เพิ่ม changed fraction หรือ index แล้วทดลองใหม่ จุดพลิกขึ้นกับ engine, cache, row width และ storage

3 · อย่าใช้ elapsed time เพียงครั้งเดียว
ทดลองซ้ำ ดู median และตรวจ bytes/log/locks ในระบบจริง แล็ปนี้สอนขอบเขตการวัด ไม่ได้ประกาศผู้ชนะสากล

ENGINE-AWARE SQL

แนวคิดร่วมกัน แต่ syntax และ cutover ต่างกัน

ตรวจ transaction, locks, replication และ DDL behavior ของ engine เสมอ

CREATE TABLE customer_new AS SELECT id, normalize_email(email) email FROM customer;
CREATE INDEX ON customer_new(id); ANALYZE customer_new;
-- validate, catch up concurrent changes, then plan transactional cutover

CREATE TABLE customer_new LIKE customer;
INSERT INTO customer_new SELECT id, LOWER(TRIM(email)) FROM customer;
RENAME TABLE customer TO customer_old, customer_new TO customer;

CREATE TABLE events_new AS events;
INSERT INTO events_new SELECT * REPLACE (transform(x) AS x) FROM events;
-- validate then exchange tables, or replace only affected partitions

CREATE TABLE facts_new AS SELECT * REPLACE (standardize(amount) AS amount) FROM facts;
-- validate and swap names in a controlled transaction

DECISION GUIDE

เมื่อไรควร UPDATE เมื่อไรควรสร้างใหม่

เลือกวิธีที่รักษาความถูกต้องและ recovery path ได้ ไม่ใช่เพียงวิธีที่ดูเร็ว

Situation	Likely choice	Reason
<5–10% rows	UPDATE	Selective work can beat a complete rewrite.
Most rows + many indexes	Benchmark rebuild	Logging and index amplification may dominate.
Partition/sort/encoding changes	Rebuild/partition replace	The physical layout itself changes.
Continuous writes	Online migration	CTAS alone misses post-snapshot changes.
Low free disk	Chunked UPDATE	Rebuild temporarily needs two copies plus indexes.
Strict rollback	Build–validate–swap	The old table remains a rollback target.

สำคัญที่สุด

ถ้ามี write ต่อเนื่อง ต้องกำหนด snapshot boundary, CDC catch-up, validation, cutover lock และ rollback มิฉะนั้นตารางใหม่อาจเร็วแต่ข้อมูลหาย

SAFE REBUILD

ความเร็วมีความหมายเมื่อสลับระบบได้ปลอดภัย

Checklist นี้ต้องปรับตาม engine และ SLA

01Snapshot boundary

02Build + transform

03Catch up changes

04Validate + cutover

05Observe + rollback