PostgreSQL Performance Tuning คู่มือปรับแต่งฐานข้อมูล

March 15, 2026

1 View

SaveSavedRemoved 0

ในโลกของการพัฒนาแอปพลิเคชันและระบบข้อมูล PostgreSQL ได้พิสูจน์ตัวเองแล้วว่าเป็นหนึ่งในระบบจัดการฐานข้อมูลเชิงสัมพันธ์ (RDBMS) ที่ทรงพลัง ยืดหยุ่น และน่าเชื่อถือที่สุด ด้วยความสามารถที่หลากหลายและชุมชนนักพัฒนาที่แข็งแกร่ง ทำให้ PostgreSQL เป็นตัวเลือกยอดนิยมสำหรับโปรเจกต์ทุกขนาด ตั้งแต่เว็บแอปพลิเคชันขนาดเล็กไปจนถึงระบบองค์กรขนาดใหญ่ที่ต้องการประสิทธิภาพสูง อย่างไรก็ตาม การติดตั้ง PostgreSQL แบบมาตรฐานอาจไม่เพียงพอที่จะดึงศักยภาพสูงสุดของมันออกมาใช้งานได้ โดยเฉพาะอย่างยิ่งเมื่อระบบของคุณต้องรองรับปริมาณงานที่เพิ่มขึ้นเรื่อยๆ การปรับแต่ง (Performance Tuning) จึงเป็นกุญแจสำคัญที่จะช่วยให้ฐานข้อมูลของคุณทำงานได้อย่างรวดเร็ว มีเสถียรภาพ และตอบสนองความต้องการของผู้ใช้งานได้อย่างมีประสิทธิภาพสูงสุด บทความนี้จะเจาะลึกถึงเทคนิคและแนวทางการปรับแต่ง PostgreSQL อย่างละเอียด เพื่อให้คุณสามารถปรับแต่งฐานข้อมูลของคุณให้ทำงานได้เต็มประสิทธิภาพ พร้อมรับมือกับความท้าทายของข้อมูลและการประมวลผลที่เปลี่ยนแปลงไปครับ

บทนำ: ทำไมต้องปรับแต่ง PostgreSQL?
1. การปรับแต่งพารามิเตอร์ใน postgresql.conf
2. การเพิ่มประสิทธิภาพ Index
3. การเพิ่มประสิทธิภาพ Query
4. การออกแบบ Schema ฐานข้อมูล
5. VACUUM และ Autovacuum: กุญแจสู่สุขภาพฐานข้อมูล
6. การเฝ้าระวังและการวินิจฉัย (Monitoring and Diagnostics)
7. การปรับแต่งระดับ Hardware และ OS
8. Connection Pooling
9. Replication และ High Availability (HA) เพื่อประสิทธิภาพ
ตารางเปรียบเทียบ: กลยุทธ์การปรับแต่ง PostgreSQL ที่พบบ่อย
คำถามที่พบบ่อย (FAQ)
สรุปและ Call to Action

บทนำ: ทำไมต้องปรับแต่ง PostgreSQL?

PostgreSQL เป็น RDBMS ที่มีชื่อเสียงด้านความเสถียร ความน่าเชื่อถือ และความสามารถในการขยายขนาด แต่เหมือนกับซอฟต์แวร์อื่นๆ ฐานข้อมูล PostgreSQL จะไม่สามารถทำงานได้เต็มประสิทธิภาพสูงสุดหากไม่มีการปรับแต่งที่เหมาะสมครับ การตั้งค่าเริ่มต้น (default settings) ของ PostgreSQL ถูกออกแบบมาให้ใช้งานได้กับสภาพแวดล้อมที่หลากหลายที่สุด ซึ่งหมายความว่ามันอาจจะไม่ได้ถูกปรับให้เหมาะสมกับทรัพยากรฮาร์ดแวร์ หรือรูปแบบการใช้งานเฉพาะของระบบคุณโดยตรง

การปรับแต่ง PostgreSQL มีความสำคัญอย่างยิ่งด้วยเหตุผลหลายประการ:

เพิ่มความเร็วในการตอบสนอง (Response Time): Query ที่ช้าลงแม้เพียงเล็กน้อยเมื่อผู้ใช้หลายคนเข้าถึงพร้อมกัน ก็สามารถส่งผลให้ระบบโดยรวมทำงานช้าลงอย่างเห็นได้ชัด การปรับแต่งที่เหมาะสมจะช่วยลดเวลาในการประมวลผล Query และส่งผลให้แอปพลิเคชันตอบสนองได้เร็วขึ้นครับ
เพิ่มปริมาณงาน (Throughput): ฐานข้อมูลที่ได้รับการปรับแต่งอย่างดีจะสามารถรองรับจำนวน Query หรือ Transaction ได้มากขึ้นในเวลาเดียวกัน ซึ่งจำเป็นอย่างยิ่งสำหรับระบบที่มีผู้ใช้งานจำนวนมาก หรือมีการรับส่งข้อมูลปริมาณมหาศาลครับ
ลดการใช้ทรัพยากร: การปรับแต่งให้เหมาะสมจะช่วยให้ PostgreSQL ใช้ CPU, RAM และ I/O ได้อย่างมีประสิทธิภาพมากขึ้น ซึ่งไม่เพียงแต่ช่วยประหยัดค่าใช้จ่ายด้านฮาร์ดแวร์ แต่ยังช่วยให้ระบบโดยรวมทำงานได้เสถียรขึ้นด้วยครับ
ลดความเสี่ยงต่อปัญหา: ฐานข้อมูลที่ทำงานหนักเกินไปโดยไม่มีการปรับแต่ง อาจนำไปสู่ปัญหาเช่น Deadlock, Latch Contention หรือการทำงานล้มเหลว (Crash) ได้ง่ายขึ้น การปรับแต่งที่ดีช่วยลดความเสี่ยงเหล่านี้ครับ
ยืดอายุการใช้งานระบบ: เมื่อระบบของคุณเติบโตขึ้น การปรับแต่งที่ทำไว้ล่วงหน้าจะช่วยให้ PostgreSQL สามารถรองรับการขยายตัวได้ดีขึ้น โดยไม่ต้องลงทุนอัปเกรดฮาร์ดแวร์บ่อยครั้งเกินไปครับ

บทความนี้จะพาคุณเจาะลึกในแต่ละส่วนของการปรับแต่ง ตั้งแต่การปรับค่าคอนฟิกูเรชัน การสร้าง Index ที่มีประสิทธิภาพ การเขียน Query ที่ดี ไปจนถึงการดูแลรักษาสุขภาพของฐานข้อมูล เพื่อให้คุณสามารถดึงประสิทธิภาพสูงสุดจาก PostgreSQL ได้อย่างแท้จริงครับ

1. การปรับแต่งพารามิเตอร์ใน postgresql.conf

ไฟล์ postgresql.conf คือหัวใจของการปรับแต่ง PostgreSQL ครับ พารามิเตอร์ต่างๆ ในไฟล์นี้ควบคุมพฤติกรรมการทำงานของฐานข้อมูลเกือบทุกด้าน การปรับค่าเหล่านี้ให้เหมาะสมกับทรัพยากรฮาร์ดแวร์และรูปแบบการใช้งานของคุณเป็นขั้นตอนแรกและสำคัญที่สุดในการเพิ่มประสิทธิภาพครับ มาดูกันว่าพารามิเตอร์สำคัญตัวไหนบ้างที่คุณควรให้ความสนใจเป็นพิเศษ

1.1 shared_buffers

shared_buffers คือจำนวนหน่วยความจำ RAM ที่ PostgreSQL ใช้สำหรับแคชข้อมูล (data cache) ครับ นี่เป็นหนึ่งในพารามิเตอร์ที่สำคัญที่สุด เพราะข้อมูลที่ถูกแคชไว้ใน RAM สามารถเข้าถึงได้เร็วกว่าการอ่านจากดิสก์หลายเท่าตัว

ค่าแนะนำ: โดยทั่วไปแล้ว ควรตั้งค่า shared_buffers เป็นประมาณ 25% ของ RAM ทั้งหมดของเซิร์ฟเวอร์ (ถ้า RAM มากกว่า 16GB) หรือ 25-40% (ถ้า RAM น้อยกว่า 16GB) แต่ไม่ควรเกิน 8GB ถึง 16GB แม้ว่าคุณจะมี RAM มากถึง 128GB หรือมากกว่าก็ตาม เพราะ PostgreSQL ยังต้องพึ่งพา OS page cache ด้วยครับ
ผลกระทบ: การตั้งค่าสูงเกินไปอาจทำให้เกิดปัญหา Out-Of-Memory หรือแย่ง RAM กับ OS page cache ซึ่งอาจทำให้ประสิทธิภาพแย่ลงได้ครับ การตั้งค่าต่ำเกินไปจะทำให้ PostgreSQL ต้องอ่านข้อมูลจากดิสก์บ่อยขึ้น ทำให้ประสิทธิภาพลดลงอย่างมาก

shared_buffers = 4GB  # ตัวอย่างสำหรับเซิร์ฟเวอร์ที่มี RAM 16GB

1.2 work_mem

work_mem คือหน่วยความจำ RAM ที่ใช้สำหรับ Query แต่ละ Query เพื่อดำเนินการ sorting, hashing หรือการทำงานชั่วคราวอื่นๆ ครับ เช่น เมื่อมีการใช้ ORDER BY, GROUP BY, DISTINCT หรือ Merge Join

ค่าแนะนำ: ค่าเริ่มต้นมักจะน้อยเกินไป (เช่น 4MB) สำหรับระบบที่มีข้อมูลขนาดใหญ่ ควรเพิ่มค่านี้เป็น 16MB, 32MB, 64MB หรือแม้กระทั่ง 128MB ขึ้นอยู่กับความซับซ้อนของ Query และปริมาณ RAM ที่มีครับ
ผลกระทบ: ถ้า work_mem ไม่เพียงพอ PostgreSQL จะต้องเขียนข้อมูลชั่วคราวลงดิสก์ (spill to disk) ซึ่งทำให้ Query ทำงานช้าลงอย่างมาก แต่ถ้าตั้งค่าสูงเกินไป และมี Query ที่ใช้ work_mem พร้อมกันหลาย Query ก็อาจทำให้ RAM หมดได้เช่นกันครับ
ข้อควรระวัง: พารามิเตอร์นี้ถูกกำหนดต่อเซสชันและต่อการดำเนินการ ดังนั้นถ้ามี 100 Query รันพร้อมกัน แต่ละ Query ใช้ 100MB นั่นหมายถึง 10GB ของ RAM ที่อาจถูกใช้ไปพร้อมกัน! ควรปรับค่านี้อย่างระมัดระวังและเฝ้าดูการใช้งาน RAM ครับ

work_mem = 32MB

1.3 maintenance_work_mem

maintenance_work_mem คือหน่วยความจำ RAM ที่ใช้สำหรับงานดูแลรักษาฐานข้อมูล เช่น VACUUM, CREATE INDEX, ALTER TABLE (เพิ่มคอลัมน์) และ ADD FOREIGN KEY ครับ

ค่าแนะนำ: สามารถตั้งค่าได้สูงกว่า work_mem มาก เพราะงานเหล่านี้มักจะไม่ถูกรันพร้อมกันหลายๆ งาน ควรตั้งค่าเป็นประมาณ 128MB, 256MB, 512MB หรือแม้กระทั่ง 1GB หรือ 2GB สำหรับระบบที่มี RAM มากและมีการสร้าง Index ขนาดใหญ่ครับ
ผลกระทบ: การตั้งค่าที่เหมาะสมจะช่วยให้งานดูแลรักษาเหล่านี้ทำงานได้เร็วขึ้นอย่างมาก โดยเฉพาะการสร้าง Index การตั้งค่าที่ต่ำเกินไปจะทำให้งานเหล่านี้ทำงานช้าลงและอาจต้องเขียนข้อมูลชั่วคราวลงดิสก์ครับ

maintenance_work_mem = 512MB

1.4 wal_buffers

wal_buffers คือหน่วยความจำ RAM ที่ใช้สำหรับแคช Write-Ahead Log (WAL) ครับ WAL คือบันทึกการเปลี่ยนแปลงทั้งหมดที่เกิดขึ้นกับฐานข้อมูล เพื่อรับประกันความคงทนของข้อมูล (durability)

ค่าแนะนำ: ค่าเริ่มต้นคือ -1 ซึ่งหมายความว่า PostgreSQL จะคำนวณอัตโนมัติ (โดยทั่วไปคือ 1/32 ของ shared_buffers สูงสุด 16MB) สำหรับฐานข้อมูลที่มีการเขียนข้อมูลจำนวนมาก (write-heavy workload) การเพิ่มค่านี้เป็น 16MB หรือ 32MB อาจช่วยเพิ่มประสิทธิภาพการเขียนได้เล็กน้อยครับ
ผลกระทบ: การตั้งค่าที่สูงเกินไปแทบไม่มีประโยชน์และอาจสิ้นเปลือง RAM โดยไม่จำเป็น การตั้งค่าที่ต่ำเกินไปอาจทำให้ต้องเขียน WAL ลงดิสก์บ่อยขึ้นครับ

wal_buffers = 16MB

1.5 effective_cache_size

effective_cache_size เป็นพารามิเตอร์ที่ไม่ได้ใช้สำหรับจัดสรร RAM โดยตรง แต่ใช้เพื่อบอก Optimizer ของ PostgreSQL ว่ามีหน่วยความจำทั้งหมดเท่าไหร่ที่ระบบปฏิบัติการและ PostgreSQL สามารถใช้ในการแคชข้อมูลได้ครับ

ค่าแนะนำ: ควรตั้งค่านี้ให้เป็นประมาณ 50-75% ของ RAM ทั้งหมดของเซิร์ฟเวอร์ครับ (รวม shared_buffers และ OS page cache)
ผลกระทบ: Optimizer จะใช้ค่านี้ในการตัดสินใจว่าจะใช้ Index หรือ Full Table Scan ถ้าค่านี้ต่ำเกินไป Optimizer อาจประเมินว่าการใช้ Index จะทำให้ต้องอ่านข้อมูลจากดิสก์มากเกินไปและเลือกที่จะทำ Full Table Scan แทน ซึ่งอาจทำให้ Query ช้าลงได้ครับ ถ้าค่านี้สูงเกินไป Optimizer อาจเลือกใช้ Index แม้ว่าการทำ Full Table Scan จะเร็วกว่าครับ

effective_cache_size = 12GB  # สำหรับเซิร์ฟเวอร์ที่มี RAM 16GB

1.6 max_connections

max_connections คือจำนวนการเชื่อมต่อพร้อมกันสูงสุดที่ฐานข้อมูลสามารถรับได้

ค่าแนะนำ: ไม่ควรตั้งค่าสูงเกินไป เพราะแต่ละการเชื่อมต่อต้องใช้ทรัพยากร (RAM) จำนวนหนึ่ง ถ้ามี max_connections สูงเกินไป อาจทำให้ RAM หมดได้ง่ายๆ ควรตั้งค่าให้เหมาะสมกับจำนวนผู้ใช้งานหรือ Application Servers ที่จะเชื่อมต่อเข้ามาครับ โดยปกติแล้ว 100-300 ก็เพียงพอสำหรับหลายๆ ระบบ หากต้องการรองรับการเชื่อมต่อจำนวนมาก ควรพิจารณาใช้ Connection Pooler เช่น PgBouncer ครับ
ผลกระทบ: การตั้งค่าต่ำเกินไปจะทำให้แอปพลิเคชันไม่สามารถเชื่อมต่อได้เมื่อมีการเชื่อมต่อเต็ม การตั้งค่าสูงเกินไปจะสิ้นเปลือง RAM และอาจทำให้ระบบทำงานช้าลงครับ

max_connections = 150

1.7 พารามิเตอร์ที่เกี่ยวข้องกับ Autovacuum

Autovacuum เป็นกระบวนการที่สำคัญมากในการรักษาสุขภาพและประสิทธิภาพของ PostgreSQL ครับ มันจะจัดการกับ “dead tuples” และป้องกัน “transaction ID wraparound” โดยอัตโนมัติ

autovacuum = on: ควรเปิดใช้งานเสมอครับ (ค่าเริ่มต้นคือ on)
log_autovacuum_min_duration = 0: ตั้งค่าเป็น 0 เพื่อบันทึกทุก Autovacuum run หรือตั้งเป็นค่าเป็นมิลลิวินาที (เช่น 250ms) เพื่อบันทึกเฉพาะ Autovacuum ที่ใช้เวลานานกว่านั้นครับ
autovacuum_max_workers: จำนวนกระบวนการ Autovacuum ที่สามารถทำงานพร้อมกันได้ ค่าเริ่มต้นคือ 3 การเพิ่มค่านี้อาจช่วยให้งาน Autovacuum เสร็จเร็วขึ้นในระบบที่มีการเปลี่ยนแปลงข้อมูลสูงครับ
autovacuum_vacuum_scale_factor และ autovacuum_analyze_scale_factor: กำหนดเปอร์เซ็นต์ของจำนวนแถวทั้งหมดที่เปลี่ยนแปลงไป เพื่อกระตุ้นให้เกิด VACUUM และ ANALYZE ตามลำดับ ค่าเริ่มต้นคือ 0.2 (20%) สำหรับตารางขนาดใหญ่มาก อาจพิจารณาลดค่านี้ลงเล็กน้อย (เช่น 0.1 หรือ 0.05) เพื่อให้ Autovacuum ทำงานบ่อยขึ้นเล็กน้อย แต่ไม่ควรต่ำเกินไปครับ
autovacuum_vacuum_cost_delay: ระยะเวลา (มิลลิวินาที) ที่ Autovacuum จะรอหลังจากใช้ทรัพยากรถึงขีดจำกัด ค่าเริ่มต้นคือ 2ms การลดค่านี้ (เช่น 0ms หรือ 1ms) จะทำให้ Autovacuum ทำงานเร็วขึ้น แต่จะใช้ทรัพยากร CPU/I/O มากขึ้นครับ ควรปรับอย่างระมัดระวัง
autovacuum_vacuum_cost_limit: ขีดจำกัดของต้นทุนที่ Autovacuum สามารถใช้ได้ก่อนที่จะหยุดชั่วคราว (ตาม autovacuum_vacuum_cost_delay) ค่าเริ่มต้นคือ 200 การเพิ่มค่านี้จะช่วยให้ Autovacuum ทำงานได้นานขึ้นก่อนที่จะหยุดพักครับ

autovacuum = on
log_autovacuum_min_duration = 500ms
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 1ms
autovacuum_vacuum_cost_limit = 1000

1.8 การปรับแต่ง Checkpoint และ WAL

checkpoint_timeout และ max_wal_size เป็นพารามิเตอร์ที่ควบคุมความถี่และปริมาณของการเขียน WAL ลงดิสก์อย่างถาวร (checkpoint)

checkpoint_timeout: ระยะเวลาสูงสุดระหว่าง Checkpoint ค่าเริ่มต้นคือ 5 นาที การเพิ่มค่านี้เป็น 15-30 นาที จะช่วยลดความถี่ของ Checkpoint ซึ่งช่วยลด I/O spikes ได้ครับ
max_wal_size: ขนาดสูงสุดของ WAL ที่จะถูกเก็บไว้ระหว่าง Checkpoint ค่าเริ่มต้นคือ 1GB การเพิ่มค่านี้ (เช่น 4GB หรือ 16GB) ควบคู่ไปกับ checkpoint_timeout จะช่วยลดความถี่ของ Checkpoint ได้ครับ
ข้อควรระวัง: การเพิ่มค่าเหล่านี้มากเกินไปจะหมายถึงระยะเวลาในการกู้คืน (recovery) ที่นานขึ้นหากเกิดปัญหาครับ

checkpoint_timeout = 15min
max_wal_size = 4GB

1.9 Cost-based Optimizer Parameters

PostgreSQL ใช้ Cost-based Optimizer ในการตัดสินใจเลือกแผนการทำงานของ Query ที่มีประสิทธิภาพที่สุด พารามิเตอร์เหล่านี้ช่วยบอก Optimizer เกี่ยวกับค่าใช้จ่ายในการเข้าถึงข้อมูลประเภทต่างๆ

random_page_cost: ค่าใช้จ่ายในการอ่านหนึ่งหน้าข้อมูลแบบสุ่ม (เช่น การใช้ Index) ค่าเริ่มต้นคือ 4.0 ถ้าคุณใช้ SSD ควรลดค่านี้ลงอย่างมาก (เช่น 1.1 หรือ 1.5) เพื่อให้ Optimizer เลือกใช้ Index มากขึ้นครับ
cpu_tuple_cost: ค่าใช้จ่ายในการประมวลผลแต่ละแถว ค่าเริ่มต้นคือ 0.01
cpu_index_tuple_cost: ค่าใช้จ่ายในการประมวลผลแต่ละ Index Entry ค่าเริ่มต้นคือ 0.005
cpu_operator_cost: ค่าใช้จ่ายในการดำเนินการ Operator แต่ละครั้ง ค่าเริ่มต้นคือ 0.0025

random_page_cost = 1.1 # สำหรับ SSD

1.10 ตัวอย่างการปรับแต่ง postgresql.conf (สำหรับเซิร์ฟเวอร์ RAM 16GB, SSD)

# Connection
max_connections = 150

# Memory
shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB
wal_buffers = 16MB

# WAL & Checkpoint
checkpoint_timeout = 15min
max_wal_size = 4GB

# Autovacuum
autovacuum = on
log_autovacuum_min_duration = 500ms
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 1ms
autovacuum_vacuum_cost_limit = 1000

# Query Optimizer
random_page_cost = 1.1 # สำหรับ SSD
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

# Logging
log_min_duration_statement = 250ms # บันทึก Query ที่ใช้เวลานานกว่า 250ms
log_connections = on
log_disconnections = on
log_error_verbosity = default # สามารถตั้งเป็น verbose เพื่อดูรายละเอียดมากขึ้น
log_line_prefix = '%m %u@%d %p %r %a %c %l ' # รูปแบบ prefix ของ log

2. การเพิ่มประสิทธิภาพ Index

Index เป็นโครงสร้างข้อมูลพิเศษที่ช่วยให้ PostgreSQL ค้นหาและเข้าถึงข้อมูลในตารางได้อย่างรวดเร็ว โดยไม่ต้องสแกนทั้งตาราง (Full Table Scan) การเลือกใช้ Index ที่เหมาะสมเป็นสิ่งสำคัญอย่างยิ่งในการเพิ่มประสิทธิภาพ Query ครับ

2.1 ชนิดของ Index และการเลือกใช้

PostgreSQL มี Index หลายชนิด แต่ละชนิดมีจุดเด่นและการใช้งานที่แตกต่างกันไปครับ

B-Tree Index (ค่าเริ่มต้น):
- การใช้งาน: เหมาะสำหรับการค้นหาแบบเท่ากับ (=), มากกว่า (>), น้อยกว่า (<), >=, <=, BETWEEN, IN และการเรียงลำดับ (ORDER BY)
- จุดเด่น: มีประสิทธิภาพสูงสำหรับการค้นหาข้อมูลแบบช่วง และการเรียงลำดับข้อมูล
- ข้อควรระวัง: ไม่เหมาะสำหรับคอลัมน์ที่มีค่าซ้ำกันมากๆ (low cardinality) หรือคอลัมน์ที่ใช้ใน LIKE '%pattern%' (leading wildcard)
Hash Index:
- การใช้งาน: เหมาะสำหรับการค้นหาแบบเท่ากับ (=) เท่านั้น
- จุดเด่น: มีขนาดเล็กกว่า B-Tree และบางครั้งเร็วกว่าสำหรับการค้นหาแบบเท่ากับ
- ข้อควรระวัง: ไม่สนับสนุนการค้นหาแบบช่วง หรือการเรียงลำดับ และไม่ปลอดภัยต่อ Crash Recovery จนกว่าจะถึง PostgreSQL 10 ดังนั้นจึงไม่ค่อยเป็นที่นิยมครับ
GiST Index (Generalized Search Tree):
- การใช้งาน: เหมาะสำหรับข้อมูลเชิงภูมิศาสตร์ (GIS), Full-Text Search, ข้อมูลแบบช่วง (range data types), arrays และข้อมูลที่ซับซ้อนอื่นๆ
- จุดเด่น: ยืดหยุ่นสูง สามารถขยายเพื่อรองรับ Data Type และ Operator ใหม่ๆ ได้
- ตัวอย่าง: ใช้กับ Data Type เช่น geometry, tsvector, point, box
GIN Index (Generalized Inverted Index):
- การใช้งาน: เหมาะสำหรับคอลัมน์ที่มีค่าหลายค่าในหนึ่งแถว เช่น arrays, JSONB, tsvector (สำหรับ Full-Text Search)
- จุดเด่น: มีประสิทธิภาพสูงในการค้นหาว่ามีค่าบางอย่างอยู่ในชุดข้อมูลหรือไม่
- ข้อควรระวัง: การสร้างและอัปเดต GIN Index อาจใช้เวลานานกว่าและมีขนาดใหญ่กว่า B-Tree
BRIN Index (Block Range Index):
- การใช้งาน: เหมาะสำหรับตารางขนาดใหญ่มาก (หลายล้านแถว) ที่ข้อมูลในคอลัมน์มีการจัดเรียงตามธรรมชาติ (naturally ordered) เช่น Timestamp ของข้อมูลที่ถูกแทรกเข้ามาเรื่อยๆ
- จุดเด่น: มีขนาดเล็กมากเมื่อเทียบกับ B-Tree และใช้ทรัพยากรน้อยมาก
- ข้อควรระวัง: ไม่เหมาะกับข้อมูลที่มีการกระจายตัวแบบสุ่ม

2.2 Partial Index

Partial Index คือ Index ที่ถูกสร้างขึ้นสำหรับ subset ของข้อมูลในตารางเท่านั้น โดยระบุเงื่อนไข WHERE ในการสร้าง Index ครับ

ประโยชน์:
- ลดขนาดของ Index ทำให้ใช้พื้นที่ดิสก์น้อยลง
- เพิ่มความเร็วในการสร้างและบำรุงรักษา Index
- เพิ่มประสิทธิภาพของ Query ที่มักจะค้นหาข้อมูลใน subset นั้นๆ
ตัวอย่าง: ถ้าคุณมีตาราง orders และส่วนใหญ่ Query มักจะค้นหาเฉพาะ orders ที่มีสถานะ 'pending' คุณสามารถสร้าง Partial Index ได้ดังนี้:
```
CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';
```

2.3 Expression Index

Expression Index คือ Index ที่สร้างขึ้นจากผลลัพธ์ของ Expression หรือ Function แทนที่จะเป็นคอลัมน์โดยตรงครับ

ประโยชน์:
- ช่วยให้ Query ที่ใช้ Expression หรือ Function ในเงื่อนไข WHERE สามารถใช้ Index ได้
- ตัวอย่างเช่น การค้นหาแบบ case-insensitive
ตัวอย่าง: หากคุณมักจะค้นหาชื่อผู้ใช้แบบ case-insensitive:
```
CREATE INDEX idx_users_lower_email ON users (lower(email));
```
จากนั้น Query จะต้องใช้ lower(email) ด้วยเพื่อให้ Index ถูกใช้งาน:
```
SELECT * FROM users WHERE lower(email) = '[email protected]';
```

2.4 การตรวจสอบและลบ Index ที่ไม่ได้ใช้งาน

Index ที่ไม่ถูกใช้งานเป็นภาระต่อระบบ เพราะมันต้องใช้พื้นที่ดิสก์และต้องถูกอัปเดตทุกครั้งที่มีการเปลี่ยนแปลงข้อมูลในตารางครับ คุณสามารถตรวจสอบ Index ที่ไม่ได้ใช้งานได้จากวิว pg_stat_user_indexes

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0
ORDER BY
    relname, indexrelname;

หากพบ Index ที่ idx_scan เป็น 0 มาเป็นเวลานาน (เช่น หลายสัปดาห์หรือหลายเดือน) คุณควรพิจารณาลบออก แต่ควรตรวจสอบให้แน่ใจว่าไม่มี Query สำคัญที่ใช้ Index นั้นในบางสถานการณ์ที่เกิดขึ้นไม่บ่อยครับ

DROP INDEX IF EXISTS index_name;

2.5 ตัวอย่างการสร้าง Index

-- สร้าง B-Tree Index บนคอลัมน์ email ในตาราง users
CREATE INDEX idx_users_email ON users (email);

-- สร้าง Composite B-Tree Index (ใช้หลายคอลัมน์)
-- มีประโยชน์เมื่อ Query มี WHERE clause บน user_id และ order_date
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- สร้าง GIN Index สำหรับคอลัมน์ JSONB ที่ใช้ @> operator
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- สร้าง BRIN Index สำหรับคอลัมน์ created_at ที่มีการจัดเรียงตามธรรมชาติ
CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at);

3. การเพิ่มประสิทธิภาพ Query

แม้ว่าคุณจะปรับแต่งไฟล์ postgresql.conf และสร้าง Index ได้อย่างดีแล้ว แต่ถ้า Query ของคุณไม่มีประสิทธิภาพ ก็อาจทำให้ระบบโดยรวมช้าลงได้อยู่ดีครับ การเขียน Query ที่ดีเป็นสิ่งสำคัญอย่างยิ่งในการเพิ่มประสิทธิภาพฐานข้อมูล

3.1 ทำความเข้าใจ EXPLAIN ANALYZE

EXPLAIN ANALYZE เป็นเครื่องมือที่ทรงพลังที่สุดในการวิเคราะห์ประสิทธิภาพของ Query ครับ มันจะแสดงแผนการทำงานของ Query (Query Plan) พร้อมกับสถิติเวลาที่ใช้จริงในการประมวลผลแต่ละขั้นตอน

EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'john.doe%';

ผลลัพธ์ของ EXPLAIN ANALYZE จะแสดงข้อมูลสำคัญดังนี้:

Node Type: ชนิดของการดำเนินการ เช่น Seq Scan, Index Scan, Bitmap Heap Scan, Hash Join, Merge Join, Nested Loop Join
Cost: ค่าใช้จ่ายโดยประมาณ (หน่วยเป็น arbitrary units) ของแต่ละ Node และของ Query ทั้งหมด โดยแสดงเป็น {startup_cost}..{total_cost}
- startup_cost: ค่าใช้จ่ายในการเตรียมการจนกว่าจะส่งผลลัพธ์แรกออกมาได้
- total_cost: ค่าใช้จ่ายทั้งหมดจนกว่าจะส่งผลลัพธ์ทั้งหมดออกมาได้
Rows: จำนวนแถวโดยประมาณที่แต่ละ Node จะส่งออกมา
Width: ขนาดโดยประมาณของแต่ละแถวเป็นไบต์
Actual Time: เวลาจริงที่ใช้ในการประมวลผล (ในหน่วยมิลลิวินาที) แสดงเป็น {startup_time}..{total_time}
Loops: จำนวนครั้งที่ Node นั้นถูกรัน
Buffers: จำนวน Buffer ที่ใช้ (Shared Hit, Shared Read, Temp Read, Temp Written)
Planning Time: เวลาที่ Optimizer ใช้ในการสร้าง Query Plan
Execution Time: เวลาทั้งหมดที่ใช้ในการดำเนินการ Query

สิ่งที่ควรสังเกตในผลลัพธ์ EXPLAIN ANALYZE:

Seq Scan บนตารางขนาดใหญ่: หากเห็น Seq Scan บนตารางที่มีข้อมูลจำนวนมาก และมีเงื่อนไข WHERE ที่สามารถใช้ Index ได้ แสดงว่า Index อาจจะไม่มีอยู่ หรือ Optimizer คิดว่าการใช้ Index ไม่คุ้มค่า
Estimated Rows vs. Actual Rows: หากมีความแตกต่างกันมาก แสดงว่าสถิติของตารางอาจไม่เป็นปัจจุบัน (ต้องรัน ANALYZE) หรือ Query ซับซ้อนเกินไปจน Optimizer คาดเดาได้ไม่ดี
Excessive Disk I/O: สังเกต Buffers: shared hit (อ่านจาก RAM) และ Buffers: shared read (อ่านจากดิสก์) ถ้า shared read สูงมาก แสดงว่า Query นั้นต้องอ่านข้อมูลจากดิสก์บ่อยครั้ง ซึ่งอาจบ่งชี้ถึง Index ที่ไม่เหมาะสม หรือ shared_buffers ไม่เพียงพอ
"Sort" หรือ "Hash Aggregate" ที่ต้อง "spill to disk": ถ้าเห็นคำว่า "actual time" นานมากในขั้นตอน Sort หรือ Hash Aggregate และมี "temporary file" หรือ "spill to disk" แสดงว่า work_mem ไม่เพียงพอ
Nested Loop Join กับตารางขนาดใหญ่: Nested Loop Join มักจะมีประสิทธิภาพดีเมื่อตารางด้านใน (inner table) มีขนาดเล็ก หรือถูกกรองด้วย Index ได้อย่างรวดเร็ว หากใช้กับตารางขนาดใหญ่มากๆ อาจทำให้ประสิทธิภาพแย่ลง

สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับ EXPLAIN ANALYZE คุณสามารถดูเอกสารของ PostgreSQL ได้ที่ อ่านเพิ่มเติม

3.2 การปรับปรุงและเขียน Query ใหม่

การปรับปรุง Query มักจะเกี่ยวข้องกับการหลีกเลี่ยง Anti-patterns และการใช้เทคนิคที่เหมาะสมครับ

หลีกเลี่ยง SELECT *: เลือกเฉพาะคอลัมน์ที่คุณต้องการจริงๆ เพื่อลดปริมาณข้อมูลที่ต้องอ่านจากดิสก์และส่งผ่านเครือข่าย
ใช้ WHERE Clause อย่างมีประสิทธิภาพ:
- หลีกเลี่ยงการใช้ Function หรือ Expression กับคอลัมน์ที่มี Index ใน WHERE Clause โดยตรง (เว้นแต่จะใช้ Expression Index) เช่น WHERE substr(name, 1, 1) = 'A' จะไม่ใช้ Index บน name
- ใช้ LIKE 'pattern%' แทน LIKE '%pattern%' เพื่อให้สามารถใช้ Index ได้ (สำหรับ B-Tree)
ใช้ JOIN แทน Subquery หรือ Correlated Subquery: บ่อยครั้งที่ JOIN มีประสิทธิภาพดีกว่า Subquery โดยเฉพาะ Correlated Subquery ที่ต้องรันซ้ำสำหรับแต่ละแถวของ Outer Query
พิจารณาใช้ UNION ALL แทน UNION: ถ้าคุณแน่ใจว่าไม่มีข้อมูลซ้ำกัน การใช้ UNION ALL จะเร็วกว่า UNION เพราะไม่ต้องเสียเวลาในการกำจัดแถวที่ซ้ำกัน
ใช้ LIMIT และ OFFSET อย่างระมัดระวัง: การใช้ OFFSET สูงๆ อาจทำให้ประสิทธิภาพแย่ลง เพราะฐานข้อมูลยังคงต้องอ่านและประมวลผลแถวทั้งหมดจนถึง OFFSET นั้น ก่อนที่จะคืนค่า LIMIT ออกมา หากต้องทำ pagination จำนวนมาก ควรพิจารณาใช้เทคนิค "Keyset Pagination" หรือ "Seek Method" โดยใช้เงื่อนไข WHERE กับคอลัมน์ที่เรียงลำดับ เช่น WHERE id > last_seen_id ORDER BY id LIMIT N ครับ

3.3 การใช้ Common Table Expressions (CTE) และ Prepared Statements

Common Table Expressions (CTE) - WITH Clause:
- ช่วยเพิ่มความสามารถในการอ่าน Query ที่ซับซ้อน และสามารถช่วยให้ Optimizer หาแผนการทำงานที่ดีขึ้นได้ในบางกรณี
- สามารถนำไปใช้ซ้ำได้ภายใน Query เดียวกัน ซึ่งอาจช่วยลดการคำนวณซ้ำ
```
WITH recent_users AS (
    SELECT user_id, username
    FROM users
    WHERE created_at > NOW() - INTERVAL '1 month'
)
SELECT ru.username, COUNT(o.order_id)
FROM recent_users ru
JOIN orders o ON ru.user_id = o.user_id
GROUP BY ru.username;
```
Prepared Statements:
- เป็น Query ที่ถูกคอมไพล์และเก็บไว้ในเซิร์ฟเวอร์ฐานข้อมูล ทำให้สามารถนำกลับมาใช้ซ้ำได้โดยไม่ต้องผ่านขั้นตอนการ Parsing และ Planning ใหม่ทุกครั้ง
- มีประโยชน์อย่างยิ่งสำหรับ Query ที่ถูกรันบ่อยๆ ด้วยพารามิเตอร์ที่แตกต่างกัน
- ช่วยลด Overheads และป้องกัน SQL Injection ได้ด้วย
```
PREPARE get_user_by_email (text) AS
    SELECT user_id, username, email FROM users WHERE email = $1;

EXECUTE get_user_by_email('[email protected]');
EXECUTE get_user_by_email('[email protected]');

DEALLOCATE get_user_by_email;
```

4. การออกแบบ Schema ฐานข้อมูล

การออกแบบ Schema ฐานข้อมูลที่ดีเป็นรากฐานสำคัญของประสิทธิภาพในระยะยาวครับ การตัดสินใจในการออกแบบตั้งแต่เริ่มต้นจะส่งผลต่อการเขียน Query การบำรุงรักษา และประสิทธิภาพโดยรวมของระบบ

4.1 Normalization vs. Denormalization

Normalization (การทำให้เป็น Normal Form):
- แนวคิด: การจัดระเบียบข้อมูลเพื่อลดความซ้ำซ้อน (redundancy) และเพิ่มความสมบูรณ์ของข้อมูล (data integrity) โดยการแบ่งข้อมูลออกเป็นตารางเล็กๆ และเชื่อมโยงกันด้วย Foreign Key
- ข้อดี: ลดพื้นที่จัดเก็บ, ลดความซ้ำซ้อน, ง่ายต่อการบำรุงรักษาข้อมูล, ป้องกัน Data Anomalies
- ข้อเสีย: อาจต้องใช้ JOIN หลายครั้งในการดึงข้อมูล ทำให้ Query ซับซ้อนและอาจช้าลงสำหรับ Read-heavy workloads
Denormalization (การลด Normal Form):
- แนวคิด: การเพิ่มความซ้ำซ้อนของข้อมูลโดยจงใจ หรือรวมตารางเข้าด้วยกัน เพื่อลดจำนวน JOIN ที่จำเป็นในการดึงข้อมูล
- ข้อดี: เพิ่มประสิทธิภาพการอ่าน (Read Performance) ลดความซับซ้อนของ Query
- ข้อเสีย: เพิ่มพื้นที่จัดเก็บ, เพิ่มความซับซ้อนในการอัปเดตข้อมูล (ต้องอัปเดตหลายที่), อาจนำไปสู่ Data Anomalies ได้ง่ายขึ้น
แนวทาง: โดยทั่วไปแล้ว ควรเริ่มต้นด้วย Normalization ก่อน และพิจารณา Denormalization เฉพาะในส่วนที่มีปัญหาด้านประสิทธิภาพการอ่านอย่างชัดเจน และประเมินผลกระทบอย่างรอบคอบครับ

4.2 การเลือกใช้ Data Type ที่เหมาะสม

การเลือก Data Type ที่เหมาะสมสำหรับแต่ละคอลัมน์ช่วยประหยัดพื้นที่จัดเก็บและเพิ่มประสิทธิภาพครับ

ใช้ Data Type ที่เล็กที่สุดเท่าที่จะทำได้:
- SMALLINT, INT, BIGINT: เลือกตามช่วงค่าที่ต้องการ แทนที่จะใช้ BIGINT เสมอ
- NUMERIC: ใช้เมื่อต้องการความแม่นยำสูง (เช่น ข้อมูลทางการเงิน) แต่ถ้าเป็นแค่ตัวเลขทั่วไปที่ไม่มีเศษส่วน ให้ใช้ INT หรือ BIGINT
- TEXT vs. VARCHAR(n): สำหรับ PostgreSQL ประสิทธิภาพของ TEXT กับ VARCHAR แทบไม่ต่างกันเลยครับ VARCHAR(n) มีประโยชน์ในการกำหนดข้อจำกัดความยาว แต่ไม่ได้ช่วยประหยัดพื้นที่จัดเก็บจริงเมื่อเทียบกับ TEXT หากคุณไม่ต้องการกำหนดความยาวที่แน่นอน TEXT มักเป็นทางเลือกที่ง่ายกว่า
พิจารณาใช้ Data Type เฉพาะทาง:
- UUID: สำหรับ Primary Key แทน BIGINT หากต้องการคีย์ที่ไม่ซ้ำกันทั่วโลก
- JSONB: สำหรับข้อมูลกึ่งโครงสร้าง (semi-structured data) มีประสิทธิภาพสูงในการ Query และ Index
- TSVECTOR และ TSQUERY: สำหรับ Full-Text Search

การเลือก Data Type ที่เหมาะสมไม่เพียงแต่ช่วยให้ตารางมีขนาดเล็กลง แต่ยังช่วยลดปริมาณ RAM ที่ใช้ใน shared_buffers และ work_mem ด้วยครับ

4.3 Table Partitioning

Partitioning คือการแบ่งตารางขนาดใหญ่ออกเป็นตารางย่อยๆ (partitions) ที่มีขนาดเล็กลง โดยยึดตามคอลัมน์ที่กำหนดไว้ (เช่น วันที่, ID Range) แม้ว่าข้อมูลจะถูกเก็บในตารางย่อย แต่ในมุมมองของแอปพลิเคชันยังคงมองเห็นเป็นตารางเดียวครับ

ประโยชน์:
- ประสิทธิภาพการ Query: Optimizer สามารถสแกนเฉพาะ Partition ที่เกี่ยวข้องได้ ทำให้ Query เร็วขึ้น
- ประสิทธิภาพการบำรุงรักษา: การ VACUUM หรือลบข้อมูลใน Partition เล็กๆ จะเร็วกว่าการทำบนตารางขนาดใหญ่
- การจัดการข้อมูล: ง่ายต่อการเก็บถาวร (archive) หรือลบข้อมูลเก่าโดยการ DETACH Partition ทั้งหมด
ชนิดของ Partitioning:
- Range Partitioning: แบ่งตามช่วงของค่า (เช่น วันที่, ID Range)
- List Partitioning: แบ่งตามค่าเฉพาะ (เช่น ภูมิภาค, สถานะ)
- Hash Partitioning: แบ่งโดยใช้ Hash Function เพื่อกระจายข้อมูลให้สม่ำเสมอ
ข้อควรระวัง: การ Partitioning เพิ่มความซับซ้อนในการจัดการฐานข้อมูลครับ ควรพิจารณาเมื่อตารางมีขนาดใหญ่มาก (หลายสิบล้านหรือหลายร้อยล้านแถว) และมีปัญหาด้านประสิทธิภาพที่ชัดเจน

4.4 Foreign Keys และ Constraint

Foreign Key Constraints ช่วยรักษาความสมบูรณ์ของข้อมูลเชิงสัมพันธ์ (referential integrity) ครับ

ประโยชน์:
- รับประกันว่าข้อมูลที่อ้างอิงถึงกันมีความถูกต้อง
- ช่วย Optimizer ในการตัดสินใจสร้าง Query Plan ที่ดีขึ้นในบางกรณี
ข้อเสีย:
- เพิ่ม Overheads ในการ INSERT, UPDATE, DELETE เนื่องจากต้องตรวจสอบ Constraint
- อาจทำให้เกิด Deadlock ได้หากไม่ระมัดระวังในการออกแบบ Transaction
คำแนะนำ: ควรใช้ Foreign Key เพื่อรักษาความสมบูรณ์ของข้อมูลเสมอ แต่ถ้าคุณพบปัญหาด้านประสิทธิภาพอย่างรุนแรงในการเขียนข้อมูล และมั่นใจว่าแอปพลิเคชันของคุณสามารถจัดการกับความสมบูรณ์ของข้อมูลได้เอง อาจพิจารณาปิด Foreign Key Constraint ชั่วคราวในระหว่างการโหลดข้อมูลจำนวนมาก หรือใช้ในบางตารางเท่านั้นครับ

4.5 ตัวอย่างการออกแบบตาราง

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- ใช้ UUID เป็น PK
    username VARCHAR(50) NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY, -- ใช้ BIGSERIAL สำหรับ PK ที่เป็นตัวเลข
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL, -- ใช้ NUMERIC สำหรับราคาเพื่อความแม่นยำ
    stock INT NOT NULL DEFAULT 0,
    category_id INT REFERENCES categories(category_id), -- Foreign Key
    specifications JSONB, -- ใช้ JSONB สำหรับข้อมูลกึ่งโครงสร้าง
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE, -- Foreign Key พร้อม onDelete
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    shipping_address JSONB
) PARTITION BY RANGE (order_date); -- ตัวอย่างการ Partitioning

CREATE TABLE orders_2023_q1 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

-- เพิ่ม Index ที่จำเป็น
CREATE INDEX idx_users_email_lower ON users (lower(email));
CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

5. VACUUM และ Autovacuum: กุญแจสู่สุขภาพฐานข้อมูล

VACUUM และ Autovacuum เป็นกระบวนการที่สำคัญที่สุดในการรักษาประสิทธิภาพและความสมบูรณ์ของฐานข้อมูล PostgreSQL ครับ หากไม่มีการ VACUUM ที่เหมาะสม ฐานข้อมูลของคุณจะประสบปัญหาประสิทธิภาพอย่างรุนแรง

5.1 MVCC และ Bloat

PostgreSQL ใช้สถาปัตยกรรม Multi-Version Concurrency Control (MVCC) ซึ่งหมายความว่าเมื่อมีการอัปเดตหรือลบข้อมูล แถวเดิมจะไม่ได้ถูกลบออกไปทันที แต่จะถูกทำเครื่องหมายว่าเป็น "dead tuple" และสร้างเวอร์ชันใหม่ขึ้นมาแทน (สำหรับการอัปเดต) ครับ

Dead Tuples: แถวที่ตายแล้วเหล่านี้ยังคงอยู่ในดิสก์จนกว่าจะถูกลบออกโดยกระบวนการ VACUUM
Bloat: หากมี Dead Tuples สะสมอยู่เป็นจำนวนมาก จะทำให้ตารางและ Index มีขนาดใหญ่เกินความจำเป็น (Bloat) ส่งผลให้:
- ใช้พื้นที่ดิสก์มากขึ้น
- Query ต้องอ่านข้อมูลจากดิสก์มากขึ้น ทำให้ช้าลง
- Cache Efficiency ลดลง
Transaction ID Wraparound: MVCC ยังเกี่ยวข้องกับ Transaction ID (XID) ที่มีจำนวนจำกัด หาก XID ไม่ถูก Freeze โดย VACUUM เป็นประจำ อาจเกิด "Transaction ID Wraparound" ซึ่งทำให้ฐานข้อมูลของคุณไม่สามารถรับการเปลี่ยนแปลงข้อมูลได้อีกต่อไป และอาจทำให้ฐานข้อมูลเสียหายได้ครับ

5.2 ความสำคัญของ Autovacuum

Autovacuum คือกระบวนการ Background ที่รัน VACUUM และ ANALYZE โดยอัตโนมัติเมื่อตรวจพบว่ามีการเปลี่ยนแปลงข้อมูลในตารางถึงเกณฑ์ที่กำหนดไว้ครับ มันเป็นสิ่งจำเป็นที่ต้องเปิดใช้งาน (autovacuum = on) และปรับแต่งให้เหมาะสม

VACUUM: ลบ Dead Tuples และกู้คืนพื้นที่ดิสก์ที่ถูกใช้งานโดย Dead Tuples ให้สามารถนำกลับมาใช้ใหม่ได้ (แต่ไม่ได้คืนพื้นที่ให้ OS ทันที) และ Freeze Transaction IDs
ANALYZE: เก็บสถิติการกระจายตัวของข้อมูลในตาราง ซึ่ง Optimizer ใช้ในการสร้าง Query Plan ที่มีประสิทธิภาพ

5.3 การปรับแต่ง Autovacuum

ดังที่ได้กล่าวไปในส่วนของ postgresql.conf พารามิเตอร์ Autovacuum มีความสำคัญอย่างยิ่งในการรักษาประสิทธิภาพครับ

autovacuum_max_workers: เพิ่มจำนวน Worker หากมีตารางขนาดใหญ่จำนวนมากที่มีการเปลี่ยนแปลงข้อมูลสูง
autovacuum_vacuum_scale_factor และ autovacuum_analyze_scale_factor: สำหรับตารางที่มีการเปลี่ยนแปลงข้อมูลบ่อยและมีขนาดใหญ่มาก การลดค่าเหล่านี้ลง (เช่น 0.1 หรือ 0.05) จะทำให้ Autovacuum ทำงานบ่อยขึ้นครับ
autovacuum_vacuum_cost_delay: การลดค่านี้ (เช่น 1ms หรือ 0ms) จะทำให้ Autovacuum ทำงานเร็วขึ้น แต่จะใช้ทรัพยากร CPU/I/O มากขึ้นครับ ควรลดลงอย่างระมัดระวังและเฝ้าระวังผลกระทบ
autovacuum_vacuum_cost_limit: การเพิ่มค่านี้จะช่วยให้ Autovacuum ทำงานได้นานขึ้นก่อนที่จะหยุดพัก

คุณยังสามารถกำหนดพารามิเตอร์ Autovacuum เฉพาะสำหรับแต่ละตารางได้ด้วย ALTER TABLE ครับ เช่น:

ALTER TABLE my_large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_cost_delay = 5);

5.4 VACUUM FULL (ควรระมัดระวัง)

VACUUM FULL จะทำการลบ Dead Tuples และกู้คืนพื้นที่ดิสก์ที่ว่างเปล่าให้กับระบบปฏิบัติการทันที (ลดขนาดไฟล์ตารางจริง) ครับ

ข้อดี: ลดขนาดของตารางและ Index ได้อย่างมีประสิทธิภาพ
ข้อเสีย:
- ต้องล็อกตารางแบบ Exclusive (Access Exclusive Lock) ซึ่งหมายความว่าตารางนั้นจะไม่สามารถถูกอ่านหรือเขียนได้ในระหว่างที่ VACUUM FULL ทำงาน (Downtime)
- ใช้เวลานานและใช้ทรัพยากรสูง
- เป็นการสร้างตารางและ Index ขึ้นมาใหม่ทั้งหมด
คำแนะนำ: ควรหลีกเลี่ยง VACUUM FULL ในสภาพแวดล้อม Production ที่ต้องการ Uptime สูง ควรใช้เมื่อตารางนั้นมี Bloat สูงมากจริงๆ และสามารถยอมรับ Downtime ได้ หากต้องการลด Bloat โดยไม่มี Downtime ให้พิจารณาใช้ pg_repack extension หรือการสร้างตารางใหม่และย้ายข้อมูลแทนครับ

5.5 ตัวอย่างการตรวจสอบ Bloat

คุณสามารถใช้ Query ต่อไปนี้เพื่อตรวจสอบ Bloat ในตารางและ Index ของคุณได้ครับ (อาจต้องใช้ pg_stat_statements หรือ pg_stat_user_tables)

-- ตรวจสอบ Bloat ในตาราง
SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    pg_size_pretty(pg_table_size(c.oid) - pg_relation_size(c.oid)) AS external_storage_size,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    n_dead_tuples AS dead_tuples,
    n_live_tuples AS live_tuples,
    ROUND(CAST(n_dead_tuples AS NUMERIC) / n_live_tuples * 100, 2) AS dead_to_live_ratio_pct,
    last_autovacuum,
    last_analyze
FROM
    pg_stat_user_tables c
ORDER BY
    dead_to_live_ratio_pct DESC
LIMIT 20;

-- ตรวจสอบ Bloat ใน Index
-- ต้องใช้ extension pgstattuple หรือเครื่องมือภายนอกเพื่อวิเคราะห์ Bloat ของ Index ได้อย่างแม่นยำ
-- แต่สามารถใช้ Query แบบง่ายๆ นี้เพื่อดูขนาดของ Index เทียบกับตารางได้
SELECT
    t.relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM
    pg_stat_user_indexes i
JOIN
    pg_stat_user_tables t ON i.relid = t.relid
ORDER BY
    pg_relation_size(i.indexrelid) DESC
LIMIT 20;

สำหรับเครื่องมือที่ซับซ้อนขึ้นในการตรวจสอบ Bloat คุณสามารถดูได้ที่ อ่านเพิ่มเติมเกี่ยวกับ pg_repack และ Bloat ครับ

6. การเฝ้าระวังและการวินิจฉัย (Monitoring and Diagnostics)

การปรับแต่งประสิทธิภาพไม่ใช่เรื่องของการตั้งค่าครั้งเดียวแล้วจบไปครับ คุณต้องเฝ้าระวังและวิเคราะห์อย่างต่อเนื่องเพื่อระบุปัญหาและปรับปรุงแก้ไขอยู่เสมอ PostgreSQL มีเครื่องมือและวิว (views) ในตัวที่ช่วยในการเฝ้าระวังได้เป็นอย่างดี

6.1 pg_stat_statements

pg_stat_statements เป็น Extension ที่ทรงพลังที่สุดในการระบุ Query ที่ทำงานช้าที่สุดและใช้ทรัพยากรมากที่สุดครับ มันจะบันทึกสถิติการทำงานของทุก Query ที่รันบนฐานข้อมูล

การติดตั้ง:
1. เพิ่ม pg_stat_statements ลงใน shared_preload_libraries ใน postgresql.conf (ต้องรีสตาร์ท PostgreSQL)
```
shared_preload_libraries = 'pg_stat_statements'
```
2. สร้าง Extension ในฐานข้อมูล:
```
CREATE EXTENSION pg_stat_statements;
```
การใช้งาน:
```
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read + 1) AS hit_ratio,
    temp_blks_read,
    temp_blks_written
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;
```
- query: ข้อความ Query
- calls: จำนวนครั้งที่ Query ถูกเรียก
- total_time: เวลาทั้งหมดที่ Query นี้ใช้ในการรัน (มิลลิวินาที)
- mean_time: เวลาเฉลี่ยต่อการรันหนึ่งครั้ง
- rows: จำนวนแถวทั้งหมดที่ Query นี้คืนค่ามา
- hit_ratio: อัตราการอ่านจาก Cache (ยิ่งสูงยิ่งดี)
- temp_blks_read, temp_blks_written: บ่งชี้ว่า Query นี้ต้องเขียนข้อมูลชั่วคราวลงดิสก์หรือไม่ (อาจหมายถึง work_mem ไม่เพียงพอ)
การรีเซ็ตสถิติ: SELECT pg_stat_statements_reset();

6.2 pg_stat_activity

pg_stat_activity แสดงข้อมูลเกี่ยวกับกระบวนการทำงานที่กำลังทำงานอยู่ทั้งหมดใน PostgreSQL ครับ มีประโยชน์มากในการตรวจสอบ Query ที่กำลังทำงานอยู่แบบเรียลไทม์

SELECT
    pid,
    datname,
    usename,
    client_addr,
    application_name,
    backend_start,
    query_start,
    state,
    wait_event_type,
    wait_event,
    query
FROM
    pg_stat_activity
WHERE
    state = 'active'
ORDER BY
    query_start;

สิ่งที่ควรสังเกต:

Long-running queries: Query ที่มี query_start นานมาก
Waiting state: ถ้า state เป็น 'waiting' หรือมี wait_event_type และ wait_event ที่บ่งชี้ถึงการรอ Lock หรือ I/O
Idle in transaction: เซสชันที่เปิด Transaction ทิ้งไว้แต่ไม่ได้ทำงานอะไร ซึ่งอาจกักเก็บ Lock และป้องกัน Autovacuum

6.3 การบันทึก Slow Queries

คุณสามารถตั้งค่าให้ PostgreSQL บันทึก Query ที่ใช้เวลานานเกินกว่าเกณฑ์ที่กำหนดลงใน Log File ได้โดยใช้พารามิเตอร์ log_min_duration_statement ใน postgresql.conf

log_min_duration_statement = 250ms # บันทึก Query ที่ใช้เวลานานกว่า 250 มิลลิวินาที

การตรวจสอบ Log File เป็นประจำจะช่วยให้คุณระบุ Query ที่มีปัญหาได้อย่างรวดเร็วครับ

6.4 เครื่องมืออื่นๆ

pg_buffercache: Extension ที่ช่วยให้คุณตรวจสอบว่าข้อมูลใดถูกแคชอยู่ใน shared_buffers
pg_top / pg_activity: เครื่องมือคล้าย top ของ Linux แต่เฉพาะสำหรับ PostgreSQL แสดงข้อมูลเกี่ยวกับกระบวนการทำงาน การใช้งาน CPU/Memory
system monitoring tools: เช่น Prometheus + Grafana, Zabbix สำหรับการเฝ้าระวังทรัพยากรฮาร์ดแวร์ (CPU, RAM, Disk I/O, Network) และเมตริกของ PostgreSQL

7. การปรับแต่งระดับ Hardware และ OS

แม้ว่าการปรับแต่ง PostgreSQL จะเป็นสิ่งสำคัญ แต่ประสิทธิภาพของฐานข้อมูลก็ขึ้นอยู่กับฮาร์ดแวร์และระบบปฏิบัติการที่รองรับด้วยครับ

7.1 I/O Subsystem

ระบบ I/O เป็นคอขวดที่พบบ่อยที่สุดสำหรับฐานข้อมูลครับ

SSD (Solid State Drives): ควรใช้ SSD เสมอสำหรับ PostgreSQL Production Workload โดยเฉพาะอย่างยิ่ง NVMe SSDs เพื่อประสิทธิภาพ I/O ที่เหนือกว่า HDD อย่างมาก
RAID Configuration:
- RAID 10: เป็นตัวเลือกที่ดีที่สุดสำหรับฐานข้อมูล ให้ทั้งประสิทธิภาพ (จากการ Stripping) และความทนทานต่อข้อมูลสูญหาย (จากการ Mirroring)
- RAID 5/6: อาจมีปัญหาด้านประสิทธิภาพการเขียน (write penalty) และการกู้คืน (rebuild time) ที่ยาวนานกว่า RAID 10
Filesystem: ควรใช้ Filesystem ที่ทันสมัยและมีประสิทธิภาพ เช่น XFS หรือ ext4 โดย XFS มักจะแนะนำสำหรับ workload ที่มี I/O หนักครับ

7.2 RAM และ CPU

RAM: ยิ่งมี RAM มากเท่าไหร่ PostgreSQL ก็ยิ่งสามารถแคชข้อมูลได้มากขึ้นเท่านั้น ซึ่งช่วยลดการเข้าถึงดิสก์ได้อย่างมาก ควรมี RAM เพียงพอสำหรับ shared_buffers, OS page cache และ work_mem ของ Query พร้อมกันหลายๆ ตัว
CPU: PostgreSQL สามารถใช้ประโยชน์จาก Multi-core CPU ได้ดี โดยแต่ละ Backend Process (การเชื่อมต่อ) จะใช้ Core ของตัวเอง CPU ที่มี Clock Speed สูง มักจะให้ประสิทธิภาพที่ดีกว่า CPU ที่มี Core เยอะแต่ Clock Speed ต่ำกว่าสำหรับ workload ที่เน้น Transaction จำนวนมากครับ

7.3 การปรับแต่งระดับ OS

Huge Pages (Linux): การเปิดใช้งาน Huge Pages (Transparent Huge Pages หรือ Static Huge Pages) สามารถช่วยลด Overhead ในการจัดการหน่วยความจำสำหรับ PostgreSQL ได้ โดยเฉพาะอย่างยิ่งเมื่อ shared_buffers มีขนาดใหญ่
Swappiness: ควรตั้งค่า vm.swappiness ให้น้อยที่สุด (เช่น 1
บทความที่เกี่ยวข้อง
SiamCafe.net — ชุมชน IT ที่ใหญ่ที่สุด · Siam2R.com — Portfolio งาน IT