การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง
กำหนด fillfactor เพื่อเว้นพื้นที่สำหรับการปรับปรุงแบบ HOT และลดการเขียนดัชนีซ้ำในแถวที่แก้ไขบ่อย
การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง เป็นบทเรียน PostgreSQL Performance & Query Optimization ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน PostgreSQL Performance & Query Optimization และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส PostgreSQL Performance & Query Optimization มีบทเรียนทั้งหมด 4 บทเรียน
เหตุใดการอัปเดตจึงมีต้นทุนสูงใน PostgreSQL
PostgreSQL ใช้ MVCC: UPDATE จะไม่เขียนทับแถวเดิมในตำแหน่งเดิม แต่จะเขียนเวอร์ชันแถวใหม่เอี่ยม (ทูเพิล) และทำเครื่องหมายเวอร์ชันเก่าว่าตายแล้ว
- ทูเพิลใหม่ต้องถูกเขียนลง ที่ใดที่หนึ่ง บนดิสก์
- หากทูเพิลใหม่อยู่บน เพจคนละหน้า กับเวอร์ชันเก่า ดัชนีทุกตัวในตารางต้องได้รับการอัปเดตให้ชี้ไปยังตำแหน่งใหม่
ในตารางที่มีการอัปเดตจำนวนมาก การเปลี่ยนแปลงดัชนีเหล่านี้จะกลายเป็นสาเหตุสำคัญของการเขียนขยายและข้อมูลส่วนเกิน บทเรียนวันนี้คือวิธีที่ fillfactor ช่วยหลีกเลี่ยงปัญหานี้
fillfactor ควบคุมอะไรจริง ๆ
fillfactor คือพารามิเตอร์พื้นที่จัดเก็บระดับต่อตาราง (และต่อดัชนี) ซึ่งแสดงเป็นเปอร์เซ็นต์ตั้งแต่ 10 ถึง 100
- พารามิเตอร์นี้บอก PostgreSQL ว่าควรบรรจุแต่ละเพจขนาด 8 KB ให้ เต็มเพียงใด เมื่อแทรกแถว
- ค่า fillfactor เท่ากับ
100(ค่าเริ่มต้นสำหรับตาราง) จะบรรจุเพจจนเต็มทั้งหมด - ค่า fillfactor เท่ากับ
90จะเหลือพื้นที่ประมาณ 10% ของทุกเพจไว้เป็น พื้นที่ว่างสำรองสำหรับการอัปเดตในอนาคต
พื้นที่สำรองนี้เป็นกุญแจสำคัญที่ทำให้การอัปเดตบนเพจเดิมมีต้นทุนต่ำลง
ALTER TABLE orders SET (fillfactor = 90);การอัปเดตแบบ HOT: ผลลัพธ์ที่ได้
การอัปเดตแบบ HOT (Heap-Only Tuple) เกิดขึ้นเมื่อ
- ไม่มีคอลัมน์ที่อัปเดตเป็นส่วนหนึ่งของดัชนีใด ๆ และ
- ทูเพิลใหม่มีขนาดพอดีกับ เพจเดิม ของเวอร์ชันเก่า
เมื่อเงื่อนไขทั้งสองข้อเป็นจริง PostgreSQL จะเชื่อมเวอร์ชันใหม่กับเวอร์ชันเก่าภายในเพจ และ ข้ามการอัปเดตดัชนีทั้งหมด จึงไม่มีการเปลี่ยนแปลงดัชนี ใช้ WAL น้อยลงมาก และเวอร์ชันเก่าสามารถถูกทำความสะอาดได้อย่างประหยัดโดยการตัดแต่งแบบ HOT
การเว้นพื้นที่ว่างด้วยค่า fillfactor ที่ต่ำลงทำให้เงื่อนไข "เพจเดิม" เกิดขึ้นได้
ตั้งค่า fillfactor เมื่อตารางใหม่
คุณสามารถประกาศพารามิเตอร์พื้นที่จัดเก็บขณะใช้ CREATE TABLE วิธีนี้สะอาดที่สุด เพราะตารางจะถูกบรรจุอย่างถูกต้องตั้งแต่การแทรกครั้งแรก
- เลือกค่าที่สำรองพื้นที่ได้เพียงพอสำหรับจำนวนเวอร์ชันแถวบนเพจโดยทั่วไประหว่างการทำงานของ vacuum
90เป็นจุดเริ่มต้นที่ใช้กันทั่วไป ส่วน70–80เหมาะกับแถวที่มีการอัปเดตถี่มาก
CREATE TABLE session_state (
session_id uuid PRIMARY KEY,
last_seen_at timestamptz NOT NULL,
hit_count integer NOT NULL DEFAULT 0,
payload jsonb
) WITH (fillfactor = 80);เปลี่ยน fillfactor ของตารางที่มีอยู่
ALTER TABLE ... SET (fillfactor = N) จะเปลี่ยนพารามิเตอร์ แต่จะ ไม่ เขียนเพจที่มีอยู่ใหม่ เฉพาะเพจที่เขียนขึ้นใหม่เท่านั้นที่จะใช้ค่าใหม่
หากต้องการใช้ค่าดังกล่าวกับข้อมูลปัจจุบัน ให้เขียนตารางใหม่ด้วย VACUUM FULL หรือ CLUSTER (ทั้งสองคำสั่งใช้ล็อก ACCESS EXCLUSIVE) หรือใช้ pg_repack เพื่อเขียนใหม่ขณะระบบออนไลน์
ALTER TABLE session_state SET (fillfactor = 80);
VACUUM FULL session_state;ยืนยันว่าการอัปเดตแบบ HOT เกิดขึ้น
คุณไม่จำเป็นต้องคาดเดา pg_stat_user_tables แสดงตัวนับที่บอกได้ว่าการอัปเดตของคุณใช้เส้นทาง HOT หรือไม่
n_tup_upd— จำนวนทูเพิลที่อัปเดตทั้งหมดn_tup_hot_upd— จำนวนทูเพิลที่เป็นการอัปเดตแบบ HOT
อัตราส่วน n_tup_hot_upd / n_tup_upd ที่สูงหมายความว่า fillfactor และการออกแบบดัชนีของคุณให้ผลลัพธ์ที่ดี
SELECT relname,
n_tup_upd,
n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'session_state';คอลัมน์ที่มีดัชนีจะขัดขวางการอัปเดตแบบ HOT
การมีพื้นที่ว่างเพียงอย่างเดียวไม่เพียงพอ หาก UPDATE แตะต้อง คอลัมน์ใด ๆ ที่มีดัชนี PostgreSQL ต้องสร้างรายการดัชนีใหม่ ดังนั้นการอัปเดตจะไม่สามารถเป็น HOT ได้ แม้ทูเพิลใหม่จะมีขนาดพอดีกับเพจเดิมก็ตาม
- หากเป็นไปได้ ให้เก็บคอลัมน์ที่อัปเดตบ่อย (ตัวนับ การประทับเวลา แฟล็กสถานะ) ไว้ นอกดัชนี
- ลบดัชนีที่ไม่ได้ใช้งานจริง เพราะแต่ละดัชนีอาจขัดขวาง HOT ได้
ตัวอย่างเช่น การทำดัชนีบน hit_count จะทำลายเป้าหมายทั้งหมดของการปรับค่า fillfactor สำหรับตารางนี้
-- This index would block HOT updates whenever hit_count changes:
-- CREATE INDEX ON session_state (hit_count);
-- Prefer indexing stable columns instead:
CREATE INDEX idx_session_last_seen ON session_state (last_seen_at);การเลือกค่า: สิ่งที่ต้องแลกเปลี่ยน
การใช้ fillfactor ต่ำลงไม่ได้ไม่มีต้นทุน สิ่งที่ต้องแลกเปลี่ยนมีดังนี้
- fillfactor ต่ำลง → มีพื้นที่ว่างต่อเพจมากขึ้น → อัปเดตแบบ HOT ได้มากขึ้น การเปลี่ยนแปลงดัชนีน้อยลง → แต่ตารางใช้จำนวนเพจมากขึ้น ดังนั้นการสแกนตามลำดับและแคชบัฟเฟอร์จะเก็บแถวต่อเพจได้น้อยลง
- fillfactor สูงขึ้น → จัดเก็บข้อมูลได้หนาแน่นขึ้น ประสิทธิภาพการสแกนและแคชดีขึ้น → แต่การอัปเดตจะล้นไปยังเพจใหม่ ทำให้เกิดการเปลี่ยนแปลงดัชนีและข้อมูลส่วนเกิน
หลักทั่วไปคือใช้ 100 กับตารางที่มีแต่การเพิ่มข้อมูลหรืออ่านเป็นหลัก และลดเป็น 70–90 เฉพาะตารางที่มีการอัปเดตจำนวนมากจริง ๆ
fillfactor กับดัชนีเช่นกัน
ดัชนีมีค่า fillfactor ของตัวเอง (ค่าเริ่มต้นคือ 90 สำหรับ B-tree) การลดค่านี้จะเว้นพื้นที่ไว้ในเพจใบ เพื่อให้รายการใหม่ไม่ทำให้ต้องแบ่งเพจบ่อยครั้งในตารางที่มีการเพิ่มข้อมูลจำนวนมากด้วยคีย์ที่เพิ่มขึ้นอย่างต่อเนื่อง
- สำหรับคีย์ที่เพิ่มข้อมูลต่อท้ายอย่างเดียวหรือเพิ่มขึ้นเรื่อย ๆ ค่าเริ่มต้นมักเพียงพอแล้ว
- สำหรับดัชนีของคีย์ที่กระจายแบบสุ่มและมีการเพิ่ม ลบ หรือแก้ไขข้อมูลอยู่เสมอ การลดค่า fillfactor ของดัชนีลงเล็กน้อยอาจช่วยลดการแบ่งเพจได้
CREATE INDEX idx_session_last_seen
ON session_state (last_seen_at)
WITH (fillfactor = 80);ตรวจสอบการตั้งค่าปัจจุบัน
หากต้องการดูว่าตารางมีค่า fillfactor ที่ไม่ใช่ค่าเริ่มต้นอยู่แล้วหรือไม่ ให้อ่านค่า reloptions จาก pg_class หากเป็น NULL หมายความว่ากำลังใช้ค่าเริ่มต้น (100 สำหรับ heap และ 90 สำหรับ B-tree)
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('session_state', 'idx_session_last_seen');ขั้นตอนการปรับแต่งที่ใช้ได้จริง
เมื่อนำแนวคิดทั้งหมดมาปรับใช้กับตารางที่มีการอัปเดตจำนวนมาก:
- 1. ยืนยันว่าปริมาณงานมีการอัปเดตเป็นหลัก และตรวจสอบสัดส่วน
n_tup_hot_updปัจจุบัน - 2. ย้ายคอลัมน์ที่มีการเปลี่ยนแปลงบ่อยออกจากดัชนี และลบดัชนีที่ไม่ได้ใช้งาน
- 3. ตั้งค่า fillfactor (เริ่มที่
90และลดลงใกล้70หากสัดส่วน HOT ยังต่ำ) - 4. เขียนตารางใหม่ (
VACUUM FULL/CLUSTER/pg_repack) เพื่อให้เพจที่มีอยู่ถูกจัดวางข้อมูลใหม่ตามค่าดังกล่าว - 5. วัดสัดส่วน HOT อีกครั้ง แล้วปรับค่า
ควรตรวจสอบผลด้วยมุมมองสถิติเสมอ อย่าปรับแต่งโดยไม่มีข้อมูล
ALTER TABLE session_state SET (fillfactor = 75);
CLUSTER session_state USING idx_session_last_seen;
ANALYZE session_state;ตรวจสอบความเข้าใจอย่างรวดเร็ว
คุณมีตารางที่มีการอัปเดตจำนวนมาก โดยคอลัมน์ status เปลี่ยนแปลงอยู่ตลอด และคุณลดค่า fillfactor เหลือ 80 แล้ว แต่ค่า n_tup_hot_upd ยังคงใกล้ศูนย์ สาเหตุที่เป็นไปได้มากที่สุดคืออะไร
สรุป
ประเด็นสำคัญในการปรับค่า fillfactor สำหรับตารางที่มีการอัปเดตจำนวนมาก:
fillfactorจะกันพื้นที่ว่างไว้ในแต่ละเพจ เพื่อให้แถวที่ถูกอัปเดตยังอยู่ในเพจเดิม ซึ่งทำให้เกิดการอัปเดตแบบ HOT- การอัปเดตแบบ HOT จะข้ามการดูแลดัชนี จึงช่วยลดการขยายตัวของข้อมูลที่ต้องเขียนและข้อมูลส่วนเกิน
- การทำ HOT ต้องมีทั้งพื้นที่ว่างในเพจเดิมและไม่มีการเปลี่ยนคอลัมน์ที่อยู่ในดัชนี ดังนั้นควรเก็บคอลัมน์ที่มีการเปลี่ยนแปลงบ่อยไว้นอกดัชนี
ALTER TABLE SET (fillfactor=N)มีผลเฉพาะกับเพจใหม่เท่านั้น หากต้องการใช้กับข้อมูลเดิม ให้เขียนตารางใหม่ด้วยVACUUM FULL/CLUSTER/pg_repack- วัดผลสำเร็จด้วย
n_tup_hot_upd / n_tup_updในpg_stat_user_tablesแล้วปรับแต่งเป็นลำดับ - การลด fillfactor แลกความหนาแน่นของการจัดเก็บกับการลดจำนวนการอัปเดตที่ต้องย้ายไปยังเพจใหม่ ควรใช้เฉพาะจุดที่ปริมาณงานมีเหตุผลรองรับ
เรียนรู้ SQL ด้วย AI tutor — ฟรี
เขียนและเรียกใช้โค้ดจริงในเบราว์เซอร์ของคุณ รับความช่วยเหลือทันทีจาก AI tutor 24/7 และเรียนรู้ต่อจากที่คุณหยุดบนเว็บหรือในแอป
- คอร์ส
- 22
- บทเรียน
- 88
คำถามที่พบบ่อย
บทเรียน “การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส PostgreSQL Performance & Query Optimization ให้อัปเกรดเป็น CoddyKit PRO คอร์ส PostgreSQL Performance & Query Optimization มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง”
กำหนด fillfactor เพื่อเว้นพื้นที่สำหรับการปรับปรุงแบบ HOT และลดการเขียนดัชนีซ้ำในแถวที่แก้ไขบ่อย คุณปฏิบัติ PostgreSQL Performance & Query Optimization ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน PostgreSQL Performance & Query Optimization หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน PostgreSQL Performance & Query Optimization บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน
บทเรียน “การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน PostgreSQL Performance & Query Optimization นี้ได้ไหม
ได้ บทเรียน PostgreSQL Performance & Query Optimization ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- การวัดการพองตัวของตารางและดัชนีอย่างแม่นยำ
- การเรียกคืนพื้นที่ด้วย pg_repack
- การปรับ Fillfactor สำหรับตารางที่มีการปรับปรุงข้อมูลสูง
- โครงสร้างภายใน TOAST และการจัดเก็บค่าขนาดใหญ่