PostgreSQL Code Review Assistant

PostgreSQL代码审查助手

Expert PostgreSQL code review for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific best practices, anti-patterns, and quality standards that are unique to PostgreSQL.

为${selection}（若未选择则为整个项目）提供专业的PostgreSQL代码审查。专注于PostgreSQL专属的最佳实践、反模式及质量标准。

🎯 PostgreSQL-Specific Review Areas

🎯 PostgreSQL专属审查领域

JSONB Best Practices

JSONB最佳实践

sql

-- ❌ BAD: Inefficient JSONB usage
SELECT * FROM orders WHERE data->>'status' = 'shipped';  -- No index support

-- ✅ GOOD: Indexable JSONB queries
CREATE INDEX idx_orders_status ON orders USING gin((data->'status'));
SELECT * FROM orders WHERE data @> '{"status": "shipped"}';

-- ❌ BAD: Deep nesting without consideration
UPDATE orders SET data = data || '{"shipping":{"tracking":{"number":"123"}}}';

-- ✅ GOOD: Structured JSONB with validation
ALTER TABLE orders ADD CONSTRAINT valid_status 
CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));

sql

-- ❌ 不良示例：低效的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'));

Array Operations Review

数组操作审查

sql

-- ❌ BAD: Inefficient array operations
SELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index

-- ✅ GOOD: GIN indexed array queries
CREATE INDEX idx_products_categories ON products USING gin(categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

-- ❌ BAD: Array concatenation in loops
-- This would be inefficient in a function/procedure

-- ✅ GOOD: Bulk array operations
UPDATE products SET categories = categories || ARRAY['new_category']
WHERE id IN (SELECT id FROM products WHERE condition);

sql

-- ❌ 不良示例：低效的数组操作
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 Schema Design Review

PostgreSQL架构设计审查

sql

-- ❌ BAD: Not using PostgreSQL features
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- ✅ GOOD: PostgreSQL-optimized schema
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL,  -- Case-insensitive email
    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,}$')
);

-- Add JSONB GIN index for metadata queries
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

sql

-- ❌ 不良示例：未使用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);

Custom Types and Domains

自定义类型与域

sql

-- ❌ BAD: Using generic types for specific data
CREATE TABLE transactions (
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    status VARCHAR(20)
);

-- ✅ GOOD: PostgreSQL custom types
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'
);

sql

-- ❌ 不良示例：为特定数据使用通用类型
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-Specific Anti-Patterns

🔍 PostgreSQL专属反模式

Performance Anti-Patterns

性能反模式

Avoiding PostgreSQL-specific indexes: Not using GIN/GiST for appropriate data types
Misusing JSONB: Treating JSONB like a simple string field
Ignoring array operators: Using inefficient array operations
Poor partition key selection: Not leveraging PostgreSQL partitioning effectively

避免使用PostgreSQL专属索引：不为合适的数据类型使用GIN/GiST索引
误用JSONB：将JSONB当作简单字符串字段处理
忽略数组操作符：使用低效的数组操作
分区键选择不当：未有效利用PostgreSQL分区功能

Schema Design Issues

架构设计问题

Not using ENUM types: Using VARCHAR for limited value sets
Ignoring constraints: Missing CHECK constraints for data validation
Wrong data types: Using VARCHAR instead of TEXT or CITEXT
Missing JSONB structure: Unstructured JSONB without validation

未使用ENUM类型：为有限值集合使用VARCHAR
忽略约束：缺少用于数据验证的CHECK约束
错误的数据类型：使用VARCHAR而非TEXT或CITEXT
缺失JSONB结构：无验证的非结构化JSONB

Function and Trigger Issues

函数与触发器问题

sql

-- ❌ BAD: Inefficient trigger function
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ✅ GOOD: Optimized trigger function
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Set trigger to fire only when needed
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();

sql

-- ❌ 不良示例：低效的触发器函数
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 Extension Usage Review

📊 PostgreSQL扩展使用审查

Extension Best Practices

扩展最佳实践

sql

-- ✅ Check if extension exists before creating
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ✅ Use extensions appropriately
-- UUID generation
SELECT uuid_generate_v4();

-- Password hashing
SELECT crypt('password', gen_salt('bf'));

-- Fuzzy text matching
SELECT word_similarity('postgres', 'postgre');

sql

-- ✅ 创建前检查扩展是否存在
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 Security Review

🛡️ PostgreSQL安全审查

Row Level Security (RLS)

sql

-- ✅ GOOD: Implementing 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);

sql

-- ✅ 良好示例：实现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);

Privilege Management

权限管理

sql

-- ❌ BAD: Overly broad permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- ✅ GOOD: Granular permissions
GRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;
GRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;

sql

-- ❌ 不良示例：权限过宽
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 Code Quality Checklist

🎯 PostgreSQL代码质量检查清单

Schema Design

架构设计

Using appropriate PostgreSQL data types (CITEXT, JSONB, arrays)
Leveraging ENUM types for constrained values
Implementing proper CHECK constraints
Using TIMESTAMPTZ instead of TIMESTAMP
Defining custom domains for reusable constraints

合理使用PostgreSQL专属数据类型（CITEXT、JSONB、数组）
为受限值集合使用ENUM类型
实现恰当的CHECK约束
使用TIMESTAMPTZ而非TIMESTAMP
为可复用约束定义自定义域

Performance Considerations

性能考量

Appropriate index types (GIN for JSONB/arrays, GiST for ranges)
JSONB queries using containment operators (@>, ?)
Array operations using PostgreSQL-specific operators
Proper use of window functions and CTEs
Efficient use of PostgreSQL-specific functions

恰当的索引类型（JSONB/数组用GIN，范围类型用GiST）
使用包含操作符（@>, ?）的JSONB查询
使用PostgreSQL专属操作符的数组操作
合理使用窗口函数和CTE
高效使用PostgreSQL专属函数

PostgreSQL Features Utilization

PostgreSQL特性利用

Using extensions where appropriate
Implementing stored procedures in PL/pgSQL when beneficial
Leveraging PostgreSQL's advanced SQL features
Using PostgreSQL-specific optimization techniques
Implementing proper error handling in functions

合理使用扩展
必要时用PL/pgSQL实现存储过程
利用PostgreSQL高级SQL特性
使用PostgreSQL专属优化技巧
在函数中实现恰当的错误处理

Security and Compliance

安全与合规

Row Level Security (RLS) implementation where needed
Proper role and privilege management
Using PostgreSQL's built-in encryption functions
Implementing audit trails with PostgreSQL features

必要时实现Row Level Security (RLS)
恰当的角色与权限管理
使用PostgreSQL内置加密函数
利用PostgreSQL特性实现审计追踪

📝 PostgreSQL-Specific Review Guidelines

📝 PostgreSQL专属审查指南

Data Type Optimization: Ensure PostgreSQL-specific types are used appropriately
Index Strategy: Review index types and ensure PostgreSQL-specific indexes are utilized
JSONB Structure: Validate JSONB schema design and query patterns
Function Quality: Review PL/pgSQL functions for efficiency and best practices
Extension Usage: Verify appropriate use of PostgreSQL extensions
Performance Features: Check utilization of PostgreSQL's advanced features
Security Implementation: Review PostgreSQL-specific security features

Focus on PostgreSQL's unique capabilities and ensure the code leverages what makes PostgreSQL special rather than treating it as a generic SQL database.

数据类型优化：确保合理使用PostgreSQL专属类型
索引策略：审查索引类型，确保利用PostgreSQL专属索引
JSONB结构：验证JSONB架构设计与查询模式
函数质量：审查PL/pgSQL函数的效率与最佳实践
扩展使用：验证PostgreSQL扩展的合理使用
性能特性：检查PostgreSQL高级特性的利用情况
安全实现：审查PostgreSQL专属安全特性

聚焦PostgreSQL的独特能力，确保代码充分利用PostgreSQL的特性，而非将其视为通用SQL数据库。

postgresql-code-review

Original

Translation

PostgreSQL Code Review Assistant

PostgreSQL代码审查助手

🎯 PostgreSQL-Specific Review Areas

🎯 PostgreSQL专属审查领域

JSONB Best Practices

JSONB最佳实践

Array Operations Review

数组操作审查

PostgreSQL Schema Design Review

PostgreSQL架构设计审查

Custom Types and Domains

自定义类型与域

🔍 PostgreSQL-Specific Anti-Patterns

🔍 PostgreSQL专属反模式

Performance Anti-Patterns

性能反模式

Schema Design Issues

架构设计问题

Function and Trigger Issues

函数与触发器问题

📊 PostgreSQL Extension Usage Review

📊 PostgreSQL扩展使用审查

Extension Best Practices

扩展最佳实践

🛡️ PostgreSQL Security Review

🛡️ PostgreSQL安全审查

Row Level Security (RLS)

Row Level Security (RLS)

Privilege Management

权限管理

🎯 PostgreSQL Code Quality Checklist

🎯 PostgreSQL代码质量检查清单

Schema Design

架构设计

Performance Considerations

性能考量

PostgreSQL Features Utilization

PostgreSQL特性利用

Security and Compliance

安全与合规

📝 PostgreSQL-Specific Review Guidelines

📝 PostgreSQL专属审查指南