PostgreSQL代码审查助手 - 专业SQL优化、JSONB最佳实践与模式设计审查

postgresql-code-review by github/awesome-copilot

8,000 周安装量

27,200 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/github/awesome-copilot --skill postgresql-code-review

开发数据分析开发运维

🇨🇳中文介绍

PostgreSQL 代码审查助手

针对 ${selection}（如未选择则审查整个项目）进行专业的 PostgreSQL 代码审查。重点关注 PostgreSQL 特有的最佳实践、反模式和质量标准。

🎯 PostgreSQL 特有审查领域

JSONB 最佳实践

-- ❌ 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'));

数组操作审查

-- ❌ 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);

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

PostgreSQL 模式设计审查

-- ❌ 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);

自定义类型和域

-- ❌ 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'
);

🔍 PostgreSQL 特有反模式

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

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

函数和触发器问题

-- ❌ 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();

📊 PostgreSQL 扩展使用审查

-- ✅ 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');

🛡️ PostgreSQL 安全审查

行级安全性 (RLS)

-- ✅ 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);

-- ❌ 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;

🎯 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 数据库。

🇺🇸English

PostgreSQL Code Review Assistant

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.

🎯 PostgreSQL-Specific Review Areas

JSONB Best Practices

-- ❌ 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'));

Array Operations Review

-- ❌ 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);

PostgreSQL Schema Design Review

-- ❌ 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);

Custom Types and Domains

-- ❌ 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'
);

🔍 PostgreSQL-Specific Anti-Patterns

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

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

Function and Trigger Issues

-- ❌ 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();

📊 PostgreSQL Extension Usage Review

Extension Best Practices

-- ✅ 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');

🛡️ PostgreSQL Security Review

Row Level Security (RLS)

-- ✅ 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);

Privilege Management

-- ❌ 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;

🎯 PostgreSQL Code Quality Checklist

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

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

PostgreSQL Features Utilization

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

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

📝 PostgreSQL-Specific Review Guidelines

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.

Weekly Installs

7.9K

Repository

github/awesome-copilot

GitHub Stars

27.0K

First Seen

Feb 25, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

codex7.8K

gemini-cli7.8K

opencode7.8K

cursor7.8K

github-copilot7.8K

kimi-cli7.8K

React 组合模式指南：Vercel 组件架构最佳实践，提升代码可维护性

102,200 周安装