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

ในโลกของการพัฒนาแอปพลิเคชันและระบบฐานข้อมูลที่ขับเคลื่อนด้วยข้อมูล (data-driven) ประสิทธิภาพของฐานข้อมูลถือเป็นหัวใจสำคัญที่ไม่อาจมองข้ามได้ครับ โดยเฉพาะอย่างยิ่งกับ PostgreSQL ซึ่งเป็นฐานข้อมูลเชิงวัตถุ-สัมพันธ์ (Object-Relational Database) แบบ Open Source ที่ได้รับความนิยมอย่างแพร่หลาย ด้วยความสามารถที่ทรงพลัง ความยืดหยุ่นสูง และฟีเจอร์ที่ครบครัน ทำให้ PostgreSQL เป็นตัวเลือกอันดับต้นๆ สำหรับโปรเจกต์หลากหลายขนาด ตั้งแต่แอปพลิเคชันขนาดเล็กไปจนถึงระบบระดับองค์กรขนาดใหญ่ แต่ถึงแม้ PostgreSQL จะแข็งแกร่งเพียงใด หากขาดการปรับแต่งที่เหมาะสม ประสิทธิภาพที่แท้จริงของมันก็อาจไม่ถูกดึงออกมาใช้ได้อย่างเต็มที่ และอาจนำไปสู่ปัญหาคอขวด (bottleneck) ที่ส่งผลกระทบต่อความเร็วในการตอบสนองของระบบโดยรวมได้ครับ บทความนี้จะพาทุกท่านดำดิ่งสู่โลกของการปรับแต่งประสิทธิภาพ PostgreSQL อย่างเจาะลึก ตั้งแต่หลักการพื้นฐานไปจนถึงเทคนิคขั้นสูง เพื่อให้ฐานข้อมูลของท่านทำงานได้อย่างรวดเร็ว มีเสถียรภาพ และรองรับปริมาณงานที่เพิ่มขึ้นได้อย่างมีประสิทธิภาพสูงสุดครับ

สารบัญ

• ทำไมการปรับแต่ง PostgreSQL จึงสำคัญ?
• การตรวจสอบและวิเคราะห์ประสิทธิภาพ (Monitoring & Diagnostics)
  • pg_stat_activity: ตรวจสอบการทำงานของเซสชัน
  • pg_stat_statements: วิเคราะห์ประสิทธิภาพของคำสั่ง SQL
  • EXPLAIN ANALYZE: เข้าใจแผนการทำงานของ Query
  • การตรวจสอบประสิทธิภาพระดับระบบปฏิบัติการ (OS Level Monitoring)
• การปรับแต่งพารามิเตอร์ใน postgresql.conf
  • พารามิเตอร์เกี่ยวกับการจัดการหน่วยความจำ (Memory Management)
    • shared_buffers
    • work_mem
    • maintenance_work_mem
    • effective_cache_size
    • wal_buffers
  • พารามิเตอร์เกี่ยวกับการจัดการ I/O และ WAL
    • fsync และ synchronous_commit
    • full_page_writes
    • checkpoint_timeout และ max_wal_size
  • พารามิเตอร์สำหรับ Query Planner
    • random_page_cost, seq_page_cost, cpu_tuple_cost, etc.
  • พารามิเตอร์สำหรับการปรับแต่ง Autovacuum
  • พารามิเตอร์สำหรับการบันทึก Log
  • พารามิเตอร์การเชื่อมต่อ (Connection Parameters)
• การปรับแต่ง Index ให้เหมาะสม
  • ประเภทของ Index และการเลือกใช้งาน
  • การสร้าง Index อย่างมีประสิทธิภาพ
  • การระบุ Index ที่ขาดหายไปหรือไม่ถูกใช้งาน
  • การ Reindex และการลด Index Bloat
• การปรับแต่ง Query (SQL Optimization)
  • เจาะลึก EXPLAIN ANALYZE อีกครั้ง
  • เทคนิคการเขียน Query ที่มีประสิทธิภาพ
  • หลีกเลี่ยง SELECT *
  • การใช้ JOINs อย่างชาญฉลาด
  • Subqueries vs. CTEs
  • การใช้ Window Functions
• การออกแบบ Schema ฐานข้อมูล
  • Normalization vs. Denormalization
  • การเลือกใช้ Data Type ที่เหมาะสม
  • การทำ Partitioning
  • Foreign Keys และผลกระทบต่อประสิทธิภาพ
• VACUUM และ Autovacuum: การจัดการ Bloat และ MVCC
  • ทำความเข้าใจ MVCC และ Bloat
  • การทำงานของ VACUUM
  • การปรับแต่ง Autovacuum
  • VACUUM FULL: เมื่อจำเป็นต้องใช้
• การปรับแต่งระดับ Hardware และ OS
  • Disk I/O: SSD และ RAID Configuration
  • RAM และ CPU
  • การปรับแต่งระดับ OS (เช่น vm.swappiness)
  • การเลือก Filesystem
• Connection Pooling: จัดการการเชื่อมต่ออย่างมีประสิทธิภาพ
  • PgBouncer
  • Odyssey
• คำถามที่พบบ่อย (FAQ)
• สรุปและ Call-to-Action

ทำไมการปรับแต่ง PostgreSQL จึงสำคัญ?

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

การปรับแต่งประสิทธิภาพ PostgreSQL จึงเป็นกระบวนการที่สำคัญเพื่อ:

• เพิ่มความเร็วในการตอบสนอง (Response Time): แอปพลิเคชันจะทำงานได้เร็วขึ้น ผู้ใช้จะได้รับประสบการณ์ที่ดีขึ้นครับ
• รองรับปริมาณงานที่สูงขึ้น (Scalability): ฐานข้อมูลสามารถจัดการกับจำนวนผู้ใช้งานและข้อมูลที่เพิ่มขึ้นได้โดยไม่เกิดปัญหาคอขวดครับ
• ลดการใช้ทรัพยากร (Resource Utilization): ใช้ CPU, RAM, Disk I/O และ Network ได้อย่างมีประสิทธิภาพสูงสุด ลดต้นทุนในการดำเนินการครับ
• เพิ่มความเสถียร (Stability): ลดโอกาสที่ฐานข้อมูลจะทำงานช้าลงหรือหยุดทำงานเมื่อมีปริมาณงานสูงครับ
• ยืดอายุการใช้งานของฮาร์ดแวร์: การทำงานที่เหมาะสมช่วยลดภาระที่ไม่จำเป็นบนฮาร์ดแวร์ครับ

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

การตรวจสอบและวิเคราะห์ประสิทธิภาพ (Monitoring & Diagnostics)

ก่อนที่เราจะเริ่มปรับแต่งสิ่งใดๆ เราจำเป็นต้องรู้ก่อนว่าปัญหาอยู่ที่ไหนและอะไรคือสาเหตุที่แท้จริงครับ การตรวจสอบและการวินิจฉัยเป็นขั้นตอนแรกและสำคัญที่สุดในการปรับแต่งประสิทธิภาพ PostgreSQL เครื่องมือและเทคนิคต่างๆ จะช่วยให้เรามองเห็นภาพรวมและเจาะลึกถึงรายละเอียดของปัญหาได้ครับ

pg_stat_activity: ตรวจสอบการทำงานของเซสชัน

pg_stat_activity คือมุมมอง (view) ที่แสดงข้อมูลเกี่ยวกับเซสชัน (connection) ทั้งหมดที่กำลังทำงานอยู่บน PostgreSQL ครับ มันเป็นเครื่องมือพื้นฐานแต่ทรงพลังในการตรวจสอบสถานะของฐานข้อมูลแบบเรียลไทม์ เราสามารถเห็นได้ว่า query ใดกำลังทำงานอยู่, ใครเป็นผู้รัน, สถานะเป็นอย่างไร, และใช้เวลานานเท่าไหร่แล้ว

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

จากผลลัพธ์นี้ เราสามารถระบุ:

• Query ที่รันนานผิดปกติ: มองหา query_start ที่นานแล้ว หรือ query ที่มีขนาดใหญ่และซับซ้อนครับ
• Query ที่ติดสถานะรอ (Waiting): ดูที่ wait_event_type และ wait_event ครับ ถ้าเห็น Lock หรือ IO บ่อยๆ อาจบ่งบอกถึงปัญหาการ deadlock หรือ I/O bottleneck ครับ
• การเชื่อมต่อที่ไม่ได้ใช้งาน (Idle connections): แม้จะไม่ได้เป็นปัญหาโดยตรง แต่อาจกินทรัพยากรการเชื่อมต่อได้ครับ

pg_stat_statements: วิเคราะห์ประสิทธิภาพของคำสั่ง SQL

pg_stat_statements เป็นส่วนขยาย (extension) ที่ยอดเยี่ยมสำหรับการรวบรวมสถิติประสิทธิภาพของคำสั่ง SQL ทั้งหมดที่ถูกรันบนฐานข้อมูล มันจะรวมสถิติของ query ที่เหมือนกันเข้าด้วยกัน ช่วยให้เราเห็นภาพรวมว่า query ใดใช้ทรัพยากรมากที่สุดโดยไม่จำเป็นต้องตรวจสอบทีละเซสชันครับ

ก่อนใช้งาน ต้องเปิดใช้งาน pg_stat_statements ใน postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 # จำนวน query ที่จะเก็บสถิติ
pg_stat_statements.track = all # เก็บสถิติทุก query

หลังจากรีสตาร์ท PostgreSQL แล้ว ให้รันคำสั่งนี้เพื่อสร้าง extension:

CREATE EXTENSION pg_stat_statements;

จากนั้น เราสามารถเรียกดูสถิติได้:

SELECT
    query,
    calls,
    total_time,
    mean_time,
    min_time,
    max_time,
    blk_read_time + blk_write_time AS total_io_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;

ผลลัพธ์นี้จะช่วยให้เรา:

• ระบุ Query ที่ทำงานช้าที่สุด: ดู total_time หรือ mean_time ที่สูงๆ ครับ
• ระบุ Query ที่ถูกเรียกใช้บ่อยที่สุด: ดู calls ครับ
• ระบุ Query ที่สร้างภาระ I/O สูง: ดู total_io_time ครับ

การรีเซ็ตสถิติ pg_stat_statements สามารถทำได้ด้วย SELECT pg_stat_statements_reset(); ครับ

EXPLAIN ANALYZE: เข้าใจแผนการทำงานของ Query

เมื่อเราพบ query ที่มีปัญหาจาก pg_stat_activity หรือ pg_stat_statements ขั้นตอนต่อไปคือการใช้ EXPLAIN ANALYZE เพื่อดูว่า PostgreSQL วางแผนที่จะรัน query นั้นอย่างไร และจริงๆ แล้วมันรันอย่างไรครับ

• EXPLAIN: แสดงแผนการทำงานที่ Query Planner ของ PostgreSQL คาดการณ์ไว้
• EXPLAIN ANALYZE: แสดงแผนการทำงานจริง พร้อมทั้งสถิติเวลาที่ใช้ไปในแต่ละขั้นตอน, จำนวนแถวที่ประมวลผล, และข้อมูล I/O จริง

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'Thailand' AND o.order_date > '2023-01-01'
ORDER BY o.total_amount DESC
LIMIT 10;

การอ่านผลลัพธ์ของ EXPLAIN ANALYZE เป็นทักษะสำคัญที่ต้องฝึกฝนครับ สิ่งที่ควรมองหาคือ:

• Scan Type: Seq Scan (Full Table Scan) มักจะช้ากว่า Index Scan หรือ Bitmap Heap Scan ครับ
• Cost และ Rows: ค่า cost ที่สูงแสดงถึงการทำงานที่หนัก rows คือจำนวนแถวที่คาดการณ์/จริง
• Actual Time vs. Expected Time: หากเวลาจริง (actual time) สูงกว่าที่คาดการณ์ไว้ (estimated cost) มาก แสดงว่า Planner อาจประเมินผิดพลาด ซึ่งอาจเกิดจากสถิติของตารางไม่อัปเดตครับ
• Buffers: แสดงจำนวนบล็อกข้อมูลที่อ่านจาก Shared Buffers (hit) และจากดิสก์ (read) ถ้า read สูง แสดงว่ามีการ I/O มากครับ
• Sorts และ Hash Joins: การ Sort หรือ Hash Join ที่ต้องทำบนดิสก์ (Disk-based) จะช้ากว่าการทำในหน่วยความจำ (Memory-based) ครับ

การทำความเข้าใจแผนการทำงานจะช่วยให้เราตัดสินใจได้ว่าจะต้องเพิ่ม Index, ปรับแต่ง Query, หรืออัปเดตสถิติของตารางครับ

การตรวจสอบประสิทธิภาพระดับระบบปฏิบัติการ (OS Level Monitoring)

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

• top / htop: ตรวจสอบการใช้ CPU, RAM, และกระบวนการที่กำลังทำงานอยู่ครับ
• iostat: ตรวจสอบ I/O ของดิสก์ (เช่น จำนวน I/O operations ต่อวินาที, เวลาเฉลี่ยในการ I/O) ครับ
• vmstat: ตรวจสอบหน่วยความจำเสมือน (virtual memory), โปรเซส, I/O, CPU activity ครับ
• netstat: ตรวจสอบสถานะการเชื่อมต่อเครือข่ายครับ

ข้อมูลเหล่านี้จะช่วยให้เราประเมินได้ว่าปัญหาคอขวดเกิดจากทรัพยากรฮาร์ดแวร์ไม่เพียงพอหรือไม่ เช่น CPU ใช้งาน 100% ตลอดเวลา, RAM ใกล้เต็มและเกิดการ swap, หรือดิสก์มี I/O latency สูงครับ

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

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

พารามิเตอร์เกี่ยวกับการจัดการหน่วยความจำ (Memory Management)

การจัดการหน่วยความจำเป็นสิ่งสำคัญที่สุดอย่างหนึ่งในการปรับแต่ง PostgreSQL ครับ

shared_buffers

• คำอธิบาย: นี่คือปริมาณหน่วยความจำที่ PostgreSQL ใช้สำหรับแคชข้อมูล (data cache) ครับ ข้อมูลที่ถูกอ่านจากดิสก์จะถูกเก็บไว้ใน shared_buffers เพื่อให้สามารถเข้าถึงได้อย่างรวดเร็วในการเรียกใช้งานครั้งต่อไปครับ
• คำแนะนำ: โดยทั่วไปแล้ว ควรตั้งค่า shared_buffers ไว้ที่ 25% ของ RAM ทั้งหมดในเซิร์ฟเวอร์ครับ สำหรับเซิร์ฟเวอร์ที่มี RAM มาก (เช่น 64GB ขึ้นไป) อาจจะตั้งค่าที่ 15-20% ก็เพียงพอ เนื่องจากส่วนที่เหลือของ RAM จะถูกใช้โดย OS cache ครับ การตั้งค่าสูงเกินไปอาจทำให้เกิดปัญหา double caching กับ OS cache ได้ครับ
• ตัวอย่าง: shared_buffers = 8GB (สำหรับเซิร์ฟเวอร์ที่มี RAM 32GB)

work_mem

• คำอธิบาย: คือปริมาณหน่วยความจำที่แต่ละเซสชันสามารถใช้ได้สำหรับปฏิบัติการภายในที่ต้องใช้หน่วยความจำชั่วคราว เช่น การ Sorting (ORDER BY, GROUP BY) หรือ Hash Tables ครับ
• คำแนะนำ: ค่าเริ่มต้นมักจะต่ำเกินไป (เช่น 4MB) ครับ ถ้า Query ของคุณมีการ Sort หรือ Hash Join ขนาดใหญ่ และคุณเห็นว่ามีการใช้ Disk-based Sorts/Hashes ใน EXPLAIN ANALYZE คุณควรเพิ่มค่านี้ครับ แต่โปรดระวัง เพราะนี่คือหน่วยความจำ ต่อเซสชัน ดังนั้นถ้ามี 100 เซสชันที่รัน Query ที่ต้องใช้ work_mem พร้อมกัน และตั้งค่าไว้ที่ 100MB ก็จะใช้ RAM ถึง 10GB ครับ การปรับเพิ่มควรเป็นแบบค่อยเป็นค่อยไป และตรวจสอบผลกระทบด้วยครับ
• ตัวอย่าง: work_mem = 64MB หรือ 128MB

maintenance_work_mem

• คำอธิบาย: คล้ายกับ work_mem แต่ใช้สำหรับงานบำรุงรักษา เช่น VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY ครับ
• คำแนะนำ: งานเหล่านี้มักจะรันเป็นครั้งคราว แต่ต้องการหน่วยความจำจำนวนมากเพื่อทำงานได้อย่างรวดเร็วครับ การตั้งค่าที่สูงขึ้นจะช่วยให้งานเหล่านี้เสร็จเร็วขึ้นและลดภาระ I/O ครับ แนะนำให้ตั้งค่าที่ 10-25% ของ RAM ทั้งหมด (แต่ไม่ควรเกิน 1-2GB สำหรับเซิร์ฟเวอร์ส่วนใหญ่)
• ตัวอย่าง: maintenance_work_mem = 1GB

effective_cache_size

• คำอธิบาย: ไม่ได้จัดสรรหน่วยความจำจริง แต่เป็นคำแนะนำสำหรับ Query Planner ว่ามีหน่วยความจำทั้งหมดเท่าไหร่ที่ระบบปฏิบัติการสามารถใช้สำหรับแคชข้อมูลบนดิสก์ รวมถึง shared_buffers ด้วยครับ Planner จะใช้ค่านี้ในการตัดสินใจว่า Index Scan หรือ Sequential Scan จะมีประสิทธิภาพมากกว่ากันครับ
• คำแนะนำ: ควรตั้งค่านี้ให้สูงกว่า shared_buffers เสมอ โดยทั่วไปคือ 50-75% ของ RAM ทั้งหมดในเซิร์ฟเวอร์ครับ
• ตัวอย่าง: effective_cache_size = 24GB (สำหรับเซิร์ฟเวอร์ที่มี RAM 32GB)

wal_buffers

• คำอธิบาย: คือปริมาณหน่วยความจำที่ใช้สำหรับบัฟเฟอร์ Write-Ahead Log (WAL) ครับ WAL คือบันทึกการเปลี่ยนแปลงทั้งหมดในฐานข้อมูล เพื่อใช้ในการกู้คืนข้อมูลและ Replication ครับ
• คำแนะนำ: ค่าเริ่มต้น -1 (256KB) มักจะเพียงพอแล้วครับ การตั้งค่าสูงขึ้นอาจช่วยลด I/O เล็กน้อยในระบบที่มี workload สูงมากๆ แต่ก็เพิ่มความเสี่ยงในการสูญเสียข้อมูลเล็กน้อยหากระบบล่มก่อนที่จะถูกเขียนลงดิสก์ครับ
• ตัวอย่าง: wal_buffers = 16MB (ไม่ค่อยจำเป็นต้องปรับเยอะ)

พารามิเตอร์เกี่ยวกับการจัดการ I/O และ WAL

fsync และ synchronous_commit

• คำอธิบาย:
  • fsync: กำหนดว่าจะบังคับให้ OS เขียน WAL ลงดิสก์จริงหรือไม่ครับ การปิด fsync จะเพิ่มความเร็ว แต่มีความเสี่ยงสูงมากที่จะข้อมูลเสียหายหรือสูญหายหากระบบล่มครับ ไม่แนะนำให้ปิดใน Production ครับ
  • synchronous_commit: ควบคุมว่าการ commit transaction จะรอจนกว่า WAL จะถูกเขียนลงดิสก์อย่างถาวรหรือไม่ครับ
• คำแนะนำ:
  • fsync = on (ค่าเริ่มต้นและค่าที่แนะนำสำหรับ Production)
  • synchronous_commit = on (ค่าเริ่มต้นและค่าที่แนะนำสำหรับ Production เพื่อความปลอดภัยของข้อมูลสูงสุด) หากต้องการประสิทธิภาพที่สูงขึ้นเล็กน้อยและยอมรับความเสี่ยงในการสูญเสียข้อมูลจากการ commit ครั้งสุดท้าย (โดยเฉพาะในระบบที่ไม่ใช่ critical) อาจตั้งเป็น off หรือ local ได้ครับ แต่ต้องเข้าใจความเสี่ยงอย่างถ่องแท้

full_page_writes

• คำอธิบาย: เมื่อ PostgreSQL เขียนข้อมูลจาก Shared Buffers ลงดิสก์เป็นครั้งแรกหลังจากการทำ Checkpoint ระบบจะเขียนทั้งหน้า (page) ลงไปใน WAL เพื่อให้แน่ใจว่าการกู้คืนข้อมูลจะสมบูรณ์ในกรณีที่เกิด partial page write ครับ
• คำแนะนำ: ควรเปิดไว้ (on) เสมอเพื่อความปลอดภัยของข้อมูลครับ การปิดจะเพิ่มความเร็วเล็กน้อย แต่มีความเสี่ยงสูงมากที่จะข้อมูลเสียหายครับ

checkpoint_timeout และ max_wal_size

• คำอธิบาย:
  • checkpoint_timeout: กำหนดช่วงเวลาสูงสุดระหว่าง Checkpoint ครับ Checkpoint คือกระบวนการที่ PostgreSQL เขียนข้อมูลที่แก้ไขใน Shared Buffers ลงดิสก์อย่างถาวรครับ
  • max_wal_size: กำหนดขนาด WAL สูงสุดที่สามารถสร้างได้ระหว่าง Checkpoint ครับ
• คำแนะนำ: การทำ Checkpoint เป็นงานที่ใช้ I/O สูงและอาจทำให้เกิด Spike ใน Disk I/O ได้ครับ เป้าหมายคือทำให้ Checkpoint เกิดขึ้นบ่อยพอที่จะกู้คืนได้เร็ว แต่ไม่บ่อยเกินไปจนเป็นภาระครับ
  • checkpoint_timeout = 10min (ค่าเริ่มต้น) สามารถเพิ่มเป็น 30min ถึง 1h ได้ หากระบบมี I/O workload สูง เพื่อลดความถี่ของ Checkpoint ครับ
  • max_wal_size = 1GB (ค่าเริ่มต้น) สามารถเพิ่มเป็น 4GB, 8GB หรือมากกว่านั้นได้ เพื่อให้มี WAL มากพอที่จะครอบคลุมช่วงเวลาระหว่าง Checkpoint ที่ยาวขึ้นครับ

  โดยทั่วไปแล้ว ควรพยายามให้ Checkpoint เกิดขึ้นไม่บ่อยกว่า 5 นาทีต่อครั้ง และไม่เกิน 1 ชั่วโมงต่อครั้งครับ และควรสังเกต checkpoint_timings ใน Log เพื่อปรับแต่งครับ

พารามิเตอร์สำหรับ Query Planner

พารามิเตอร์เหล่านี้ช่วยให้ Query Planner ประเมินค่าใช้จ่ายในการเข้าถึงข้อมูลประเภทต่างๆ ได้อย่างถูกต้องครับ

random_page_cost, seq_page_cost, cpu_tuple_cost, etc.

• คำอธิบาย:
  • seq_page_cost: ค่าใช้จ่ายในการอ่านหนึ่งหน้าข้อมูลแบบ Sequential (จากดิสก์) ครับ
  • random_page_cost: ค่าใช้จ่ายในการอ่านหนึ่งหน้าข้อมูลแบบ Random (จากดิสก์) ครับ
  • cpu_tuple_cost: ค่าใช้จ่ายในการประมวลผลหนึ่งแถวข้อมูลครับ
  • cpu_index_tuple_cost: ค่าใช้จ่ายในการประมวลผลหนึ่งแถวที่ได้จาก Index Scan ครับ
  • cpu_operator_cost: ค่าใช้จ่ายในการประมวลผลหนึ่ง Operator (เช่น +, -, =, >) ครับ
• คำแนะนำ:
  • ค่าเริ่มต้น seq_page_cost = 1.0 และ random_page_cost = 4.0 ครับ
  • ถ้าใช้ SSDs ควรลด random_page_cost ให้ใกล้เคียงกับ seq_page_cost มากขึ้น (เช่น random_page_cost = 1.1 หรือ 1.5) เพื่อให้ Planner เลือก Index Scan บ่อยขึ้น เนื่องจาก SSDs มี Random I/O ที่เร็วมากครับ
  • หาก CPU ของคุณแรงมาก อาจลด cpu_tuple_cost และ cpu_operator_cost ลงได้เล็กน้อยครับ

พารามิเตอร์สำหรับการปรับแต่ง Autovacuum

Autovacuum เป็นกระบวนการที่สำคัญมากในการรักษาประสิทธิภาพและความสมบูรณ์ของ PostgreSQL ครับ การปรับแต่งที่ถูกต้องจะช่วยลด Bloat และป้องกัน Transaction ID Wraparound ได้ครับ เราจะเจาะลึกเรื่องนี้ในส่วนของ VACUUM ครับ แต่พารามิเตอร์หลักๆ ที่ควรพิจารณาคือ:

• autovacuum = on (ควรเปิดไว้เสมอ)
• autovacuum_max_workers: จำนวน Worker สูงสุดที่ Autovacuum สามารถรันพร้อมกันได้ครับ (ค่าเริ่มต้น 3)
• autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor: กำหนดว่าจะเริ่ม Vacuum/Analyze เมื่อมีเปอร์เซ็นต์ของแถวที่เปลี่ยนแปลงไปเท่าไหร่ครับ
• autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit: ควบคุมความเร็วของ Autovacuum เพื่อไม่ให้กินทรัพยากรมากเกินไปครับ

พารามิเตอร์สำหรับการบันทึก Log

Log ที่มีรายละเอียดเพียงพอจะช่วยในการวินิจฉัยปัญหาประสิทธิภาพได้ครับ

• log_min_duration_statement: บันทึก Query ทั้งหมดที่ใช้เวลานานกว่าค่าที่กำหนดครับ (เช่น log_min_duration_statement = 500ms จะบันทึก Query ที่ใช้เวลาเกิน 500 มิลลิวินาที) เป็นเครื่องมือที่ดีในการระบุ Query ที่ช้าครับ
• log_lock_waits = on: บันทึกเมื่อ Query ต้องรอ Lock เกินกว่า deadlock_timeout ครับ
• log_connections = on, log_disconnections = on: บันทึกการเชื่อมต่อและตัดการเชื่อมต่อ ซึ่งอาจมีประโยชน์ในการตรวจสอบพฤติกรรมการเชื่อมต่อของแอปพลิเคชันครับ

พารามิเตอร์การเชื่อมต่อ (Connection Parameters)

• max_connections: จำนวนการเชื่อมต่อสูงสุดที่ PostgreSQL ยอมรับได้ครับ การตั้งค่าสูงเกินไปอาจทำให้แต่ละการเชื่อมต่อกินหน่วยความจำมากเกินไปจนระบบล่มได้ครับ
• คำแนะนำ: ตั้งค่าให้เหมาะสมกับจำนวนการเชื่อมต่อที่แอปพลิเคชันของคุณต้องการจริงๆ และเผื่อไว้สำหรับแอดมินและการเชื่อมต่อภายในครับ หากใช้ Connection Pooler (เช่น PgBouncer) คุณสามารถตั้งค่า max_connections ใน PostgreSQL ให้ต่ำลงได้ และจัดการการเชื่อมต่อที่ PgBouncer แทนครับ

การปรับแต่ง Index ให้เหมาะสม

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

ประเภทของ Index และการเลือกใช้งาน

PostgreSQL มี Index หลายประเภท แต่ละประเภทเหมาะสำหรับกรณีการใช้งานที่แตกต่างกันครับ

การสร้าง Index อย่างมีประสิทธิภาพ

• Composite Indexes: Index ที่สร้างจากหลายคอลัมน์ (e.g., CREATE INDEX ON users (last_name, first_name);) มีประโยชน์เมื่อ Query ของคุณมักจะใช้คอลัมน์เหล่านี้ร่วมกันใน WHERE clause ครับ ลำดับของคอลัมน์ใน Index มีความสำคัญครับ คอลัมน์ที่ใช้ในการกรองข้อมูลหลักควรอยู่ข้างหน้าครับ
• Partial Indexes: Index ที่ครอบคลุมเฉพาะส่วนหนึ่งของข้อมูลในตาราง (e.g., CREATE INDEX ON orders (order_status) WHERE order_status = 'pending';) มีประโยชน์เมื่อคุณมีข้อมูลจำนวนมาก แต่ Query ส่วนใหญ่สนใจแค่บางส่วนของข้อมูลเท่านั้น ช่วยลดขนาด Index และเพิ่มความเร็วในการสร้าง/อัปเดต Index ครับ
• Expression Indexes: Index ที่สร้างจากผลลัพธ์ของ Expression หรือ Function (e.g., CREATE INDEX ON users (lower(email));) มีประโยชน์เมื่อคุณใช้ Function ใน WHERE clause บ่อยๆ ครับ
• INCLUDE Clause (Covering Indexes): PostgreSQL 11+ ช่วยให้คุณสามารถเพิ่มคอลัมน์ลงใน Index โดยที่ไม่ต้องรวมอยู่ใน Key หลักได้ (e.g., CREATE INDEX ON products (category_id) INCLUDE (price, name);) ทำให้ Query ที่เลือกเฉพาะคอลัมน์ที่อยู่ใน Index (ทั้ง Key และ Included) สามารถทำงานได้โดยไม่ต้องไปอ่านข้อมูลจากตารางจริง (Index Only Scan) ช่วยลด I/O ได้มากครับ

-- ตัวอย่างการสร้าง Index แบบ Composite
CREATE INDEX idx_customer_country_region ON customers (country, region);

-- ตัวอย่างการสร้าง Index แบบ Partial
CREATE INDEX idx_products_low_stock ON products (product_id) WHERE stock_quantity < 10;

-- ตัวอย่างการสร้าง Index แบบ Expression
CREATE INDEX idx_user_email_lower ON users (lower(email));

-- ตัวอย่างการสร้าง Index แบบ Covering (PostgreSQL 11+)
CREATE INDEX idx_orders_customer_id_amount ON orders (customer_id) INCLUDE (total_amount, order_date);

การระบุ Index ที่ขาดหายไปหรือไม่ถูกใช้งาน

• Index ที่ขาดหายไป: ใช้ EXPLAIN ANALYZE เพื่อดูว่า Query ที่ช้ากำลังทำ Seq Scan บนตารางขนาดใหญ่หรือไม่ หรือดูจาก pg_stat_statements ว่า Query ใดที่มี total_time สูงแต่ไม่มี Index รองรับครับ
• Index ที่ไม่ถูกใช้งาน: pg_stat_user_indexes เป็น view ที่แสดงสถิติการใช้งาน Index ครับ

  
  SELECT
      relname AS table_name,
      indexrelname AS index_name,
      idx_scan,
      idx_tup_read,
      idx_tup_fetch
  FROM
      pg_stat_user_indexes
  ORDER BY
      idx_scan ASC
  LIMIT 10;
  

  Index ที่มี idx_scan ใกล้เคียง 0 อาจเป็น Index ที่ไม่จำเป็นและควรพิจารณาลบออก เพื่อลดภาระในการ Insert/Update/Delete ครับ

การ Reindex และการลด Index Bloat

Index Bloat (พื้นที่ว่างเปล่าใน Index) เกิดขึ้นเมื่อข้อมูลถูกอัปเดตหรือลบ ทำให้ Index ต้องสร้างเวอร์ชันใหม่ของแถวนั้นๆ ทิ้งพื้นที่เก่าไว้เบื้องหลังครับ Bloat จะทำให้ Index มีขนาดใหญ่ขึ้นและประสิทธิภาพลดลงครับ

• REINDEX TABLE tablename; หรือ REINDEX INDEX indexname;: เป็นการสร้าง Index ขึ้นมาใหม่ทั้งหมด ซึ่งจะกำจัด Bloat ได้ครับ แต่จะทำการ Lock ตาราง (หรือ Index) ในระหว่างกระบวนการ ทำให้แอปพลิเคชันไม่สามารถเข้าถึงได้ชั่วคราวครับ
• CREATE INDEX CONCURRENTLY ...;: หากต้องการสร้าง Index ใหม่ (หรือ Reindex) โดยไม่ Lock ตาราง สามารถใช้ CONCURRENTLY ได้ครับ แต่มันจะใช้เวลานานกว่าและใช้ทรัพยากรมากกว่า และหากมีข้อผิดพลาดระหว่างทาง อาจมี Index ที่เสียค้างอยู่ได้ครับ

การปรับแต่ง Query (SQL Optimization)

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

เจาะลึก EXPLAIN ANALYZE อีกครั้ง

การวิเคราะห์ผลลัพธ์ของ EXPLAIN ANALYZE อย่างละเอียดคือจุดเริ่มต้นของการปรับแต่ง Query ครับ สิ่งที่ควรให้ความสนใจเพิ่มเติมคือ:

• Filter Clause: ดูว่า Filter ที่ใช้ใน Query นั้นทำงานที่ Node ไหน ถ้ามันอยู่ใน Node ที่ประมวลผลข้อมูลจำนวนมาก แสดงว่ามันกำลังกรองข้อมูลช้าไปครับ
• Join Methods:
  • Nested Loop Join: มีประสิทธิภาพดีเมื่อตารางด้านใน (inner table) ถูก Index อย่างดีและมีจำนวนแถวน้อยครับ
  • Hash Join: มีประสิทธิภาพดีเมื่อตารางใดตารางหนึ่ง (หรือทั้งสอง) มีขนาดค่อนข้างใหญ่และสามารถสร้าง Hash Table ในหน่วยความจำได้ครับ
  • Merge Join: มีประสิทธิภาพดีเมื่อข้อมูลถูกเรียงลำดับอยู่แล้ว หรือสามารถเรียงลำดับได้อย่างรวดเร็วครับ
• Parallel Query: PostgreSQL สามารถรัน Query บางประเภทแบบขนานกันได้ (Parallel Query) ถ้าเห็น Gather หรือ Parallel Worker แสดงว่ากำลังใช้คุณสมบัตินี้ครับ ตรวจสอบว่ามันช่วยลดเวลาจริงได้มากน้อยแค่ไหนครับ

เทคนิคการเขียน Query ที่มีประสิทธิภาพ

• ใช้ WHERE clause ให้มากที่สุด: กรองข้อมูลให้น้อยที่สุดตั้งแต่แรก ช่วยลดจำนวนแถวที่ต้องประมวลผลครับ
• เลือกเฉพาะคอลัมน์ที่จำเป็น: แทนที่จะ SELECT * ให้เลือกเฉพาะคอลัมน์ที่คุณต้องการจริงๆ ครับ ช่วยลดปริมาณข้อมูลที่ต้องส่งผ่านเครือข่ายและประมวลผลครับ
• หลีกเลี่ยง Subqueries ที่ไม่มีประสิทธิภาพ: Subqueries บางประเภท (โดยเฉพาะ Correlated Subqueries) อาจทำให้ Query ทำงานช้าลงได้ บางครั้งการใช้ JOIN หรือ CTEs (Common Table Expressions) จะมีประสิทธิภาพดีกว่าครับ
• ใช้ LIMIT และ OFFSET อย่างระมัดระวัง: การใช้ OFFSET จำนวนมาก (เช่น OFFSET 100000) จะทำให้ Query ต้องอ่านแถวที่ไม่จำเป็นจำนวนมากก่อนที่จะส่งผลลัพธ์ที่ต้องการกลับมาครับ หากเป็นไปได้ ให้ใช้เงื่อนไข WHERE เพื่อกรองข้อมูลแทนการใช้ OFFSET หรือใช้เทคนิค Cursor/Keyset Pagination ครับ

หลีกเลี่ยง SELECT *

การเลือกคอลัมน์ทั้งหมด (SELECT *) เป็นพฤติกรรมที่ไม่ดีต่อประสิทธิภาพด้วยเหตุผลหลายประการครับ:

• เพิ่ม I/O: ต้องอ่านข้อมูลจากดิสก์มากขึ้น เพราะต้องอ่านทุกคอลัมน์
• เพิ่ม Network Traffic: ส่งข้อมูลผ่านเครือข่ายมากขึ้น
• เพิ่ม Memory Usage: ใช้หน่วยความจำในฝั่งไคลเอ็นต์มากขึ้นในการจัดเก็บข้อมูลที่ไม่จำเป็น
• ป้องกัน Index-Only Scan: หาก Query ต้องการแค่คอลัมน์ที่อยู่ใน Index มันสามารถทำ Index-Only Scan ได้ (ไม่ต้องไปอ่านข้อมูลจากตารางจริง) แต่ถ้ามี SELECT * จะต้องกลับไปอ่านจากตารางอยู่ดีครับ

การใช้ JOINs อย่างชาญฉลาด

• ลำดับของตารางใน JOIN: แม้ว่า Query Planner ของ PostgreSQL จะฉลาดพอสมควรในการเลือกลำดับ JOIN ที่เหมาะสม แต่บางครั้งการจัดลำดับตารางให้ถูกต้องด้วยตัวเองก็สามารถช่วยได้ครับ โดยทั่วไปแล้ว ควรเริ่มจากตารางที่เล็กที่สุด หรือตารางที่มีเงื่อนไข WHERE ที่สามารถกรองข้อมูลได้จำนวนมากก่อนครับ
• เลือกประเภท JOIN ที่เหมาะสม:
  • INNER JOIN: ใช้เมื่อต้องการแถวที่ตรงกันทั้งสองตารางครับ
  • LEFT JOIN: ใช้เมื่อต้องการแถวจากตารางด้านซ้ายทั้งหมด และแถวที่ตรงกันจากตารางด้านขวาครับ
  • RIGHT JOIN/FULL JOIN: พิจารณาการใช้งานอย่างรอบคอบ เพราะอาจส่งผลให้ Query ซับซ้อนและช้าลงได้ครับ
• ใช้ Index บนคอลัมน์ JOIN: ตรวจสอบให้แน่ใจว่าคอลัมน์ที่ใช้ในการ JOIN มี Index ที่เหมาะสมครับ

Subqueries vs. CTEs

Subqueries คือ Query ซ้อน Query ครับ ในขณะที่ CTEs (Common Table Expressions) หรือ WITH clause ช่วยให้เราสามารถสร้างผลลัพธ์ชั่วคราวที่สามารถอ้างอิงได้ใน Query หลักครับ

-- ตัวอย่าง Subquery
SELECT
    c.customer_name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders
FROM
    customers c;

-- ตัวอย่าง CTE (มักมีประสิทธิภาพดีกว่าในหลายกรณี)
WITH CustomerOrders AS (
    SELECT
        customer_id,
        COUNT(*) AS total_orders
    FROM
        orders
    GROUP BY
        customer_id
)
SELECT
    c.customer_name,
    co.total_orders
FROM
    customers c
LEFT JOIN
    CustomerOrders co ON c.id = co.customer_id;

CTEs มักจะอ่านง่ายกว่าและ Query Planner ของ PostgreSQL มักจะสามารถ Optimize CTEs ได้ดีกว่า Subqueries บางประเภทครับ

การใช้ Window Functions

Window Functions (เช่น ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER (...)) สามารถช่วยให้คุณได้ผลลัพธ์ที่ซับซ้อนโดยไม่ต้องใช้ Self-Join หรือ Subquery ที่ซับซ้อน ซึ่งมักจะมีประสิทธิภาพดีกว่าครับ

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM
    orders
ORDER BY
    customer_id, order_date;

Window Functions มักจะใช้หน่วยความจำ work_mem สำหรับการ Sorting ดังนั้นควรตรวจสอบ EXPLAIN ANALYZE หากพบปัญหาด้านประสิทธิภาพครับ

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

การออกแบบ Schema ฐานข้อมูลตั้งแต่เริ่มต้นมีผลกระทบอย่างใหญ่หลวงต่อประสิทธิภาพในระยะยาวครับ การแก้ไข Schema ในภายหลังอาจเป็นเรื่องที่ยากและใช้เวลานานครับ

Normalization vs. Denormalization

• Normalization: คือการจัดระเบียบตารางเพื่อลดความซ้ำซ้อนของข้อมูลและเพิ่มความสมบูรณ์ของข้อมูลครับ (เช่น 1NF, 2NF, 3NF, BCNF) ข้อดีคือลดพื้นที่จัดเก็บ, ลดโอกาสเกิดความไม่สอดคล้องของข้อมูล, อัปเดตข้อมูลได้ง่ายครับ ข้อเสียคือต้องใช้ JOINs มากขึ้นในการดึงข้อมูล ทำให้ Query ซับซ้อนและอาจช้าลงได้ครับ
• Denormalization: คือการจงใจเพิ่มความซ้ำซ้อนของข้อมูลในตาราง เพื่อลดจำนวน JOINs ที่จำเป็นในการดึงข้อมูลครับ ข้อดีคือ Query ทำงานได้เร็วขึ้น, ลดความซับซ้อนของ Query ครับ ข้อเสียคือเพิ่มพื้นที่จัดเก็บ, เพิ่มโอกาสเกิดความไม่สอดคล้องของข้อมูล, อัปเดตข้อมูลยากขึ้นครับ

คำแนะนำ: เริ่มต้นด้วยการออกแบบแบบ Normalized และ Denormalize เฉพาะส่วนที่จำเป็นจริงๆ เมื่อพบปัญหาคอขวดด้านประสิทธิภาพครับ การ Denormalize ก่อนเวลาอันควรอาจสร้างปัญหาในอนาคตครับ

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

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

• ใช้ Data Type ที่เล็กที่สุดที่เหมาะสม:
  • SMALLINT แทน INTEGER ถ้าค่าไม่เกิน 32,767 ครับ
  • INTEGER แทน BIGINT ถ้าค่าไม่เกิน 2 พันล้านครับ
  • VARCHAR(n) แทน TEXT หากคุณรู้ความยาวสูงสุดของสตริง (แต่ PostgreSQL จัดการ TEXT ได้ดีขึ้นในเวอร์ชันใหม่ๆ, VARCHAR(n) ไม่ได้ประหยัดพื้นที่เท่าที่คิด เว้นแต่จะระบุ n ที่เล็กมาก) ครับ
  • DATE แทน TIMESTAMP หากไม่ต้องการเก็บเวลาครับ
• หลีกเลี่ยง Data Type ที่ไม่จำเป็น: เช่น การใช้ NUMERIC สำหรับตัวเลขที่ไม่ต้องการความแม่นยำสูงมาก เพราะ NUMERIC มี Overheads สูงกว่า INTEGER หรือ FLOAT ครับ
• พิจารณา UUID: สำหรับ Primary Key ในระบบกระจาย (distributed system) แต่โปรดทราบว่า UUID จะใช้พื้นที่มากกว่า BIGINT และการสร้าง Index บน UUID อาจมีประสิทธิภาพด้อยกว่า BIGINT เล็กน้อยเนื่องจากการสุ่มของค่าครับ

การทำ Partitioning

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

• ข้อดี:
  • Query Performance: Query สามารถสแกนเฉพาะ Partition ที่เกี่ยวข้องได้ (Partition Pruning) ทำให้เร็วขึ้นมากครับ
  • Maintenance: การ Vacuum หรือ Reindex ทำได้เร็วขึ้น เพราะทำแค่ Partition เดียวครับ
  • Data Archiving/Deletion: การลบข้อมูลเก่าทำได้ง่ายเพียงแค่ Drop Partition นั้นทิ้งไปครับ
• ประเภทของ Partitioning:
  • Range Partitioning: แบ่งตามช่วงของค่า (เช่น ตามวันที่ หรือ ID) ครับ
  • List Partitioning: แบ่งตามรายการของค่า (เช่น ตามภูมิภาค หรือสถานะ) ครับ
  • Hash Partitioning: แบ่งตาม Hash Value ของคอลัมน์ เพื่อกระจายข้อมูลให้สม่ำเสมอครับ
• ตั้งแต่ PostgreSQL 10+ มี Declarative Partitioning ที่ทำให้การจัดการ Partition ง่ายขึ้นมากครับ

-- ตัวอย่าง Declarative Partitioning (PostgreSQL 10+)
CREATE TABLE sensor_data (
    id BIGSERIAL,
    device_id INTEGER NOT NULL,
    measurement_time TIMESTAMP WITH TIME ZONE NOT NULL,
    temperature NUMERIC,
    humidity NUMERIC
) PARTITION BY RANGE (measurement_time);

CREATE TABLE sensor_data_y2023_q1 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-04-01 00:00:00+00');

CREATE TABLE sensor_data_y2023_q2 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-04-01 00:00:00+00') TO ('2023-07-01 00:00:00+00');

Foreign Keys และผลกระทบต่อประสิทธิภาพ

Foreign Keys เป็นกลไกสำคัญในการรักษาความสัมพันธ์และความสมบูรณ์ของข้อมูลครับ

• ข้อดี:
  • Data Integrity: ป้องกันการอ้างอิงถึงข้อมูลที่ไม่มีอยู่จริงครับ
  • Query Planner: ช่วยให้ Query Planner เข้าใจความสัมพันธ์ของข้อมูลและอาจนำไปสู่แผนการทำงานที่ดีขึ้นครับ
• ข้อควรพิจารณาด้านประสิทธิภาพ:
  • Index: ตรวจสอบให้แน่ใจว่าคอลัมน์ที่เป็น Foreign Key มี Index ครับ (PostgreSQL จะสร้าง Index บน Primary Key โดยอัตโนมัติ แต่สำหรับ Foreign Key เราต้องสร้างเอง) การไม่มี Index บน Foreign Key จะทำให้การลบ/อัปเดตแถวในตารางแม่ช้าลงมากครับ
  • Constraint Checking: การมี Foreign Key เพิ่ม Overhead ในการ Insert/Update/Delete เพราะต้องตรวจสอบความถูกต้องของข้อมูลครับ

VACUUM และ Autovacuum: การจัดการ Bloat และ MVCC

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

ทำความเข้าใจ MVCC และ Bloat

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

การทำงานของ VACUUM

• VACUUM: จะสแกนตารางและทำเครื่องหมายแถวที่ตายแล้วให้เป็น "ใช้ได้อีกครั้ง" (reusable) แต่ไม่คืนพื้นที่ว่างให้ระบบปฏิบัติการครับ
• VACUUM FULL: จะเขียนตารางใหม่ทั้งหมดโดยไม่รวมแถวที่ตายแล้ว และคืนพื้นที่ว่างให้กับระบบปฏิบัติการครับ แต่กระบวนการนี้จะ Lock ตารางนั้นทั้งหมดและใช้เวลานานมาก ไม่แนะนำให้ใช้ใน Production บ่อยๆ ครับ
• VACUUM ANALYZE: ทำ VACUUM และตามด้วย ANALYZE ซึ่งจะอัปเดตสถิติของตาราง เพื่อให้ Query Planner สามารถสร้างแผนการทำงานที่มีประสิทธิภาพได้ครับ

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

Autovacuum คือกระบวนการ Background ที่รัน VACUUM และ ANALYZE โดยอัตโนมัติเมื่อมีจำนวนแถวที่เปลี่ยนแปลงถึงเกณฑ์ที่กำหนดครับ การปรับแต่ง Autovacuum ให้เหมาะสมเป็นสิ่งสำคัญอย่างยิ่งครับ

# postgresql.conf
autovacuum = on
autovacuum_max_workers = 5 # จำนวน worker ที่ Autovacuum สามารถใช้ได้พร้อมกัน (ค่าเริ่มต้น 3)
autovacuum_vacuum_cost_delay = 10ms # หน่วงเวลาของ worker เพื่อไม่ให้กิน I/O มากเกินไป (ค่าเริ่มต้น 20ms)
autovacuum_analyze_cost_delay = 10ms # หน่วงเวลาสำหรับ ANALYZE (ค่าเริ่มต้น 20ms)
autovacuum_vacuum_cost_limit = 1000 # ขีดจำกัด I/O cost ของ worker (ค่าเริ่มต้น 200)

# พารามิเตอร์ต่อตาราง (สามารถตั้งใน ALTER TABLE ได้)
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.05); # Vacuum เมื่อ 5% ของแถวถูกอัปเดต/ลบ
ALTER TABLE my_table SET (autovacuum_analyze_scale_factor = 0.02); # Analyze เมื่อ 2% ของแถวถูกเปลี่ยนแปลง
ALTER TABLE my_table SET (autovacuum_vacuum_threshold = 500); # Vacuum เมื่อมี dead tuples ถึง 500 แถว
ALTER TABLE my_table SET (autovacuum_analyze_threshold = 250); # Analyze เมื่อมีแถวเปลี่ยนแปลงถึง 250 แถว

• autovacuum_max_workers: เพิ่มค่านี้ถ้าคุณมีตารางจำนวนมากที่ต้อง Vacuum บ่อยๆ และมีทรัพยากร CPU เพียงพอครับ
• autovacuum_vacuum_cost_delay: ลดค่านี้ (เช่น 10ms หรือ 0ms) ในระบบที่มี I/O Subsystem ที่เร็ว (เช่น SSDs) เพื่อให้ Autovacuum ทำงานเร็วขึ้นครับ แต่ถ้าลดมากเกินไป อาจทำให้ Autovacuum กินทรัพยากรมากเกินไปจนกระทบ Query ปกติได้ครับ
• autovacuum_vacuum_scale_factor และ autovacuum_analyze_scale_factor: สำหรับตารางที่มีการเปลี่ยนแปลงข้อมูลบ่อยและขนาดใหญ่ อาจต้องลดค่า scale_factor ลง เพื่อให้ Autovacuum ทำงานบ่อยขึ้นก่อนที่ Bloat จะสะสมมากเกินไปครับ แต่ก็ต้องไม่บ่อยเกินไปจนเป็นภาระครับ
• autovacuum_vacuum_threshold และ autovacuum_analyze_threshold: สำหรับตารางเล็กๆ การใช้ scale_factor อาจทำให้ Autovacuum ทำงานไม่บ่อยพอเพราะเปอร์เซ็นต์การเปลี่ยนแปลงไม่ถึงเกณฑ์ ดังนั้นการตั้งค่า threshold แบบ absolute number จะช่วยให้ Autovacuum ทำงานได้ดีขึ้นครับ

VACUUM FULL: เมื่อจำเป็นต้องใช้

VACUUM FULL ควรใช้เป็นทางเลือกสุดท้าย เมื่อตารางหรือ Index มี Bloat สูงมากจนส่งผลกระทบต่อประสิทธิภาพอย่างรุนแรง และคุณไม่สามารถใช้ pg_repack (extension ที่สามารถ Rebuild ตารางและ Index แบบออนไลน์ได้) ได้ครับ เนื่องจาก VACUUM FULL จะ Lock ตาราง ดังนั้นควรวางแผนการทำงานในช่วงเวลาที่ระบบมี Load น้อยที่สุดครับ

สามารถตรวจสอบ Bloat ได้ด้วย extension pg_freespace หรือ pg_relation_size ร่วมกับ pg_total_relation_size ครับ หรือใช้ Query ที่ซับซ้อนขึ้นเพื่อคำนวณ Bloat โดยประมาณครับ

อ่านเพิ่มเติมเกี่ยวกับ Bloat ใน PostgreSQL

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

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

Disk I/O: SSD และ RAID Configuration

• SSD (Solid State Drives): เป็นสิ่งที่จำเป็นอย่างยิ่งสำหรับฐานข้อมูลที่มี Transaction สูงและ Random I/O เยอะครับ SSDs มีความเร็วในการอ่าน/เขียนแบบ Random ที่เหนือกว่า HDDs มากครับ
• RAID Configuration:
  • RAID 10 (1+0): เป็นตัวเลือกที่ดีที่สุดสำหรับฐานข้อมูล ให้ทั้งประสิทธิภาพ (จากการ Stripping) และความทนทานต่อความผิดพลาด (จากการ Mirroring) ครับ
  • RAID 5/6: อาจเป็นทางเลือกสำหรับ Storage ที่ต้องการพื้นที่เยอะและยอมรับประสิทธิภาพที่ต่ำกว่า RAID 10 ได้เล็กน้อยครับ แต่ควรระวังเรื่อง Write Penalty ครับ
• I/O Scheduler: บน Linux ควรใช้ I/O Scheduler ที่เหมาะสมกับ SSDs เช่น noop หรือ deadline ครับ

RAM และ CPU

• RAM: ยิ่งมี RAM มากเท่าไหร่ PostgreSQL ก็จะสามารถแคชข้อมูลได้มากขึ้นเท่านั้นครับ ซึ่งช่วยลดการอ่านจากดิสก์ได้อย่างมากครับ แนะนำให้มี RAM เพียงพอที่จะเก็บ Hot Data (ข้อมูลที่เข้าถึงบ่อย) ไว้ในหน่วยความจำได้ครับ
• CPU: PostgreSQL สามารถใช้ CPU ได้หลาย Core โดยเฉพาะกับ Parallel Querys ครับ การมี CPU ที่มี Core จำนวนมากและความเร็ว Clock สูงจะช่วยเพิ่มประสิทธิภาพได้ครับ

การปรับแต่งระดับ OS (เช่น vm.swappiness)

• vm.swappiness: กำหนดความถี่ที่ Kernel จะใช้พื้นที่ Swap บนดิสก์ครับ สำหรับเซิร์ฟเวอร์ฐานข้อมูล เราต้องการให้ Kernel เก็บข้อมูลไว้ใน RAM ให้มากที่สุด ดังนั้นควรตั้งค่า vm.swappiness ให้ต่ำ (เช่น 1 หรือ 10) เพื่อลดการ Swap ที่จะทำให้ประสิทธิภาพลดลงอย่างมากครับ
• Transparent Huge Pages (THP): บน Linux ควรปิด THP (Transparent Huge Pages) สำหรับ PostgreSQL ครับ เพราะมันอาจทำให้เกิดปัญหา Performance Spikes ได้ครับ

# ตรวจสอบ vm.swappiness
cat /proc/sys/vm/swappiness

# ตั้งค่า vm.swappiness (ชั่วคราว)
sudo sysctl vm.swappiness=1

# ตั้งค่า vm.swappiness (ถาวร)
echo "vm.swappiness = 1" | sudo tee -a /etc/sysctl.conf
sudo sysctl -p

# ปิด Transparent Huge Pages (THP)
# ชั่วคราว:
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo never | sudo tee /sys/kernel/mm/transparent_hugepage/defrag
# ถาวร: (แก้ไขไฟล์ grub หรือ rc.local)

การเลือก Filesystem

• ext4: เป็น Filesystem ทั่วไปที่ใช้กันแพร่หลายและมีความน่าเชื่อถือครับ
• XFS: มักถูกแนะนำสำหรับฐานข้อมูลขนาดใหญ่และ Workload ที่ใช้ I/O สูง เนื่องจากมีความสามารถในการจัดการกับไฟล์ขนาดใหญ่และ Directory จำนวนมากได้ดีกว่าครับ
• เมื่อ Format Filesystem ควรใช้ Block Size ที่เหมาะสม (เช่น 4KB) และพิจารณาการตั้งค่า noatime หรือ nodiratime ใน /etc/fstab เพื่อลด Write I/O ที่ไม่จำเป็นครับ

Connection Pooling: จัดการการเชื่อมต่ออย่างมีประสิทธิภาพ

การสร้างและปิดการเชื่อมต่อกับฐานข้อมูลเป็นกระบวนการที่ใช้ทรัพยากรสูงครับ หากแอปพลิเคชันของคุณเปิดและปิดการเชื่อมต่อบ่อยๆ หรือมีการเชื่อมต่อพร้อมกันจำนวนมาก อาจทำให้ประสิทธิภาพของฐานข้อมูลลดลงได้ครับ Connection Pooler จะช่วยจัดการการเชื่อมต่อเหล่านี้ได้อย่างมีประสิทธิภาพครับ

PgBouncer

• คำอธิบาย: PgBouncer เป็น Connection Pooler ที่ได้รับความนิยมมากที่สุดสำหรับ PostgreSQL ครับ มันจะทำหน้าที่เป็นตัวกลางระหว่างแอปพลิเคชันกับฐานข้อมูล โดยจะเปิดการเชื่อมต่อกับ PostgreSQL ไว้ล่วงหน้าและนำการเชื่อมต่อเหล่านั้นกลับมาใช้ใหม่ (re-use) สำหรับ Query ใหม่ๆ จากแอปพลิเคชันครับ
• ข้อดี:
  • ลด Overhead การเชื่อมต่อ: ไม่ต้องสร้างและปิดการเชื่อมต่อใหม่ทุกครั้งครับ
  • จำกัดจำนวนการเชื่อมต่อ: ช่วยควบคุมจำนวนการเชื่อมต่อที่เข้าสู่ PostgreSQL จริงๆ ทำให้ max_connections ใน PostgreSQL
    

    บทความที่เกี่ยวข้อง

    • Prometheus Grafana Monitoring ระบบ Server แบบ Pro
    • NAS Storage คู่มือเลือก Synology QNAP สำหรับองค์กร
    • Terraform Infrastructure as Code สำหรับ Cloud AWS
    SiamCafe.net — ชุมชน IT ที่ใหญ่ที่สุด · Siam2R.com — Portfolio งาน IT