PostgreSQL 代码审查助手

针对 ${selection}（如果未选择，则为整个项目）的专家级 PostgreSQL 代码审查。重点关注 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 特定审查指南

1. 数据类型优化：确保适当使用了 PostgreSQL 特定类型
2. 索引策略：审查索引类型，确保利用了 PostgreSQL 特定索引
3. JSONB 结构：验证 JSONB 模式设计和查询模式
4. 函数质量：审查 PL/pgSQL 函数的效率和最佳实践
5. 扩展使用：验证 PostgreSQL 扩展的适当使用
6. 性能特性：检查 PostgreSQL 高级特性的利用情况
7. 安全实施：审查 PostgreSQL 特定安全特性

重点关注 PostgreSQL 的独特能力，确保代码充分利用了 PostgreSQL 的特性，而不是将其视为一个通用的 SQL 数据库。