postgresql-code-review

PostgreSQL 程式碼審查助手

針對 ${selection}（若未選取則為整個專案）進行專家級 PostgreSQL 程式碼審查。專注於 PostgreSQL 特有的最佳實務、反模式及品質標準。

🎯 PostgreSQL 專屬審查領域

JSONB 最佳實務

-- ❌ 不良：低效的 JSONB 使用
SELECT * FROM orders WHERE data->>'status' = 'shipped';  -- 無索引支援

-- ✅ 良好：可索引的 JSONB 查詢
CREATE INDEX idx_orders_status ON orders USING gin((data->'status'));
SELECT * FROM orders WHERE data @> '{"status": "shipped"}';

-- ❌ 不良：未經考慮的深度巢狀
UPDATE orders SET data = data || '{"shipping":{"tracking":{"number":"123"}}}';

-- ✅ 良好：結構化 JSONB 並加上驗證
ALTER TABLE orders ADD CONSTRAINT valid_status 
CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));

陣列操作審查

-- ❌ 不良：低效的陣列操作
SELECT * FROM products WHERE 'electronics' = ANY(categories);  -- 無索引

-- ✅ 良好：GIN 索引陣列查詢
CREATE INDEX idx_products_categories ON products USING gin(categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

-- ❌ 不良：在迴圈中進行陣列串接
-- 在函式或程序中這樣做會很沒效率

-- ✅ 良好：批次陣列操作
UPDATE products SET categories = categories || ARRAY['new_category']
WHERE id IN (SELECT id FROM products WHERE condition);

PostgreSQL 綱要設計審查

-- ❌ 不良：未使用 PostgreSQL 功能
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- ✅ 良好：PostgreSQL 最佳化綱要
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL,  -- 不區分大小寫的電子郵件
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- 為 metadata 查詢新增 JSONB GIN 索引
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

自訂型別與網域

-- ❌ 不良：對特定資料使用通用型別
CREATE TABLE transactions (
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    status VARCHAR(20)
);

-- ✅ 良好：PostgreSQL 自訂型別
CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');
CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);

CREATE TABLE transactions (
    amount positive_amount NOT NULL,
    currency currency_code NOT NULL,
    status transaction_status DEFAULT 'pending'
);

🔍 PostgreSQL 專屬反模式

效能反模式

避免使用 PostgreSQL 專屬索引：未針對適當資料型別使用 GIN/GiST
誤用 JSONB：將 JSONB 當作簡單字串欄位處理
忽略陣列運算子：使用低效的陣列操作
分割鍵選擇不當：未有效利用 PostgreSQL 分割功能

綱要設計問題

未使用 ENUM 型別：對有限值集合使用 VARCHAR
忽略約束：缺少用於資料驗證的 CHECK 約束
錯誤的資料型別：使用 VARCHAR 而非 TEXT 或 CITEXT
缺少 JSONB 結構：未經驗證的非結構化 JSONB

函式與觸發器問題

-- ❌ 不良：低效的觸發器函式
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();  -- 應使用 TIMESTAMPTZ
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ✅ 良好：最佳化的觸發器函式
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 設定觸發器僅在必要時觸發
CREATE TRIGGER update_modified_time_trigger
    BEFORE UPDATE ON table_name
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION update_modified_time();

📊 PostgreSQL 擴充套件使用審查

擴充套件最佳實務

-- ✅ 在建立前檢查擴充套件是否存在
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ✅ 適當使用擴充套件
-- UUID 產生
SELECT uuid_generate_v4();

-- 密碼雜湊
SELECT crypt('password', gen_salt('bf'));

-- 模糊文字比對
SELECT word_similarity('postgres', 'postgre');

🛡️ PostgreSQL 安全審查

列層級安全 (RLS)

-- ✅ 良好：實作 RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_data_policy ON sensitive_data
    FOR ALL TO application_role
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

權限管理

-- ❌ 不良：權限過於寬鬆
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- ✅ 良好：細粒度權限
GRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;
GRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;

🎯 PostgreSQL 程式碼品質檢查清單

綱要設計

[ ] 使用適當的 PostgreSQL 資料型別 (CITEXT, JSONB, 陣列)
[ ] 對約束值利用 ENUM 型別
[ ] 實作適當的 CHECK 約束
[ ] 使用 TIMESTAMPTZ 而非 TIMESTAMP
[ ] 為可重複使用的約束定義自訂網域

效能考量

[ ] 適當的索引型別 (JSONB/陣列用 GIN，範圍用 GiST)
[ ] 使用包含運算子 (@>, ?) 的 JSONB 查詢
[ ] 使用 PostgreSQL 專屬運算子的陣列操作
[ ] 正確使用視窗函式與 CTE
[ ] 有效使用 PostgreSQL 專屬函式

PostgreSQL 功能利用

[ ] 在適當情況下使用擴充套件
[ ] 在有益時以 PL/pgSQL 實作預存程序
[ ] 利用 PostgreSQL 的進階 SQL 功能
[ ] 使用 PostgreSQL 專屬最佳化技術
[ ] 在函式中實作適當的錯誤處理

安全與合規

[ ] 在需要時實作列層級安全 (RLS)
[ ] 適當的角色與權限管理
[ ] 使用 PostgreSQL 內建的加密函式
[ ] 使用 PostgreSQL 功能實作稽核軌跡

📝 PostgreSQL 專屬審查指南

資料型別最佳化：確保適當使用 PostgreSQL 專屬型別
索引策略：審查索引型別，確保使用 PostgreSQL 專屬索引
JSONB 結構：驗證 JSONB 綱要設計與查詢模式
函式品質：審查 PL/pgSQL 函式的效率與最佳實務
擴充套件使用：確認適當使用 PostgreSQL 擴充套件
效能功能：檢查 PostgreSQL 進階功能的利用情況
安全實作：審查 PostgreSQL 專屬安全功能

專注於 PostgreSQL 的獨特能力，確保程式碼充分利用 PostgreSQL 的優勢，而非將其視為通用 SQL 資料庫。

您可能也喜歡

golang-database

2.3Kdatabase

Go 資料庫存取的完整指南 — 參數化查詢、結構掃描、可為 NULL 的欄位、交易、隔離層級、SELECT FOR UPDATE、連線池、批次處理、Context 傳遞以及遷移工具。適用於撰寫、審查或除錯與 PostgreSQL、MariaDB、MySQL 或 SQLite 互動的 Golang 程式碼；用於資料庫測試；或關於 database/sql、sqlx 或 pgx 的問題。不產生資料庫綱要或遷移 SQL。