postgresql-optimization

PostgreSQL 開發助手

針對 ${selection}（若無選取則為整個專案）提供專家級 PostgreSQL 指導。專注於 PostgreSQL 特有功能、最佳化模式與進階能力。

PostgreSQL 特有功能

JSONB 操作

-- 進階 JSONB 查詢
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 為 JSONB 效能建立 GIN 索引
CREATE INDEX idx_events_data_gin ON events USING gin(data);

-- JSONB 包含性與路徑查詢
SELECT * FROM events 
WHERE data @> '{"type": "login"}'
  AND data #>> '{user,role}' = 'admin';

-- JSONB 聚合
SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';

陣列操作

-- PostgreSQL 陣列
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    categories INTEGER[]
);

-- 陣列查詢與操作
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
SELECT * FROM posts WHERE array_length(tags, 1) > 3;

-- 陣列聚合
SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;

視窗函數與分析

-- 進階視窗函數
SELECT 
    product_id,
    sale_date,
    amount,
    -- 累計總和
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
    -- 移動平均
    AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
    -- 排名
    DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
    -- 滯後/領先比較
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
FROM sales;

全文檢索

-- PostgreSQL 全文檢索
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- 更新搜尋向量
UPDATE documents 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- 為搜尋效能建立 GIN 索引
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);

-- 搜尋查詢
SELECT * FROM documents 
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');

-- 排序結果
SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
FROM documents 
WHERE search_vector @@ plainto_tsquery('postgresql')
ORDER BY rank DESC;

PostgreSQL 效能調校

查詢最佳化

-- 使用 EXPLAIN ANALYZE 進行效能分析
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'::date
GROUP BY u.id, u.name;

-- 從 pg_stat_statements 找出慢查詢
SELECT query, calls, total_time, mean_time, rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

索引策略

-- 複合索引用於多欄位查詢
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 部分索引用於過濾查詢
CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';

-- 表達式索引用於計算值
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 涵蓋索引避免查詢資料表
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

連線與記憶體管理

-- 檢查連線使用情況
SELECT count(*) as connections, state 
FROM pg_stat_activity 
GROUP BY state;

-- 監控記憶體使用
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

PostgreSQL 進階資料型別

自訂型別與網域

-- 建立自訂型別
CREATE TYPE address_type AS (
    street TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT
);

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- 使用網域進行資料驗證
CREATE DOMAIN email_address AS TEXT 
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- 使用自訂型別的資料表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    address address_type,
    status order_status DEFAULT 'pending'
);

範圍型別

-- PostgreSQL 範圍型別
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    reservation_period tstzrange,
    price_range numrange
);

-- 範圍查詢
SELECT * FROM reservations 
WHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');

-- 排除重疊範圍
ALTER TABLE reservations 
ADD CONSTRAINT no_overlap 
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);

幾何型別

-- PostgreSQL 幾何型別
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    coordinates POINT,
    coverage CIRCLE,
    service_area POLYGON
);

-- 幾何查詢
SELECT name FROM locations 
WHERE coordinates <-> point(40.7128, -74.0060) < 10; -- 10 單位內

-- 為幾何資料建立 GiST 索引
CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

PostgreSQL 擴充套件與工具

常用擴充套件

-- 啟用常用擴充套件
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- UUID 產生
CREATE EXTENSION IF NOT EXISTS "pgcrypto";     -- 加密函數
CREATE EXTENSION IF NOT EXISTS "unaccent";     -- 移除文字重音符號
CREATE EXTENSION IF NOT EXISTS "pg_trgm";      -- 三元組比對
CREATE EXTENSION IF NOT EXISTS "btree_gin";    -- 為 btree 型別提供 GIN 索引

-- 使用擴充套件
SELECT uuid_generate_v4();                     -- 產生 UUID
SELECT crypt('password', gen_salt('bf'));      -- 密碼雜湊
SELECT similarity('postgresql', 'postgersql'); -- 模糊比對

監控與維護

-- 資料庫大小與成長
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size;

-- 資料表與索引大小
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 索引使用統計
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;  -- 未使用的索引

PostgreSQL 專屬最佳化技巧

• 使用 EXPLAIN (ANALYZE, BUFFERS) 進行詳細查詢分析
• 根據工作負載設定 postgresql.conf（OLTP 或 OLAP）
• 使用連線池（pgbouncer） 處理高併發應用
• 定期執行 VACUUM 和 ANALYZE 以維持最佳效能
• 分割大型資料表，使用 PostgreSQL 10+ 宣告式分割
• 使用 pg_stat_statements 監控查詢效能

監控與維護

查詢效能監控

-- 找出慢查詢
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- 檢查索引使用情況
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

資料庫維護

• VACUUM 和 ANALYZE：定期維護以維持效能
• 索引維護：監控並重建碎片化的索引
• 統計資料更新：保持查詢規劃器統計資料為最新
• 日誌分析：定期檢視 PostgreSQL 日誌

常見查詢模式

分頁

-- ❌ 不好：對大型資料集使用 OFFSET
SELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20;

-- ✅ 好：基於游標的分頁
SELECT * FROM products 
WHERE id > $last_id 
ORDER BY id 
LIMIT 20;

聚合

-- ❌ 不好：效率低的分組
SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

-- ✅ 好：使用部分索引最佳化
CREATE INDEX idx_orders_recent ON orders(user_id) 
WHERE order_date >= '2024-01-01';

SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

JSON 查詢

-- ❌ 不好：效率低的 JSON 查詢
SELECT * FROM users WHERE data::text LIKE '%admin%';

-- ✅ 好：使用 JSONB 運算子與 GIN 索引
CREATE INDEX idx_users_data_gin ON users USING gin(data);

SELECT * FROM users WHERE data @> '{"role": "admin"}';

最佳化檢查清單

查詢分析

• [ ] 對昂貴查詢執行 EXPLAIN ANALYZE
• [ ] 檢查大型資料表上的循序掃描
• [ ] 驗證適當的連線演算法
• [ ] 檢視 WHERE 子句的選擇性
• [ ] 分析排序與聚合操作

索引策略

• [ ] 為頻繁查詢的欄位建立索引
• [ ] 對多欄位搜尋使用複合索引
• [ ] 考慮對過濾查詢使用部分索引
• [ ] 移除未使用或重複的索引
• [ ] 監控索引膨脹與碎片化

安全審查

• [ ] 僅使用參數化查詢
• [ ] 實作適當的存取控制
• [ ] 在需要時啟用列級安全性
• [ ] 稽核敏感資料存取
• [ ] 使用安全的連線方式

效能監控

• [ ] 設定查詢效能監控
• [ ] 設定適當的日誌設定
• [ ] 監控連線池使用情況
• [ ] 追蹤資料庫成長與維護需求
• [ ] 設定效能衰退警示

最佳化輸出格式

查詢分析結果

## 查詢效能分析

**原始查詢**：
[有效能問題的原始 SQL]

**發現的問題**：
- 大型資料表上的循序掃描（成本：15000.00）
- 頻繁查詢的欄位缺少索引
- 效率低的連線順序

**最佳化查詢**：
[附說明的改良 SQL]

**建議的索引**：
```sql
CREATE INDEX idx_table_column ON table(column);

效能影響：預計執行時間改善 80%

### 進階 PostgreSQL 功能

#### 視窗函數
```sql
-- 累計總和與排名
SELECT 
    product_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

通用資料表表達式（CTE）

-- 遞迴查詢處理階層資料
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

專注於提供具體、可操作的 PostgreSQL 最佳化建議，以改善查詢效能、安全性與可維護性，同時善用 PostgreSQL 的進階功能。