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

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

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

บทความนี้ SiamLancard.com ได้รวบรวมความรู้และเทคนิคเชิงลึกเกี่ยวกับการปรับแต่งประสิทธิภาพ PostgreSQL หรือที่เรียกว่า “PostgreSQL Performance Tuning” มานำเสนอในรูปแบบคู่มือที่ครบถ้วนและเข้าใจง่ายครับ เราจะเจาะลึกตั้งแต่หลักการพื้นฐาน ไปจนถึงเทคนิคขั้นสูง เพื่อให้คุณสามารถนำไปปรับใช้กับฐานข้อมูล PostgreSQL ของคุณได้อย่างมีประสิทธิภาพสูงสุด ไม่ว่าคุณจะเป็น DBA มือใหม่ หรือนักพัฒนาที่ต้องการเพิ่มความเร็วให้กับแอปพลิเคชันของคุณ บทความนี้จะเป็นแหล่งข้อมูลสำคัญที่จะช่วยให้คุณปลดล็อกศักยภาพสูงสุดของ PostgreSQL ได้อย่างแน่นอนครับ

สารบัญ

• ความสำคัญของการปรับแต่ง PostgreSQL
  • ทำไมต้องปรับแต่ง PostgreSQL?
  • ผลกระทบของประสิทธิภาพที่ต่ำ
• เริ่มต้นกับการวินิจฉัยปัญหา
  • การตรวจสอบสถานะปัจจุบัน
  • เครื่องมือพื้นฐาน: pg_stat_activity และ pg_stat_statements
  • ทำความเข้าใจ EXPLAIN ANALYZE
• การปรับแต่งไฟล์ postgresql.conf
  • หน่วยความจำ (Memory Parameters)
    • shared_buffers
    • work_mem
    • maintenance_work_mem
    • effective_cache_size
  • การจัดการ WAL (Write-Ahead Log)
    • wal_buffers
    • checkpoint_timeout, max_wal_size, min_wal_size
  • การเชื่อมต่อและ Concurrency
    • max_connections
    • listen_addresses
  • Logging (การบันทึก Log)
    • log_statement, log_min_duration_statement
    • log_destination, logging_collector
  • Autovacuum (การทำงานอัตโนมัติ)
    • autovacuum (enable/disable)
    • autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor
    • autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
  • อื่นๆ (Miscellaneous Parameters)
    • fsync และ synchronous_commit
    • random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost
• การปรับแต่ง Index
  • ทำไมต้อง Index?
  • ประเภทของ Index และกรณีการใช้งาน
    • B-Tree Index
    • Hash Index
    • GIN Index
    • GiST Index
    • BRIN Index
    • SP-GiST Index
  • ตารางเปรียบเทียบ Index ประเภทต่างๆ
  • เมื่อไหร่ควรสร้าง Index?
  • เมื่อไหร่ไม่ควรสร้าง Index?
  • Index ขั้นสูง
    • Partial Indexes
    • Expression Indexes
    • Unique Indexes
    • Covering Indexes (Index Only Scans)
• การปรับแต่ง Query
  • การเขียน Query ที่มีประสิทธิภาพ
  • หลีกเลี่ยง SELECT *
  • การใช้ JOIN ที่เหมาะสม
  • การใช้ WHERE clause อย่างมีประสิทธิภาพ
  • การใช้ LIMIT และ OFFSET
  • การจัดการ Subqueries และ CTEs
  • การใช้ Window Functions
• การจัดการ Vacuum และ Analyze
  • ทำความเข้าใจ Bloat (Table and Index Bloat)
  • VACUUM vs VACUUM FULL
  • ANALYZE
  • Autovacuum: ประโยชน์และการกำหนดค่าเชิงลึก
  • การเรียกใช้แบบ Manual
• การเลือก Hardware และ Storage
  • CPU: Core Count vs. Clock Speed
  • RAM: ความสำคัญต่อ shared_buffers และ work_mem
  • Storage: SSD vs. NVMe, RAID configurations
  • Network: Latency และ Throughput
• การมอนิเตอร์และเครื่องมือ
  • pg_stat_statements
  • pg_activity
  • Prometheus & Grafana
  • pgBadger
  • Enterprise Monitoring Solutions
• เทคนิคขั้นสูงและ Best Practices
  • Connection Pooling (PgBouncer, Odyssey)
  • Partitioning
  • Foreign Data Wrappers (FDW)
  • Materialized Views
  • Read Replicas (สำหรับ Scale-out อ่าน)
  • การสำรองและกู้คืนข้อมูลที่มีประสิทธิภาพ
• คำถามที่พบบ่อย (FAQ)
• สรุปและ Call-to-Action

ความสำคัญของการปรับแต่ง PostgreSQL

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

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

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

ผลกระทบของประสิทธิภาพที่ต่ำ

• ประสบการณ์ผู้ใช้แย่ลง: แอปพลิเคชันโหลดช้า, รายงานออกช้า, การทำธุรกรรมใช้เวลานาน
• การสูญเสียรายได้: ลูกค้าที่หงุดหงิดอาจเลิกใช้บริการ ส่งผลให้ยอดขายลดลง
• ภาระงานของทีมพัฒนา/DBA เพิ่มขึ้น: ต้องใช้เวลาแก้ไขปัญหาประสิทธิภาพ แทนที่จะพัฒนาฟีเจอร์ใหม่ๆ
• ความเสี่ยงด้านความน่าเชื่อถือ: ระบบล่มบ่อยครั้ง ทำให้ข้อมูลไม่พร้อมใช้งาน
• ค่าใช้จ่ายด้านโครงสร้างพื้นฐานสูงขึ้น: ต้องเพิ่มฮาร์ดแวร์ หรือย้ายไปใช้เซิร์ฟเวอร์ที่มีประสิทธิภาพสูงขึ้นก่อนเวลาอันควร

เริ่มต้นกับการวินิจฉัยปัญหา

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

การตรวจสอบสถานะปัจจุบัน

เริ่มต้นด้วยการตรวจสอบสถานะโดยรวมของระบบครับ ว่ามีอะไรผิดปกติหรือไม่:

• CPU Usage: สูงเกินไปหรือไม่? มี process ใดที่ใช้ CPU มากเป็นพิเศษ?
• Memory Usage: RAM ใกล้เต็มหรือไม่? มีการใช้ Swap memory มากเกินไปหรือเปล่า?
• Disk I/O: Disk I/O สูงผิดปกติหรือไม่? มี latency สูงหรือไม่?
• Network Traffic: มีการรับส่งข้อมูลทางเครือข่ายมากเกินไปหรือไม่?
• Active Connections: มีการเชื่อมต่อฐานข้อมูลมากเกินกว่าที่คาดไว้หรือไม่?

เครื่องมือพื้นฐานอย่าง top, htop, iostat, vmstat บน Linux สามารถช่วยในการตรวจสอบเบื้องต้นได้ดีครับ

เครื่องมือพื้นฐาน: pg_stat_activity และ pg_stat_statements

PostgreSQL มีวิว (views) ที่มีประโยชน์อย่างมากสำหรับการมอนิเตอร์และวินิจฉัยปัญหาครับ

pg_stat_activity

วิวนี้แสดงข้อมูลเกี่ยวกับ Process ที่กำลังทำงานอยู่ใน PostgreSQL ทั้งหมด ทำให้เราสามารถเห็นได้ว่ามี Query ใดบ้างที่กำลังทำงานอยู่ ใครเป็นคนรัน และใช้เวลานานแค่ไหนแล้ว

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

จากผลลัพธ์ คุณสามารถระบุ Query ที่ทำงานนานผิดปกติ (long-running queries) หรือ Query ที่ติดสถานะ waiting เพื่อหาต้นเหตุของปัญหาได้ครับ

pg_stat_statements

pg_stat_statements เป็นส่วนเสริม (extension) ที่ยอดเยี่ยมสำหรับการติดตามสถิติการทำงานของ Query ทั้งหมดที่รันผ่านฐานข้อมูลของคุณ มันจะรวม Query ที่เหมือนกันเข้าด้วยกัน และให้ข้อมูลสถิติ เช่น จำนวนครั้งที่รัน, เวลาที่ใช้โดยเฉลี่ย, จำนวน Rows ที่ส่งกลับมา, และอื่นๆ อีกมากมายครับ

ก่อนใช้งาน คุณต้องเปิดใช้งาน extension นี้ในไฟล์ postgresql.conf ก่อนครับ โดยการเพิ่ม pg_stat_statements เข้าไปในพารามิเตอร์ shared_preload_libraries และรีสตาร์ท PostgreSQL:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

จากนั้นสร้าง extension ในฐานข้อมูลที่คุณต้องการมอนิเตอร์:

CREATE EXTENSION pg_stat_statements;

และ Query เพื่อดู Query ที่ใช้ทรัพยากรมากที่สุด:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    stddev_exec_time
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

ข้อมูลจาก pg_stat_statements จะช่วยให้คุณระบุ Query ที่เป็นคอขวด (bottleneck) ของระบบได้อย่างแม่นยำครับ

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

นี่คือเครื่องมือที่ทรงพลังที่สุดในการทำความเข้าใจว่า PostgreSQL วางแผนที่จะรัน Query ของคุณอย่างไร และใช้เวลาเท่าไหร่ในการรันจริง

• EXPLAIN: แสดงแผนการรัน Query (Query Plan) โดยไม่รัน Query จริง มันจะบอกว่า PostgreSQL จะใช้ Index อะไร, จะ Join ตารางอย่างไร, และจะ Scan ข้อมูลแบบไหน
• EXPLAIN ANALYZE: จะรัน Query จริงๆ และแสดงแผนการรันพร้อมกับเวลาจริงที่ใช้ในแต่ละขั้นตอน (execution time) และจำนวนแถวที่ส่งกลับมาจริง (actual rows)

ตัวอย่างการใช้งาน:

EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100 ORDER BY product_name;

ผลลัพธ์จะซับซ้อน แต่มีข้อมูลที่มีค่ามหาศาลครับ สิ่งที่ต้องมองหาคือ:

• Scan Type: เป็น Sequential Scan (สแกนทั้งตาราง) หรือ Index Scan? Index Scan มักจะเร็วกว่าสำหรับ Query ที่มี WHERE clause
• Cost: ค่าประมาณการต้นทุนการรัน Query (ยิ่งน้อยยิ่งดี)
• Rows: จำนวนแถวที่คาดการณ์ vs จำนวนแถวที่แท้จริง ถ้าแตกต่างกันมาก อาจหมายถึงสถิติไม่แม่นยำ
• Actual Time: เวลาจริงที่ใช้ในแต่ละ Node
• Buffers: จำนวน Block ที่อ่านจาก Cache หรือ Disk

การทำความเข้าใจแผนการรัน Query จาก EXPLAIN ANALYZE เป็นทักษะที่จำเป็นอย่างยิ่งในการปรับแต่ง Query และการสร้าง Index ครับ หากคุณยังไม่คุ้นเคย ลองฝึกฝนการอ่านแผนการรัน Query บ่อยๆ นะครับ

การปรับแต่งไฟล์ postgresql.conf

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

ข้อควรระวัง: ก่อนทำการเปลี่ยนแปลงใดๆ ควรสำรองข้อมูลและทดสอบการเปลี่ยนแปลงในสภาพแวดล้อม Development หรือ Staging ก่อนเสมอครับ การปรับค่าที่ไม่เหมาะสมอาจทำให้ระบบทำงานผิดปกติหรือช้าลงกว่าเดิมได้

หน่วยความจำ (Memory Parameters)

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

shared_buffers

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

# postgresql.conf
shared_buffers = 8GB

work_mem

• คำอธิบาย: นี่คือจำนวนหน่วยความจำที่แต่ละ Query สามารถใช้ได้สำหรับการดำเนินการภายใน เช่น Sorting (ORDER BY, GROUP BY), Hashing (Hash Join), และการสร้าง Temporary Table ก่อนที่จะต้องเขียนข้อมูลลงดิสก์
• คำแนะนำ: ค่าเริ่มต้นมักจะน้อยเกินไป (4MB) ถ้ามี RAM เพียงพอ การเพิ่มค่านี้จะช่วยให้ Query ที่มีการ Sort หรือ Hash ทำงานได้เร็วขึ้นอย่างเห็นได้ชัดครับ แต่ต้องระวัง เพราะนี่คือค่าต่อ แต่ละ Query ถ้ามี 100 Query ทำงานพร้อมกันและแต่ละ Query ใช้ work_mem = 256MB, คุณจะต้องการ RAM อย่างน้อย 25.6GB สำหรับ work_mem เพียงอย่างเดียว ดังนั้นควรตั้งค่าอย่างระมัดระวัง อาจเริ่มต้นที่ 16MB หรือ 32MB และค่อยๆ เพิ่มขึ้น โดยดูจาก EXPLAIN ANALYZE ที่ระบุว่า “disk-based sort”
• ตัวอย่าง: work_mem = 32MB

# postgresql.conf
work_mem = 32MB

maintenance_work_mem

• คำอธิบาย: เป็นจำนวนหน่วยความจำที่ใช้สำหรับงานบำรุงรักษา เช่น VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, CLUSTER
• คำแนะนำ: งานเหล่านี้มักจะรันเป็นครั้งคราว (หรือโดย autovacuum) และไม่พร้อมกันหลายๆ ครั้ง ดังนั้นสามารถตั้งค่าให้สูงกว่า work_mem ได้อย่างปลอดภัยครับ เพื่อให้งานบำรุงรักษาเหล่านี้ทำงานได้เร็วขึ้น โดยทั่วไปแนะนำที่ 256MB ถึง 1GB หรือมากกว่านั้น ขึ้นอยู่กับ RAM ทั้งหมดของเซิร์ฟเวอร์
• ตัวอย่าง: maintenance_work_mem = 512MB

# postgresql.conf
maintenance_work_mem = 512MB

effective_cache_size

• คำอธิบาย: พารามิเตอร์นี้ไม่ได้จัดสรรหน่วยความจำจริง แต่เป็นการบอก PostgreSQL Optimizer ว่ามีหน่วยความจำทั้งหมดเท่าไหร่ที่ระบบปฏิบัติการสามารถใช้สำหรับแคชไฟล์บนดิสก์ รวมถึง shared_buffers ด้วยครับ เพื่อให้ Optimizer สามารถตัดสินใจเลือกแผนการรัน Query ได้อย่างเหมาะสม โดยจะสมมติว่าข้อมูลที่อยู่ใน OS cache จะเข้าถึงได้เร็วกว่าข้อมูลที่ต้องอ่านจากดิสก์โดยตรง
• คำแนะนำ: ควรตั้งค่าเป็นประมาณ 50-75% ของ RAM ทั้งหมดของเซิร์ฟเวอร์ หรือสูงกว่า shared_buffers มากๆ ครับ
• ตัวอย่าง: หากเซิร์ฟเวอร์มี RAM 32GB, คุณอาจตั้งค่า effective_cache_size = 24GB

# postgresql.conf
effective_cache_size = 24GB

การจัดการ WAL (Write-Ahead Log)

WAL เป็นส่วนสำคัญในการรับประกันความสมบูรณ์ของข้อมูลและใช้ในการกู้คืนระบบ (recovery) การปรับแต่ง WAL สามารถมีผลต่อประสิทธิภาพ I/O อย่างมากครับ

wal_buffers

• คำอธิบาย: จำนวนหน่วยความจำที่ PostgreSQL ใช้สำหรับแคช WAL records ก่อนที่จะเขียนลงดิสก์
• คำแนะนำ: ค่าเริ่มต้นคือ -1 ซึ่งหมายถึง 1/32 ของ shared_buffers สูงสุด 16MB หากมีการทำธุรกรรม (transactions) จำนวนมากพร้อมกัน การเพิ่มค่านี้เป็น 16MB หรือ 32MB อาจช่วยลด I/O ได้ครับ
• ตัวอย่าง: wal_buffers = 16MB

# postgresql.conf
wal_buffers = 16MB

checkpoint_timeout, max_wal_size, min_wal_size

• คำอธิบาย: Checkpoint คือกระบวนการที่ PostgreSQL เขียนข้อมูลที่ถูกแก้ไขจาก shared_buffers ลงดิสก์อย่างถาวร และอัปเดตไฟล์ควบคุม (control file) เพื่อระบุจุดที่สามารถเริ่มต้นการกู้คืนได้ พารามิเตอร์เหล่านี้ควบคุมความถี่และปริมาณของ WAL ที่ใช้ระหว่าง Checkpoint ครับ
• คำแนะนำ:
  • checkpoint_timeout: ระยะเวลาสูงสุดระหว่าง checkpoint (ค่าเริ่มต้น 5 นาที) การเพิ่มค่านี้อาจลดภาระ I/O แต่จะเพิ่มเวลาในการกู้คืนระบบหากเกิดความล้มเหลว อาจตั้งค่าเป็น 15-30 นาที
  • max_wal_size: ขนาดสูงสุดของ WAL ที่จะเขียนก่อนที่จะบังคับให้เกิด checkpoint (ค่าเริ่มต้น 1GB) การเพิ่มค่านี้จะช่วยลดความถี่ของ checkpoint
  • min_wal_size: ขนาดต่ำสุดของ WAL ที่จะคงไว้ (ค่าเริ่มต้น 80MB)

  การปรับสามค่านี้ให้เหมาะสมจะช่วยลด spike ของ I/O ที่เกิดจาก checkpoint ได้ครับ เป้าหมายคือให้ checkpoint เกิดขึ้นน้อยลงและกระจายตัวมากขึ้น เพื่อลดภาระการเขียนลงดิสก์อย่างกะทันหัน

• ตัวอย่าง:

  # postgresql.conf
  checkpoint_timeout = 15min
  max_wal_size = 4GB
  min_wal_size = 1GB
          

การเชื่อมต่อและ Concurrency

max_connections

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

# postgresql.conf
max_connections = 100

listen_addresses

• คำอธิบาย: กำหนดว่า PostgreSQL จะรับการเชื่อมต่อจาก IP address ใดบ้าง
• คำแนะนำ: เพื่อความปลอดภัย ควรระบุเฉพาะ IP ที่จำเป็นเท่านั้น เช่น localhost สำหรับการเชื่อมต่อภายใน หรือ IP ของเซิร์ฟเวอร์แอปพลิเคชัน ถ้าต้องการให้เข้าถึงจากภายนอก ควรตั้งค่าเป็น '*' แต่ต้องระมัดระวังเรื่องความปลอดภัย และตรวจสอบ pg_hba.conf อย่างละเอียดครับ
• ตัวอย่าง: listen_addresses = 'localhost' หรือ listen_addresses = '192.168.1.100'

# postgresql.conf
listen_addresses = 'localhost'

Logging (การบันทึก Log)

การกำหนดค่า Log ที่เหมาะสมจะช่วยให้คุณสามารถวินิจฉัยปัญหาและระบุ Query ที่เป็นปัญหาได้ง่ายขึ้น

log_statement, log_min_duration_statement

• คำอธิบาย:
  • log_statement: กำหนดว่าจะบันทึก Query ประเภทใดลงใน Log (none, ddl, mod, all)
  • log_min_duration_statement: บันทึกเฉพาะ Query ที่ใช้เวลาในการรันนานกว่าระยะเวลาที่กำหนด (มิลลิวินาที) นี่คือพารามิเตอร์ที่สำคัญมากสำหรับการหา Query ที่ช้า
• คำแนะนำ:
  • log_statement = 'none' ใน Production เพื่อลดขนาด Log และภาระงาน (ยกเว้นคุณต้องการตรวจสอบ DDL หรือ Mod)
  • ตั้งค่า log_min_duration_statement เป็นค่าที่เหมาะสม เช่น 500ms (0.5 วินาที) หรือ 1000ms (1 วินาที) เพื่อระบุ Query ที่ช้าโดยไม่สร้าง Log มากเกินไป
• ตัวอย่าง:

  # postgresql.conf
  log_statement = 'none'
  log_min_duration_statement = 500ms
          

log_destination, logging_collector

• คำอธิบาย:
  • log_destination: รูปแบบของ Log (stderr, csvlog, syslog)
  • logging_collector: เปิดใช้งาน Process ที่จะรวบรวม Log และเขียนลงไฟล์
• คำแนะนำ:
  • ใน Production แนะนำให้ใช้ logging_collector = on และ log_destination = 'csvlog' เพื่อให้สามารถประมวลผล Log ด้วยเครื่องมือภายนอกได้ง่ายขึ้น (เช่น pgBadger)
  • ตั้งค่า log_directory และ log_filename เพื่อจัดระเบียบไฟล์ Log
• ตัวอย่าง:

  # postgresql.conf
  log_destination = 'csvlog'
  logging_collector = on
  log_directory = 'pg_log'
  log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
  log_rotation_age = 1d
  log_rotation_size = 10MB
          

Autovacuum (การทำงานอัตโนมัติ)

Autovacuum เป็น Process พื้นหลังที่สำคัญอย่างยิ่งในการรักษาประสิทธิภาพของ PostgreSQL โดยการกำจัด “dead tuples” และอัปเดตสถิติของตาราง เพื่อให้ Query Optimizer มีข้อมูลที่ถูกต้อง

autovacuum (enable/disable)

• คำอธิบาย: เปิดหรือปิดการทำงานของ autovacuum
• คำแนะนำ: ควรเปิดใช้งานเสมอ (on) การปิด autovacuum อาจนำไปสู่ปัญหา Bloat, Wraparound และประสิทธิภาพที่ลดลงอย่างรุนแรง

# postgresql.conf
autovacuum = on

autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor

• คำอธิบาย: กำหนดเปอร์เซ็นต์ของจำนวนแถวในตารางที่ต้องเปลี่ยนแปลง (insert/update/delete) เพื่อให้ autovacuum หรือ autoanalyze ทำงาน
• คำแนะนำ: ค่าเริ่มต้นคือ 0.2 (20%) ซึ่งอาจจะสูงเกินไปสำหรับตารางขนาดใหญ่ ทำให้ autovacuum ทำงานช้าเกินไป ลองลดค่านี้เป็น 0.05 หรือ 0.1 สำหรับตารางที่มีการเปลี่ยนแปลงข้อมูลบ่อยๆ เพื่อให้ autovacuum ทำงานบ่อยขึ้นและจัดการ Bloat ได้ทันท่วงทีครับ
• ตัวอย่าง:

  # postgresql.conf
  autovacuum_vacuum_scale_factor = 0.1
  autovacuum_analyze_scale_factor = 0.05
          

autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit

• คำอธิบาย: ควบคุมความเร็วในการทำงานของ autovacuum เพื่อไม่ให้แย่งทรัพยากรจาก Query หลักมากเกินไป
• คำแนะนำ:
  • autovacuum_vacuum_cost_delay: ระยะเวลาที่ autovacuum จะ “พัก” หลังจากทำงานไปถึง autovacuum_vacuum_cost_limit (ค่าเริ่มต้น 20ms) การลดค่านี้จะทำให้ autovacuum ทำงานเร็วขึ้น
  • autovacuum_vacuum_cost_limit: จำนวน “ต้นทุน” I/O สูงสุดที่ autovacuum สามารถใช้ได้ก่อนที่จะพัก (ค่าเริ่มต้น 200) การเพิ่มค่านี้จะทำให้ autovacuum ทำงานได้ “หนัก” ขึ้นต่อครั้ง

  หากพบว่า autovacuum ทำงานช้าเกินไปและเกิด Bloat ได้ง่าย ลองลด autovacuum_vacuum_cost_delay เป็น 10ms หรือ 5ms และเพิ่ม autovacuum_vacuum_cost_limit เป็น 400-500 เพื่อให้ autovacuum ทำงานอย่างกระตือรือร้นมากขึ้นครับ

• ตัวอย่าง:

  # postgresql.conf
  autovacuum_vacuum_cost_delay = 10ms
  autovacuum_vacuum_cost_limit = 400
          

อื่นๆ (Miscellaneous Parameters)

fsync และ synchronous_commit

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

random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost

• คำอธิบาย: พารามิเตอร์เหล่านี้เป็นค่าประมาณการ “ต้นทุน” ที่ Query Optimizer ใช้ในการคำนวณแผนการรัน Query ต่างๆ
• คำแนะนำ:
  • random_page_cost: ต้นทุนการอ่าน Block แบบสุ่ม (ค่าเริ่มต้น 4.0) หากคุณใช้ SSD หรือ NVMe ซึ่งการเข้าถึงข้อมูลแบบสุ่มทำได้เร็วมาก ควรลดค่านี้ลง เช่น random_page_cost = 1.1 หรือ 1.0 เพื่อให้ Optimizer เลือกใช้ Index Scan บ่อยขึ้น
  • cpu_tuple_cost: ต้นทุนการประมวลผลแต่ละแถว (ค่าเริ่มต้น 0.01)
  • cpu_index_tuple_cost: ต้นทุนการประมวลผลแต่ละแถวที่ได้จาก Index (ค่าเริ่มต้น 0.005)
  • cpu_operator_cost: ต้นทุนการประมวลผลแต่ละ Operator (ค่าเริ่มต้น 0.0025)

  การปรับค่าเหล่านี้จะส่งผลต่อการตัดสินใจของ Optimizer ว่าจะเลือกใช้ Sequential Scan, Index Scan, หรือ Join แบบใด การปรับค่าเริ่มต้นให้สอดคล้องกับฮาร์ดแวร์ของคุณจะช่วยให้ Optimizer เลือกแผนการรัน Query ที่มีประสิทธิภาพมากขึ้นครับ

• ตัวอย่าง:

  # postgresql.conf
  random_page_cost = 1.1
  

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

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

ทำไมต้อง Index?

ลองนึกภาพสมุดโทรศัพท์ที่ไม่มีการจัดเรียงตามตัวอักษร หากคุณต้องการหาเบอร์โทรศัพท์ของ “สมชาย” คุณจะต้องพลิกดูทุกหน้าจนกว่าจะเจอ แต่ถ้าสมุดโทรศัพท์ถูกจัดเรียงตามตัวอักษร คุณก็จะสามารถหา “สมชาย” ได้อย่างรวดเร็ว Index ทำหน้าที่คล้ายกันนี้ครับ

• เพิ่มความเร็วในการค้นหา: Index ช่วยให้ PostgreSQL สามารถค้นหาแถวข้อมูลที่ตรงกับเงื่อนไขได้อย่างรวดเร็ว โดยไม่ต้องสแกนทั้งตาราง (Sequential Scan)
• เพิ่มความเร็วในการจัดเรียง: สำหรับ Query ที่มีการ ORDER BY Index สามารถช่วยให้ไม่ต้องมีการ Sort ข้อมูลจริง
• ช่วยในการทำ Join: Index สามารถเพิ่มประสิทธิภาพของ Join Operation ได้ โดยเฉพาะอย่างยิ่ง Nested Loop Join

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

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

B-Tree Index

• คำอธิบาย: เป็น Index ประเภทที่ใช้กันมากที่สุดในฐานข้อมูลเชิงสัมพันธ์ เหมาะสำหรับการค้นหาแบบเท่ากับ (=), มากกว่า (>), น้อยกว่า (<), ระหว่าง (BETWEEN), และ LIKE ที่มีรูปแบบ 'prefix%'
• กรณีการใช้งาน:
  • Primary Keys และ Foreign Keys
  • คอลัมน์ที่ใช้ใน WHERE clause บ่อยๆ
  • คอลัมน์ที่ใช้ใน ORDER BY และ GROUP BY
• ตัวอย่าง:

  CREATE INDEX idx_products_price ON products (price);
  

Hash Index

• คำอธิบาย: ใช้สำหรับค้นหาแบบเท่ากับ (=) เท่านั้น โดยใช้ Hash Function ในการแปลงค่าข้อมูลเป็น Hash Value แล้วจัดเก็บ Hash Value นั้นๆ
• ข้อจำกัด: ใน PostgreSQL รุ่นเก่า (ก่อน 10) Hash Index ไม่ได้ถูก write-ahead logged และไม่ crash-safe จึงไม่แนะนำให้ใช้ใน Production ครับ แต่ใน PostgreSQL 10 เป็นต้นไป ได้รับการปรับปรุงให้ crash-safe แล้ว
• กรณีการใช้งาน: คอลัมน์ที่ต้องการค้นหาแบบเท่ากับอย่างรวดเร็ว และมีข้อมูลที่มี Cardinality สูง (ค่าไม่ซ้ำกันเยอะ)
• ตัวอย่าง: (ใช้เมื่อแน่ใจว่า PostgreSQL version >= 10)

  CREATE INDEX idx_users_email ON users USING HASH (email);
  

GIN Index (Generalized Inverted Index)

• คำอธิบาย: เหมาะสำหรับคอลัมน์ที่มีข้อมูลเป็น Array, JSONB, HSTORE หรือ Full-text Search โดยจะสร้าง Index สำหรับแต่ละ Element ภายในข้อมูลนั้นๆ
• กรณีการใช้งาน:
  • ค้นหาคำในคอลัมน์ Full-text Search (tsvector)
  • ค้นหา Element ใน Array
  • ค้นหา Key-Value ใน JSONB หรือ HSTORE
• ตัวอย่าง:

  CREATE INDEX idx_documents_content ON documents USING GIN (to_tsvector('english', content));
  CREATE INDEX idx_products_tags ON products USING GIN (tags); -- tags เป็น array
  CREATE INDEX idx_orders_jsonb ON orders USING GIN (order_details jsonb_path_ops); -- สำหรับ JSONB
  

GiST Index (Generalized Search Tree)

• คำอธิบาย: เป็นโครงสร้าง Index แบบ Tree ที่ยืดหยุ่น สามารถใช้กับข้อมูลที่ซับซ้อน เช่น Geographic Data (PostGIS), Range Types, และ Full-text Search (บางกรณี)
• กรณีการใช้งาน:
  • Spatial Data (เช่น ค้นหาจุดในพื้นที่)
  • Range Queries (เช่น ค้นหาช่วงเวลาที่ทับซ้อนกัน)
  • Full-text Search (ทางเลือกอื่นนอกเหนือจาก GIN)
• ตัวอย่าง: (ต้องมี PostGIS extension)

  CREATE INDEX idx_locations_geom ON locations USING GiST (geom);
  

BRIN Index (Block Range Index)

• คำอธิบาย: เหมาะสำหรับตารางขนาดใหญ่มาก (หลายร้อยล้านแถวขึ้นไป) ที่ข้อมูลมีการจัดเรียงตามธรรมชาติ (Natural Order) เช่น ตาราง Log ที่มีการเพิ่มข้อมูลใหม่ๆ เข้าไปเรื่อยๆ โดยมี Timestamp เป็นคอลัมน์หลัก BRIN จะเก็บช่วงของค่า (min/max) ของคอลัมน์นั้นๆ สำหรับแต่ละ Block ของข้อมูล
• กรณีการใช้งาน:
  • ตาราง Log ที่ข้อมูลถูกเพิ่มเข้ามาตามลำดับเวลา
  • คอลัมน์ที่มีค่าเรียงลำดับต่อเนื่องกัน (monotonically increasing/decreasing)
  • ต้องการ Index ที่มีขนาดเล็กมาก
• ตัวอย่าง:

  CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp_column);
  

SP-GiST Index (Space-Partitioned GiST)

• คำอธิบาย: คล้ายกับ GiST แต่มีประสิทธิภาพดีกว่าสำหรับข้อมูลบางประเภทที่โครงสร้าง Tree แบบธรรมดาไม่เหมาะ เช่น ข้อมูลแบบ Hierarchical (Quadtree, K-D tree) หรือ Geometric Data
• กรณีการใช้งาน:
  • ค้นหาในโครงสร้างข้อมูลแบบ Tree หรือ Hierarchical
  • Geometric Data (จุด, เส้น, รูปหลายเหลี่ยม)
• ตัวอย่าง:

  CREATE INDEX idx_points_location ON points USING SPGiST (location); -- location เป็น point type
  

ตารางเปรียบเทียบ Index ประเภทต่างๆ

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

เมื่อไหร่ควรสร้าง Index?

• คอลัมน์ที่เป็น Primary Key หรือ Unique Constraint (PostgreSQL จะสร้าง Index ให้อัตโนมัติอยู่แล้ว)
• คอลัมน์ที่เป็น Foreign Key (ไม่ได้สร้างอัตโนมัติ)
• คอลัมน์ที่ใช้ใน WHERE clause บ่อยๆ
• คอลัมน์ที่ใช้ใน ORDER BY หรือ GROUP BY บ่อยๆ
• คอลัมน์ที่ใช้ใน JOIN clause
• คอลัมน์ที่มี Cardinality สูง (มีค่าที่แตกต่างกันมาก)

เมื่อไหร่ไม่ควรสร้าง Index?

• คอลัมน์ที่มี Cardinality ต่ำมาก (มีค่าซ้ำกันเยอะ เช่น คอลัมน์เพศ ‘ชาย’/’หญิง’) เพราะ Query Optimizer อาจเลือก Sequential Scan ที่เร็วกว่า
• ตารางขนาดเล็กมาก (ไม่กี่พันแถว) เพราะ Overhead ของ Index อาจมากกว่าประโยชน์ที่ได้รับ
• คอลัมน์ที่มีการอัปเดตหรือ Insert บ่อยมาก เพราะทุกครั้งที่มีการเปลี่ยนแปลงข้อมูล Index ก็ต้องถูกอัปเดตด้วย ซึ่งเพิ่มภาระ I/O
• คอลัมน์ที่มีการเข้าถึงน้อยมาก

Index ขั้นสูง

Partial Indexes

เป็น Index ที่ครอบคลุมเฉพาะส่วนหนึ่งของข้อมูลในตาราง โดยมี WHERE clause เป็นตัวกำหนด

• ประโยชน์: ลดขนาดของ Index, เพิ่มความเร็วในการสร้างและบำรุงรักษา Index, ลด Overhead ในการอัปเดต
• กรณีการใช้งาน: คอลัมน์ที่ Query บ่อยๆ สำหรับสถานะข้อมูลบางอย่าง เช่น is_active = true, status = 'pending'
• ตัวอย่าง:

  CREATE INDEX idx_orders_pending ON orders (order_id) WHERE status = 'pending';
  

  Index นี้จะถูกใช้เมื่อ Query มี WHERE status = 'pending' เท่านั้น

Expression Indexes

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

• ประโยชน์: เพิ่มความเร็วให้กับ Query ที่ใช้ Function หรือ Expression เดียวกันใน WHERE clause
• กรณีการใช้งาน:
  • ค้นหาแบบ case-insensitive: LOWER(column_name)
  • ค้นหาจากส่วนหนึ่งของคอลัมน์: SUBSTRING(column_name, 1, 10)
  • Query ที่คำนวณจากหลายคอลัมน์
• ตัวอย่าง:

  CREATE INDEX idx_users_lower_email ON users (LOWER(email));
  -- Query จะใช้ Index นี้: SELECT * FROM users WHERE LOWER(email) = '[email protected]';
  

Unique Indexes

รับประกันว่าไม่มีข้อมูลซ้ำกันในคอลัมน์ที่ถูก Index ซึ่ง PostgreSQL จะสร้าง Index นี้โดยอัตโนมัติเมื่อคุณกำหนด UNIQUE constraint หรือ PRIMARY KEY

• ประโยชน์: enforces uniqueness, เพิ่มความเร็วในการค้นหาข้อมูลที่ไม่ซ้ำกัน

Covering Indexes (Index Only Scans)

คือ Index ที่มีคอลัมน์ทั้งหมดที่ Query ต้องการอยู่ในตัว Index เอง ทำให้ PostgreSQL สามารถดึงข้อมูลได้โดยตรงจาก Index โดยไม่ต้องเข้าถึง Table Heap (ตารางจริง) เลย ซึ่งจะเร็วกว่ามาก

• ประโยชน์: ลด I/O, เพิ่มประสิทธิภาพอย่างมาก
• กรณีการใช้งาน: Query ที่เลือก (SELECT) คอลัมน์ที่มีอยู่ใน Index และมี WHERE clause ที่ใช้คอลัมน์ใน Index นั้นๆ
• ตัวอย่าง:

  CREATE INDEX idx_products_price_name ON products (price) INCLUDE (product_name);
  -- Query นี้สามารถใช้ Index Only Scan ได้:
  -- SELECT product_name FROM products WHERE price > 100;
  

  (INCLUDE clause มีใน PostgreSQL 11+)

การเลือกและสร้าง Index ที่เหมาะสมต้องอาศัยการวิเคราะห์ Query ที่ช้า (จาก pg_stat_statements) และการทดสอบด้วย EXPLAIN ANALYZE อย่างสม่ำเสมอครับ

สำหรับข้อมูลเชิงลึกเกี่ยวกับการออกแบบ Index ลอง อ่านเพิ่มเติมเรื่อง “เทคนิคการออกแบบ Index ที่มีประสิทธิภาพใน PostgreSQL” ครับ

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

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

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

เป้าหมายหลักของการปรับแต่ง Query คือการลดจำนวนข้อมูลที่ต้องอ่านจากดิสก์ (I/O) และลดภาระการประมวลผลของ CPU

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

• ปัญหา: การใช้ SELECT * ดึงข้อมูลทุกคอลัมน์ในตาราง แม้ว่าคุณจะต้องการเพียงไม่กี่คอลัมน์ก็ตาม ซึ่งทำให้ดึงข้อมูลที่ไม่จำเป็นมากขึ้น ส่งผลให้ใช้ I/O และ Network Bandwidth มากขึ้น
• วิธีแก้ไข: ระบุชื่อคอลัมน์ที่คุณต้องการอย่างชัดเจน
• ตัวอย่าง:

  -- ไม่ดี
  SELECT * FROM users WHERE id = 1;
  
  -- ดี
  SELECT id, username, email FROM users WHERE id = 1;
  

การใช้ JOIN ที่เหมาะสม

• ปัญหา: การ Join ตารางจำนวนมาก หรือการ Join ที่ไม่มีประสิทธิภาพ อาจทำให้ Query ช้าลงอย่างมาก
• วิธีแก้ไข:
  • ใช้ INNER JOIN แทน LEFT JOIN เมื่อไม่ต้องการข้อมูลจากตารางด้านขวาที่ไม่มีค่าตรงกัน (non-matching rows)
  • ตรวจสอบให้แน่ใจว่าคอลัมน์ที่ใช้ Join (เช่น Foreign Keys) มี Index
  • พยายาม Join ตารางที่เล็กกว่าก่อน (ถ้าเป็นไปได้) เพื่อลดจำนวนแถวที่ต้องประมวลผลในการ Join ถัดไป

การใช้ WHERE clause อย่างมีประสิทธิภาพ

• ปัญหา: การใช้ Function กับคอลัมน์ใน WHERE clause หรือการใช้ Operator ที่ไม่สามารถใช้ Index ได้ จะทำให้ Index ไม่ถูกใช้งาน และต้องทำ Sequential Scan
• วิธีแก้ไข:
  • หลีกเลี่ยงการใช้ Function กับคอลัมน์ที่ Index อยู่ใน WHERE clause เช่น WHERE DATE(created_at) = '2023-01-01' แทนที่จะเป็น WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
  • หากจำเป็นต้องใช้ Function ให้พิจารณาสร้าง Expression Index
  • ใช้ LIKE 'prefix%' เพื่อให้ B-Tree Index สามารถใช้งานได้ แต่ LIKE '%suffix' หรือ LIKE '%substring%' จะไม่ใช้ B-Tree Index
  • ใช้ EXPLAIN ANALYZE เพื่อตรวจสอบว่า Index ถูกใช้งานหรือไม่

การใช้ LIMIT และ OFFSET

• ปัญหา: การใช้ OFFSET จำนวนมาก (เช่น OFFSET 100000) จะทำให้ PostgreSQL ต้องสแกนและทิ้ง 100,000 แถวก่อนที่จะเริ่มส่งผลลัพธ์ ซึ่งไม่มีประสิทธิภาพ
• วิธีแก้ไข:
  • สำหรับ Pagination ที่มีข้อมูลเยอะๆ ให้ใช้เทคนิค “Keyset Pagination” หรือ “Seek Method” แทน OFFSET โดยใช้คอลัมน์ที่มี Index และไม่ซ้ำกัน (เช่น ID หรือ Timestamp) ในการระบุจุดเริ่มต้นของหน้าถัดไป
  • ตัวอย่าง Keyset Pagination:

    -- หน้าแรก
    SELECT id, title FROM posts ORDER BY id LIMIT 10;
    
    -- หน้าถัดไป (หลังจากได้ ID สุดท้ายของหน้าแรกคือ 123)
    SELECT id, title FROM posts WHERE id > 123 ORDER BY id LIMIT 10;
    

การจัดการ Subqueries และ CTEs

• ปัญหา: บางครั้ง Subquery หรือ Common Table Expressions (CTEs) สามารถเขียนได้หลายวิธี และบางวิธีอาจมีประสิทธิภาพดีกว่า
• วิธีแก้ไข:
  • ตรวจสอบว่า Subquery สามารถแปลงเป็น JOIN ได้หรือไม่ บางครั้ง JOIN มีประสิทธิภาพดีกว่า
  • ใช้ CTEs (WITH clause) เพื่อทำให้ Query อ่านง่ายขึ้น และบางครั้ง Optimizer สามารถใช้ประโยชน์จาก CTEs ได้ดี
  • ทดสอบประสิทธิภาพของแต่ละวิธีด้วย EXPLAIN ANALYZE

การใช้ Window Functions

Window Functions (เช่น ROW_NUMBER(), RANK(), SUM() OVER (...)) เป็นเครื่องมือที่ทรงพลังสำหรับการวิเคราะห์ข้อมูลและสร้างรายงานที่ซับซ้อน สามารถช่วยลดความจำเป็นในการใช้ Subquery หรือ Self-Join ที่อาจไม่มีประสิทธิภาพได้

• ประโยชน์: ช่วยให้สามารถคำนวณค่า Aggregate หรือ Ranking บนชุดของแถวที่เกี่ยวข้องกับแถวปัจจุบัน โดยไม่ต้อง Group ข้อมูลทั้งหมด
• ตัวอย่าง: การหา 3 สินค้าที่มียอดขายสูงสุดในแต่ละหมวดหมู่

  SELECT
      category,
      product_name,
      sales,
      rank
  FROM (
      SELECT
          category,
          product_name,
          sales,
          RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank
      FROM
          products_sales
  ) AS ranked_sales
  WHERE
      rank <= 3;
  

การปรับแต่ง Query เป็นกระบวนการที่ต้องทำอย่างต่อเนื่อง โดยเฉพาะเมื่อข้อมูลมีปริมาณเพิ่มขึ้น หรือมีรูปแบบการเข้าถึงข้อมูลที่เปลี่ยนไปครับ

การจัดการ Vacuum และ Analyze

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

ทำความเข้าใจ Bloat (Table and Index Bloat)

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

VACUUM vs VACUUM FULL

VACUUM (Standard)

• การทำงาน: สแกนตารางและ Index เพื่อทำเครื่องหมาย dead tuples ว่าสามารถนำพื้นที่กลับมาใช้ใหม่ได้ (reclaim space) แต่ไม่ได้ลดขนาดไฟล์บนดิสก์จริงในทันที มันจะนำพื้นที่ที่ว่างกลับมาใช้ใหม่สำหรับการ Insert หรือ Update ใหม่ๆ
• ข้อดี: ทำงานแบบ Non-blocking (ไม่ล็อกตาราง), ใช้ทรัพยากรน้อย, รันได้บ่อยๆ
• ข้อเสีย: ไม่ลดขนาดไฟล์บนดิสก์ทันที, ไม่ช่วยแก้ปัญหา Bloat ที่สะสมมานาน
• เมื่อใช้: ใช้เป็นประจำ (ส่วนใหญ่ผ่าน Autovacuum) เพื่อป้องกันการสะสมของ dead tuples และ Transaction ID Wraparound

VACUUM (VERBOSE, ANALYZE) my_table;

VACUUM FULL

• การทำงาน: สร้างตารางและ Index ขึ้นมาใหม่ทั้งหมด โดยคัดลอกเฉพาะข้อมูลที่มีชีวิตอยู่เท่านั้น ทำให้ขนาดไฟล์บนดิสก์ลดลงจริง
• ข้อดี: กู้คืนพื้นที่ดิสก์ทั้งหมดที่เกิดจาก Bloat ได้อย่างสมบูรณ์
• ข้อเสีย: ล็อกตารางทั้งหมด (exclusive lock) ทำให้ไม่สามารถทำ Query, Insert, Update, Delete ได้เลยในขณะที่รันอยู่, ใช้เวลาและทรัพยากรมาก
• เมื่อใช้: ใช้เป็นมาตรการสุดท้ายเมื่อตารางมี Bloat สะสมมากจนเป็นปัญหาจริงๆ และสามารถยอมรับ Downtime ได้

VACUUM FULL (VERBOSE) my_table;

ข้อแนะนำสำคัญ: โดยทั่วไป VACUUM FULL ไม่ควรใช้เป็นส่วนหนึ่งของการบำรุงรักษาประจำวันใน Production ครับ ควรพึ่งพา Autovacuum เป็นหลัก และหากมี Bloat รุนแรง ควรพิจารณาใช้เครื่องมือเช่น pg_repack ซึ่งสามารถทำ Rebuild ตารางแบบ Online ได้ หรือใช้การ Partitioning เพื่อลดขนาดของตารางและ Index

ANALYZE

• การทำงาน: รวบรวมสถิติเกี่ยวกับข้อมูลในตารางและ Index เช่น จำนวนแถว, ค่าสูงสุด/ต่ำสุด, ความถี่ของค่า (value distribution) สถิติเหล่านี้จะถูกใช้โดย Query Optimizer ในการสร้างแผนการรัน Query ที่มีประสิทธิภาพ
• ข้อดี: ช่วยให้ Query Optimizer เลือกแผนการรัน Query ที่ดีที่สุด
• เมื่อใช้: ควรทำเมื่อข้อมูลในตารางมีการเปลี่ยนแปลงอย่างมีนัยสำคัญ หรือเป็นส่วนหนึ่งของ VACUUM หรือ AUTOVACUUM
  

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

  • Network Troubleshooting: เทคนิคแก้ปัญหาเครือข่ายอย่างเป็นระบบ
  • Next.js 15 สิ่งใหม่ที่ต้องรู้ สำหรับ Frontend Developer
  • Content Delivery Network: CDN Architecture, Edge Computing, Caching Strategies และ Performance
  SiamCafe.net — ชุมชน IT ที่ใหญ่ที่สุด · Siam2R.com — Portfolio งาน IT