ตารางคลัสเตอร์และตารางต่างประเทศใน PostgreSQL

ฉันชื่อยาคูปอฟ อาซัต ฉันเป็นสถาปนิกข้อมูล และนี่คือความต่อเนื่องของตำนานประเภทตารางใน PostgreSQL ในส่วนนี้ เราจะพูดถึงตารางแบบคลัสเตอร์และตารางต่างประเทศ มาดูตัวอย่างการสร้างสรรค์ ขอบเขตการใช้งาน และข้อดีข้อเสียของการใช้งานกัน

ตารางคลัสเตอร์ใน PostgreSQL

ใน PostgreSQL ตารางคลัสเตอร์คือตารางที่มีการเปลี่ยนแปลงลำดับทางกายภาพของแถวเพื่อให้ตรงกับลำดับของแถวในดัชนี สิ่งนี้แตกต่างจากพฤติกรรมปกติของตาราง โดยที่ลำดับทางกายภาพของแถวอาจไม่ตรงกับลำดับของแถวตามที่กำหนดโดยดัชนีใดๆ

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

ในกระบวนการทำงานกับ DBMS ในระดับดิสก์ เนื้อหาของตารางจะเปลี่ยนแปลงอยู่ตลอดเวลา ตัวอย่างเช่น คุณได้อัปเดตข้อมูลแล้ว และแถวที่อัปเดตของคุณไปอยู่ในอีกหน้าหนึ่งของตาราง (ในที่นี้เราควรพูดถึง "FILLFACTOR") โดยมีทูเพิลที่ไม่ทำงานในตำแหน่งปัจจุบัน จากนั้นกระบวนการดูดอัตโนมัติจะลบทูเพิลที่ตายแล้วออก และช่องที่ว่างก็เต็มไปด้วยแถวที่ได้รับใหม่

แบบทดสอบง่ายๆ ที่คุณเองก็ทำได้ สร้างคำสั่งต่อไปนี้ลงในตารางที่สร้างขึ้นใหม่ตามปกติ:

INSERT INTO test(id,name) VALUES(1, ‘Peter’);
INSERT INTO test(id,name) VALUES(2, ‘Ivan’);
INSERT INTO test(id,name) VALUES(3, ‘Sergey’);

หลังจากดำเนินการค้นหา SQL (โปรดทราบว่าไม่มี ORDER BY):

SELECT *
   FROM test;

คุณจะเห็นภาพที่คาดหวัง:

แต่ด้วยการทำการอัพเดตแถว

UPDATE test
  SET name = ‘Ruslan’
WHERE id = 2;

จากนั้นเมื่อรัน SQL เดียวกัน คุณจะได้รับ:

ลำดับแถวเปลี่ยนไป! เอนโทรปีได้เติบโตขึ้น

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

แต่เมื่อคุณมีลำดับ ดังในตารางทางด้านขวา คุณจะรู้ได้อย่างชัดเจนว่าเลข 4 อยู่ระหว่าง 3 ถึง 5 นั่นคือจุดรวมของการจัดระเบียบลำดับและตารางแบบคลัสเตอร์: พวกมันช่วยสร้างโครงสร้างที่ได้รับคำสั่งให้ปราศจากความสับสนวุ่นวาย หากคุณสุ่มเลือกตำแหน่งสุ่มในตารางสีน้ำเงินตามลำดับเพื่อค้นหาหมายเลข 4 ผลลัพธ์ที่เป็นไปได้สามประการ:

• จำนวนเท่ากับจำนวนที่ต้องการ
• จำนวนน้อยกว่าที่ต้องการ
• จำนวนมากกว่าที่ต้องการ

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

ลองพิจารณาตัวอย่างการสร้างตารางแบบคลัสเตอร์:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

CLUSTER [VERBOSE] test.cluster_table USING id_idx;

ที่นี่ฉันสร้างตารางชื่อ cluster_table และตั้งค่า FILLFACTOR เป็น 90% นี่คือเปอร์เซ็นต์การเติม ไม่มีผลกระทบต่อตารางคลัสเตอร์ของเราแต่อย่างใด นี่เป็นเพียงตัวอย่างวิธีตั้งค่าคุณสมบัติเมื่อสร้างตารางประเภทนี้ ต่อไป ฉันสร้างดัชนี BTree บนตาราง (CREATE INDEX) ในช่อง id และเรียกใช้คำสั่ง CLUSTER คำสั่ง CLUSTER ทำการจัดกลุ่มตารางโดยใช้ดัชนีที่เราสร้างไว้ก่อนหน้านี้

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

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

เริ่มแรกตารางถูกวางไว้ในไฟล์หมายเลข 45969 หลังจากคำสั่ง CLUSTER ชื่อไฟล์ก็เปลี่ยนไป ข้อมูลถูกย้ายจากไฟล์หนึ่งไปยังอีกไฟล์หนึ่ง ดังนั้น การบล็อกจึงเกิดขึ้น และด้วยเหตุนี้ การรับส่งข้อมูลขาเข้าจึงไม่สามารถใช้ตารางนี้ได้จนกว่าจะพร้อมใช้งาน

คุณยังสามารถสร้างดัชนีสำหรับการจัดกลุ่มในภายหลังที่มีหลายคอลัมน์ (ดัชนีหลายคอลัมน์) หรือระบุลำดับจากมากไปน้อยสำหรับบางคอลัมน์ (DESC / ASC)

คุณสามารถเลือกใช้คำสั่ง CLUSTER VERBOSE ซึ่งจะส่งกลับรายละเอียดว่า PostgreSQL ทำอะไรบ้าง เช่น มีกี่เพจ เพจไหนถูกย้าย และอื่นๆ

กรณีทดสอบและลำดับข้อมูล

มาทำแบบทดสอบกันหน่อย:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

INSERT INTO test.cluster_table
  SELECT (random( )*100)::INTEGER, 'test'
     FROM generate_series(1,100) AS g(i);

SELECT id
   FROM test.cluster_table;

มาสร้างตาราง โดยสร้างดัชนีตามช่อง id จากนั้นสร้าง 100 แถวตามต้องการโดยใช้คำสั่ง Generate_series ผลลัพธ์คือข้อมูลที่ไม่เรียงลำดับ:

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

ในกรณีนี้ เรามาทำคลัสเตอร์ตารางด้วยคำสั่ง CLUSTER VERBOSE โดยใช้ดัชนีที่ฉันสร้างไว้ล่วงหน้า:

CLUSTER VERBOSE test.cluster_table USING id_idx;

SELECT id
 FROM test.cluster_table;

Voila ข้อมูลจะถูกจัดเรียงโดยไม่เรียงลำดับ:

แต่มีกับดักอยู่ที่นี่ มาอัปเดตแถวทั้งหมดกัน แต่จริงๆ แล้ว เปลี่ยนค่าของแถวเดียวก็เพียงพอแล้ว

UPDATE test.cluster_table
   SET id = id * (random( )::INTEGER);

SELECT id
 FROM test.cluster_table;

ในกรณีนี้ ความโกลาหลจะกลับมาที่ตารางคลัสเตอร์ของเรา:

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

CLUSTER VERBOSE test.cluster_table;

SELECT id
   FROM test.cluster_table;

คุณจะสามารถสังเกตลำดับได้อีกครั้งหลังจากคำสั่ง CLUSTER เท่านั้น นี่คือจุดอ่อนของตารางแบบคลัสเตอร์: การเปลี่ยนแปลงใดๆ ในคีย์การจัดกลุ่มสามารถนำมาซึ่งความไม่เป็นระเบียบให้กับข้อมูลได้ทันที

เมื่อตารางคลัสเตอร์มีความเหมาะสม

ตารางแบบคลัสเตอร์จะเหมาะสมหากข้อมูลของคุณเป็นตารางอ้างอิง (ดี หรือ SCD — ขนาดที่เปลี่ยนแปลงช้าๆ) เช่น ระบบที่อยู่ ตารางประเภทนี้จะสะดวกหากคุณอัปโหลดข้อมูลใหม่ไม่บ่อยนัก เช่น เดือนละครั้ง

หากตารางเปลี่ยนแปลงบ่อยมากและอยู่ภายใต้การดำเนินการ INSERT, UPDATE และ DELETE ก็จะต้องมีการรวมกลุ่มอย่างต่อเนื่อง ซึ่งไม่สะดวกและมีความสำคัญโดยทั่วไป วัตถุประสงค์ของการทำคลัสเตอร์คือการหลีกเลี่ยง ORDER BY ที่ไม่จำเป็นในการสืบค้นตารางอย่างต่อเนื่องโดยฟิลด์หรือฟิลด์ที่คลัสเตอร์

ข้อมูลเมตาของตารางคลัสเตอร์

จากข้อมูลเมตาของตารางแบบคลัสเตอร์ คุณจะเข้าใจได้ว่าตารางเป็นแบบคลัสเตอร์:

SELECT  c.oid AS “OID”,
        c.relname AS “Relation name”
  FROM pg_class c INNER JOIN pg_index i 
                  ON i.indrelid = c.oid
WHERE c.relkind = ‘r’ AND 
      c.relhasindex AND 
      i.indisclustered;

ค่า "จริง" ในช่อง relhasindex บ่งชี้ว่ามีดัชนีที่รองรับการจัดกลุ่ม เมื่อเราสร้างคลัสเตอร์ใหม่ในคำสั่ง CLUSTER ถัดไป PostgreSQL จะใช้ดัชนีที่ระบุจากข้อมูลเมตา

ตารางต่างประเทศใน PostgreSQL

ตารางต่างประเทศใน PostgreSQL คือตารางที่เก็บอยู่นอกฐานข้อมูล ตารางเหล่านี้สามารถอยู่บนเซิร์ฟเวอร์ฐานข้อมูลอื่นหรือในไฟล์ได้ เป็นต้น ตารางต่างประเทศมีประโยชน์ในแง่ของการรับข้อมูลจากแหล่งอื่นอย่างรวดเร็ว หากคุณมีความสามารถในการเข้าร่วม

นอกจากนี้ หากคุณปรับเปลี่ยน คุณสามารถจัดเตรียมสิ่งที่เรียกว่าวงจรชีวิตข้อมูลเพื่อจัดเตรียมตัววัดนโยบายการเก็บรักษาได้ ชุดเครื่องมือต่อไปนี้สามารถช่วยคุณได้ที่นี่:

• ดู (ตารางเสมือน)
• ชุดตารางปกติที่คั่นด้วยตรรกะการเก็บข้อมูล (การออกแบบ POOD) พร้อมข้อมูลล่าสุด
• ตารางต่างประเทศที่เน้นไปที่ไฟล์ที่จัดเก็บข้อมูลนอกฐานข้อมูลบนดิสก์ราคาถูก (ที่นี่คุณจะพบข้อมูลเก่าที่เกินเกณฑ์ชี้วัดนโยบายการเก็บรักษา)

มีตารางและประเภทการเชื่อมต่อต่างประเทศมากมาย เช่น:

• ไฟล์ CSV
• การเชื่อมต่อกับ RDBMS อื่น ๆ อีกมากมาย
• การเชื่อมต่อกับฐานข้อมูล NoSQL บางส่วน

มาดูตัวอย่างตารางต่างประเทศที่ใช้ไฟล์ CSV กัน ส่วนขยาย file_fdw ที่ใช้ fdw — wrapper ข้อมูลต่างประเทศ — จะช่วยเราในเรื่องนี้:

CREATE EXTENSION file_fdw;

CREATE SERVER csv_log FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER csv_log 
  OPTIONS (filename '/var/lib/postgresql/file.csv', 
                   delimiter ‘;', format 'csv');

ฉันสร้างตารางต่างประเทศและอธิบายคุณลักษณะโดยระบุเซิร์ฟเวอร์สำหรับ fdw ซึ่งฉันสร้างขึ้นล่วงหน้าพร้อมตัวเลือกสำหรับการทำงานกับไฟล์

หากฉันสร้างแบบสอบถาม SQL ไปยังตารางต่างประเทศ ฉันจะเห็นข้อมูลที่แสดงในไฟล์ เนื่องจากมีการลงทะเบียนตารางต่างประเทศ (หมายความว่ามีรายการอยู่ในข้อมูลเมตาของ PostgreSQL) ฉันจึงมีสมมติฐาน: ข้อมูลไม่จัดเก็บไว้ในไฟล์ภายนอก แต่อยู่ในไฟล์ข้อมูล PostgreSQL หรือไม่

SELECT  oid AS “OID”,
        pg_relation_filepath(oid) AS “File path”,
        pg_relation_size(oid) AS “Relation Size”  
 FROM pg_class
WHERE relname = ‘csv’;

ผลการดำเนินการ:

ดังนั้น ตารางภายนอกในฐานะออบเจ็กต์จะถูกลงทะเบียนในข้อมูลเมตา (มีตัวระบุ OID ของออบเจ็กต์) แต่ไม่มีไฟล์ข้อมูลที่เกี่ยวข้อง กล่าวคือ ข้อมูลจะถูกนำเสนอในแหล่งข้อมูลภายนอกเท่านั้น

แบบสอบถามไปยังตารางต่างประเทศ

แบบสอบถามไปยังตารางต่างประเทศทำงานอย่างไร ลองใช้ไฟล์ CSV เป็นตัวอย่าง

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

มีห้องสมุดโต๊ะต่างประเทศมากมายสำหรับความต้องการที่หลากหลาย ตัวอย่างเช่น postgres_fdw ด้วยความช่วยเหลือนี้ เราสามารถเชื่อมต่อกับ PostgreSQL จาก PostgreSQL ได้ มันเหมือนกับลิงค์ฐานข้อมูลมาก:

CREATE EXTENSION postgres_fdw;

DROP FOREIGN TABLE test.csv;

CREATE SERVER pg_log FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.10', port '5432', dbname ‘course_db');

CREATE USER MAPPING FOR test SERVER pg_log 
OPTIONS (user 'test', password 'test');

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER pg_log 
  OPTIONS (schema_name 'test', table_name ‘user');

มี "ไลบรารีจำนวนมาก" จำนวนมากสำหรับการทำงานกับแหล่งข้อมูลภายนอก ตัวอย่างเช่น:

• ออราเคิล, MySQL, SQLite, เซิร์ฟเวอร์ MS SQL, Sybase
• แคสแซนดรา, MongoBD, HBase, Redis, Neo4j
• ทวิตเตอร์, โทรเลข
• JSON, XLM, GeoFiles, LDAP

ข้อมูลเมตาของตารางต่างประเทศ

ตามที่เราค้นพบ ตารางที่แปลกในฐานะวัตถุได้รับการแก้ไขในข้อมูลเมตา:

SELECT  oid AS "OID",
        relname AS “Relation name",
        CASE
         WHEN relpersistence = 'p' THEN 'Permanent'
         WHEN relpersistence = 't' THEN 'Temporary'
         ELSE 'Unlogged'
       END AS “Type”,
       relkind AS “Subtype”
 FROM pg_class
WHERE relname = ‘csv’;

มันเป็นตารางถาวร (น่าประหลาดใจ) แต่มีตัวชี้ "f" ซึ่งเป็นประเภทย่อยของความสัมพันธ์ และมันบ่งบอกว่าโต๊ะของเราเป็นของต่างประเทศนั่นคือภายนอก

ในซีรีย์ต่อไป

นั่นคือทั้งหมดสำหรับวันนี้ เราจะวิเคราะห์ในเนื้อหาต่อไปนี้:

• ตารางที่แบ่งพาร์ติชัน
• ตารางที่สืบทอดมา