PostgreSQL Fundamentals¶

Tại sao PostgreSQL?¶

Supabase chọn PostgreSQL vì:

┌─────────────────────────────────────────────────────────────┐
│                    POSTGRESQL STRENGTHS                      │
├─────────────────────────────────────────────────────────────┤
│  ✅ ACID Compliant      - Transactions, data integrity     │
│  ✅ SQL Standard        - Familiar syntax                   │
│  ✅ Extensions          - pg_cron, pgmq, PostGIS, pgvector │
│  ✅ JSON Support        - JSONB for flexible schema        │
│  ✅ Performance         - Advanced indexing, query planner │
│  ✅ Mature              - 35+ years, battle-tested         │
└─────────────────────────────────────────────────────────────┘

Data Types¶

Common Types¶

Type	Description	Example
`UUID`	Unique identifier	`gen_random_uuid()`
`TEXT`	Variable-length string	`'Hello World'`
`INTEGER`	Whole number	`42`
`BIGINT`	Large whole number	`9223372036854775807`
`BOOLEAN`	True/False	`TRUE`, `FALSE`
`TIMESTAMPTZ`	Timestamp with timezone	`NOW()`
`DATE`	Date only	`'2024-01-15'`
`JSONB`	Binary JSON	`'{"key": "value"}'`
`ARRAY`	Array of values	`'{1,2,3}'`

UUID vs Serial¶

-- ✅ Recommended: UUID (distributed-friendly)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

-- ❌ Not recommended: Serial (sequential, predictable)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Table Design Best Practices¶

Standard Table Template¶

CREATE TABLE public.items (
  -- Primary key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive')),

  -- Foreign keys
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  category_id UUID REFERENCES categories(id) ON DELETE SET NULL,

  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  -- Constraints
  CONSTRAINT unique_name_per_user UNIQUE (user_id, name)
);

-- Enable RLS
ALTER TABLE public.items ENABLE ROW LEVEL SECURITY;

-- Index for common queries
CREATE INDEX idx_items_user_id ON public.items(user_id);
CREATE INDEX idx_items_status ON public.items(status);

Constraints¶

NOT NULL¶

-- Column must have a value
name TEXT NOT NULL

UNIQUE¶

-- Single column unique
email TEXT UNIQUE

-- Multi-column unique
UNIQUE (organization_id, email)

CHECK¶

-- Validate column values
status TEXT CHECK (status IN ('draft', 'published', 'archived'))
priority INTEGER CHECK (priority BETWEEN 1 AND 5)

FOREIGN KEY¶

-- Reference another table
user_id UUID REFERENCES auth.users(id)

-- With cascade options
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE
category_id UUID REFERENCES categories(id) ON DELETE SET NULL

Indexes¶

When to Create Index¶

-- Index columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);

-- Composite index for multi-column queries
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);

-- Partial index for filtered queries
CREATE INDEX idx_tasks_active ON tasks(status)
WHERE status != 'archived';

Index Types¶

Type	Use Case
B-tree (default)	Equality, range queries
GIN	JSONB, arrays, full-text search
GiST	Geometric, full-text search
BRIN	Large tables with natural ordering

-- GIN index for JSONB
CREATE INDEX idx_metadata ON items USING GIN (metadata);

-- Full-text search
CREATE INDEX idx_search ON posts USING GIN (to_tsvector('english', title || ' ' || content));

JSONB for Flexible Data¶

Store JSON Data¶

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert with JSONB
INSERT INTO events (type, metadata)
VALUES ('user.signup', '{"source": "google", "campaign": "summer2024"}');

Query JSONB¶

-- Access nested value
SELECT metadata->>'source' FROM events;

-- Filter by JSONB value
SELECT * FROM events
WHERE metadata->>'source' = 'google';

-- Check if key exists
SELECT * FROM events
WHERE metadata ? 'campaign';

-- Contains operator
SELECT * FROM events
WHERE metadata @> '{"source": "google"}';

Common SQL Patterns¶

Soft Delete¶

CREATE TABLE items (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  deleted_at TIMESTAMPTZ  -- NULL = not deleted
);

-- Soft delete
UPDATE items SET deleted_at = NOW() WHERE id = '...';

-- Query non-deleted
SELECT * FROM items WHERE deleted_at IS NULL;

Audit Columns¶

CREATE TABLE items (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by UUID REFERENCES auth.users(id)
);

Updated_at Trigger¶

-- Create trigger function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to table
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON items
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Supabase Extensions¶

Enable Extensions¶

-- Enable commonly used extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      -- UUID functions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";       -- Crypto functions
CREATE EXTENSION IF NOT EXISTS "pg_cron";        -- Cron jobs
CREATE EXTENSION IF NOT EXISTS "pgmq";           -- Message queue
CREATE EXTENSION IF NOT EXISTS "http";           -- HTTP requests

Useful Functions¶

-- Generate UUID
SELECT gen_random_uuid();

-- Current timestamp
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- Date arithmetic
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 hour';

Hands-on Exercise¶

Create a Blog Schema¶

-- Categories
CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE
);

-- Posts
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  content TEXT,
  excerpt TEXT,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
  category_id UUID REFERENCES categories(id),
  author_id UUID REFERENCES auth.users(id),
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at DESC)
  WHERE status = 'published';

-- Enable RLS
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Tổng kết¶

Key Takeaways¶

Use UUID for primary keys
Add constraints for data integrity
Create indexes for query performance
Use JSONB for flexible schema
Enable RLS on all tables

Best Practices¶

Always define NOT NULL where appropriate
Use CHECK constraints for enums
Add foreign keys with proper ON DELETE
Create indexes for WHERE/JOIN columns
Use updated_at trigger for audit

Q&A¶

Đã quen với PostgreSQL chưa?
Có use case nào cần JSONB?
Questions về indexing?