pyfagorass 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.