ปรับปรุง PostgreSQL 16 บน Ubuntu 26: Indexing Strategy ประสิทธิภาพสูง!

byBoonyadol Morruchai (Senior Full-stack Developer) •17:20

0

Case Study: การปรับปรุงประสิทธิภาพฐานข้อมูล PostgreSQL 16 บน Ubuntu Server 26 ด้วย Indexing Strategy

ผมทำงานที่บริษัทพัฒนาซอฟต์แวร์ด้านการวิเคราะห์ข้อมูลขนาดใหญ่มาประมาณ 10 ปีแล้ว และตลอดเวลา ผมเคยเจอปัญหาที่เกิดขึ้นซ้ำๆ กับทีมพัฒนาของเรา นั่นคือ ฐานข้อมูล PostgreSQL ของเรา (เวอร์ชัน 16 บน Ubuntu Server 26) เริ่มทำงานช้าลงเรื่อยๆ แม้ว่าปริมาณข้อมูลจะไม่ได้เพิ่มขึ้นอย่างรวดเร็ว แต่เวลาในการตอบสนอง query ต่างๆ ก็เริ่มยาวขึ้นเรื่อยๆ ทำให้ระบบโดยรวมทำงานได้ไม่ราบรื่น แถมยังทำให้ทีมต้องเสียเวลาในการ debug ปัญหาเหล่านี้อีกด้วย ปัญหานี้เกิดขึ้นบ่อยครั้งกับระบบการจัดการข้อมูลลูกค้าของเรา โดยเฉพาะ query ที่ใช้ในการสร้างรายงานยอดขายรายเดือน ซึ่งเป็น workload ที่หนักที่สุดของระบบ

บทความนี้จะช่วยให้คุณและทีมงานสามารถปรับปรุงประสิทธิภาพของ PostgreSQL 16 บน Ubuntu Server 26 ได้อย่างมีประสิทธิภาพ โดยจะเน้นไปที่การใช้ Indexing Strategy อย่างชาญฉลาด ผมจะเล่าถึงปัญหาที่เคยเจอ พร้อมทั้งแนวทางแก้ไขที่เป็นรูปธรรม พร้อมตัวอย่าง code ที่สามารถนำไปใช้ได้เลยทันที รวมถึงข้อควรระวังที่มักจะหลุดลุ่ย และวิธีการแก้ไข เพื่อให้คุณสามารถนำไปปรับใช้กับโปรเจกต์ของคุณได้จริง

ปัญหาหลัก: Query ช้าเนื่องจาก Index ไม่เหมาะสม

a person holding a piece of paper over a laptop — Photo by Jakub Żerdzicki on Unsplash

ตอนแรกที่ผมเริ่มพบปัญหาคือ Query ที่ใช้ในการเลือกข้อมูลลูกค้าที่มีเงื่อนไขซับซ้อน (เช่น เลือกลูกค้าที่มียอดขายเกิน 1 ล้านบาทในช่วงปี 2026 และมีประวัติการซื้อสินค้าประเภท A) ใช้เวลานานมากจนน่าตกใจ ตอนแรกผมคิดว่าอาจจะเป็นปัญหาเรื่อง hardware แต่หลังจากตรวจสอบแล้ว พบว่าปัญหาหลักคือ Index ที่เราสร้างไว้ไม่เหมาะสมกับ query เหล่านี้ เรามี Index หลายตัว แต่ Index เหล่านั้นไม่ได้ถูก optimize เพื่อให้ PostgreSQL สามารถใช้ได้อย่างมีประสิทธิภาพสูงสุด

การวิเคราะห์ Query และการสร้าง Index

วิเคราะห์ Query: ก่อนอื่น เราต้องเข้าใจ query ที่ทำงานช้าๆ ใช้เครื่องมือ PostgreSQL เช่น `EXPLAIN ANALYZE` เพื่อดูว่า PostgreSQL ใช้ index อะไรบ้าง และทำไมมันถึงไม่สามารถใช้ index ได้อย่างมีประสิทธิภาพ ผมมักจะใช้ `EXPLAIN ANALYZE` ใน conjunction กับ `pg_stat_statements` extension เพื่อระบุ query ที่ใช้เวลานานที่สุด


  EXPLAIN ANALYZE SELECT *
  FROM customers
  WHERE sales_amount > 1000000
  AND product_category = 'A'
  AND created_at BETWEEN '2026-01-01' AND '2026-12-31';

Output จาก `EXPLAIN ANALYZE` จะแสดงให้เห็นว่า PostgreSQL กำลังทำการ full table scan เนื่องจากไม่มี index ที่เหมาะสม และ query นี้มีเงื่อนไขหลายตัว ซึ่งทำให้ PostgreSQL ต้องสแกนทุกแถวเพื่อหาแถวที่ตรงตามเงื่อนไข

สร้าง Index ที่เหมาะสม: หลังจากวิเคราะห์ query แล้ว เราจะสร้าง index ที่เหมาะสมกับ query นั้น ในกรณีนี้ เราสามารถสร้าง index ที่ครอบคลุมเงื่อนไขทั้งหมดได้ดังนี้:
```
      CREATE INDEX idx_customers_sales_category_created_at ON customers (sales_amount, product_category, created_at);
      
```
Index นี้จะช่วยให้ PostgreSQL สามารถใช้ index ได้อย่างมีประสิทธิภาพมากขึ้น และลดเวลาในการตอบสนอง query

การปรับแต่ง Index และ Query

graphs of performance analytics on a laptop screen — Photo by Luke Chesser on Unsplash

การสร้าง index เพียงอย่างเดียวไม่เพียงพอ เราต้องปรับแต่ง index และ query ให้เหมาะสมด้วย นอกจากนี้ เรายังสามารถใช้ query hints เพื่อบังคับให้ PostgreSQL ใช้ index ที่เราสร้างไว้ได้


  SELECT *
  FROM customers
  WHERE sales_amount > 1000000
  AND product_category = 'A'
  AND created_at BETWEEN '2026-01-01' AND '2026-12-31'
  OPTION (FORCE INDEX);

คำสั่ง `OPTION (FORCE INDEX)` จะบังคับให้ PostgreSQL ใช้ index `idx_customers_sales_category_created_at` ในการ execute query นี้ แต่การใช้ query hints ควรทำด้วยความระมัดระวัง เพราะอาจทำให้ PostgreSQL ไม่สามารถปรับแต่ง query ได้อย่างเหมาะสม

สิ่งที่ควรระวัง / ข้อผิดพลาดที่เจอบ่อย

จากการทำงานจริง ผมพบว่ามีข้อผิดพลาดที่มักจะเกิดขึ้นในการปรับปรุงประสิทธิภาพฐานข้อมูล PostgreSQL ดังนี้:

Index Over-Indexing: การสร้าง index มากเกินไปจะทำให้ PostgreSQL ใช้เวลานานขึ้นในการ execute query เนื่องจาก PostgreSQL ต้องตัดสินใจว่าจะใช้ index ตัวไหนบ้าง เราควรสร้าง index เฉพาะ query ที่ใช้บ่อยจริงๆ เท่านั้น
Index Cardinality ที่ต่ำ: Index จะมีประสิทธิภาพมากขึ้นเมื่อมี cardinality สูง Cardinality คือจำนวนค่าที่เป็นไปได้ของ column ที่เราสร้าง index ถ้า column ที่เราสร้าง index มีค่าที่เป็นไปได้น้อย index ก็จะไม่ค่อยมีประสิทธิภาพเท่าที่ควร เราควรสร้าง index เฉพาะ column ที่มี cardinality สูง
Data Type ที่ไม่เหมาะสม: การใช้ data type ที่ไม่เหมาะสมจะทำให้ index ไม่สามารถใช้ได้อย่างมีประสิทธิภาพ เช่น การใช้ `TEXT` แทน `VARCHAR` จะทำให้ index ไม่สามารถใช้เพื่อค้นหาข้อมูลได้อย่างมีประสิทธิภาพ
ไม่ทำ Maintenance Index: Index จะเสื่อมสภาพเมื่อข้อมูลในตารางมีการเปลี่ยนแปลง เราควรทำ maintenance index เป็นประจำด้วยคำสั่ง `ANALYZE` และ `VACUUM`

สรุปและข้อเสนอแนะ

จากการปรับปรุงประสิทธิภาพฐานข้อมูล PostgreSQL 16 บน Ubuntu Server 26 ด้วย indexing strategy ผมได้เรียนรู้ว่า การวิเคราะห์ query อย่างละเอียด การสร้าง index ที่เหมาะสม การปรับแต่ง index และ query รวมถึงการดูแลรักษา index เป็นสิ่งสำคัญอย่างยิ่งในการปรับปรุงประสิทธิภาพของ PostgreSQL ผมอยากจะเน้นย้ำว่าการทำ indexing ไม่ใช่แค่การสร้าง index แต่เป็นการทำความเข้าใจ query และ data ที่เราใช้งาน

ตอนนี้ผมเริ่มใช้ PostgreSQL 16 อยู่ และ Ubuntu Server 26 เป็นประจำ ผมรู้สึกว่าการใช้ indexing strategy ช่วยลดเวลาในการตอบสนอง query ได้อย่างมาก ทำให้ระบบโดยรวมทำงานได้ราบรื่นขึ้น ผมอยากจะแนะนำให้ทีมงานของคุณลองนำแนวทางที่ผมได้เล่ามานี้ไปปรับใช้ ผมเชื่อว่าคุณจะได้ประโยชน์อย่างแน่นอน

Next Step: เริ่มต้นด้วยการใช้ `pg_stat_statements` extension เพื่อระบุ query ที่ใช้เวลานานที่สุด และใช้ `EXPLAIN ANALYZE` เพื่อวิเคราะห์ query เหล่านั้น จากนั้น สร้าง index ที่เหมาะสม และปรับแต่ง index และ query ให้เหมาะสม อย่าลืมทำ maintenance index เป็นประจำด้วยคำสั่ง `ANALYZE` และ `VACUUM`

คำถาม

คำถาม 1: ผมควรจะสร้าง index บน column ใดบ้าง? คำตอบ: คุณควรจะสร้าง index บน column ที่ใช้ในการ filter ข้อมูล (WHERE clause) และ column ที่ใช้ในการ sort ข้อมูล (ORDER BY clause) นอกจากนี้ คุณควรจะสร้าง index บน column ที่มี cardinality สูง

คำถาม 2: ผมควรจะทำ maintenance index อย่างไร? คำตอบ: คุณควรจะทำ maintenance index เป็นประจำด้วยคำสั่ง `ANALYZE` และ `VACUUM` คำสั่ง `ANALYZE` จะช่วยให้ PostgreSQL สามารถประมาณค่า statistical ของตารางได้ และคำสั่ง `VACUUM` จะช่วยให้ PostgreSQL สามารถลบข้อมูลที่ถูกทำเครื่องหมายว่าถูกลบออกไปได้

คำถาม 3: ผมควรจะใช้ query hints อย่างไร? คำตอบ: คุณควรใช้ query hints เฉพาะในกรณีที่จำเป็นเท่านั้น การใช้ query hints มากเกินไปจะทำให้ PostgreSQL ไม่สามารถปรับแต่ง query ได้อย่างเหมาะสม

Keyword: PostgreSQL, Indexing, Performance Optimization, Ubuntu Server, PostgreSQL 16, Query Optimization, Database Performance