Skip to content
← All Skills
🐘

PostgreSQL

Data & MessagingOfficial Site ›

Primary relational database สำหรับ trading data, user management, และ transaction records

What I Can Do

  • ออกแบบ schema สำหรับ financial/trading data
  • เขียน complex queries รวมถึง window functions, CTEs
  • จัดการ migration ด้วย golang-migrate
  • Optimize query performance ด้วย EXPLAIN ANALYZE
  • Setup replication และ backup strategy

Commands I Use Daily

bash
# เชื่อมต่อ database
psql -h localhost -U postgres -d trading

# psql meta-commands ที่ใช้บ่อย
\dt                    # list ทุก tables
\d+ orders             # ดู schema detail + size ของ table
\di                    # list ทุก indexes
\x                     # toggle expanded display (อ่านง่ายขึ้น)

# ดู query plan — ต้องใช้ทุกครั้งก่อน optimize
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

# ดู active queries — ใช้ตอน debug ว่าอะไรช้า
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat%';

# ดู dead tuples — ถ้าเยอะแปลว่าต้อง vacuum
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

# kill query ที่ค้าง
SELECT pg_terminate_backend(<pid>);

Relational Database Concepts

Relational database เก็บข้อมูลในรูปแบบ tables ที่มีความสัมพันธ์กัน — ใช้ SQL เป็นภาษาสำหรับ query และ manipulate data ข้อมูลถูก organize ตาม schema ที่กำหนดไว้ล่วงหน้า

Tables / Rows / Columns

  • Table = collection ของข้อมูลประเภทเดียวกัน (เช่น users, orders)
  • Row = 1 record ของข้อมูล
  • Column = attribute ของข้อมูล มี data type กำกับ

Primary Key & Foreign Key

  • Primary Key — unique identifier ของแต่ละ row เช่น id UUID PRIMARY KEY
  • Foreign Key — reference ไปยัง primary key ของ table อื่น เพื่อสร้าง relationship เช่น user_id UUID REFERENCES users(id)

Basic CRUD

  • INSERT INTO users (email) VALUES ('a@b.com');
  • SELECT * FROM users WHERE id = $1;
  • UPDATE users SET status = 'active' WHERE id = $1;
  • DELETE FROM users WHERE id = $1;

Data Types

PostgreSQL มี data types ที่หลากหลาย — VARCHAR, TEXT, INTEGER, BIGINT, DECIMAL, BOOLEAN, TIMESTAMPTZ, UUID, JSONB, ARRAY

WHERE / ORDER BY / LIMIT

  • WHERE — filter rows ตามเงื่อนไข
  • ORDER BY column DESC — เรียงลำดับ
  • LIMIT 20 OFFSET 40 — pagination

Constraints (NOT NULL, UNIQUE, CHECK)

  • NOT NULL — ห้าม NULL
  • UNIQUE — ห้ามค่าซ้ำ
  • CHECK (price > 0) — enforce business rules ที่ database level

JOINs

  • INNER JOIN — เฉพาะ rows ที่ match ทั้ง 2 tables
  • LEFT JOIN — ทุก rows จาก left table + matched rows จาก right
  • RIGHT JOIN — ทุก rows จาก right table
  • FULL OUTER JOIN — ทุก rows จากทั้ง 2 tables

Indexes (B-tree, GIN)

  • B-tree (default) — ใช้สำหรับ equality (=) และ range (<, >, BETWEEN) queries
  • GIN — ใช้สำหรับ JSONB, full-text search, array contains
  • สร้าง index บน columns ที่ใช้ใน WHERE, JOIN, ORDER BY บ่อย

Transactions (ACID)

  • Atomicity — ทุก operation ใน transaction สำเร็จหมดหรือ rollback หมด
  • Consistency — data อยู่ใน valid state เสมอ
  • Isolation — transactions ไม่เห็น uncommitted changes ของกัน
  • Durability — committed data ไม่หายแม้ system crash

ENUM Types

CREATE TYPE order_status AS ENUM ('pending', 'filled', 'cancelled'); — จำกัด values ที่เป็นไปได้ type-safe กว่า VARCHAR + CHECK

Migrations

ใช้ migration tool (golang-migrate, Flyway) เพื่อ version control database schema — แต่ละ migration มี up (apply) และ down (rollback)

EXPLAIN Basics

EXPLAIN SELECT ... แสดง query plan ที่ PostgreSQL จะใช้ — ดูว่าเป็น Seq Scan หรือ Index Scan, ประเมิน cost และจำนวน rows

Connection Management

PostgreSQL มี connection limit — แต่ละ connection ใช้ ~10MB memory ต้อง manage connection pool ใน application layer (เช่น pgxpool ใน Go)

Sequences

SERIAL / BIGSERIAL ใช้ sequence ในการ auto-generate incrementing IDs — sequence เป็น separate object ที่ guarantee uniqueness แม้ transaction rollback

Partial & Covering Indexes

  • Partial index — index เฉพาะ rows ที่ match condition เช่น WHERE status = 'pending' ลดขนาด index
  • Covering index — INCLUDE (col) เพิ่ม columns ใน index เพื่อ avoid table lookup (index-only scan)

EXPLAIN ANALYZE Deep Dive

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) — ดู actual execution time, buffer hits/reads, row estimates vs actuals ใช้ identify Seq Scan บน large tables, bad row estimates, memory spills

Table Partitioning

แบ่ง table ใหญ่เป็น partitions ตาม range (date), list (status), หรือ hash — query scan เฉพาะ partitions ที่เกี่ยวข้อง, maintenance (VACUUM, reindex) ทำแยกแต่ละ partition ได้

VACUUM & Autovacuum

PostgreSQL ใช้ MVCC — dead tuples จาก UPDATE/DELETE ต้องถูก VACUUM เพื่อ reclaim space autovacuum ทำงานอัตโนมัติ แต่ต้อง tune parameters สำหรับ high-write tables

pg_stat Views

  • pg_stat_user_tables — row counts, dead tuples, sequential vs index scans
  • pg_stat_user_indexes — index usage stats, หา unused indexes
  • pg_stat_activity — active connections และ queries

Replication (Streaming, Logical)

  • Streaming replication — binary-level replication สำหรับ read replicas และ HA failover
  • Logical replication — row-level changes, selective tables, cross-version ใช้สำหรับ zero-downtime migration

Connection Pooling (PgBouncer)

PgBouncer นั่งหน้า PostgreSQL เพื่อ pool connections — application เปิด connection กับ PgBouncer แทน, PgBouncer reuse actual database connections ลด overhead

Lock Management

  • ROW SHARE, ROW EXCLUSIVE, SHARE, EXCLUSIVE — lock levels ต่างๆ
  • SELECT ... FOR UPDATE — row-level lock สำหรับ read-then-update pattern
  • pg_locks view ใช้ debug lock contention และ deadlocks

Related Skills

  • Go — backend ที่ interact กับ PostgreSQL
  • Kafka — consume events แล้วเขียนลง PostgreSQL
  • Docker — run PostgreSQL ใน development environment