PostgreSQL generated columns เพื่อเร่งตัวกรองในหน้าจอแอดมิน

ทำไมหน้าจอแอดมินถึงช้าและยุ่งได้เร็ว\n\nหน้าจอแอดมินมักจะเริ่มจากเรื่องง่าย ๆ: ตาราง สักตัวกรอง และการเรียงลำดับ “ใหม่สุดก่อน” แต่เมื่อระบบใช้งานจริง คำขอจากฝ่ายต่าง ๆ เริ่มเพิ่มขึ้น Support ต้องการค้นหาลูกค้าด้วยชื่อ อีเมล และโทรศัพท์ ฝ่ายขายอยากเรียงตาม “กิจกรรมล่าสุด” ฝ่ายการเงินอยากกรอง “ยอดค้างชำระ” คำขอแต่ละอย่างเพิ่มเงื่อนไข การ join และการคำนวณเล็ก ๆ น้อย ๆ\n\nรายการแอดมินส่วนใหญ่ช้าลงด้วยเหตุผลเดียวกัน: ทุกการคลิกจะเปลี่ยนคำสั่ง SQL. การกรองและการเรียงลำดับอาจทำให้ฐานข้อมูลต้องสแกนแถวจำนวนมาก โดยเฉพาะเมื่อคำสั่งต้องคำนวณค่าต่อแถวก่อนจะตัดสินได้ว่าแถวไหนตรงตามเงื่อนไข\n\nจุดเปลี่ยนที่พบบ่อยคือเมื่อ WHERE และ ORDER BY ถูกเติมด้วยนิพจน์แทนคอลัมน์ตรง ๆ แทนที่จะกรองด้วยคอลัมน์ธรรมดา คุณอาจกรองด้วย lower(email), date_trunc('day', last_seen_at) หรือ CASE ที่แม็ปสถานะหลายตัวเป็นกลุ่มเดียว นิพจน์เหล่านี้ไม่เพียงแต่ช้าลง แต่ยังทำให้ SQL อ่านยาก หายากต่อการทำดัชนี และเสี่ยงต่อความผิดพลาด\n\nSQL แอดมินที่ยุ่งมักมาจากรูปแบบซ้ำ ๆ บางอย่าง:\n\n- กล่องค้นหาหนึ่งอันที่เช็กหลายฟิลด์ด้วยกฎต่างกัน\n- การเรียงโดยค่าที่ได้จากการคำนวณ (ชื่อเต็ม ค่าคะแนนความสำคัญ หรือ “เหตุการณ์ล่าสุด”)\n- กฎธุรกิจที่คัดลอกไปมาข้ามหน้าจอ (active vs inactive, paid vs overdue)\n- การปรับแต่งเล็ก ๆ น้อย ๆ (trim, lower, coalesce) กระจายอยู่ทุกที่\n- ค่าคำนวณเดียวกันถูกใช้ทั้งในรายการ ตัวกรอง และการเรียงลำดับ\n\nทีมมักพยายามซ่อนความยุ่งเหยิงนี้ไว้ที่ชั้นแอป: ตัวสร้างคำสั่ง SQL แบบไดนามิก การ join แบบมีเงื่อนไข หรือการคำนวณล่วงหน้าในโค้ด นั่นอาจช่วยได้ แต่ก็ทำให้ตรรกะกระจายระหว่าง UI และฐานข้อมูล ซึ่งทำให้การดีบักคำสั่งช้าทำได้ยาก\n\nเป้าหมายชัดเจน: คำสั่งที่เร็วและอ่านได้ เมื่อค่าที่คำนวณกลับมาใช้บ่อยบนหน้าจอแอดมิน PostgreSQL generated columns จะเก็บกฎไว้ในที่เดียวและยังให้ฐานข้อมูลปรับปรุงประสิทธิภาพได้\n\n## Generated columns อธิบายแบบง่าย ๆ\n\nGenerated column คือคอลัมน์ปกติในตารางที่ค่าของมันถูกคำนวณจากคอลัมน์อื่น ๆ คุณไม่ต้องเขียนค่านั้นเอง PostgreSQL จะเติมค่าให้โดยใช้นิพจน์ที่คุณกำหนด\n\nใน PostgreSQL generated columns เป็นแบบเก็บจริง (stored). PostgreSQL คำนวณค่าตอนแทรกหรืออัปเดตแถวแล้วบันทึกลงดิสก์เหมือนคอลัมน์ทั่วไป นั่นมักตรงกับความต้องการของหน้าจอแอดมิน: อ่านเร็ว และสามารถทำดัชนีค่าที่คำนวณได้\n\nสิ่งนี้ต่างจากการคำนวณเดิมในทุก ๆ คำสั่ง ถ้าคุณยังคงเขียน WHERE lower(email) = lower($1) ซ้ำ ๆ หรือเรียงด้วย last_name || ', ' || first_name คุณต้องจ่ายค่าการคำนวณซ้ำ ๆ และ SQL ของคุณจะรกขึ้น Generated column ย้ายการคำนวณซ้ำ ๆ นั้นไปไว้ในการออกแบบตาราง ทำให้คำสั่งเรียบง่ายและผลลัพธ์คงที่ทั่วระบบ\n\nเมื่อข้อมูลต้นทางเปลี่ยน PostgreSQL จะอัปเดตค่าที่คำนวณให้โดยอัตโนมัติสำหรับแถวนั้น แอปของคุณไม่ต้องจำว่าต้องซิงก์ค่า\n\nแบบจำลองความคิดที่มีประโยชน์:\n\n- นิยามสูตรครั้งเดียว\n- PostgreSQL คำนวณตอนเขียน (writes)\n- คำสั่งอ่านเหมือนคอลัมน์ปกติ\n- เพราะเป็นแบบเก็บจริง คุณสามารถทำดัชนีได้\n\nถ้าคุณเปลี่ยนสูตรภายหลัง คุณต้องเปลี่ยนสคีมา วางแผนเหมือนมิเกรชันปกติ เพราะแถวที่มีอยู่จะต้องอัปเดตให้ตรงกับนิพจน์ใหม่\n\n## กรณีที่เหมาะสำหรับฟิลด์ที่คำนวณในการกรองและการเรียงลำดับ\n\nGenerated columns เด่นเมื่อค่านั้นถูกอนุมานจากคอลัมน์อื่น ๆ เสมอ และคุณกรองหรือเรียงบนค่านั้นบ่อย ๆ พวกมันไม่ค่อยมีประโยชน์กับรายงานแบบครั้งเดียว\n\n### ฟิลด์ค้นหาที่ผู้ใช้มักใช้จริง\n\nการค้นหาในแอดมินไม่ค่อยเป็น “การค้นหาล้วน ๆ” คนมักคาดหวังว่ากล่องค้นหาจะจัดการข้อความรก ๆ ตัวพิมพ์ใหญ่เล็กไม่สม่ำเสมอ และช่องว่างที่เกินมาได้ ถ้าคุณเก็บ search key ที่ถูกปรับให้อยู่ในรูปแบบปกติไว้ คำสั่ง WHERE ก็จะอ่านง่ายและพฤติกรรมสอดคล้องกันทั่วหน้าจอ\n\nตัวอย่างที่เหมาะสมรวมถึงชื่อเต็มรวมกันที่ทำให้เป็นตัวพิมพ์เล็กและตัดช่องว่าง ข้อความที่ลบช่องว่างซ้ำ หรือป้ายสถานะที่ได้จากหลายฟิลด์\n\nตัวอย่าง: แทนที่จะเขียน lower(trim(first_name || ' ' || last_name)) ซ้ำ ๆ ให้สร้าง full_name_key ครั้งเดียวแล้วกรองด้วยค่านั้น\n\n### คีย์เรียงลำดับที่ตรงกับการเรียงของคน\n\nการเรียงลำดับคือที่ที่ฟิลด์คำนวณมักคืนผลเร็วที่สุด เพราะการเรียงอาจบังคับให้ PostgreSQL ประเมินนิพจน์สำหรับหลายแถว\n\nคีย์เรียงลำดับที่พบบ่อยได้แก่ ระดับเชิงตัวเลข (เช่น แผนบริการแม็ปเป็น 1, 2, 3), timestamp ของ “กิจกรรมล่าสุด” เดียว (เช่น max ของสอง timestamp), หรือโค้ดที่เติมศูนย์เพื่อให้เรียงถูกต้องแบบข้อความ\n\nเมื่อคีย์เรียงเป็นคอลัมน์ธรรมดาที่ทำดัชนี ORDER BY จะถูกลงค่าใช้จ่ายมากขึ้น\n\n### ธงที่อนุมานได้สำหรับตัวกรองด่วน\n\nผู้ใช้แอดมินชอบกล่องติ๊กเช่น “ค้างชำระ” หรือ “มูลค่าสูง” พวกนี้ทำงานได้ดีเป็น generated columns เมื่อตรรกะคงที่และอ้างอิงเฉพาะข้อมูลในแถวเท่านั้น\n\nตัวอย่าง: ถ้ารายการลูกค้าต้องการ “มีข้อความยังไม่อ่าน” และ “ค้างชำระ” ให้สร้าง has_unread boolean (จาก unread_count > 0) และ is_overdue (จาก due_date < now() และ paid_at is null) เพื่อให้ตัวกรองใน UI กลายเป็นเงื่อนไขง่าย ๆ\n\n## การเลือกระหว่าง generated columns, ดัชนี, และตัวเลือกอื่น ๆ\n\nหน้าจอแอดมินต้องการสามอย่าง: การกรองเร็ว การเรียงเร็ว และ SQL ที่คุณยังอ่านออกได้เมื่อผ่านไปหลายเดือน การตัดสินใจจริงคือการเลือกว่าการคำนวณจะอยู่ที่ไหน: ในตาราง ในดัชนี ใน view หรือในโค้ดแอป\n\nGenerated columns เหมาะเมื่อคุณต้องการให้ค่าทำตัวเหมือนคอลัมน์จริง: อ้างอิงง่าย แสดงใน SELECT ได้ และไม่ลืมเวลาเพิ่มตัวกรองใหม่ พวกมันยังเข้ากันดีกับดัชนีแบบปกติ\n\nExpression indexes เพิ่มเร็วกว่าเพราะไม่ต้องเปลี่ยนโครงสร้างตาราง ถ้าคุณเน้นความเร็วและไม่กังวลเรื่อง SQL ที่ดูรก การทำดัชนีแบบนิพจน์มักพอเพียง ข้อด้อยคือเรื่องความอ่านได้ และคุณพึ่งพา planner ให้จับคู่กับนิพจน์ที่ตรงเป๊ะ\n\nViews ช่วยเมื่อคุณต้องการ “รูปร่าง” ของข้อมูลที่ใช้ร่วมกัน โดยเฉพาะถ้ารายการแอดมิน join หลายตาราง แต่ view ที่ซับซ้อนอาจซ่อนงานที่หนักและเพิ่มจุดที่ต้องดีบัก\n\nTriggers สามารถทำให้คอลัมน์ปกติซิงก์ได้ แต่เพิ่มชิ้นส่วนที่เคลื่อนไหว พวกมันอาจทำให้การอัปเดตแบบกลุ่มช้าลงและมักถูกมองข้ามเมื่อดีบัก\n\nบางครั้งทางเลือกที่ดีที่สุดคือคอลัมน์ธรรมดาที่เติมโดยแอป หากผู้ใช้สามารถแก้ไขค่าได้ หรือสูตรเปลี่ยนบ่อยตามนโยบายธุรกิจ (ไม่ใช่แค่ข้อมูลแถว) การเก็บไว้อย่างชัดเจนจะชัดเจนกว่า\n\nวิธีตัดสินใจอย่างรวดเร็ว:\n\n- ต้องการคำสั่งอ่านง่ายและสูตรคงที่ที่อิงแค่ข้อมูลแถว? ใช้ generated column.\n- ต้องการความเร็วสำหรับตัวกรองเฉพาะและไม่กังวนนักเรื่อง SQL รก? ใช้ expression index.\n- ต้องการรูปร่างข้อมูลแบบ join ที่ใช้ซ้ำหลายที่? พิจารณา view.\n- ต้องการตรรกะข้ามตารางหรือผลข้างเคียง? ให้โฟกัสที่โลจิกในแอปก่อน ใช้ trigger เป็นทางเลือกสุดท้าย.\n\n## ทีละขั้นตอน: เพิ่ม generated column และใช้ในคำสั่ง\n\nเริ่มจากคำสั่งรายการแอดมินช้าที่คุณรู้สึกช้าใน UI เขียนลงว่าหน้าจอใช้ตัวกรองและการเรียงลำดับอะไรบ้าง ปรับปรุงคำสั่งเดียวที่เห็นผลก่อน\n\nเลือกฟิลด์ที่คำนวณซึ่งลดงานซ้ำ และตั้งชื่อตามมาตรฐาน snake_case เพื่อให้คนอื่นคาดเดาได้ว่ามันเก็บอะไรโดยไม่ต้องอ่านนิพจน์ใหม่\n\n### 1) เพิ่ม generated column (STORED)\n\nsql\nALTER TABLE customers\nADD COLUMN full_name_key text\nGENERATED ALWAYS AS (\n lower(concat_ws(' ', last_name, first_name))\n) STORED;\n\n\nตรวจผลกับข้อมูลจริงก่อนเพิ่มดัชนี:\n\nsql\nSELECT id, first_name, last_name, full_name_key\nFROM customers\nORDER BY id DESC\nLIMIT 5;\n\n\nถ้าผลลัพธ์ผิด ให้แก้นิพจน์ตอนนี้ STORED หมายความว่า PostgreSQL จะเก็บค่าที่อัปเดตตอนแทรกและอัปเดตแถว\n\n### 2) เพิ่มดัชนีที่ตรงกับหน้าจอแอดมิน\n\nถ้าหน้าจอแอดมินกรองตาม status และเรียงตามชื่อ ให้สร้างดัชนีแบบนี้:\n\nsql\nCREATE INDEX customers_status_full_name_key_idx\nON customers (status, full_name_key);\n\n\n### 3) อัปเดตคำสั่งแอดมินให้ใช้คอลัมน์ใหม่\n\nก่อนหน้านี้คุณอาจมี ORDER BY ที่ยุ่ง หลังจากนี้มันชัดเจน:\n\nsql\nSELECT id, status, first_name, last_name\nFROM customers\nWHERE status = 'active'\nORDER BY full_name_key ASC\nLIMIT 50 OFFSET 0;\n\n\nใช้ generated columns กับส่วนที่คนกรองและเรียงลำดับทุกวัน ไม่ใช่หน้าจอหายาก\n\n## รูปแบบการทำดัชนีที่ตรงกับหน้าจอแอดมินจริง\n\nหน้าจอแอดมินมักทำพฤติกรรมซ้ำ ๆ: กรองด้วยไม่กี่ฟิลด์ เรียงลำดับด้วยคอลัมน์หนึ่ง แล้วแบ่งหน้า การตั้งค่าที่ดีที่สุดไม่ใช่ “ทำดัชนีทุกอย่าง” แต่เป็น “ทำดัชนีรูปแบบของคำสั่งที่ใช้บ่อยที่สุด”\n\nกฎปฏิบัติ: ใส่คอลัมน์กรองที่ใช้บ่อยไว้ด้านหน้า และคอลัมน์เรียงที่ใช้บ่อยไว้ท้าย ถ้าคุณใช้ multi-tenant workspace_id มักจะอยู่ด้านหน้า เช่น (workspace_id, status, created_at)\n\nการค้นหาด้วยข้อความเป็นปัญหาอีกเรื่อง กล่องค้นหาหลายที่กลายเป็น ILIKE '%term%' ซึ่งยากจะเร่งด้วย btree ธรรมดา แนวทางที่ช่วยได้คือเก็บคอลัมน์ช่วยค้นหาที่ถูกทำให้เป็นมาตรฐานแทนการค้นหาในข้อความดิบ (ตัวพิมพ์เล็ก ตัดช่องว่าง บางทีรวมกัน) ถ้า UI ของคุณยอมรับการค้นหาแบบพยางค์ขึ้นต้น (term%) ดัชนี btree บนคอลัมน์ที่ปรับแล้วจะช่วยได้ ถ้าต้องเป็น contains (%term%) ให้พิจารณาปรับพฤติกรรม UI หรือจำกัดชุดข้อมูลในการค้นหา\n\nตรวจ selectivity ก่อนเพิ่มดัชนี ถ้าค่า 95% ของแถวมีค่าเดียวกัน (เช่น status = 'active') การทำดัชนีคอลัมน์นั้นอย่างเดียวช่วยน้อย ให้จับคู่กับคอลัมน์ที่มีการกระจายมากกว่า หรือใช้ partial index สำหรับกรณีส่วนน้อย\n\n## ตัวอย่างสมจริง: หน้าแอดมินลูกค้าที่ยังเร็ว\n\nลองนึกภาพหน้าลูกค้าทั่วไป: กล่องค้นหา ตัวกรองไม่กี่อย่าง (inactive, ช่วงยอดคงเหลือ) และคอลัมน์เรียง “Last seen” ที่เรียงได้ เมื่อเวลาผ่านไป SQL จะเต็มไปด้วย LOWER(), TRIM(), COALESCE(), คำนวณวันที่ และ CASE ซ้ำ ๆ\n\nวิธีหนึ่งที่ทำให้มันเร็วและอ่านง่ายคือย้ายนิพจน์ที่ซ้ำไปเป็น generated columns\n\n### ตารางและ generated columns\n\nสมมติว่าตาราง customers มี name, email, last_seen, และ balance ให้เพิ่มฟิลด์คำนวณสามตัว:\n\n- search_key: ข้อความปกติสำหรับการค้นหาแบบง่าย\n- is_inactive: boolean สำหรับกรองโดยไม่ต้องเขียนตรรกะวันที่ซ้ำ\n- balance_bucket: ป้ายสำหรับแบ่งกลุ่มยอดง่าย ๆ\n\nsql\nALTER TABLE customers\n ADD COLUMN search_key text\n GENERATED ALWAYS AS (\n lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))\n ) STORED,\n ADD COLUMN is_inactive boolean\n GENERATED ALWAYS AS (\n last_seen IS NULL OR last_seen < (now() - interval '90 days')\n ) STORED,\n ADD COLUMN balance_bucket text\n GENERATED ALWAYS AS (\n CASE\n WHEN balance < 0 THEN 'negative'\n WHEN balance < 100 THEN '0-99'\n WHEN balance < 500 THEN '100-499'\n ELSE '500+'\n END\n ) STORED;\n\n\nตอนนี้คำสั่งสำหรับแอดมินอ่านเหมือน UI\n\n### ตัวกรอง + การเรียงที่อ่านง่าย\n\n“ลูกค้าที่ไม่ active เรียงตามกิจกรรมล่าสุด” จะกลายเป็น:\n\nsql\nSELECT id, name, email, last_seen, balance\nFROM customers\nWHERE is_inactive = true\nORDER BY last_seen DESC NULLS LAST\nLIMIT 50;\n\n\nและการค้นหาเบื้องต้นจะเป็น:\n\nsql\nSELECT id, name, email, last_seen, balance\nFROM customers\nWHERE search_key LIKE '%' || lower(trim($1)) || '%'\nORDER BY last_seen DESC NULLS LAST\nLIMIT 50;\n\n\nผลลัพธ์จริงคือความสม่ำเสมอ ฟิลด์เดียวกันขับเคลื่อนหลายหน้าจอโดยไม่ต้องเขียนตรรกะซ้ำ:\n\n- กล่องค้นหาหน้ารายการลูกค้าใช้ search_key\n- แท็บ “ลูกค้าที่ไม่ active” ใช้ is_inactive\n- ชิปตัวกรองยอดใช้ balance_bucket\n\n## ความผิดพลาดและกับดักที่พบบ่อย\n\nGenerated columns ดูเหมือนคำตอบง่าย: ใส่การคำนวณไว้ในตารางและคำสั่งจะสะอาดขึ้น แต่มันจะช่วยก็ต่อเมื่อสอดคล้องกับรูปแบบการกรองและการเรียง และเมื่อคุณเพิ่มดัชนีที่ถูกต้อง\n\nความผิดพลาดที่พบบ่อย:\n\n- คิดว่ามันจะเร็วขึ้นโดยไม่ต้องทำดัชนี ค่าที่คำนวณยังต้องการดัชนีเพื่อกรองหรือเรียงที่ขนาดใหญ่\n- อัดตรรกะมากเกินไปในฟิลด์เดียว ถ้าคอลัมน์ที่สร้างขึ้นกลายเป็นโปรแกรมย่อม ๆ ผู้คนจะไม่ไว้วางใจ เก็บให้สั้นและตั้งชื่อชัดเจน\n- ใช้ฟังก์ชันที่ไม่ immutable PostgreSQL กำหนดให้นิพจน์สำหรับ stored generated column ต้องเป็น immutable ฟังก์ชันอย่าง now() และ random() ทำให้เกิดปัญหาและมักไม่ถูกอนุญาต\n- มองข้ามค่าใช้จ่ายในการเขียน แทรกและอัปเดตต้องรักษาค่าที่คำนวณไว้ การอ่านเร็วขึ้นไม่คุ้มค่าถ้าการนำเข้าข้อมูลหรือการซิงก์ช้าจนเกินไป\n- สร้างคอลัมน์ที่ซ้ำเกือบเหมือนกัน มากกว่าที่จะมาตรฐานหนึ่งหรือสองรูปแบบ (เช่น key ปกติเดียว)\n\nถ้ารายการแอดมินของคุณต้องการการค้นหาแบบ contains (ILIKE '%ann%') generated column เพียงอย่างเดียวคงไม่เพียงพอ คุณอาจต้องใช้แนวทางการค้นหาอื่น แต่สำหรับการกรองและเรียงลำดับประจำวันที่ใช้เป็นประจำ generated columns พร้อมดัชนีที่ถูกต้องมักทำให้ประสิทธิภาพคาดเดาได้มากขึ้น\n\n## เช็คลิสต์ก่อนปล่อยขึ้นโปรดักชัน\n\nก่อนจะนำการเปลี่ยนแปลงไปใช้กับแอดมิน ลองยืนยันว่าค่าที่คำนวณ คำสั่ง และดัชนีสอดคล้องกัน\n\n- สูตรคงที่และอธิบายในประโยคเดียวได้\n- คำสั่งใช้ generated column จริงใน WHERE และ/หรือ ORDER BY\n- ดัชนีตรงกับการใช้งานจริง ไม่ใช่การทดสอบครั้งเดียว\n- เปรียบเทียบผลลัพธ์กับตรรกะเดิมในกรณีขอบเขต (NULL, สตริงว่าง, ช่องว่างประหลาด, ตัวพิมพ์ผสม)\n- ทดสอบประสิทธิภาพการเขียนถ้าตารางมีงานเขียนมาก (imports, background updates, integrations)\n\n## ขั้นตอนต่อไป: นำไปใช้กับหน้าจอแอดมินของคุณ\n\nเลือกจุดเริ่มต้นเล็ก ๆ ที่มีผลกระทบสูง: 2–3 หน้าจอแอดมินที่คนเปิดทุกวัน (orders, customers, tickets). จดว่าจุดไหนรู้สึกช้า (ตัวกรองตามช่วงวันที่, การเรียงตาม “กิจกรรมล่าสุด”, การค้นหาชื่อรวม, การกรองตามป้ายสถานะ) จากนั้นมาตรฐานฟิลด์ที่คำนวณสั้น ๆ ที่นำกลับมาใช้ข้ามหน้าจอได้\n\nแผนการไล่ปรับที่วัดผลได้และย้อนกลับได้ง่าย:\n\n- เพิ่ม generated column(s) โดยใช้ชื่อตรงไปตรงมา\n- รันระบบเก่าและใหม่ขนานกันสั้น ๆ ถ้าจะทดแทนตรรกะเดิม\n- เพิ่มดัชนีที่ตรงกับตัวกรองหรือการเรียงหลัก\n- เปลี่ยนคำสั่งหน้าจอให้ใช้คอลัมน์ใหม่\n- วัดก่อนและหลัง (เวลา query และแถวที่สแกน) แล้วลบวิธีแก้เดิมออก\n\nถ้าคุณสร้างเครื่องมือแอดมินภายในด้วย AppMaster (appmaster.io) ฟิลด์คำนวณเหล่านี้เข้ากับโมเดลข้อมูลที่แชร์ได้ดี: ฐานข้อมูลถือกฎไว้ และ UI ของคุณสามารถชี้ไปที่ชื่อฟิลด์ตรง ๆ แทนการเขียนนิพจน์ซ้ำข้ามหน้าจอ