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;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);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 matchingMonitoring & 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 indexesPostgreSQL-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"}';📋 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**:CREATE INDEX idx_table_column ON table(column);
**Performance Impact**: Expected 80% improvement in execution time
🚀 Advanced PostgreSQL Features
Window Functions
-- 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.