สัปดาห์ที่ 8: การออกแบบทางกายภาพ
Indexing & Partitioning

จาก Logical Design สู่ Physical Design — ทำให้คลังข้อมูลเร็วและยั่งยืน

Project Phase

Phase 2: Physical Tuning ของโครงการ

“A good star schema without indexes is like a library without a catalog.”

Week 8 • PostgreSQL Indexes • Partitioning • EXPLAIN ANALYZE

Learning Outcomes

1

อธิบายความต่างของ Logical vs Physical design และผลกระทบต่อ query performance

2

เลือกและสร้าง Index ที่เหมาะ: B-Tree, Bitmap, BRIN, Composite/Covering

3

ออกแบบ Partitioning แบบ Range/List/Hash และอธิบาย partition pruning

4

สาธิตด้วย EXPLAIN ANALYZE และปรับจูนบนข้อมูลจริง 50k แถว

Roadmap วันนี้

Part 1

Foundations

Physical vs Logical, storage, row vs columnar

Part 2

Indexing

B-Tree • Bitmap • BRIN • Covering

Part 3

Partitioning

Range/List/Hash • Pruning

Part 4

Lab & Demo

50k rows + EXPLAIN + Tuning

ทุก part มี คำนิยามพร้อมลิงก์, ตัวอย่างจริง, และโค้ด copy-paste ได้ทันที

Part 1: Foundations

“Why physical design?”

Logical บอกว่าเก็บอะไร — Physical บอกว่าเก็บอย่างไรให้เร็วและถูก

• Star schema ที่ไม่มี index ต้อง full scan ทุกครั้ง → ช้า
• DW อ่านมาก เขียนน้อย → ปรับจูนเพื่อ read performance ได้เต็มที่

Logical vs Physical Design

Logical Design

• Star schema, snowflake, grain
• Fact + Dimension (เช่น fact_sales, dim_product)
• ไม่ผูกกับ DBMS ใด

Physical Design

• Storage, pages/blocks, tablespace
• Indexes, Partitioning, Tuning
• ผูกกับ PostgreSQL / Snowflake / BigQuery

ตัวอย่าง: Logical บอก fact_sales(date_key, product_key, amount) — Physical เลือก partition by date_key + index on product_key

Storage: Row vs Columnar

Row Store (Postgres heap)

เก็บทั้งแถวใน 8KB page — ดีกับ OLTP (อ่าน/เขียนทีละแถว)

Heap + TOAST สำหรับค่าขนาดใหญ่

Columnar (DW)

ClickHouse, Snowflake micro-partitions, BigQuery Capacitor — อ่านเฉพาะคอลัมน์ที่ SELECT, บีบอัดสูง

เหมาะกับ OLAP: SELECT SUM(amount) WHERE year=2024 ข้ามคอลัมน์อื่นทั้งหมด

Takeaway: บน PostgreSQL ที่เป็น row store เราจึงต้องพึ่ง index + partitioning มาก — ส่วน Snowflake/BigQuery ใช้ clustering / partitioning แทน index แบบดั้งเดิม

Demo Dataset (50,000 rows)

Star schema สังเคราะห์สำหรับห้องแล็บ — ดาวน์โหลดและรันได้ทันที

Schema

dim_product(100 rows)
dim_customer(500 rows)
dim_date(731 rows, 2023-2024)
fact_sales(50k rows)
  sale_id PK, date_key FK, product_key FK,
  customer_key FK, quantity, amount

⬇ dim_product.csv ⬇ dim_customer.csv ⬇ dim_date.csv ⬇ fact_sales.csv (1.3M)

Sample (fact_sales head)

sale_id	date_key	product	qty	amount
1	523	42	3	872.10
2	112	7	8	214.50
3	701	89	1	45.20
…	…	…	…	…

⬇ wk08_demo.sql (DDL + Index + Partition)

สร้างด้วย gen_wk08.py — seed 42, reproducible

Copy-paste: psql -f data/wk08/wk08_demo.sql หรือดู code ถัดไป

Part 2: Indexing

“Catalog ของห้องสมุด”

Index คือโครงสร้างช่วยค้นหาโดยไม่ต้องสแกนทั้งตาราง — แลกมาด้วยพื้นที่และเวลาตอนเขียน

โฟกัส: B-Tree • Bitmap • BRIN • Composite • Covering • Star-join indexing

B-Tree Index — จุดเริ่มต้น

How it works

ต้นไม้สมดุล B-Tree — ค้นหา =, <, >, BETWEEN, ORDER BY ใน O(log n)

CREATE INDEX idx_fact_sales_date
  ON fact_sales(date_key);
-- หรือ B-Tree เป็น default
CREATE INDEX idx ON fact_sales
  USING btree (date_key);

Postgres index types →

When to use in DW

• FK ของ fact: product_key, customer_key
• high-cardinality (เช่น 500 customers)
• ใช้กับ JOIN + EXPLAIN จะเห็น Index Scan

EXPLAIN ANALYZE
SELECT * FROM fact_sales
WHERE date_key = 523;
-- Seq Scan -> Index Scan หลังสร้าง index

Beyond B-Tree

Bitmap

บิตเวกเตอร์ต่อค่าต่าง → ดีกับ low-cardinality (tier, region)

-- Postgres จำลองด้วย B-Tree + bitmap scan
-- Oracle มี native bitmap
SELECT * FROM dim_customer
WHERE tier='Gold'; -- BitmapAnd/Or

Bitmap index →

BRIN

Block Range Index — สรุป min/max ต่อ block range, เล็กมาก

CREATE INDEX idx_brin
 ON fact_sales USING BRIN(date_key)
 WITH (pages_per_range=128);

ดีกับ append-only fact เรียงตามเวลา

Hash & อื่นๆ

Hash เฉพาะ = — ไม่เรียงลำดับ, น้อยครั้งใน DW

CREATE INDEX idx_hash
 ON dim_customer USING hash(email);

GIN สำหรับ JSON/FTS

DW เลือก: Fact FK = B-Tree หรือ BRIN, Dimension low-card = bitmap scan อัตโนมัติ

Composite & Covering Index

Composite (หลายคอลัมน์)

ลำดับสำคัญ: คอลัมน์กรองบ่อยก่อน

CREATE INDEX idx_fact_cover
 ON fact_sales(date_key, product_key);
-- ใช้ได้กับ WHERE date_key=... AND product_key=...
-- หรือ WHERE date_key=... (leftmost prefix)

Multicolumn indexes →

Covering + INCLUDE

Index-only scan — ไม่ต้องแตะ heap

CREATE INDEX idx_cover
 ON fact_sales(date_key, product_key)
 INCLUDE (amount, quantity);
-- SELECT amount WHERE date_key=... อ่านแค่ index

Index-only scans →

Star-join tip: บน fact_sales สร้าง composite บน (date_key, product_key, customer_key) ตาม query pattern ยอดนิยมของคุณ — วัดด้วย pg_stat_statements

Indexing Star Schema

dim_date
PK date_key
B-Tree PK

fact_sales
FK date/product/customer
B-Tree / BRIN

dim_product
PK product_key
B-Tree PK

Rule of thumb

Fact FK ทุกตัว → index (เว้นแต่จะ partition นั้นอยู่แล้ว)
Dimension PK → primary key (B-Tree)
Where-filter บ่อย → composite / covering

DW-specific

Bitmap scan อัตโนมัติเมื่อ filter หลาย FK
Columnar: ไม่ต้อง index — ใช้ clustering / clustering

อ้างอิง: Kimball Star Schema • Use The Index, Luke!

EXPLAIN ANALYZE ก่อน/หลัง Index

Before (Seq Scan)

EXPLAIN ANALYZE
SELECT SUM(amount) FROM fact_sales
WHERE date_key BETWEEN 400 AND 430;
-- Seq Scan on fact_sales
--   Filter: (date_key >=400 AND <=430)
--   Rows: 2134  Time: 18.4 ms

ต้องอ่านทุก page

After (Index Scan + Pruning)

CREATE INDEX idx_fact_sales_date
  ON fact_sales(date_key);
ANALYZE fact_sales;
EXPLAIN ANALYZE
SELECT SUM(amount) FROM fact_sales
WHERE date_key BETWEEN 400 AND 430;
-- Index Scan using idx_fact_sales_date
--   Buffers: shared hit=45
--   Time: 1.2 ms (15x faster)

EXPLAIN docs →

-- Copy-paste: วัดขนาด index
SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE relname LIKE 'idx_fact%';

Part 3: Partitioning

“Divide and conquer”

Partitioning แบ่งตารางใหญ่เป็นชิ้นเล็ก — query อ่านเฉพาะชิ้นที่เกี่ยวข้อง (partition pruning)

ประเภท: Range • List • Hash — และใช้ร่วมกับ index ได้

Why Partition?

⚡

Pruning

WHERE date_key BETWEEN 400-430 → scan 1 จาก 8 partitions

Partition pruning →

🧹

Maintenance

DETACH old partition แทน DELETE ล้านแถว

📦

Manageability

VACUUM / REINDEX ทีละ partition — ลด lock

เมื่อไหร่ควร partition? Fact > 10M แถว, filter ตามเวลา/ภูมิภาค บ่อย, ต้องลบ/archiving เป็นช่วง — ไม่ใช่ทุกตารางต้อง partition! Postgres Partitioning docs →

Types of Partitioning

Range

ตามช่วงต่อเนื่อง — เช่น date_key, order_date

PARTITION BY RANGE (date_key)
FOR VALUES FROM (1) TO (91) -- Q1

List

ตามค่าที่ระบุ — เช่น region

PARTITION BY LIST (region)
FOR VALUES IN ('North','South')

Hash

กระจายสม่ำเสมอ — เช่น customer_key

PARTITION BY HASH (customer_key)
PARTITIONS 4

Cloud: Snowflake clustering • BigQuery partitioned tables • ClickHouse partitioning

Demo: Declarative Partitioning

Copy-paste (PostgreSQL 15+):

CREATE TABLE fact_sales_part (
  sale_id INT NOT NULL,
  date_key INT NOT NULL,
  product_key INT NOT NULL,
  customer_key INT NOT NULL,
  quantity INT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (sale_id, date_key)
) PARTITION BY RANGE (date_key);

-- 8 quarterly partitions (731 days ~ 8 quarters)
CREATE TABLE fact_sales_part_q1 PARTITION OF fact_sales_part
  FOR VALUES FROM (1) TO (92);
CREATE TABLE fact_sales_part_q2 PARTITION OF fact_sales_part
  FOR VALUES FROM (92) TO (183);
-- ... q3 .. q8

INSERT INTO fact_sales_part SELECT * FROM fact_sales;

-- Verify pruning
EXPLAIN SELECT SUM(amount) FROM fact_sales_part
WHERE date_key BETWEEN 400 AND 430;
-- Append -> Seq Scan on fact_sales_part_q5 only

เต็มไฟล์: wk08_demo.sql • Docs →

Maintenance & Gotchas

Do ✅

• Partition key ควรรวมใน PK / unique constraint
• สร้าง index แยกต่อ partition (local index)
• Archiving: DETACH PARTITION แทน DELETE

ALTER TABLE fact_sales_part
  DETACH PARTITION fact_sales_part_q1;
-- เร็วกว่า DELETE WHERE date_key <92

Avoid ❌

• Partition เล็กเกิน (หลายพัน partitions → planner ช้า)
• เลือกคอลัมน์ที่ไม่ถูก filter บ่อย
• ลืม ANALYZE หลัง load

-- Check partition sizes
SELECT relname,
 pg_size_pretty(pg_relation_size(relid))
FROM pg_partition_tree('fact_sales_part');

ดูเพิ่ม: Partition maintenance docs • Snowflake micro-partitions

Part 4: Hands-On Lab

Index + Partition Together

รวมพลัง: Pruning ตัด partition ที่ไม่เกี่ยว • Index scan ใน partition ที่เหลือ

Lab ถัดไป: รันโค้ดจริงบน 50k แถว, วัด EXPLAIN, เปรียบเทียบเวลา

Lab: Step-by-Step (Copy-Paste)

Load — psql -f data/wk08/wk08_demo.sql (สร้างตาราง + โหลด CSV)

Baseline — รัน query แบบไม่มี index, ดู EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT d.year, p.category, SUM(f.amount)
FROM fact_sales f JOIN dim_date d ON f.date_key=d.date_key
JOIN dim_product p ON f.product_key=p.product_key
WHERE d.year=2024 AND p.category='Electronics'
GROUP BY 1,2;

Create Index — CREATE INDEX idx_fact_sales_date ON fact_sales(date_key); ANALYZE fact_sales;
Partition — สร้าง fact_sales_part ตามไฟล์ wk08_demo.sql
Compare — รัน EXPLAIN ANALYZE อีกครั้งบน partitioned + indexed table, เปรียบเทียบ Buffers/Time

⬇ wk08_demo.sql ⬇ fact_sales.csv

Week 8 Summary

1. Physical design = performance — Indexes ช่วยค้นหา, Partitioning ช่วยตัดข้อมูลที่ไม่เกี่ยว

2. Index เลือกให้ถูก: B-Tree สำหรับ FK, BRIN สำหรับ fact เรียงเวลา, Covering สำหรับ index-only scan

3. Partition แบบ Range ตามเวลาเป็นมาตรฐาน DW — ใช้ pruning ให้เป็นประโยชน์

4. วัดจริงด้วย EXPLAIN ANALYZE — อย่าเดา, ให้ข้อมูลบอก

⬇ Download Lab Files Next: ETL & ELT →

สัปดาห์ที่ 8: การออกแบบทางกายภาพ Indexing & Partitioning