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 Array Operations Window Functions & Analytics Full Text Search � PostgreSQL Performance Tuning Query Optimization Index Strategies Connection & Memory Management �️ PostgreSQL Advanced Data Types Custom Types & Domains Range Types Geometric Types 📊 PostgreSQL Extensions & Tools Useful Extensions Monitoring & Maintenance 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 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 Aggregation JSON Queries 📋 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 sql CREATE INDEX idx table column ON table(column); 🚀 Advanced PostgreSQL Features Window Functions Common Table Expressions (CTEs) Focus on providing specific, actionable PostgreSQL optimizations that improve query performance, security, and maintainability while leveraging PostgreSQL's advanced features.

postgresql-optimization