postgresql-optimization

name	postgresql-optimization
description	PostgreSQL-specific development assistant focusing on unique PostgreSQL features, advanced data types, and PostgreSQL-exclusive capabilities. Covers JSONB operations, array types, custom types, range/geometric types, full-text search, window functions, and PostgreSQL extensions ecosystem.

PostgreSQL Development Assistant

Expert PostgreSQL guidance for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific features, optimization patterns, and advanced capabilities.

� PostgreSQL-Specific Features

JSONB Operations

-- Advanced JSONB queries
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN index for JSONB performance
CREATE INDEX idx_events_data_gin ON events USING gin(data);

-- JSONB containment and path queries
SELECT * FROM events 
WHERE data @> '{"type": "login"}'
  AND data #>> '{user,role}' = 'admin';

-- JSONB aggregation
SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';

Array Operations

-- PostgreSQL arrays
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    categories INTEGER[]
);

-- Array queries and operations
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
SELECT * FROM posts WHERE array_length(tags, 1) > 3;

-- Array aggregation
SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;

Window Functions & Analytics

-- Advanced window functions
SELECT 
    product_id,
    sale_date,
    amount,
    -- Running totals
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
    -- Moving averages
    AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
    -- Rankings
    DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
    -- Lag/Lead for comparisons
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
FROM sales;

Full-Text Search

-- PostgreSQL full-text search
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- Update search vector
UPDATE documents 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- GIN index for search performance
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);

-- Search queries
SELECT * FROM documents 
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');

-- Ranking results
SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
FROM documents 
WHERE search_vector @@ plainto_tsquery('postgresql')
ORDER BY rank DESC;

� PostgreSQL Performance Tuning

Query Optimization

-- EXPLAIN ANALYZE for performance analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'::date
GROUP BY u.id, u.name;

-- Identify slow queries from pg_stat_statements
SELECT query, calls, total_time, mean_time, rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

AcademyHub Query Optimization Patterns

-- ✅ GOOD: Optimized GORM queries following Clean Architecture
-- Use Preload selectively for mobile API relationships
SELECT s.*, c.name as class_name, p.name as academyhub_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id  
LEFT JOIN academyhub p ON s.academyhub_id = p.id
WHERE s.academyhub_id = $1 
  AND s.deleted_at IS NULL
ORDER BY s.name
LIMIT 20;

-- ✅ GOOD: Cursor-based pagination for mobile lists (avoid OFFSET)
SELECT s.*, c.name as class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE s.academyhub_id = $1 
  AND s.id > $last_id
  AND s.deleted_at IS NULL
ORDER BY s.id
LIMIT 20;

-- ✅ GOOD: Efficient violation filtering with status and date
SELECT v.*, s.name as student_name
FROM violations v
JOIN students s ON v.student_id = s.id
WHERE v.academyhub_id = $1
  AND v.status = 'pending'
  AND v.date >= $start_date
  AND v.date <= $end_date
  AND v.deleted_at IS NULL
ORDER BY v.date DESC, v.id DESC;

-- ❌ BAD: N+1 queries from improper GORM usage
-- SELECT * FROM students WHERE academyhub_id = $1; -- Then separate query per student for class
-- SELECT * FROM classes WHERE id = ?; -- Repeated for each student

-- ✅ GOOD: Single query with proper joins for dashboard statistics
SELECT 
    COUNT(CASE WHEN s.status = 'active' THEN 1 END) as active_students,
    COUNT(CASE WHEN s.status = 'graduated' THEN 1 END) as graduated_students,
    COUNT(v.id) FILTER (WHERE v.status = 'pending') as pending_violations,
    COUNT(pa.id) FILTER (WHERE pa.status = 'completed') as completed_payments
FROM academyhub p
LEFT JOIN students s ON p.id = s.academyhub_id AND s.deleted_at IS NULL
LEFT JOIN violations v ON p.id = v.academyhub_id AND v.deleted_at IS NULL
LEFT JOIN payments pa ON p.id = pa.academyhub_id AND pa.deleted_at IS NULL
WHERE p.id = $1;

Index Strategies

-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';

-- Expression indexes for computed values
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- Covering indexes to avoid table lookups
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

AcademyHub Backend Indexing Strategies

-- Composite indexes for GORM relationships (following backend architecture)
CREATE INDEX idx_students_academyhub_class ON students(academyhub_id, class_id);
CREATE INDEX idx_violations_student_date ON violations(student_id, date DESC);
CREATE INDEX idx_payments_user_status_created ON payments(user_id, status, created_at DESC);

-- Partial indexes for active records (common in AcademyHub queries)
CREATE INDEX idx_active_students ON students(academyhub_id, class_id) 
WHERE deleted_at IS NULL;
CREATE INDEX idx_pending_violations ON violations(academyhub_id, status) 
WHERE status = 'pending' AND deleted_at IS NULL;

-- Expression indexes for case-insensitive searches (mobile API requirements)
CREATE INDEX idx_students_name_lower ON students(lower(name)) 
WHERE deleted_at IS NULL;
CREATE INDEX idx_parents_phone_pattern ON parents(phone text_pattern_ops) 
WHERE deleted_at IS NULL;

-- Covering indexes for mobile list endpoints (reduce table lookups)
CREATE INDEX idx_students_mobile_list ON students(academyhub_id, class_id, name) 
INCLUDE (email, phone, created_at, updated_at) 
WHERE deleted_at IS NULL;

-- JSONB indexes optimized for mobile API filtering
CREATE INDEX idx_students_mobile_data_gin ON students USING gin(mobile_data jsonb_path_ops);
CREATE INDEX idx_violations_details_gin ON violations USING gin(details jsonb_path_ops);

-- Multi-column partial covering index for dashboard queries
CREATE INDEX idx_dashboard_stats ON students(academyhub_id, status, class_id) 
INCLUDE (created_at, updated_at) 
WHERE deleted_at IS NULL AND status IN ('active', 'graduated');

Connection & Memory Management

-- Check connection usage
SELECT count(*) as connections, state 
FROM pg_stat_activity 
GROUP BY state;

-- Monitor memory usage
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

�️ PostgreSQL Advanced Data Types

Custom Types & Domains

-- Create custom types
CREATE TYPE address_type AS (
    street TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT
);

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Use domains for data validation
CREATE DOMAIN email_address AS TEXT 
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Table using custom types
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    address address_type,
    status order_status DEFAULT 'pending'
);

Range Types

-- PostgreSQL range types
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    reservation_period tstzrange,
    price_range numrange
);

-- Range queries
SELECT * FROM reservations 
WHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');

-- Exclude overlapping ranges
ALTER TABLE reservations 
ADD CONSTRAINT no_overlap 
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);

Geometric Types

-- PostgreSQL geometric types
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    coordinates POINT,
    coverage CIRCLE,
    service_area POLYGON
);

-- Geometric queries
SELECT name FROM locations 
WHERE coordinates <-> point(40.7128, -74.0060) < 10; -- Within 10 units

-- GiST index for geometric data
CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

📊 PostgreSQL Extensions & Tools

Useful Extensions

-- Enable commonly used extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";     -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "unaccent";     -- Remove accents from text
CREATE EXTENSION IF NOT EXISTS "pg_trgm";      -- Trigram matching
CREATE EXTENSION IF NOT EXISTS "btree_gin";    -- GIN indexes for btree types

-- Using extensions
SELECT uuid_generate_v4();                     -- Generate UUIDs
SELECT crypt('password', gen_salt('bf'));      -- Hash passwords
SELECT similarity('postgresql', 'postgersql'); -- Fuzzy matching

Monitoring & Maintenance

-- Database size and growth
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size;

-- Table and index sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;  -- Unused indexes

AcademyHub Production Monitoring Requirements

-- Enable pg_stat_statements for query performance monitoring (required in production)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Monitor slow queries specific to AcademyHub mobile API patterns
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
WHERE query LIKE '%students%' 
   OR query LIKE '%violations%' 
   OR query LIKE '%payments%'
ORDER BY total_exec_time DESC 
LIMIT 10;

-- Connection pool monitoring (aligned with GORM connection settings)
SELECT 
    count(*) as total_connections,
    count(*) FILTER (WHERE state = 'active') as active_connections,
    count(*) FILTER (WHERE state = 'idle') as idle_connections,
    count(*) FILTER (WHERE application_name LIKE '%academyhub%') as academyhub_connections
FROM pg_stat_activity;

-- Table bloat monitoring for high-write tables
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size,
    CASE 
        WHEN pg_total_relation_size(schemaname||'.'||tablename) > 0 THEN
            round(100.0 * (pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) / pg_total_relation_size(schemaname||'.'||tablename))
        ELSE 0
    END as index_ratio_percent
FROM pg_tables 
WHERE tablename IN ('students', 'violations', 'payments', 'classes', 'parents')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- GORM-specific query pattern monitoring
-- Track queries with N+1 patterns or inefficient joins
SELECT 
    query,
    mean_exec_time,
    rows,
    shared_blks_read
FROM pg_stat_statements 
WHERE query LIKE '%LEFT JOIN%' 
   OR query LIKE '%INNER JOIN%'
   OR query LIKE '%CROSS JOIN%'
ORDER BY mean_exec_time DESC 
LIMIT 5;

-- Cache hit ratio monitoring (critical for mobile API performance)
SELECT 
    'index hit rate' as name,
    (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) as ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 
    'table hit rate' as name,
    sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as ratio
FROM pg_statio_user_tables;

PostgreSQL-Specific Optimization Tips

• Use EXPLAIN (ANALYZE, BUFFERS) for detailed query analysis
• Configure postgresql.conf for your workload (OLTP vs OLAP)
• Use connection pooling (pgbouncer) for high-concurrency applications
• Regular VACUUM and ANALYZE for optimal performance
• Partition large tables using PostgreSQL 10+ declarative partitioning
• Use pg_stat_statements for query performance monitoring

📊 Monitoring and Maintenance

Query Performance Monitoring

-- Identify slow queries
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Database Maintenance

• VACUUM and ANALYZE: Regular maintenance for performance
• Index Maintenance: Monitor and rebuild fragmented indexes
• Statistics Updates: Keep query planner statistics current
• Log Analysis: Regular review of PostgreSQL logs

🛠️ Common Query Patterns

Pagination

-- ❌ BAD: OFFSET for large datasets
SELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20;

-- ✅ GOOD: Cursor-based pagination
SELECT * FROM products 
WHERE id > $last_id 
ORDER BY id 
LIMIT 20;

Aggregation

-- ❌ BAD: Inefficient grouping
SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

-- ✅ GOOD: Optimized with partial index
CREATE INDEX idx_orders_recent ON orders(user_id) 
WHERE order_date >= '2024-01-01';

SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

JSON Queries

-- ❌ BAD: Inefficient JSON querying
SELECT * FROM users WHERE data::text LIKE '%admin%';

-- ✅ GOOD: JSONB operators and GIN index
CREATE INDEX idx_users_data_gin ON users USING gin(data);

SELECT * FROM users WHERE data @> '{"role": "admin"}';

AcademyHub Mobile API JSONB Optimization

-- Optimized JSONB structure for mobile API responses
-- Store normalized data in separate columns + denormalized JSONB for mobile
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    class_id INTEGER,
    academyhub_id INTEGER,
    -- Denormalized JSONB for mobile API efficiency
    mobile_data JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Populate mobile_data JSONB with related data
UPDATE students 
SET mobile_data = jsonb_build_object(
    'name', name,
    'email', email,
    'phone', phone,
    'class_name', (SELECT name FROM classes WHERE id = students.class_id),
    'academyhub_name', (SELECT name FROM academyhub WHERE id = students.academyhub_id),
    'parent_info', (
        SELECT jsonb_build_object('name', name, 'phone', phone) 
        FROM parents WHERE student_id = students.id LIMIT 1
    )
);

-- GIN index for efficient mobile API queries
CREATE INDEX idx_students_mobile_data_gin ON students USING gin(mobile_data jsonb_path_ops);

-- Mobile API query - retrieve complete student data in single query
SELECT 
    id,
    mobile_data || jsonb_build_object(
        'created_at', to_char(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
        'updated_at', to_char(updated_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')
    ) as student_data
FROM students 
WHERE academyhub_id = $1 
  AND mobile_data @> '{"class_name": "XII-A"}'
ORDER BY name
LIMIT 20;

-- Efficient filtering on nested JSONB fields
SELECT id, mobile_data
FROM students
WHERE mobile_data #>> '{parent_info,name}' ILIKE '%ahmad%'
   OR mobile_data -> 'class_name' ? 'XII-A';

📋 Optimization Checklist

Query Analysis

• Run EXPLAIN ANALYZE for expensive queries
• Check for sequential scans on large tables
• Verify appropriate join algorithms
• Review WHERE clause selectivity
• Analyze sort and aggregation operations

Index Strategy

• Create indexes for frequently queried columns
• Use composite indexes for multi-column searches
• Consider partial indexes for filtered queries
• Remove unused or duplicate indexes
• Monitor index bloat and fragmentation

Security Review

• Use parameterized queries exclusively
• Implement proper access controls
• Enable row-level security where needed
• Audit sensitive data access
• Use secure connection methods

Performance Monitoring

• Set up query performance monitoring
• Configure appropriate log settings
• Monitor connection pool usage
• Track database growth and maintenance needs
• Set up alerting for performance degradation

🎯 Optimization Output Format

Query Analysis Results

## Query Performance Analysis

**Original Query**:
[Original SQL with performance issues]

**Issues Identified**:
- Sequential scan on large table (Cost: 15000.00)
- Missing index on frequently queried column
- Inefficient join order

**Optimized Query**:
[Improved SQL with explanations]

**Recommended Indexes**:
```sql
CREATE INDEX idx_table_column ON table(column);

Performance Impact: Expected 80% improvement in execution time

## 🚀 Advanced PostgreSQL Features

### Window Functions
```sql
-- Running totals and rankings
SELECT 
    product_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

Common Table Expressions (CTEs)

-- Recursive queries for hierarchical data
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

Focus on providing specific, actionable PostgreSQL optimizations that improve query performance, security, and maintainability while leveraging PostgreSQL's advanced features.