postgresql-optimization

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 Optim…

);\n\n-- Table using custom types\nCREATE TABLE customers (\n id SERIAL PRIMARY KEY,\n email email_address NOT NULL,\n address address_type,\n status order_status DEFAULT 'pending'\n);\n```\n\n### Range Types\n```sql\n-- PostgreSQL range types\nCREATE TABLE reservations (\n id SERIAL PRIMARY KEY,\n room_id INTEGER,\n reservation_period tstzrange,\n price_range numrange\n);\n\n-- Range queries\nSELECT * FROM reservations \nWHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');\n\n-- Exclude overlapping ranges\nALTER TABLE reservations \nADD CONSTRAINT no_overlap \nEXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);\n```\n\n### Geometric Types\n```sql\n-- PostgreSQL geometric types\nCREATE TABLE locations (\n id SERIAL PRIMARY KEY,\n name TEXT,\n coordinates POINT,\n coverage CIRCLE,\n service_area POLYGON\n);\n\n-- Geometric queries\nSELECT name FROM locations \nWHERE coordinates \u003c-> point(40.7128, -74.0060) \u003c 10; -- Within 10 units\n\n-- GiST index for geometric data\nCREATE INDEX idx_locations_coords ON locations USING gist(coordinates);\n```\n\n## 📊 PostgreSQL Extensions & Tools\n\n### Useful Extensions\n```sql\n-- Enable commonly used extensions\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"; -- UUID generation\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\"; -- Cryptographic functions\nCREATE EXTENSION IF NOT EXISTS \"unaccent\"; -- Remove accents from text\nCREATE EXTENSION IF NOT EXISTS \"pg_trgm\"; -- Trigram matching\nCREATE EXTENSION IF NOT EXISTS \"btree_gin\"; -- GIN indexes for btree types\n\n-- Using extensions\nSELECT uuid_generate_v4(); -- Generate UUIDs\nSELECT crypt('password', gen_salt('bf')); -- Hash passwords\nSELECT similarity('postgresql', 'postgersql'); -- Fuzzy matching\n```\n\n### Monitoring & Maintenance\n```sql\n-- Database size and growth\nSELECT pg_size_pretty(pg_database_size(current_database())) as db_size;\n\n-- Table and index sizes\nSELECT schemaname, tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size\nFROM pg_tables \nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;\n\n-- Index usage statistics\nSELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes \nWHERE idx_scan = 0; -- Unused indexes\n```\n\n### PostgreSQL-Specific Optimization Tips\n- **Use EXPLAIN (ANALYZE, BUFFERS)** for detailed query analysis\n- **Configure postgresql.conf** for your workload (OLTP vs OLAP)\n- **Use connection pooling** (pgbouncer) for high-concurrency applications\n- **Regular VACUUM and ANALYZE** for optimal performance\n- **Partition large tables** using PostgreSQL 10+ declarative partitioning\n- **Use pg_stat_statements** for query performance monitoring\n\n## 📊 Monitoring and Maintenance\n\n### Query Performance Monitoring\n```sql\n-- Identify slow queries\nSELECT query, calls, total_time, mean_time, rows\nFROM pg_stat_statements \nORDER BY total_time DESC \nLIMIT 10;\n\n-- Check index usage\nSELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes \nWHERE idx_scan = 0;\n```\n\n### Database Maintenance\n- **VACUUM and ANALYZE**: Regular maintenance for performance\n- **Index Maintenance**: Monitor and rebuild fragmented indexes\n- **Statistics Updates**: Keep query planner statistics current\n- **Log Analysis**: Regular review of PostgreSQL logs\n\n## 🛠️ Common Query Patterns\n\n### Pagination\n```sql\n-- ❌ BAD: OFFSET for large datasets\nSELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20;\n\n-- ✅ GOOD: Cursor-based pagination\nSELECT * FROM products \nWHERE id > $last_id \nORDER BY id \nLIMIT 20;\n```\n\n### Aggregation\n```sql\n-- ❌ BAD: Inefficient grouping\nSELECT user_id, COUNT(*) \nFROM orders \nWHERE order_date >= '2024-01-01' \nGROUP BY user_id;\n\n-- ✅ GOOD: Optimized with partial index\nCREATE INDEX idx_orders_recent ON orders(user_id) \nWHERE order_date >= '2024-01-01';\n\nSELECT user_id, COUNT(*) \nFROM orders \nWHERE order_date >= '2024-01-01' \nGROUP BY user_id;\n```\n\n### JSON Queries\n```sql\n-- ❌ BAD: Inefficient JSON querying\nSELECT * FROM users WHERE data::text LIKE '%admin%';\n\n-- ✅ GOOD: JSONB operators and GIN index\nCREATE INDEX idx_users_data_gin ON users USING gin(data);\n\nSELECT * FROM users WHERE data @> '{\"role\": \"admin\"}';\n```\n\n## 📋 Optimization Checklist\n\n### Query Analysis\n- [ ] Run EXPLAIN ANALYZE for expensive queries\n- [ ] Check for sequential scans on large tables\n- [ ] Verify appropriate join algorithms\n- [ ] Review WHERE clause selectivity\n- [ ] Analyze sort and aggregation operations\n\n### Index Strategy\n- [ ] Create indexes for frequently queried columns\n- [ ] Use composite indexes for multi-column searches\n- [ ] Consider partial indexes for filtered queries\n- [ ] Remove unused or duplicate indexes\n- [ ] Monitor index bloat and fragmentation\n\n### Security Review\n- [ ] Use parameterized queries exclusively\n- [ ] Implement proper access controls\n- [ ] Enable row-level security where needed\n- [ ] Audit sensitive data access\n- [ ] Use secure connection methods\n\n### Performance Monitoring\n- [ ] Set up query performance monitoring\n- [ ] Configure appropriate log settings\n- [ ] Monitor connection pool usage\n- [ ] Track database growth and maintenance needs\n- [ ] Set up alerting for performance degradation\n\n## 🎯 Optimization Output Format\n\n### Query Analysis Results\n```\n## Query Performance Analysis\n\n**Original Query**:\n[Original SQL with performance issues]\n\n**Issues Identified**:\n- Sequential scan on large table (Cost: 15000.00)\n- Missing index on frequently queried column\n- Inefficient join order\n\n**Optimized Query**:\n[Improved SQL with explanations]\n\n**Recommended Indexes**:\n```sql\nCREATE INDEX idx_table_column ON table(column);\n```\n\n**Performance Impact**: Expected 80% improvement in execution time\n```\n\n## 🚀 Advanced PostgreSQL Features\n\n### Window Functions\n```sql\n-- Running totals and rankings\nSELECT \n product_id,\n order_date,\n amount,\n SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,\n ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank\nFROM sales;\n```\n\n### Common Table Expressions (CTEs)\n```sql\n-- Recursive queries for hierarchical data\nWITH RECURSIVE category_tree AS (\n SELECT id, name, parent_id, 1 as level\n FROM categories \n WHERE parent_id IS NULL\n \n UNION ALL\n \n SELECT c.id, c.name, c.parent_id, ct.level + 1\n FROM categories c\n JOIN category_tree ct ON c.parent_id = ct.id\n)\nSELECT * FROM category_tree ORDER BY level, name;\n```\n\nFocus on providing specific, actionable PostgreSQL optimizations that improve query performance, security, and maintainability while leveraging PostgreSQL's advanced features.\n---","attachment_filenames":[],"attachments":[],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Development Assistant","type":"text"}]},{"type":"paragraph","content":[{"text":"Expert PostgreSQL guidance for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific features, optimization patterns, and advanced capabilities.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"� PostgreSQL-Specific Features","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"JSONB Operations","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Advanced JSONB queries\nCREATE TABLE events (\n id SERIAL PRIMARY KEY,\n data JSONB NOT NULL,\n created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- GIN index for JSONB performance\nCREATE INDEX idx_events_data_gin ON events USING gin(data);\n\n-- JSONB containment and path queries\nSELECT * FROM events \nWHERE data @> '{\"type\": \"login\"}'\n AND data #>> '{user,role}' = 'admin';\n\n-- JSONB aggregation\nSELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Array Operations","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL arrays\nCREATE TABLE posts (\n id SERIAL PRIMARY KEY,\n tags TEXT[],\n categories INTEGER[]\n);\n\n-- Array queries and operations\nSELECT * FROM posts WHERE 'postgresql' = ANY(tags);\nSELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];\nSELECT * FROM posts WHERE array_length(tags, 1) > 3;\n\n-- Array aggregation\nSELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Window Functions & Analytics","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Advanced window functions\nSELECT \n product_id,\n sale_date,\n amount,\n -- Running totals\n SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,\n -- Moving averages\n AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,\n -- Rankings\n DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,\n -- Lag/Lead for comparisons\n LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount\nFROM sales;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Full-Text Search","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL full-text search\nCREATE TABLE documents (\n id SERIAL PRIMARY KEY,\n title TEXT,\n content TEXT,\n search_vector tsvector\n);\n\n-- Update search vector\nUPDATE documents \nSET search_vector = to_tsvector('english', title || ' ' || content);\n\n-- GIN index for search performance\nCREATE INDEX idx_documents_search ON documents USING gin(search_vector);\n\n-- Search queries\nSELECT * FROM documents \nWHERE search_vector @@ plainto_tsquery('english', 'postgresql database');\n\n-- Ranking results\nSELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank\nFROM documents \nWHERE search_vector @@ plainto_tsquery('postgresql')\nORDER BY rank DESC;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"� PostgreSQL Performance Tuning","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Optimization","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- EXPLAIN ANALYZE for performance analysis\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) \nSELECT u.name, COUNT(o.id) as order_count\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id\nWHERE u.created_at > '2024-01-01'::date\nGROUP BY u.id, u.name;\n\n-- Identify slow queries from pg_stat_statements\nSELECT query, calls, total_time, mean_time, rows,\n 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent\nFROM pg_stat_statements \nORDER BY total_time DESC \nLIMIT 10;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Index Strategies","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Composite indexes for multi-column queries\nCREATE INDEX idx_orders_user_date ON orders(user_id, order_date);\n\n-- Partial indexes for filtered queries\nCREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';\n\n-- Expression indexes for computed values\nCREATE INDEX idx_users_lower_email ON users(lower(email));\n\n-- Covering indexes to avoid table lookups\nCREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Connection & Memory Management","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Check connection usage\nSELECT count(*) as connections, state \nFROM pg_stat_activity \nGROUP BY state;\n\n-- Monitor memory usage\nSELECT name, setting, unit \nFROM pg_settings \nWHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"�️ PostgreSQL Advanced Data Types","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Custom Types & Domains","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Create custom types\nCREATE TYPE address_type AS (\n street TEXT,\n city TEXT,\n postal_code TEXT,\n country TEXT\n);\n\nCREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');\n\n-- Use domains for data validation\nCREATE DOMAIN email_address AS TEXT \nCHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

);\n\n-- Table using custom types\nCREATE TABLE customers (\n id SERIAL PRIMARY KEY,\n email email_address NOT NULL,\n address address_type,\n status order_status DEFAULT 'pending'\n);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Range Types","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL range types\nCREATE TABLE reservations (\n id SERIAL PRIMARY KEY,\n room_id INTEGER,\n reservation_period tstzrange,\n price_range numrange\n);\n\n-- Range queries\nSELECT * FROM reservations \nWHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');\n\n-- Exclude overlapping ranges\nALTER TABLE reservations \nADD CONSTRAINT no_overlap \nEXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Geometric Types","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL geometric types\nCREATE TABLE locations (\n id SERIAL PRIMARY KEY,\n name TEXT,\n coordinates POINT,\n coverage CIRCLE,\n service_area POLYGON\n);\n\n-- Geometric queries\nSELECT name FROM locations \nWHERE coordinates \u003c-> point(40.7128, -74.0060) \u003c 10; -- Within 10 units\n\n-- GiST index for geometric data\nCREATE INDEX idx_locations_coords ON locations USING gist(coordinates);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"📊 PostgreSQL Extensions & Tools","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Useful Extensions","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Enable commonly used extensions\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"; -- UUID generation\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\"; -- Cryptographic functions\nCREATE EXTENSION IF NOT EXISTS \"unaccent\"; -- Remove accents from text\nCREATE EXTENSION IF NOT EXISTS \"pg_trgm\"; -- Trigram matching\nCREATE EXTENSION IF NOT EXISTS \"btree_gin\"; -- GIN indexes for btree types\n\n-- Using extensions\nSELECT uuid_generate_v4(); -- Generate UUIDs\nSELECT crypt('password', gen_salt('bf')); -- Hash passwords\nSELECT similarity('postgresql', 'postgersql'); -- Fuzzy matching","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Monitoring & Maintenance","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Database size and growth\nSELECT pg_size_pretty(pg_database_size(current_database())) as db_size;\n\n-- Table and index sizes\nSELECT schemaname, tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size\nFROM pg_tables \nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;\n\n-- Index usage statistics\nSELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes \nWHERE idx_scan = 0; -- Unused indexes","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL-Specific Optimization Tips","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use EXPLAIN (ANALYZE, BUFFERS)","type":"text","marks":[{"type":"strong"}]},{"text":" for detailed query analysis","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Configure postgresql.conf","type":"text","marks":[{"type":"strong"}]},{"text":" for your workload (OLTP vs OLAP)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use connection pooling","type":"text","marks":[{"type":"strong"}]},{"text":" (pgbouncer) for high-concurrency applications","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular VACUUM and ANALYZE","type":"text","marks":[{"type":"strong"}]},{"text":" for optimal performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partition large tables","type":"text","marks":[{"type":"strong"}]},{"text":" using PostgreSQL 10+ declarative partitioning","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use pg_stat_statements","type":"text","marks":[{"type":"strong"}]},{"text":" for query performance monitoring","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"📊 Monitoring and Maintenance","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Performance Monitoring","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Identify slow queries\nSELECT query, calls, total_time, mean_time, rows\nFROM pg_stat_statements \nORDER BY total_time DESC \nLIMIT 10;\n\n-- Check index usage\nSELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes \nWHERE idx_scan = 0;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Database Maintenance","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"VACUUM and ANALYZE","type":"text","marks":[{"type":"strong"}]},{"text":": Regular maintenance for performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index Maintenance","type":"text","marks":[{"type":"strong"}]},{"text":": Monitor and rebuild fragmented indexes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Statistics Updates","type":"text","marks":[{"type":"strong"}]},{"text":": Keep query planner statistics current","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Log Analysis","type":"text","marks":[{"type":"strong"}]},{"text":": Regular review of PostgreSQL logs","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"🛠️ Common Query Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Pagination","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- ❌ BAD: OFFSET for large datasets\nSELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20;\n\n-- ✅ GOOD: Cursor-based pagination\nSELECT * FROM products \nWHERE id > $last_id \nORDER BY id \nLIMIT 20;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Aggregation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- ❌ BAD: Inefficient grouping\nSELECT user_id, COUNT(*) \nFROM orders \nWHERE order_date >= '2024-01-01' \nGROUP BY user_id;\n\n-- ✅ GOOD: Optimized with partial index\nCREATE INDEX idx_orders_recent ON orders(user_id) \nWHERE order_date >= '2024-01-01';\n\nSELECT user_id, COUNT(*) \nFROM orders \nWHERE order_date >= '2024-01-01' \nGROUP BY user_id;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"JSON Queries","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- ❌ BAD: Inefficient JSON querying\nSELECT * FROM users WHERE data::text LIKE '%admin%';\n\n-- ✅ GOOD: JSONB operators and GIN index\nCREATE INDEX idx_users_data_gin ON users USING gin(data);\n\nSELECT * FROM users WHERE data @> '{\"role\": \"admin\"}';","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"📋 Optimization Checklist","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Analysis","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Run EXPLAIN ANALYZE for expensive queries","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Check for sequential scans on large tables","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Verify appropriate join algorithms","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Review WHERE clause selectivity","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Analyze sort and aggregation operations","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Index Strategy","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Create indexes for frequently queried columns","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Use composite indexes for multi-column searches","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Consider partial indexes for filtered queries","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Remove unused or duplicate indexes","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Monitor index bloat and fragmentation","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Security Review","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Use parameterized queries exclusively","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Implement proper access controls","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Enable row-level security where needed","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Audit sensitive data access","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Use secure connection methods","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Performance Monitoring","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Set up query performance monitoring","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Configure appropriate log settings","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Monitor connection pool usage","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Track database growth and maintenance needs","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Set up alerting for performance degradation","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"🎯 Optimization Output Format","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Analysis Results","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"## Query Performance Analysis\n\n**Original Query**:\n[Original SQL with performance issues]\n\n**Issues Identified**:\n- Sequential scan on large table (Cost: 15000.00)\n- Missing index on frequently queried column\n- Inefficient join order\n\n**Optimized Query**:\n[Improved SQL with explanations]\n\n**Recommended Indexes**:\n```sql\nCREATE INDEX idx_table_column ON table(column);","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance Impact","type":"text","marks":[{"type":"strong"}]},{"text":": Expected 80% improvement in execution time","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"\n## 🚀 Advanced PostgreSQL Features\n\n### Window Functions\n```sql\n-- Running totals and rankings\nSELECT \n product_id,\n order_date,\n amount,\n SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,\n ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank\nFROM sales;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Common Table Expressions (CTEs)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Recursive queries for hierarchical data\nWITH RECURSIVE category_tree AS (\n SELECT id, name, parent_id, 1 as level\n FROM categories \n WHERE parent_id IS NULL\n \n UNION ALL\n \n SELECT c.id, c.name, c.parent_id, ct.level + 1\n FROM categories c\n JOIN category_tree ct ON c.parent_id = ct.id\n)\nSELECT * FROM category_tree ORDER BY level, name;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Focus on providing specific, actionable PostgreSQL optimizations that improve query performance, security, and maintainability while leveraging PostgreSQL's advanced features.","type":"text"}]}]},"metadata":{"date":"2026-06-05","name":"postgresql-optimization","author":"@skillopedia","source":{"stars":34253,"repo_name":"awesome-copilot","origin_url":"https://github.com/github/awesome-copilot/blob/HEAD/skills/postgresql-optimization/SKILL.md","repo_owner":"github","body_sha256":"cb7a20b204fecdc1bf122736aa4b518c4fee6c9127b1cc5525e5b9e7f2e28dcb","cluster_key":"3fd0c68b18c0c28a71c8289583251f3c08db7f08229f9244b58bc2bd680b1086","clean_bundle":{"format":"clean-skill-bundle-v1","source":"github/awesome-copilot/skills/postgresql-optimization/SKILL.md","bundle_sha256":"7c199e81ce58e02e4f35bb72b7e44c8f43504fd5cd71783c272c395d7a6a4e3e","attachment_count":0,"text_attachments":0,"binary_attachments":0},"cluster_size":2,"skill_md_path":"skills/postgresql-optimization/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":1},"version":"v1","category":"data-analytics","import_tag":"clean-skills-v1","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."}},"renderedAt":1782979251268}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.