0Pricing
SQL Academy · บทเรียน

ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)

สร้างดัชนีโดยไม่บล็อกการเขียนด้วย CREATE INDEX CONCURRENTLY และกู้คืนจากการสร้างที่ถูกขัดจังหวะ

ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY) เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

ปัญหาของ CREATE INDEX

CREATE INDEX ปกติจะใช้การล็อกระดับ SHARE ซึ่งจะบล็อกการเขียนข้อมูล แต่ไม่บล็อกการอ่าน ตลอดระยะเวลาที่สร้างดัชนี สำหรับตารางที่มีพันล้านแถว นั่นหมายถึงระบบเขียนข้อมูลหยุดทำงานหลายนาที

CONCURRENTLY: วิธีแก้ปัญหา

CREATE INDEX CONCURRENTLY จะสร้างดัชนีโดยไม่บล็อกการเขียนข้อมูล:

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

วิธีการทำงาน

สแกนตารางสองรอบ:

  1. รอบแรก: สร้างดัชนีจากแถวปัจจุบัน
  2. รอบที่สอง: ตรวจจับแถวที่มีการเปลี่ยนแปลงระหว่างรอบแรก

นอกจากนี้ยังรอช่วงสั้น ๆ สำหรับธุรกรรมที่ทำงานพร้อมกันและเริ่มต้นก่อนการสร้างดัชนี

ไม่สามารถทำงานภายในธุรกรรมได้

CONCURRENTLY ทำการ COMMIT สองครั้ง จึงไม่สามารถอยู่ภายในบล็อก BEGIN/COMMIT ที่ระบุไว้อย่างชัดเจนได้:

BEGIN;
CREATE INDEX CONCURRENTLY ...;  -- ERROR
COMMIT;

ช้า แต่ทำงานออนไลน์ได้

CONCURRENTLY ใช้เวลานานกว่า CREATE INDEX ปกติ เนื่องจากต้องสแกนสองรอบและรอการล็อก แต่ข้อดีคือไม่มีช่วงเวลาที่ระบบหยุดให้บริการ

การกู้คืนเมื่อเกิดข้อผิดพลาด

หาก CONCURRENTLY ล้มเหลว ไม่ว่าจะถูกขัดจังหวะหรือเกิดข้อผิดพลาดของระบบ ดัชนีจะค้างอยู่ในสถานะ INVALID:

SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;

-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;

REINDEX CONCURRENTLY

สร้างดัชนีใหม่โดยไม่บล็อก ตั้งแต่ PG 12 เป็นต้นไป:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;

DROP INDEX CONCURRENTLY

ไม่บล็อกเช่นกัน:

DROP INDEX CONCURRENTLY old_idx;

ดัชนี UNIQUE

ดัชนี UNIQUE ก็สามารถสร้างด้วย CONCURRENTLY ได้เช่นกัน ซึ่งมีประโยชน์เมื่อต้องการเพิ่มข้อจำกัด UNIQUE แบบออนไลน์:

CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;

ดัชนีของคีย์ต่างประเทศ

รูปแบบมาตรฐานเมื่อต้องการเพิ่ม FK ให้กับตารางขนาดใหญ่:

-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);

-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

ต้นทุนการสร้างดัชนีพร้อมกัน

การสแกนตารางเต็มรูปแบบสองรอบพร้อมการจัดการข้อมูลประกอบ ใช้ CPU และ IO มาก หากเป็นไปได้ ควรทำงานในช่วงที่มีการใช้งานน้อย

ตรวจสอบความคืบหน้า

PG 12 ขึ้นไปจะแสดงความคืบหน้าใน pg_stat_progress_create_index:

SELECT phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

สรุป

CONCURRENTLY เป็นวิธีจัดการดัชนีที่เหมาะกับระบบจริง

  • CREATE/DROP/REINDEX CONCURRENTLY ไม่บล็อกการเขียนข้อมูล
  • ไม่สามารถอยู่ภายในธุรกรรมได้
  • ความล้มเหลวจะทิ้งดัชนีไว้ในสถานะ INVALID — ต้องล้างออก
  • ตรวจสอบได้ด้วย pg_stat_progress_create_index

แบบทดสอบสั้น ๆ

เหตุใด CREATE INDEX CONCURRENTLY จึงทำงานภายในบล็อก BEGIN/COMMIT ที่ระบุไว้อย่างชัดเจนไม่ได้

คำถามที่พบบ่อย

บทเรียน “ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)”

สร้างดัชนีโดยไม่บล็อกการเขียนด้วย CREATE INDEX CONCURRENTLY และกู้คืนจากการสร้างที่ถูกขัดจังหวะ คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 2 จากทั้งหมด 4 บทเรียน

บทเรียน “ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. การย้ายข้อมูลแบบออนไลน์: เหตุผลที่ ALTER TABLE ล็อกตาราง
  2. ดัชนีแบบทำงานพร้อมกัน (CREATE INDEX CONCURRENTLY)
  3. การเปลี่ยนชื่อคอลัมน์โดยไม่หยุดให้บริการ
  4. เครื่องมือ: Flyway, Liquibase, Sqitch
← กลับไปที่ SQL Academy