สรุป PostgreSQL จากหนังสือ Seven Databases in Seven weeks

“Every good carpenter must understand what’s in their tool belt”

หนังสือ Seven Databases in Seven weeks เป็นหนังสือที่เล่าถึง modern databases ที่ถูกหยิบมา 7 ตัว โดยจะเล่า database ทีละ chapter นั่นคือ PostgreSQL, Apache HBase, MongoDB, Apache CouchDB, Neo4J, DynamoDB, และ Redis

โดยผู้เขียนจะแบ่ง chapter นั้นเป็นการอ่านแบบ 3 วัน เพื่อที่ว่า 1 week นั้นผู้อ่านจะได้มีเวลาอ่านและทำแบบฝึกหัดไปด้วย

เราจึงเห็นว่าเป็นหนังสือที่ทำการเล่าเรื่องได้น่าสนใจ เลยลองสรุปเนื้อหาที่อ่านภายใน week แรกดู นั่นก็คือ PostgreSQL

PostgreSQL

PostgreSQL (”Post-greS-Q-L”)

PostgreSQL หรือว่า Postgres คือ relational database management system (RDBMS)

Relational database คือ two-dimensional tables ที่ประกอบไปด้วย rows และ columns

และถึงแม้ว่าจะมี database รุ่นใหม่อย่างพวก NoSQL เข้ามา ตัว relational database ก็ยังคงเป็นที่นิยม แต่แน่นอนว่า NoSQL ไม่ได้เกิดมาเพื่อฆ่า Relational database เพราะ database แต่ละตัวต่างมีข้อดีและข้อเสียที่ต่างกัน

Fun Facts

ที่มาของคำว่า relational นั้นความจริงแล้วมันไม่ได้ชื่อว่า relation เพียงเพราะว่าความเป็น table ‘relate’ กันผ่าน foreign key แต่เกิดจาก mathematics

RDBMS ถูก built on top ของทฤษฎีของ Relational Algebra

Relational Algebra cr. https://www.researchgate.net

ถ้าเราแปลง SQL query นี้ปรับเป็นสมการจะได้ตามภาพ

History of Postgres

PostgreSQL นั้นเกิดขึ้นจริงๆในปี 1995

ซึ่งความจริงแล้วมีประวัติที่ยาวนานกว่านั้นคือนางอยู่ในโปรเจคหนึ่งภายใต้ UC Berkeley ที่มีตั้งแต่ 1970s แล้ว และโปรเจคนั้นถูกเรียกว่า Interactive Graphics and Retrienal System (หรือ Ingres) แล้วค่อยๆพัฒนา version ไปเรื่อยๆเป็น post-Ingres ซึ่งเป็นที่มาของคำว่า “Postgres” นี่เอง

จนโปรเจคนี้ก็จบไปในปี 1993 และถูกนำมาเข้า Open Source community ในชื่อ Postgres95 จนสุดท้ายปรับมาเป็น PostgreSQL แบบที่เรารู้จักในปัจจุบัน

Relations, CRUD, and Joins

PostgreSQL นั้นก็เหมือน database อื่นๆ คือ PostgreSQL จะให้ back-end server โดยวิธีการเข้าไปทำงานคือผ่าน command-line shell (psql) เพื่อที่จะ connect ไปยัง server ผ่าน port 5432

psql 7dbs

เมื่อเราเข้าไปสู่หน้า prompt แล้ว โดยสังเกตว่าถ้ามี # ข้างหน้าจะแปลว่า run as admin แต่ถ้า $ จะเป็น regular user

7dbs=# \\h CREATE INDEX

CRUD

คือ operationในการกระทำกับ data ที่ย่อมาจาก Create, Read, Update, Delete

1. Create

CREATE TABLE countries (
	country_code char(2) PRIMARY KEY,
	country_name text UNIQUE
)
INSERT INTO countries (country_code, country_name)
VALUES ('us', 'United States'), ('mx', 'Mexico'), ('ll', 'Loompaland');

2. Read

SELECT * 
FROM countries;

3. Update

UPDATE cities
SET postal_code = '11110'
WHERE name = 'Nonthaburi'

4. Delete

DELETE FROM countries
WHERE country_code = 'll';

Joins

1. Inner Join
2. Outer Join

Fast Lookup and Index

Index คือ data structure หนึ่งที่ถูกสร้างขึ้นเพื่อเลี่ยงการ full table scan เมื่อเรา query

โดย PostgreSQL จะสร้าง index จาก Primary Key หรือที่เราเรียกว่า B-tree Index

• B-tree Index

key นั้นจะเป็น Primary key value ส่วน value จะ point ไปที่ row on disk

• Hash index

เป็นอีกวิธีในการทำ index เหมือนกับ B-tree แต่ index value จะ unique และมี performance ที่ดีกว่า B-tree index

Advanced Queries, Code, and Rules

Advanced Queries

1. Aggregate functions

ตัวอย่างเช่น count(), min(), max()

SELECT COUNT(title)
FROM events

2. Grouping

GROUP BY เป็นวิธีที่บอกกับ Postgres ว่าจะยุบ rows ให้อยู่เป็น group

SELECT venue_id, count(*)
FROM events
GROUP BY venue_id

result:

venue_id   |  count
-----------+--------
         1 |     1
	 2 |     2
	 3 |     1

HAVING จะคล้าย WHERE clause ยกเว้นมันสามารถใช้ Aggregate function ได้

SELECT venue_id
FROM events
GROUP BY venue_id
HAVING count(*) >= 2 AND venue_id IS NOT NULL;

result:

venue_id  |  count
----------+--------
        2 |     2

3. Window Functions

คล้าย GROUP BY แต่ต่างกันตรงที่ GROUP BY จะคืน result ออกมาเป็น single row ในขณะที่ Window function จะไม่ collapse rows

cr https://www.oreilly.com/

ในหนังสือระบุว่า “Window Functions ไม่เป็นที่นิยมมากนัก เพราะ PostgreSQL เป็นหนึ่งในไม่กี่ open source ที่ implement เรื่องนี้”

แต่ตัดภาพกลับมา 2022 ในปัจจุบันมีหลายเจ้าทำ window functions มากมาย ทั้ง open source (อย่าง sqlite มาตอนปลายปี 2018 ส่วนหนังสือออกต้นปี 2018) และรวมถึงพวก managed services on cloud แล้วอย่าง BigQuery, Redshift

ACID concept

PostgreSQL จะใช้ ACID compliance ที่ย่อมาจาก:

• Atomic: all operation succeed or none do
• Consistent (คนละ C กับใน CAP theorem): data will always be in a good state and never in am inconsistent state
• Isolated: transaction don’t interfere with one another
• Durable: committed transaction is safe even after a server crash

ตัวอย่างเช่น

ในกรณีที่มี transaction ที่ จะ transfer เงินไปกลับ 5000

BEGIN TRANSACTION;
	UPDATE account SET total=total+5000.0 WHERE account_id=1337;
	UPDATE account SET total=total-5000.0 WHERE account_id=1337;
END;

แล้วในระหว่างที่กำลัง Update แรกและสอง, server ดันตายขึ้นมา แต่เมื่อถูก wrap ใน transaction ก็จะการันตีได้ว่า เงินทั้งหมดที่ต้อง Update ใน transaction นี้จะถูก rolled back

Code

1. Store Procedures

การเขียน SQL ไม่ตอบโจทย์ทุกอย่าง ในบางทีที่เราอยากรัน as code ดังนั้น Store Procedure ก็จะเป็นคำตอบ

ซึ่ง Procedure จะทำหน้าที่เหมือนเป็น function ตัวหนึ่ง โดยจะถูกเรียกผ่าน trigger หรือ application ก็ได้

ในหนังสือเขาจะเขียน procedures เป็น PL/pgSQL โดยเขียนสร้างเป็น function แล้วเรียกผ่าน function นั้นได้ผ่าน SELECT ซึ่งเป็นแบบเก่า
ณ ขณะนั้นที่หนังสือออกในปี 2018 เป็นช่วงเดียวกับที่ PostgreSQL v.11 มาพอดี และปล่อย SQL command ตัว CREATE PROCEDURE

เลยขออนุญาติเอาตัวอย่างที่เป็น CREATE PROCEDURE ให้ดูแทน

CREATE PROCEDURE plus(INOUT a int,INOUT b int) AS $$
BEGIN
    a := a + b;
END;
$$ LANGUAGE plpgsql;

เวลาเรียก

CALL plus(1,1)

ข้อดีของ store procedures คือ มี huge performance

แต่ข้อเสียคือ อาจจะกินค่า architecture cost สูง (ซึ่งไปหามาเพิ่ม: ก็คือกิน RAM นั่นเอง)

ส่วนใครที่สนใจว่าการเขียน store procedure แบบเก่าเป็นยังไง ลองลิ้งนี้ดูได้

2. Trigger

trigger จะรัน function หรือ store procedure เมื่อมีการ event เกิดขึ้น เช่น มีการ insert หรือ update กับ table นั้น

เช่น

CREATE TRIGGER log_events
	 AFTER UPDATE ON events
   FOR EACH ROW EXECUTE PROCEDURE log_event();

ในตัวอย่างจะเป็นการสร้าง trigger ที่ชื่อว่า log_events ในกรณีที่ table events มีการ update ขึ้น โดยจะไป trigger procedure ที่ชื่อ log_event()

3. View

View นั้นมาเพื่อลดความซับซ้อนของ queries

ตัวอย่างเช่น เราอยากดูวันหยุดจาก table ที่ชื่อ events และมีคำว่า day ในนั้นและไม่มี venue

CREATE VIEW holidays AS
  SELECT event_id AS holiday_id, title 
  FROM events
  WHERE title LIKE '%Day%' AND venue_id IS NULL;

เราก็สามารถสร้างตัวนี้เป็น view ได้ และในครั้งต่อไป ก็สามารถ query ผ่านเรียกเหมือน table เลย

SELECT * FROM holidays WHERE date <= '2021-02-01';

แต่ข้อจำกัดของ View คือ ไม่สามารถ Update view โดยตรงได้ Rules จึงเข้ามาแก้ไขปัญหานี้

4. Rules

เนื่องจาก flow ของ table ตอนนี้เป็น

events → holidays

หากเราอยากจะ update view ที่ชื่อ holidays วิธีของ rule คือ จะทำการ capture การ update จาก table holidays โดยตรง แทนที่จะเป็น events

CREATE RULE update_holidays AS ON UPDATE TO holidays DO INSTEAD
   UPDATE events
   SET title = NEW.name,
       starts = NEW.date
   WHERE title = OLD.name

Fuzzy Search

PostgreSQL สามารถทำ text searching ได้เหมือนกัน เราจะไม่เอามา compare กับพวก searching framework จริงๆนะ (อย่างพวก Elasticsearch) แต่จะเล่าว่า สิ่งที่ Postgres ทำได้คือการเสิร์ชจาก database โดยแบ่งออกได้หลายวิธีในการค้นหาและมี usecase ที่หลากหลาย ลองดูว่าแต่ละเคสเหมาะกับอะไร

1. SQL standard string matches

• LIKE and ILIKE

ILIKE เป็น case insensitive

% matches any number or character, _ matches exactly one character

SELECT title  
FROM movies  
WHERE title ILIKE 'stardust%';

result:

   title
--------------
Stardust 
Stardust Memories

ถ้าเราอยากได้ stardust แบบที่ไม่จบแค่ตัว string ตัวสุดท้าย ก็จะปรับได้นิดนึง

SELECT title  
FROM movies  
WHERE title ILIKE 'stardust_%';

result:

   title 
-------------- 
Stardust Memories

• Regex

ใน regular expression นั้น จะใช้ ~ operator

! คือ not matching, * คือ case insensitive

SELECT COUNT(*) FROM movies WHERE title !~* '^the.*';

เคสที่เหมาะ: 2 case นี้เป็น case ที่เหมาะกับ match string precisely นิดนึง

2. Bride of Levenshtein

โดยคอนเซปคือ จะคำนวนว่า string 2 ตัวนั้นห่างกันกี่ steps โดยดูจากความต่างของ string นั้น

SELECT levenshtein('bat', 'fads')

ผลที่ได้คือจะได้ Levenshtein distance มาเป็น 3 เพราะ (b⇒f, t⇒d) และเพิ่มตัว (+s) มา

ถ้าเป็นคำๆเดียวกัน (’bat’, ‘bat’) จะได้ Levenshtein เป็น 0

เคสที่เหมาะ: Levenshtein distance เหมาะกับเคสที่สำหรับ misspelling ที่น้อยมากๆ

3. Trigram

วิธีการทำงานของ trigram คือจะให้ผลลัพท์ที่เป็น character 3 ตัวจาก input

SELECT show_trgm('Avatar')

result:

show_trgm
----------------------
{" a", "  av", "ar. ", ata, aba, tar, vat}

ซึ่งในการหา matching string ก็วัดผลโดยคำนวนจากจำนวนที่ trigram พ่นมาเทียบกับ string ในฐานข้อมูลของเราว่าตรงกันมากแค่ไหน

และยิ่ง input string ยิ่งยาว จะยิ่งทำให้ trigram มีความแม่นยำยิ่งขึ้น

SELECT title
FROM movies
WHERE title % 'Avatre';

result:

     title
-------------------
      Avatar

เคสที่เหมาะ: Trigram เป็นเคสที่เหมาะกับ reasonable misspelled matches

4. Full-text searching

วิธีนี้จะเป็นกรณีที่ user จำคำเป๊ะๆได้ แต่จำแค่ได้บางส่วนของทั้งหมด

SELECT title
FROM movies
WHERE title @@ 'night & day'
title
-------------------
A hard **Day**'s Night
Six Days Seven Nights

วิธีนี้จะเอาเรื่อง natural language processing flexibility เข้ามาด้วย

ข้อดีคือจะมาช่วย ignore word บางอย่างเช่น a กับ the ได้ และคำที่เป็น pluralization (คำพหูพจน์)

5. Metaphones

วิธีนี้เหมาะกับ ไม่รู้วิธีสะกดแต่รู้ว่าออกเสียงยังไง เช่น

อยากหาคำว่า “Bruce Willis” แต่ลองสะกดเป็น “Broos Wils” และระบุจำนวน character ของ output ด้วย

เช่น เรากำลังตามหาหนังที่ Bat Man เลยใส่ไปจำนวน character เป็น 6

SELECT title
FROM movies NATURAL JOIN movies_actors NATURAL JOIN actors
WHERE metaphone(name, 6) = metaphone('Broos Wils', 6)

และยังมี function เช่น dmetaphone() (double metaphone), dmetaphone_alt() for alternative name pronunciations, soundex()

6. Mixing

เช่น เอา trigram มาผสมกับ metaphone ก็ได้ แล้วแต่เราจะลอง

7. Multidimensional vector

เป็น method ที่หา closest points ที่อยู่ใน boundary ของ cube package

ซึ่งตรงนี้ขอไม่สรุปเพิ่มละ เดี๋ยวจะเยอะเกิน

Postgres และ JSON

Postgres นั้น support JSON ตั้งแต่ version 9.3 โดยมี 2 support format คือ JSON และ JSONB

โดย 2 ตัวนี้แตกต่างกันตรง

JSON type

• store JSON as text
• optimized for faster data input

JSONB

• store JSON using a decomposed binary format
• optimized for faster processing

Strength ของ PostgreSQL

• PostgreSQL เป็นหนึ่งใน top open source relational database ที่มีอายุมากกว่า 30 ปี
• มี academic research, industrial movement, และ production use มากมาย
• flexible เรื่องการ query มากๆ สามารถ extract ข้อมูลได้ตามต้องการและสะดวก ด้วยการ joins, filters, views และ index
• และด้วยเหตุผลด้านบน programming language หลายเจ้าต้อง battled-tested driver support กับ PostgreSQL เพราะนางคือเจ้าแม่ที่เหมือนบรรทัดฐาน และรวมถึงจำนวน driver support ก็มีเยอะมากเช่นกัน

Weakness ของ PostgreSQL

• ไม่เหมาะการทำ Partition
• ถ้าอยาก scale out (คืออยากจะ multiple parallel db มากกว่ามี cluster เครื่องเดียว) ก็อาจจะต้องไปหา way อื่น
• เคสที่ไม่ต้องการ full database เราอาจจะไปมองหา cache อย่าง Redis
• เคสที่ต้องการ high-volume read and write ก็อาจจะมองหา key value database แทน
• เคสที่ต้องเก็บไฟล์ BLOB ใหญ่ๆ ก็ไม่เหมาะกับ PostgreSQL

Conclusion

PostgreSQL เป็นหนึ่งใน database ที่เก่าแก่และ robust ที่สุดในบรรดา 7 ตัว

มี ACID เพื่อรับประกัน data safety และมีวิธี search ที่หลากหลาย ที่ไม่ได้มีแค่การทำ regex, LIKE และทั้งนี้ ผู้เขียนก็กล่าวเพิ่มว่าหากใช้ module ที่ถูกต้อง, มีการ tune engine ดีๆ, ทำ index ดีๆ PostgreSQL ก็จะ performs well กับข้อมูลหลาย terabytes เลย

หากมีตรงไหนเขียนผิดสามารถทักได้เลยนะคะ ขอบคุณที่อ่านถึงตรงนี้ค่า