Covers JSONB operations, array types, window functions, full-text search, custom types, range types, and geometric types with practical examples
Includes query optimization strategies using EXPLAIN ANALYZE, index design patterns (composite, partial, covering, expression), and connection/memory management
Provides monitoring and maintenance techniques via pg_stat_statements, pg
Confirm successful installation by checking the skill directory location:
.cursor/skills/postgresql-optimization
Restart Cursor to activate postgresql-optimization. Access via /postgresql-optimization in your agent's command palette.
β
Security Notice
We perform automated surface-level scans (Gen AI Scanner, Socket, Snyk) during installation. These checks detect common vulnerabilities but do not guarantee complete security. Always review skill source code and verify the publisher's reputation before production use.
Skills execute code in your environment. Always review source, verify the publisher, and test in isolation before production.
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 queriesCREATETABLE events ( id SERIALPRIMARYKEY,data JSONB NOTNULL, created_at TIMESTAMPTZ DEFAULTNOW());-- GIN index for JSONB performanceCREATEINDEX idx_events_data_gin ON events USING gin(data);-- JSONB containment and path queriesSELECT*FROM events
WHEREdata @>'{"type": "login"}'ANDdata#>> '{user,role}' = 'admin';-- JSONB aggregationSELECT jsonb_agg(data)FROM events WHEREdata ? 'user_id';
Array Operations
-- PostgreSQL arraysCREATETABLE posts ( id SERIALPRIMARYKEY, tags TEXT[], categories INTEGER[]);-- Array queries and operationsSELECT*FROM posts WHERE'postgresql'=ANY(tags);SELECT*FROM posts WHERE tags && ARRAY['database','sql'];SELECT*FROM posts WHERE array_length(tags,1)>3;-- Array aggregationSELECT array_agg(DISTINCT category)FROM posts, unnest(categories)as category;
-- PostgreSQL full-text searchCREATETABLE documents ( id SERIALPRIMARYKEY, title TEXT, content TEXT, search_vector tsvector
);-- Update search vectorUPDATE documents
SET search_vector = to_tsvector('english', title ||' '|| content);-- GIN index for search performanceCREATEINDEX idx_documents_search ON documents USING gin(search_vector);-- Search queriesSELECT*FROM documents
WHERE search_vector @@ plainto_tsquery('english','postgresql database');-- Ranking resultsSELECT*, ts_rank(search_vector, plainto_tsquery('postgresql'))as rank
FROM documents
WHERE search_vector @@ plainto_tsquery('postgresql')ORDERBY rank DESC;
οΏ½ PostgreSQL Performance Tuning
Query Optimization
-- EXPLAIN ANALYZE for performance analysisEXPLAIN(ANALYZE, BUFFERS, FORMAT TEXT)SELECT u.name,COUNT(o.id)as order_count
FROM users u
LEFTJOIN orders o ON u.id = o.user_id
WHERE u.created_at >'2024-01-01'::dateGROUPBY u.id, u.name;-- Identify slow queries from pg_stat_statementsSELECT 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
ORDERBY total_time DESCLIMIT10;
Index Strategies
-- Composite indexes for multi-column queriesCREATEINDEX idx_orders_user_date ON orders(user_id, order_date);-- Partial indexes for filtered queriesCREATEINDEX idx_active_users ON users(created_at)WHEREstatus='active';-- Expression indexes for computed valuesCREATEINDEX idx_users_lower_email ON users(lower(email));-- Covering indexes to avoid table lookupsCREATEINDEX idx_orders_covering ON orders(user_id,status) INCLUDE (total, created_at);
Connection & Memory Management
-- Check connection usageSELECTcount(*)as connections, state
FROM pg_stat_activity
GROUPBY state;-- Monitor memory usageSELECT 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 typesCREATETYPE address_type AS( street TEXT, city TEXT, postal_code TEXT, country TEXT);CREATETYPE order_status ASENUM('pending','processing','shipped','delivered','cancelled');-- Use domains for data validationCREATE DOMAIN email_address ASTEXTCHECK(VALUE~*'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');-- Table using custom typesCREATETABLE customers ( id SERIAL
β
Make data-driven prioritization decisions faster
Stakeholder Communication
Draft PRDs, status updates, and stakeholder presentations
βΊAccess to product documentation and roadmap tools (Jira, Notion, etc.)
βΊUnderstanding of product management frameworks (RICE, Jobs-to-be-Done, etc.)
βΊStakeholder contact information and communication channels
Time Estimate
30-60 minutes to see productivity improvements
Steps
1Install product management skill
2Start with user story generation for known feature
3Progress to competitive analysis: research 2-3 competitors
4Use for roadmap prioritization: apply RICE/ICE scoring
5Draft stakeholder communications and refine based on feedback
6Build template library for recurring PM tasks
7Share effective prompts with product team
Common Pitfalls
β Not validating competitive researchβverify facts before sharing
β Accepting user stories without involving engineering team
β Over-relying on frameworks without qualitative judgment
β Not customizing outputs to company culture and communication style
β Skipping stakeholder validation of generated requirements
Best Practices
β Do
+Validate research and competitive analysis with real data
+Collaborate with engineering when generating technical requirements
+Customize frameworks and templates to your company context
+Use skill for first drafts, refine with stakeholder input
+Document successful prompt patterns for PM tasks
+Combine AI efficiency with human judgment and intuition
β Don't
βDon't publish competitive analysis without fact-checking
βDon't finalize user stories without engineering review
βDon't make prioritization decisions solely on AI scoring
βDon't skip customer validation of generated requirements
βDon't ignore company-specific context and culture
π‘ Pro Tips
β Provide context: company goals, constraints, customer feedback
β Ask for alternatives: 'Show 3 ways to prioritize this roadmap'
β Request stakeholder-specific formatting: 'Executive summary vs. engineering spec'
β Use skill for 70% generation + 30% customization to company needs
When to Use This
β Use when
Use for user story writing, competitive research, roadmap prioritization, stakeholder communication, and PRD drafting. Best for reducing repetitive documentation and research work.
β Avoid when
Avoid for strategic product vision (requires deep customer empathy), pricing decisions (needs market and financial expertise), or when face-to-face customer discovery is more valuable than speed.
Learning Path
1Basic: user stories, feature specs, status updates
