PostgreSQL ขั้นสูง สอน Indexing, Query Optimization, Replication และ Partitioning 2026

PostgreSQL หรือที่เรียกสั้นๆ ว่า Postgres เป็นฐานข้อมูลเชิงสัมพันธ์แบบ Open Source ที่ทรงพลังที่สุดในโลก ด้วยความสามารถระดับ Enterprise ทั้ง ACID Compliance, Extensibility, Concurrency Control และ Advanced Query Optimization ทำให้องค์กรระดับโลกอย่าง Instagram, Spotify, Netflix และ Uber เลือกใช้ PostgreSQL เป็นฐานข้อมูลหลัก ในปี 2026 PostgreSQL ครองอันดับหนึ่งในฐานข้อมูล Open Source ด้วยฟีเจอร์ที่เหนือชั้นกว่าคู่แข่งทุกตัว

บทความนี้จะพาคุณดำดิ่งสู่ PostgreSQL ขั้นสูง ตั้งแต่ระบบ Indexing ที่หลากหลาย การใช้ EXPLAIN ANALYZE เพื่อวิเคราะห์ Query Performance ไปจนถึง Partitioning, Replication, VACUUM Tuning และ Security ที่จำเป็นสำหรับการจัดการฐานข้อมูลระดับ Production จริง

PostgreSQL vs MySQL — เปรียบเทียบเชิงลึก

คำถามที่พบบ่อยที่สุดคือ PostgreSQL ต่างจาก MySQL อย่างไร ทั้งสองเป็น Relational Database ที่นิยมใช้กันมาก แต่ปรัชญาการออกแบบและความสามารถต่างกันมาก PostgreSQL ถูกออกแบบมาเพื่อความถูกต้องของข้อมูล (Data Integrity) และการขยายขีดความสามารถ (Extensibility) เป็นหลัก ในขณะที่ MySQL ถูกออกแบบเพื่อความเร็วในการอ่านเป็นสำคัญ

ติดตั้ง PostgreSQL

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# CentOS/RHEL
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Docker (แนะนำสำหรับ Development)
docker run -d --name postgres16 \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

# ตรวจสอบเวอร์ชัน
psql --version
# เข้าใช้งาน
sudo -u postgres psql

Indexing Deep Dive — หัวใจของ Query Performance

Index คือโครงสร้างข้อมูลเสริมที่ PostgreSQL สร้างขึ้นเพื่อเร่งความเร็วในการค้นหาข้อมูล เปรียบเสมือนสารบัญของหนังสือ แทนที่จะอ่านทั้งเล่ม (Full Table Scan) เราสามารถเปิดไปหน้าที่ต้องการได้เลย PostgreSQL มี Index หลายประเภท แต่ละแบบเหมาะกับการใช้งานที่ต่างกัน การเลือก Index ที่ถูกต้องเป็นความแตกต่างระหว่าง Query ที่ทำงานเสร็จใน 1 มิลลิวินาที กับ Query ที่ใช้เวลา 10 วินาที

B-tree Index (Default)

B-tree เป็น Index ประเภทเริ่มต้นของ PostgreSQL เหมาะสำหรับการเปรียบเทียบค่าด้วย Operator เช่น =, <, >, <=, >=, BETWEEN และ IN ข้อมูลจะถูกจัดเรียงเป็นโครงสร้างต้นไม้แบบสมดุล (Balanced Tree) ทำให้การค้นหามีความซับซ้อนเป็น O(log n) เสมอ ไม่ว่าข้อมูลจะมีกี่ล้าน Row ก็ค้นหาได้เร็ว

-- สร้าง B-tree Index (Default)
CREATE INDEX idx_users_email ON users (email);

-- Composite Index (หลายคอลัมน์)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Unique Index (ไม่ซ้ำ)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Partial Index (เฉพาะ Row ที่ตรงเงื่อนไข)
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'active';

-- Index with INCLUDE (Covering Index)
CREATE INDEX idx_users_email_cover ON users (email)
INCLUDE (name, phone);
-- Query ที่ SELECT name, phone WHERE email = ... จะไม่ต้อง Heap Lookup

Hash Index

Hash Index ใช้ Hash Function ในการแปลงค่าเป็น Hash Value เหมาะสำหรับการเปรียบเทียบแบบ Equality (=) เท่านั้น ไม่สามารถใช้กับ Range Query (<, >, BETWEEN) ได้ ใน PostgreSQL 10+ Hash Index ถูกปรับปรุงให้ Write-Ahead Log (WAL) ได้แล้ว ทำให้ปลอดภัยในการใช้งาน Production มีขนาดเล็กกว่า B-tree สำหรับ Equality Lookup

-- Hash Index
CREATE INDEX idx_sessions_token ON sessions USING hash (session_token);

-- เหมาะกับ Query แบบนี้
SELECT * FROM sessions WHERE session_token = 'abc123xyz';
-- ไม่เหมาะกับ Range Query

GIN Index (Generalized Inverted Index)

GIN Index ถูกออกแบบมาสำหรับข้อมูลที่มีหลายค่าในหนึ่ง Row เช่น Array, JSONB, Full-text Search โครงสร้างภายในเป็น Inverted Index ที่ Map จากค่าแต่ละค่าไปยัง Row ที่มีค่านั้นอยู่ GIN Index มีประสิทธิภาพสูงในการค้นหา แต่การ INSERT/UPDATE จะช้ากว่า B-tree เพราะต้อง Update Index Entry หลายตัวพร้อมกัน

-- GIN สำหรับ JSONB
CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);

-- Query JSONB
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM products WHERE attributes ? 'warranty';

-- GIN สำหรับ Array
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['python', 'tutorial'];

-- GIN สำหรับ Full-text Search
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', title || ' ' || body));
SELECT * FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & indexing');

GiST Index (Generalized Search Tree)

GiST เป็น Index ที่ยืดหยุ่นมาก รองรับข้อมูลหลายมิติ เช่น Geometric Data, Range Types, PostGIS Geography Data และ Full-text Search GiST อาจไม่เร็วเท่า GIN สำหรับ Full-text Search แต่ใช้พื้นที่น้อยกว่าและ Update เร็วกว่า เหมาะกับข้อมูลที่เปลี่ยนแปลงบ่อย

-- GiST สำหรับ Range Types
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    period TSRANGE
);
CREATE INDEX idx_reservations_period ON reservations USING gist (period);

-- Query ที่ใช้ Range Overlap
SELECT * FROM reservations WHERE period && '[2026-04-01, 2026-04-07]'::tsrange;

-- GiST สำหรับ PostGIS (Geographic)
CREATE INDEX idx_locations_geom ON locations USING gist (geom);
SELECT * FROM locations WHERE ST_DWithin(geom, ST_MakePoint(100.5, 13.7)::geography, 5000);

-- Exclusion Constraint (ไม่ให้ Booking ซ้อนกัน)
ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, period WITH &&);

BRIN Index (Block Range Index)

BRIN เป็น Index ที่ใช้พื้นที่น้อยมาก โดยเก็บค่า Min/Max ของแต่ละ Block (กลุ่มของ Pages) เหมาะสำหรับตารางขนาดใหญ่ที่ข้อมูลเรียงลำดับตาม Physical Storage เช่น ตาราง Log ที่เพิ่มข้อมูลตามลำดับเวลา BRIN Index อาจมีขนาดเพียง 0.1% ของ B-tree Index สำหรับตารางเดียวกัน แต่แลกมาด้วย Precision ที่ต่ำกว่า

-- BRIN สำหรับ Time-series Data
CREATE INDEX idx_logs_created ON logs USING brin (created_at)
WITH (pages_per_range = 32);

-- เหมาะกับตาราง Log ขนาดใหญ่
-- ตาราง 100 ล้าน Row: B-tree ~2 GB vs BRIN ~2 MB
SELECT * FROM logs WHERE created_at BETWEEN '2026-04-01' AND '2026-04-07';

EXPLAIN ANALYZE — วิเคราะห์ Query Performance

EXPLAIN ANALYZE เป็นเครื่องมือที่สำคัญที่สุดในการวิเคราะห์ประสิทธิภาพ Query ของ PostgreSQL โดยจะแสดง Query Plan ที่ PostgreSQL เลือกใช้ พร้อมเวลาจริงที่ใช้ในแต่ละขั้นตอน การอ่าน EXPLAIN output เป็นทักษะที่ DBA ทุกคนต้องเชี่ยวชาญ

-- EXPLAIN แสดง Query Plan (ไม่รัน Query จริง)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE แสดง Plan + รัน Query จริง + แสดงเวลาจริง
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN พร้อม Options เพิ่มเติม
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

อ่าน EXPLAIN Output

-- ตัวอย่าง Output
Seq Scan on users  (cost=0.00..1520.00 rows=50000 width=120) (actual time=0.015..12.345 rows=50000 loops=1)
  Filter: (status = 'active')
  Rows Removed by Filter: 10000
Planning Time: 0.085 ms
Execution Time: 15.230 ms

-- ความหมาย:
-- cost=0.00..1520.00 = Startup cost .. Total cost (หน่วยสมมติ)
-- rows=50000 = จำนวน Row ที่ Planner คาดว่าจะได้
-- actual time = เวลาจริง (มิลลิวินาที)
-- rows=50000 = จำนวน Row ที่ได้จริง
-- loops=1 = จำนวนรอบที่รัน

Scan Types ที่ต้องรู้

Query Anti-Patterns ที่ต้องหลีกเลี่ยง

หลายครั้งที่ Query ช้าไม่ใช่เพราะขาด Index แต่เพราะเขียน Query ผิดวิธี ต่อไปนี้คือ Anti-patterns ที่พบบ่อยและวิธีแก้ไข การเข้าใจ Anti-patterns เหล่านี้จะช่วยให้คุณเขียน Query ที่มีประสิทธิภาพตั้งแต่แรก

-- Anti-pattern 1: Function ใน WHERE clause (Index ใช้ไม่ได้)
-- BAD
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- GOOD: สร้าง Expression Index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Anti-pattern 2: Leading Wildcard
-- BAD (ไม่ใช้ Index)
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD: ใช้ Full-text Search หรือ pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%phone%';

-- Anti-pattern 3: SELECT * (ดึงข้อมูลเกินจำเป็น)
-- BAD
SELECT * FROM orders WHERE user_id = 123;
-- GOOD
SELECT id, total, status FROM orders WHERE user_id = 123;

-- Anti-pattern 4: N+1 Query
-- BAD (Loop ใน Application)
-- for user in users: SELECT * FROM orders WHERE user_id = user.id
-- GOOD (JOIN หรือ Batch)
SELECT u.name, o.total FROM users u
JOIN orders o ON u.id = o.user_id WHERE u.id IN (1, 2, 3, 4, 5);

-- Anti-pattern 5: ไม่ใช้ LIMIT
-- BAD
SELECT * FROM logs ORDER BY created_at DESC;
-- GOOD
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- Anti-pattern 6: Implicit Type Casting
-- BAD (column เป็น INT แต่เปรียบเทียบกับ TEXT)
SELECT * FROM users WHERE id = '123';
-- GOOD
SELECT * FROM users WHERE id = 123;

CTEs และ Window Functions

Common Table Expressions (CTEs) และ Window Functions เป็นฟีเจอร์ขั้นสูงของ SQL ที่ PostgreSQL รองรับอย่างสมบูรณ์ ช่วยให้เขียน Query ซับซ้อนได้อย่างมีระเบียบ อ่านง่าย และมีประสิทธิภาพ CTEs ช่วยแบ่ง Query ออกเป็นส่วนย่อยที่อ่านง่าย ส่วน Window Functions ช่วยคำนวณค่าโดยอ้างอิง Row อื่นๆ ใน Partition เดียวกัน

-- CTE (Common Table Expression)
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY DATE_TRUNC('month', created_at)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
              LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
    FROM monthly_sales
)
SELECT * FROM growth ORDER BY month;

-- Recursive CTE (สำหรับข้อมูลแบบ Tree)
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 0 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

-- Window Functions
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Stored Procedures และ Functions (PL/pgSQL)

PL/pgSQL เป็นภาษา Procedural ของ PostgreSQL ที่ช่วยให้เขียน Logic ซับซ้อนฝั่ง Database ได้ ลด Round-trip ระหว่าง Application กับ Database และรับประกัน Consistency ของข้อมูล PL/pgSQL รองรับ Variables, Conditions, Loops, Exception Handling และ Transaction Control อย่างครบถ้วน

-- Function ที่คืนค่า
CREATE OR REPLACE FUNCTION calculate_tax(amount DECIMAL, tax_rate DECIMAL DEFAULT 0.07)
RETURNS DECIMAL AS $$
BEGIN
    RETURN ROUND(amount * tax_rate, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT calculate_tax(1000);  -- 70.00
SELECT calculate_tax(1000, 0.10);  -- 100.00

-- Function ที่คืนหลาย Row (RETURNS TABLE)
CREATE OR REPLACE FUNCTION get_top_customers(min_orders INT DEFAULT 5)
RETURNS TABLE(customer_name TEXT, total_orders BIGINT, total_spent DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT u.name, COUNT(o.id), SUM(o.total)
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
    HAVING COUNT(o.id) >= min_orders
    ORDER BY SUM(o.total) DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- Procedure (PostgreSQL 11+, ไม่คืนค่า, รองรับ Transaction Control)
CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT, receiver_id INT, amount DECIMAL
) AS $$
DECLARE
    sender_balance DECIMAL;
BEGIN
    SELECT balance INTO sender_balance FROM accounts WHERE id = sender_id FOR UPDATE;
    IF sender_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: balance=%, amount=%', sender_balance, amount;
    END IF;
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    INSERT INTO transactions (from_id, to_id, amount) VALUES (sender_id, receiver_id, amount);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL transfer_funds(1, 2, 500.00);

Triggers — การทำงานอัตโนมัติ

Trigger คือ Function ที่ถูกเรียกอัตโนมัติเมื่อมี Event (INSERT, UPDATE, DELETE) เกิดขึ้นกับตาราง ใช้สำหรับ Audit Log, Data Validation, Automatic Timestamps, Cascading Updates และอื่นๆ Trigger ช่วยให้ Business Logic อยู่ที่ชั้น Database ซึ่งรับประกันว่าจะถูกเรียกทุกครั้ง ไม่ว่าจะเข้าถึงจาก Application ใดก็ตาม

-- Function สำหรับ Trigger
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- สร้าง Trigger
CREATE TRIGGER trg_users_updated
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_at();

-- Audit Trigger
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by, changed_at)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,
        current_user,
        NOW()
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_changes();

Views และ Materialized Views

View คือ Virtual Table ที่สร้างจาก Query เมื่อ Query เข้า View จะรัน Query ใหม่ทุกครั้ง ส่วน Materialized View จะเก็บผลลัพธ์จริงเป็น Physical Table ทำให้อ่านเร็วเท่า Table ปกติ แต่ข้อมูลอาจไม่ Real-time ต้อง Refresh เป็นระยะ Materialized View เหมาะสำหรับ Report Dashboard หรือ Query ที่ซับซ้อนและไม่ต้องการข้อมูล Real-time

-- View (รัน Query ใหม่ทุกครั้ง)
CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    u.id AS user_id,
    u.name,
    COUNT(o.id) AS total_orders,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS avg_order,
    MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

SELECT * FROM v_order_summary WHERE total_spent > 10000;

-- Materialized View (เก็บผลลัพธ์จริง)
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS orders,
    SUM(total) AS revenue,
    AVG(total) AS avg_order
FROM orders
GROUP BY DATE(created_at)
WITH DATA;

CREATE UNIQUE INDEX idx_mv_daily_revenue ON mv_daily_revenue (date);

-- Refresh (ต้องทำเมื่อข้อมูลเปลี่ยน)
REFRESH MATERIALIZED VIEW mv_daily_revenue;
-- Refresh แบบไม่ Block Read
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

Partitioning — แบ่งตารางขนาดใหญ่

เมื่อตารางมีข้อมูลหลายร้อยล้าน Row การจัดการและค้นหาข้อมูลจะช้าลงอย่างมาก Partitioning แบ่งตารางใหญ่ออกเป็นหลาย Partition เล็กๆ ทำให้ Query ที่กรองด้วย Partition Key สามารถข้าม Partition ที่ไม่เกี่ยวข้องได้ (Partition Pruning) ลดจำนวนข้อมูลที่ต้อง Scan อย่างมาก และช่วยให้ Maintenance Operations เช่น VACUUM หรือ DROP Partition ทำได้เร็วขึ้น

Range Partitioning

-- สร้าง Partitioned Table (Range by Date)
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INT NOT NULL,
    total DECIMAL(12,2),
    status VARCHAR(20),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- สร้าง Partitions
CREATE TABLE orders_2026_q1 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE orders_2026_q3 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE orders_2026_q4 PARTITION OF orders
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

-- Default Partition (สำหรับข้อมูลที่ไม่ตรงกับ Partition ใดเลย)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Query จะทำ Partition Pruning อัตโนมัติ
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-04-01' AND created_at < '2026-07-01';
-- จะ Scan เฉพาะ orders_2026_q2 เท่านั้น

List Partitioning

-- List Partitioning (แบ่งตามค่าเฉพาะ)
CREATE TABLE sales (
    id BIGSERIAL,
    region VARCHAR(20) NOT NULL,
    amount DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE sales_asia PARTITION OF sales FOR VALUES IN ('TH', 'JP', 'KR', 'SG');
CREATE TABLE sales_europe PARTITION OF sales FOR VALUES IN ('UK', 'DE', 'FR', 'IT');
CREATE TABLE sales_america PARTITION OF sales FOR VALUES IN ('US', 'CA', 'MX', 'BR');

Hash Partitioning

-- Hash Partitioning (กระจายข้อมูลสม่ำเสมอ)
CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid(),
    user_id INT NOT NULL,
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Replication — สำเนาฐานข้อมูล

Replication ทำให้ PostgreSQL สามารถมีสำเนาหลายชุดที่ Sync กันอัตโนมัติ ใช้สำหรับ High Availability (HA), Read Scaling และ Disaster Recovery PostgreSQL รองรับ Replication สองแบบหลัก คือ Streaming Replication ที่สำเนาข้อมูลทั้ง Cluster และ Logical Replication ที่สำเนาเฉพาะ Table ที่ต้องการ

Streaming Replication

-- Primary Server: postgresql.conf
-- wal_level = replica
-- max_wal_senders = 5
-- wal_keep_size = 1024    -- MB

-- Primary: สร้าง Replication User
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'strongpassword';
-- pg_hba.conf: host replication replicator standby_ip/32 scram-sha-256

-- Standby Server: สร้างจาก Base Backup
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

-- -R จะสร้าง standby.signal และตั้งค่า primary_conninfo ให้อัตโนมัติ
-- เริ่ม Standby
sudo systemctl start postgresql

-- ตรวจสอบ Replication Status (บน Primary)
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Logical Replication

-- Publisher (Source): postgresql.conf
-- wal_level = logical

-- สร้าง Publication
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- หรือ FOR ALL TABLES

-- Subscriber (Target):
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=primary_host dbname=mydb user=replicator password=strongpassword'
    PUBLICATION my_pub;

-- ข้อดีของ Logical Replication:
-- 1. เลือก Replicate เฉพาะบาง Table
-- 2. Subscriber สามารถ Write ได้ (ไม่ใช่ Read-only)
-- 3. Replicate ข้าม PostgreSQL Version ได้
-- 4. ใช้สำหรับ Data Migration แบบ Zero-downtime

Connection Pooling

PostgreSQL สร้าง Process ใหม่สำหรับทุก Connection ซึ่งใช้ Memory ประมาณ 5-10 MB ต่อ Connection ถ้ามี 500 Connections พร้อมกันจะใช้ RAM 2.5-5 GB เฉพาะ Connection Overhead Connection Pooler ช่วยลดจำนวน Connection จริงไปยัง PostgreSQL โดย Reuse Connection ที่มีอยู่ ทำให้รองรับ Concurrent Users ได้มากขึ้นด้วยทรัพยากรเท่าเดิม

# PgBouncer (แนะนำ — เบา, เร็ว)
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction        # session | transaction | statement
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5

# Application เชื่อมต่อผ่าน PgBouncer (port 6432) แทน PostgreSQL (port 5432) โดยตรง

Backup Strategies

การ Backup เป็นสิ่งสำคัญที่สุดสำหรับ Database ใดๆ PostgreSQL มี Backup หลายวิธี แต่ละวิธีเหมาะกับสถานการณ์ที่ต่างกัน การมี Backup Strategy ที่ดีหมายถึงการทดสอบ Restore เป็นประจำด้วย ไม่ใช่แค่ Backup อย่างเดียว

# pg_dump — Logical Backup (เหมาะกับ DB ขนาดเล็ก-กลาง)
pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump
pg_dump -h localhost -U postgres -d mydb --schema-only -f schema.sql
pg_dump -h localhost -U postgres -d mydb -t users -t orders -f partial.sql

# pg_restore — Restore จาก Custom Format
pg_restore -h localhost -U postgres -d mydb_restored -C mydb_backup.dump

# pg_basebackup — Physical Backup (เหมาะกับ DB ขนาดใหญ่)
pg_basebackup -h localhost -U replicator -D /backups/base_backup -Ft -z -P
# -Ft = Tar format, -z = Compress, -P = Progress

# Continuous Archiving (Point-in-Time Recovery / PITR)
# postgresql.conf:
# archive_mode = on
# archive_command = 'cp %p /archive/%f'
# ช่วยให้ Restore ไปยังจุดเวลาใดก็ได้

# Barman (Backup and Recovery Manager) — Enterprise
barman backup main-server
barman list-backup main-server
barman recover main-server 20260408T120000 /var/lib/postgresql/16/restore --target-time "2026-04-08 11:59:00"

VACUUM และ Autovacuum Tuning

PostgreSQL ใช้ MVCC (Multi-Version Concurrency Control) ที่สร้างเวอร์ชันใหม่ของ Row ทุกครั้งที่ UPDATE หรือ DELETE เวอร์ชันเก่า (Dead Tuples) จะยังอยู่ในตารางจนกว่า VACUUM จะมาทำความสะอาด ถ้า VACUUM ทำงานไม่ทัน ตารางจะ Bloat ขึ้นเรื่อยๆ ทำให้ Seq Scan ช้าลงและใช้พื้นที่เกินจำเป็น การ Tune Autovacuum ให้เหมาะสมกับ Workload เป็นทักษะสำคัญของ DBA

-- ตรวจสอบ Dead Tuples
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- VACUUM แบบต่างๆ
VACUUM orders;                  -- ปกติ (ไม่ Block)
VACUUM FULL orders;             -- Rewrite ตารางใหม่ (Block, คืนพื้นที่)
VACUUM ANALYZE orders;          -- VACUUM + Update Statistics
VACUUM (VERBOSE) orders;        -- แสดง Detail

-- Autovacuum Tuning (postgresql.conf)
-- autovacuum = on
-- autovacuum_max_workers = 3
-- autovacuum_naptime = 60                    -- ตรวจทุก 60 วินาที
-- autovacuum_vacuum_threshold = 50           -- Dead tuples ขั้นต่ำ
-- autovacuum_vacuum_scale_factor = 0.1       -- % ของตาราง (10%)
-- autovacuum_analyze_threshold = 50
-- autovacuum_analyze_scale_factor = 0.05

-- Per-table Tuning (สำหรับตารางที่ UPDATE บ่อย)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- VACUUM เมื่อ Dead > 1%
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.005
);

-- ตรวจสอบ Table Bloat
SELECT
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Monitoring — ตรวจสอบประสิทธิภาพ

pg_stat_statements

pg_stat_statements เป็น Extension ที่สำคัญที่สุดสำหรับ Monitoring เก็บสถิติของทุก Query ที่รันผ่าน PostgreSQL ทำให้สามารถหา Slow Queries, Most Frequent Queries และ Resource-heavy Queries ได้อย่างง่ายดาย ทุก Production Server ควรเปิด Extension นี้เสมอ

-- เปิดใช้งาน
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

-- Top 10 Slowest Queries
SELECT
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Top 10 Most Frequent Queries
SELECT LEFT(query, 80), calls, rows,
       ROUND(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 10;

-- Reset Statistics
SELECT pg_stat_statements_reset();

pgBadger

# pgBadger — Log Analyzer สำหรับ PostgreSQL
# ติดตั้ง
sudo apt install pgbadger

# สร้าง Report จาก Log
pgbadger /var/log/postgresql/postgresql-16-main.log -o report.html

# postgresql.conf สำหรับ pgBadger
# log_min_duration_statement = 200   -- Log Query ที่นานกว่า 200ms
# log_checkpoints = on
# log_connections = on
# log_disconnections = on
# log_lock_waits = on
# log_temp_files = 0
# log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

PostgreSQL Extensions ที่น่าสนใจ

หนึ่งในจุดแข็งที่สุดของ PostgreSQL คือระบบ Extension ที่ทำให้สามารถเพิ่มความสามารถได้อย่างไม่จำกัด โดยไม่ต้อง Fork หรือแก้ไข Core PostgreSQL Extension ถูกพัฒนาโดยทั้ง Community และองค์กรต่างๆ ทั่วโลก

-- PostGIS — Geographic Information System
CREATE EXTENSION postgis;
SELECT ST_Distance(
    ST_MakePoint(100.523, 13.736)::geography,  -- Bangkok
    ST_MakePoint(100.493, 13.753)::geography    -- Another point
);

-- pg_trgm — Trigram Matching (Fuzzy Search)
CREATE EXTENSION pg_trgm;
SELECT similarity('postgresql', 'postgre');  -- 0.7
SELECT * FROM products WHERE name % 'iphon';  -- จะเจอ 'iPhone'

-- TimescaleDB — Time-series Database
CREATE EXTENSION timescaledb;
SELECT create_hypertable('sensor_data', 'time');
-- ทำ INSERT/Query เหมือน Table ปกติ แต่ได้ Performance ของ Time-series DB

-- pgvector — Vector Similarity Search (AI/ML)
CREATE EXTENSION vector;
CREATE TABLE items (id SERIAL, embedding vector(384));
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops);
SELECT * FROM items ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector LIMIT 5;

-- uuid-ossp — UUID Generation
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

-- pg_cron — Job Scheduling
CREATE EXTENSION pg_cron;
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE orders');

Security — การรักษาความปลอดภัย

Roles และ Privileges

-- สร้าง Role
CREATE ROLE app_readonly LOGIN PASSWORD 'strongpass';
CREATE ROLE app_readwrite LOGIN PASSWORD 'strongpass';
CREATE ROLE app_admin LOGIN PASSWORD 'strongpass' CREATEDB;

-- Grant Privileges
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

GRANT CONNECT ON DATABASE mydb TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

Row-Level Security (RLS)

-- เปิด RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: User เห็นเฉพาะข้อมูลของตัวเอง
CREATE POLICY user_orders ON orders
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::INT);

-- Policy: Admin เห็นทุกอย่าง
CREATE POLICY admin_all ON orders
    FOR ALL
    TO app_admin
    USING (true);

-- ใช้งาน: ตั้ง Session Variable
SET app.current_user_id = '42';
SELECT * FROM orders;  -- เห็นเฉพาะ orders ของ user_id = 42

Performance Tuning — ปรับแต่ง PostgreSQL

การปรับแต่ง Configuration ของ PostgreSQL ให้เหมาะกับ Hardware และ Workload เป็นสิ่งจำเป็นสำหรับ Production Server ค่า Default ของ PostgreSQL ถูกตั้งให้ทำงานได้บนทุก Hardware แม้แต่เครื่องที่มี RAM เพียง 512 MB จึงไม่ได้ใช้ทรัพยากรอย่างเต็มที่ การปรับค่าตามทรัพยากรจริงจะเพิ่มประสิทธิภาพได้หลายเท่า

# postgresql.conf — Performance Parameters
# สมมติ Server: 32 GB RAM, 8 Cores, SSD

# Memory
shared_buffers = 8GB               # 25% ของ RAM
effective_cache_size = 24GB        # 75% ของ RAM
work_mem = 64MB                    # ต่อ Sort/Hash Operation
maintenance_work_mem = 2GB         # สำหรับ VACUUM, CREATE INDEX
huge_pages = try                   # ใช้ Huge Pages ถ้ามี

# Write Ahead Log (WAL)
wal_buffers = 64MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

# Query Planner
random_page_cost = 1.1             # SSD (default 4.0 สำหรับ HDD)
effective_io_concurrency = 200     # SSD (default 1 สำหรับ HDD)
seq_page_cost = 1.0

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 100

# Connection
max_connections = 200              # ใช้ร่วมกับ PgBouncer
# คำนวณ: max_connections * work_mem ต้องไม่เกิน RAM ที่เหลือ

Query Optimization Checklist

สรุปขั้นตอนการ Optimize Query สำหรับใช้งานจริง เป็น Checklist ที่ DBA และ Developer ควรทำเมื่อพบ Slow Query สิ่งสำคัญคือต้องมี Metrics ก่อน Optimize เสมอ ไม่ควร Optimize โดยไม่มีข้อมูลสนับสนุน

1. วัดก่อน: ใช้ EXPLAIN ANALYZE ดู Query Plan และเวลาจริง
2. ตรวจ Statistics: รัน ANALYZE ให้ข้อมูลสถิติเป็นปัจจุบัน
3. ดู Scan Type: Seq Scan บนตารางใหญ่ = สัญญาณว่าต้องสร้าง Index
4. ตรวจ Index Usage: ดูว่า Index ที่มีถูกใช้หรือไม่ ลบ Index ที่ไม่ใช้ (เปลืองพื้นที่และช้าตอน Write)
5. ลด Row Count: เพิ่ม WHERE ที่เข้มงวดขึ้น ใช้ LIMIT
6. ลด Column Count: เลิกใช้ SELECT * ระบุ Column ที่ต้องการ
7. ตรวจ Join Order: PostgreSQL เลือก Join Order ให้ แต่บางครั้งอาจไม่ Optimal
8. ใช้ Covering Index: เพิ่ม INCLUDE เพื่อหลีกเลี่ยง Heap Lookup
9. Partition: ถ้าตารางใหญ่เกินไป แบ่ง Partition
10. Cache: ใช้ Materialized View หรือ Application-level Cache สำหรับ Query ที่ซ้ำบ่อย

คำสั่ง PostgreSQL ที่ใช้บ่อย (Cheatsheet)

สรุป

PostgreSQL เป็นฐานข้อมูลที่ทรงพลังที่สุดในโลก Open Source การเข้าใจ Indexing ที่หลากหลาย ตั้งแต่ B-tree, Hash, GIN, GiST ไปจนถึง BRIN ช่วยให้คุณเลือก Index ที่เหมาะสมกับ Query Pattern ของคุณ การใช้ EXPLAIN ANALYZE เป็นประจำช่วยให้เข้าใจว่า PostgreSQL ตัดสินใจรัน Query อย่างไร ส่วน Partitioning และ Replication ช่วยให้รองรับข้อมูลจำนวนมหาศาลและมี High Availability

ไม่ว่าคุณจะเป็น Developer ที่ต้องเขียน Query ให้เร็ว หรือ DBA ที่ต้องดูแลฐานข้อมูลระดับ Production การเรียนรู้ PostgreSQL ขั้นสูงจะเป็นการลงทุนที่คุ้มค่าที่สุดในอาชีพของคุณ เริ่มต้นด้วยการติดตั้ง PostgreSQL บนเครื่องพัฒนา ทดลองสร้าง Index แต่ละแบบ ใช้ EXPLAIN ANALYZE วิเคราะห์ Query และค่อยๆ เรียนรู้ฟีเจอร์ขั้นสูงทีละอย่าง