Đặc thù PostgreSQL và Performance

-- B-tree (default)
CREATE INDEX idx_orders_user ON orders (user_id);

-- GIN cho JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- Hoặc compact hơn nếu chỉ dùng @>
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- GIN cho array
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- GIST cho geometry / range
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- BRIN cho time-series (cực gọn)
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- Chỉ index khách hàng active — index nhỏ + nhanh hơn
CREATE INDEX idx_users_active_email ON users (email) WHERE status = 'active';

-- Index trên expression — giải bài "non-sargable" hay gặp
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Query có thể dùng index này:
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

CREATE INDEX idx_a_b ON t (a, b);

-- Pre-18: không dùng được idx_a_b vì thiếu điều kiện trên 'a'
-- PG 18+: planner có thể Skip Scan — duyệt distinct 'a', tìm 'b' trong từng group
SELECT * FROM t WHERE b = 10;

-- Production: dùng CONCURRENTLY để không block write
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- Toàn bộ JSONB
CREATE INDEX idx_payload ON events USING GIN (payload);

-- Chỉ key cụ thể (nhỏ hơn)
CREATE INDEX idx_payload_type ON events USING BTREE ((payload->>'type'));

-- Query path
CREATE INDEX idx_payload_user_id
    ON events USING BTREE ((payload->'user'->>'id'));

-- jsonb_set
UPDATE events
SET payload = jsonb_set(payload, '{processed}', 'true', true)
WHERE id = 1;

-- Merge (||)
UPDATE events
SET payload = payload || '{"reviewed": true}'::jsonb;

-- Remove key (-)
UPDATE events SET payload = payload - 'temp_field';

-- NDJSON output (default) — streaming từng row, memory-efficient
COPY users TO STDOUT WITH (FORMAT JSON);

-- JSON array output — [] bao toàn bộ
COPY users TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);

-- Có thể pipe thẳng vào file
COPY users TO '/tmp/users.json' WITH (FORMAT JSON);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    tags TEXT[] DEFAULT '{}'
);

-- Operator
SELECT * FROM posts WHERE tags @> ARRAY['vue'];        -- contains
SELECT * FROM posts WHERE tags && ARRAY['vue','react']; -- overlap
SELECT * FROM posts WHERE 'vue' = ANY(tags);
SELECT * FROM posts WHERE NOT ('react' = ALL(tags));

-- unnest — biến array thành rows
SELECT unnest(tags) AS tag FROM posts WHERE id = 1;

-- Aggregate ngược lại
SELECT array_agg(name ORDER BY name) FROM users;

CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT uuidv7(),     -- 18+
    -- DEFAULT gen_random_uuid() cho pre-18 (v4 random)
    email TEXT
);

-- Top extension dev thường dùng
CREATE EXTENSION IF NOT EXISTS pg_trgm;            -- fuzzy search, similarity
CREATE EXTENSION IF NOT EXISTS pgcrypto;           -- crypto, hash
CREATE EXTENSION IF NOT EXISTS postgis;            -- GIS, geometry
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- query monitoring
CREATE EXTENSION IF NOT EXISTS vector;             -- AI embedding (pgvector)
CREATE EXTENSION IF NOT EXISTS timescaledb;        -- time-series
CREATE EXTENSION IF NOT EXISTS unaccent;           -- bỏ dấu tiếng Việt

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Tìm gần đúng
SELECT name, similarity(name, 'iPone') AS sim
FROM products
WHERE name % 'iPone'                  -- threshold default 0.3
ORDER BY sim DESC;

CREATE EXTENSION vector;

CREATE TABLE docs (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)            -- OpenAI text-embedding-3-small
);

-- IVFFlat hoặc HNSW index
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- Semantic search
SELECT id, content, embedding <=> '[...]'::vector AS distance
FROM docs
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;

-- 18+: PK temporal — không trùng theo time range
CREATE TABLE room_reservations (
    room_id INT,
    valid_during TSRANGE,
    PRIMARY KEY (room_id, valid_during WITHOUT OVERLAPS)
);

-- 18+: FK temporal
CREATE TABLE bookings (
    ... ,
    FOREIGN KEY (room_id, period) REFERENCES room_reservations
        (room_id, valid_during) PERIOD (period)
);

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC LIMIT 10;

Limit  (cost=0.42..8.45 rows=10 width=120) (actual time=0.05..0.10 rows=10 loops=1)
  ->  Index Scan using idx_orders_user_created on orders
       (cost=0.42..234.50 rows=300 width=120)
       Index Cond: (user_id = 1)
       Filter: (status = 'paid')
       Rows Removed by Filter: 50
       Buffers: shared hit=15
Planning Time: 0.15 ms
Execution Time: 0.13 ms

-- Bước 1: Generate advice từ plan hiện tại
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM orders o JOIN customers c ON o.cust_id = c.id;
-- Output example: JOIN_ORDER(o c)  HASH_JOIN(c)  SEQ_SCAN(o c)

-- Bước 2: Lock plan theo advice
SET pg_plan_advice.advice = 'JOIN_ORDER(o c) HASH_JOIN(c) SEQ_SCAN(o c)';

-- Pin join order khi planner chọn sai trên dữ liệu skewed
SET pg_plan_advice.advice = 'JOIN_ORDER(orders customers) NESTED_LOOP(customers)';

-- Force hash join cho batch query nightly
SET pg_plan_advice.advice = 'HASH_JOIN(events)';

-- Theo dõi hint có được honor không
-- Log: "pg_plan_advice: hint 'SEQ_SCAN(o c)' not honored because index scan is cheaper"

-- Reclaim space (thay VACUUM FULL)
REPACK orders;

-- Reorder theo index (thay CLUSTER)
REPACK orders USING INDEX orders_created_at_idx;

-- Online mode — chỉ lock ACCESS EXCLUSIVE vài ms lúc swap file cuối
REPACK (CONCURRENTLY) orders USING INDEX orders_created_at_idx;

# Memory
shared_buffers = 25% RAM            # cache buffer
effective_cache_size = 75% RAM      # hint planner
work_mem = 32MB                     # mỗi sort/hash op
maintenance_work_mem = 512MB        # VACUUM, CREATE INDEX

# Checkpoint
checkpoint_timeout = 10min
max_wal_size = 4GB

# Postgres 18/19 — AIO
io_method = io_uring                # Linux 5.10+ only
io_max_concurrency = 256

# Logging
log_min_duration_statement = 1000ms  # log query > 1s

# Autovacuum — PG 19 parallel
autovacuum_max_parallel_workers = 4

-- Cluster-wide default
ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;

-- Per-table override (ưu tiên hơn)
ALTER TABLE events SET (autovacuum_parallel_workers = 6);

-- Enable checksum với rate limit (không ảnh hưởng production)
SELECT pg_enable_data_checksums(cost_delay := 10, cost_limit := 1000);

-- Theo dõi tiến trình
SHOW data_checksums;
-- Giá trị: 'off' → 'inprogress-on' → 'on'

-- Disable nếu cần (cẩn thận!)
-- SELECT pg_disable_data_checksums();

-- Sequence synchronization: replicate sequence values cùng table
CREATE PUBLICATION my_pub FOR ALL TABLES, ALL SEQUENCES;

-- EXCEPT TABLE: loại trừ bảng không muốn replicate
CREATE PUBLICATION prod_pub FOR ALL TABLES
    EXCEPT (TABLE audit_log, temp_imports);

-- Filter row trong publication (PG 15+ có WHERE clause)
CREATE PUBLICATION us_customers FOR TABLE customers
    WHERE (country = 'US');