postgres-advanced-patterns

PostgreSQL Advanced Patterns Advanced patterns for high-performance PostgreSQL database design, querying, and optimization. Performance Optimization 1. Effective Indexing 2. Query Optimization 3. Connection Pooling Advanced Query Patterns Window Functions Common Table Expressions (CTEs) JSON Operations Database Design Patterns 1. Partitioning 2. Materialized Views 3. Constraints and Validation Transactions and Concurrency Transaction Isolation Row-Level Locking Monitoring and Maintenance Query Performance Maintenance Tasks Best Practices 1. Always use parameterized queries to prevent SQL inje…

);\n\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email email_address NOT NULL UNIQUE\n);\n```\n\n## Transactions and Concurrency\n\n### Transaction Isolation\n\n```sql\n-- Serializable transactions for critical operations\nBEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;\nSELECT * FROM accounts WHERE id = 1 FOR UPDATE;\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE id = 2;\nCOMMIT;\n\n-- Repeatable read for consistent snapshots\nBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;\nSELECT SUM(balance) FROM accounts;\n-- Consistent view maintained throughout transaction\nCOMMIT;\n```\n\n### Row-Level Locking\n\n```sql\n-- Pessimistic locking\nSELECT * FROM orders WHERE id = 123 FOR UPDATE;\n\n-- Shared lock for read-only access\nSELECT * FROM products WHERE id = 456 FOR SHARE;\n\n-- Skip locked rows\nSELECT * FROM queue WHERE processed = false\nFOR UPDATE SKIP LOCKED\nLIMIT 10;\n```\n\n## Monitoring and Maintenance\n\n### Query Performance\n\n```sql\n-- Find slow queries\nSELECT query, mean_exec_time, calls\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 10;\n\n-- Check index usage\nSELECT schemaname, tablename, indexname, idx_scan\nFROM pg_stat_user_indexes\nORDER BY idx_scan ASC;\n\n-- Table bloat\nSELECT schemaname, tablename, \n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size\nFROM pg_tables\nWHERE schemaname = 'public'\nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;\n```\n\n### Maintenance Tasks\n\n```sql\n-- Vacuum and analyze\nVACUUM ANALYZE users;\n\n-- Reindex\nREINDEX TABLE users;\n\n-- Update statistics\nANALYZE users;\n```\n\n## Best Practices\n\n1. **Always use parameterized queries** to prevent SQL injection\n2. **Create indexes on foreign keys** for join performance\n3. **Use connection pooling** for better resource management\n4. **Monitor query performance** with pg_stat_statements\n5. **Regular VACUUM and ANALYZE** for statistics\n6. **Use appropriate transaction isolation levels**\n7. **Avoid N+1 queries** with proper joins or batching\n8. **Implement retry logic** for transaction conflicts\n\n## Integration Points\n\nComplements:\n- **backend-implementation-patterns**: For API data access\n- **tdd-workflow**: For database testing\n- **verification-loop**: For query performance checks\n- **security-implementation-guide**: For secure queries\n---","attachment_filenames":["references/query-patterns.md"],"attachments":[{"filename":"references/query-patterns.md","content":"# PostgreSQL Query Patterns\n\nAdvanced query patterns for PostgreSQL.\n\n## Common Table Expressions (CTEs)\n\n### Recursive CTE (Hierarchies)\n\n```sql\n-- Organization hierarchy\nWITH RECURSIVE org_tree AS (\n -- Base case: top-level\n SELECT id, name, manager_id, 1 as level\n FROM employees\n WHERE manager_id IS NULL\n\n UNION ALL\n\n -- Recursive case\n SELECT e.id, e.name, e.manager_id, t.level + 1\n FROM employees e\n JOIN org_tree t ON e.manager_id = t.id\n)\nSELECT * FROM org_tree ORDER BY level, name;\n```\n\n### Multiple CTEs\n\n```sql\nWITH\nactive_users AS (\n SELECT * FROM users WHERE status = 'active'\n),\nrecent_orders AS (\n SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'\n),\nuser_order_summary AS (\n SELECT\n u.id,\n u.name,\n COUNT(o.id) as order_count,\n SUM(o.total) as total_spent\n FROM active_users u\n LEFT JOIN recent_orders o ON u.id = o.user_id\n GROUP BY u.id, u.name\n)\nSELECT * FROM user_order_summary ORDER BY total_spent DESC;\n```\n\n## Window Functions\n\n### Row Numbering\n\n```sql\n-- Rank users by total purchases\nSELECT\n name,\n total_purchases,\n ROW_NUMBER() OVER (ORDER BY total_purchases DESC) as rank,\n RANK() OVER (ORDER BY total_purchases DESC) as rank_with_ties,\n DENSE_RANK() OVER (ORDER BY total_purchases DESC) as dense_rank\nFROM users;\n```\n\n### Running Totals\n\n```sql\nSELECT\n date,\n amount,\n SUM(amount) OVER (ORDER BY date) as running_total,\n AVG(amount) OVER (\n ORDER BY date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as moving_avg_7day\nFROM sales;\n```\n\n### Partitioned Windows\n\n```sql\n-- Rank within each category\nSELECT\n category,\n product_name,\n sales,\n RANK() OVER (PARTITION BY category ORDER BY sales DESC) as category_rank\nFROM products;\n```\n\n### Lead/Lag\n\n```sql\n-- Compare to previous/next row\nSELECT\n date,\n revenue,\n LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,\n revenue - LAG(revenue) OVER (ORDER BY date) as day_over_day_change,\n LEAD(revenue) OVER (ORDER BY date) as next_day_revenue\nFROM daily_revenue;\n```\n\n## JSON Operations\n\n### JSON Extraction\n\n```sql\n-- Extract from JSON\nSELECT\n data->>'name' as name, -- Text extraction\n data->'address'->>'city' as city, -- Nested extraction\n (data->>'age')::int as age, -- Cast to type\n data->'tags' as tags -- Keep as JSON\nFROM users;\n```\n\n### JSON Aggregation\n\n```sql\n-- Build JSON from rows\nSELECT json_agg(\n json_build_object(\n 'id', id,\n 'name', name,\n 'email', email\n )\n) as users\nFROM users WHERE status = 'active';\n```\n\n### JSONB Contains\n\n```sql\n-- Query JSONB fields\nSELECT * FROM products\nWHERE attributes @> '{\"color\": \"red\"}'; -- Contains\n\nSELECT * FROM products\nWHERE attributes ? 'color'; -- Has key\n\nSELECT * FROM products\nWHERE attributes ?| ARRAY['color', 'size']; -- Has any key\n```\n\n## Array Operations\n\n```sql\n-- Array contains\nSELECT * FROM posts WHERE 'javascript' = ANY(tags);\n\n-- Array overlap\nSELECT * FROM posts WHERE tags && ARRAY['javascript', 'typescript'];\n\n-- Array aggregation\nSELECT\n user_id,\n ARRAY_AGG(DISTINCT tag ORDER BY tag) as all_tags\nFROM post_tags\nGROUP BY user_id;\n\n-- Unnest array\nSELECT id, UNNEST(tags) as tag FROM posts;\n```\n\n## Upsert Patterns\n\n### ON CONFLICT\n\n```sql\n-- Insert or update\nINSERT INTO users (email, name, updated_at)\nVALUES ('[email protected]', 'John', NOW())\nON CONFLICT (email)\nDO UPDATE SET\n name = EXCLUDED.name,\n updated_at = NOW();\n\n-- Insert or ignore\nINSERT INTO users (email, name)\nVALUES ('[email protected]', 'John')\nON CONFLICT (email) DO NOTHING;\n```\n\n## Conditional Aggregation\n\n```sql\nSELECT\n DATE_TRUNC('month', created_at) as month,\n COUNT(*) as total_orders,\n COUNT(*) FILTER (WHERE status = 'completed') as completed,\n COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,\n SUM(total) FILTER (WHERE status = 'completed') as revenue\nFROM orders\nGROUP BY DATE_TRUNC('month', created_at)\nORDER BY month;\n```\n\n## Lateral Joins\n\n```sql\n-- Get top 3 posts per user\nSELECT u.name, p.title, p.created_at\nFROM users u\nCROSS JOIN LATERAL (\n SELECT title, created_at\n FROM posts\n WHERE user_id = u.id\n ORDER BY created_at DESC\n LIMIT 3\n) p;\n```\n\n## Full-Text Search\n\n```sql\n-- Create search index\nCREATE INDEX posts_search_idx ON posts\nUSING GIN (to_tsvector('english', title || ' ' || content));\n\n-- Search query\nSELECT title, ts_rank(\n to_tsvector('english', title || ' ' || content),\n plainto_tsquery('english', 'search terms')\n) as rank\nFROM posts\nWHERE to_tsvector('english', title || ' ' || content)\n @@ plainto_tsquery('english', 'search terms')\nORDER BY rank DESC;\n```\n\n## Date/Time Patterns\n\n```sql\n-- Generate date series\nSELECT generate_series(\n '2024-01-01'::date,\n '2024-12-31'::date,\n '1 day'::interval\n) as date;\n\n-- Group by time bucket\nSELECT\n DATE_TRUNC('hour', created_at) as hour,\n COUNT(*) as count\nFROM events\nGROUP BY DATE_TRUNC('hour', created_at);\n\n-- Time zone conversion\nSELECT\n created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' as eastern_time\nFROM orders;\n```\n\n## Performance Tips\n\n| Pattern | Optimization |\n|---------|--------------|\n| `LIMIT` on subqueries | Reduces rows early |\n| `EXISTS` vs `IN` | EXISTS often faster for correlated |\n| `DISTINCT ON` | Faster than GROUP BY for first per group |\n| Partial indexes | Index only relevant rows |\n| `EXPLAIN ANALYZE` | Always check query plan |\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5532,"content_sha256":"f41b26ac24450142510dce07feee5991e85fa0bb2bcb69ac4ef44798c47b8e3b"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Advanced Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"Advanced patterns for high-performance PostgreSQL database design, querying, and optimization.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Performance Optimization","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Effective Indexing","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- B-tree index for equality and range queries\nCREATE INDEX idx_users_email ON users(email);\n\n-- Partial index for filtered queries\nCREATE INDEX idx_active_users ON users(email) WHERE active = true;\n\n-- Composite index for multiple columns\nCREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);\n\n-- GiST index for full-text search\nCREATE INDEX idx_products_search ON products USING GiST(to_tsvector('english', name || ' ' || description));\n\n-- GIN index for JSON queries\nCREATE INDEX idx_metadata_gin ON events USING GIN(metadata);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Query Optimization","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Use EXPLAIN ANALYZE to understand query plans\nEXPLAIN ANALYZE\nSELECT * FROM orders WHERE user_id = 123;\n\n-- Avoid SELECT *\nSELECT id, name, email FROM users WHERE active = true;\n\n-- Use EXISTS instead of IN for large subqueries\nSELECT * FROM users u\nWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);\n\n-- Batch operations instead of loops\nINSERT INTO logs (event, created_at)\nSELECT unnest(ARRAY['login', 'logout', 'update']), NOW();","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Connection Pooling","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"import { Pool } from 'pg';\n\nconst pool = new Pool({\n max: 20,\n idleTimeoutMillis: 30000,\n connectionTimeoutMillis: 2000,\n});\n\n// Use pool for queries\nconst result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Advanced Query Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Window Functions","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Running totals\nSELECT \n date,\n amount,\n SUM(amount) OVER (ORDER BY date) as running_total\nFROM transactions;\n\n-- Row numbering with partitions\nSELECT \n user_id,\n purchase_date,\n amount,\n ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date DESC) as purchase_rank\nFROM purchases;\n\n-- Moving averages\nSELECT \n date,\n price,\n AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d\nFROM stock_prices;","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 CTE for hierarchical data\nWITH RECURSIVE org_chart AS (\n SELECT id, name, manager_id, 1 as level\n FROM employees\n WHERE manager_id IS NULL\n \n UNION ALL\n \n SELECT e.id, e.name, e.manager_id, oc.level + 1\n FROM employees e\n JOIN org_chart oc ON e.manager_id = oc.id\n)\nSELECT * FROM org_chart ORDER BY level, name;\n\n-- Multiple CTEs for complex queries\nWITH \n active_users AS (\n SELECT id FROM users WHERE active = true\n ),\n recent_orders AS (\n SELECT user_id, COUNT(*) as order_count\n FROM orders\n WHERE created_at > NOW() - INTERVAL '30 days'\n GROUP BY user_id\n )\nSELECT u.id, u.name, COALESCE(ro.order_count, 0) as recent_orders\nFROM active_users au\nJOIN users u ON au.id = u.id\nLEFT JOIN recent_orders ro ON u.id = ro.user_id;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"JSON Operations","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Query JSON columns\nSELECT data->>'name' as name, \n data->'address'->>'city' as city\nFROM customers\nWHERE data->>'status' = 'active';\n\n-- JSON aggregation\nSELECT user_id,\n json_agg(json_build_object('id', id, 'title', title)) as posts\nFROM posts\nGROUP BY user_id;\n\n-- JSON path queries\nSELECT * FROM events\nWHERE metadata @> '{\"type\": \"purchase\"}';","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Design Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Partitioning","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Range partitioning by date\nCREATE TABLE events (\n id BIGSERIAL,\n event_type TEXT,\n created_at TIMESTAMP NOT NULL\n) PARTITION BY RANGE (created_at);\n\nCREATE TABLE events_2026_01 PARTITION OF events\nFOR VALUES FROM ('2026-01-01') TO ('2026-02-01');\n\nCREATE TABLE events_2026_02 PARTITION OF events\nFOR VALUES FROM ('2026-02-01') TO ('2026-03-01');","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Materialized Views","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Create materialized view for expensive queries\nCREATE MATERIALIZED VIEW user_stats AS\nSELECT \n user_id,\n COUNT(DISTINCT order_id) as total_orders,\n SUM(amount) as total_spent,\n MAX(created_at) as last_order_date\nFROM orders\nGROUP BY user_id;\n\n-- Refresh periodically\nREFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;\n\n-- Create index on materialized view\nCREATE INDEX idx_user_stats_user_id ON user_stats(user_id);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Constraints and Validation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Check constraints\nALTER TABLE products\nADD CONSTRAINT price_positive CHECK (price > 0);\n\n-- Exclusion constraints\nCREATE TABLE bookings (\n room_id INT,\n during TSRANGE,\n EXCLUDE USING GIST (room_id WITH =, during WITH &&)\n);\n\n-- Domain types for reusable constraints\nCREATE DOMAIN email_address AS TEXT\nCHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Z|a-z]{2,}

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

);\n\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email email_address NOT NULL UNIQUE\n);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Transactions and Concurrency","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Transaction Isolation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Serializable transactions for critical operations\nBEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;\nSELECT * FROM accounts WHERE id = 1 FOR UPDATE;\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE id = 2;\nCOMMIT;\n\n-- Repeatable read for consistent snapshots\nBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;\nSELECT SUM(balance) FROM accounts;\n-- Consistent view maintained throughout transaction\nCOMMIT;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Row-Level Locking","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Pessimistic locking\nSELECT * FROM orders WHERE id = 123 FOR UPDATE;\n\n-- Shared lock for read-only access\nSELECT * FROM products WHERE id = 456 FOR SHARE;\n\n-- Skip locked rows\nSELECT * FROM queue WHERE processed = false\nFOR UPDATE SKIP LOCKED\nLIMIT 10;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Monitoring and Maintenance","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Performance","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Find slow queries\nSELECT query, mean_exec_time, calls\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 10;\n\n-- Check index usage\nSELECT schemaname, tablename, indexname, idx_scan\nFROM pg_stat_user_indexes\nORDER BY idx_scan ASC;\n\n-- Table bloat\nSELECT schemaname, tablename, \n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size\nFROM pg_tables\nWHERE schemaname = 'public'\nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Maintenance Tasks","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Vacuum and analyze\nVACUUM ANALYZE users;\n\n-- Reindex\nREINDEX TABLE users;\n\n-- Update statistics\nANALYZE users;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Always use parameterized queries","type":"text","marks":[{"type":"strong"}]},{"text":" to prevent SQL injection","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create indexes on foreign keys","type":"text","marks":[{"type":"strong"}]},{"text":" for join performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use connection pooling","type":"text","marks":[{"type":"strong"}]},{"text":" for better resource management","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor query performance","type":"text","marks":[{"type":"strong"}]},{"text":" with pg_stat_statements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular VACUUM and ANALYZE","type":"text","marks":[{"type":"strong"}]},{"text":" for statistics","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use appropriate transaction isolation levels","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid N+1 queries","type":"text","marks":[{"type":"strong"}]},{"text":" with proper joins or batching","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement retry logic","type":"text","marks":[{"type":"strong"}]},{"text":" for transaction conflicts","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Integration Points","type":"text"}]},{"type":"paragraph","content":[{"text":"Complements:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"backend-implementation-patterns","type":"text","marks":[{"type":"strong"}]},{"text":": For API data access","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"tdd-workflow","type":"text","marks":[{"type":"strong"}]},{"text":": For database testing","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"verification-loop","type":"text","marks":[{"type":"strong"}]},{"text":": For query performance checks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"security-implementation-guide","type":"text","marks":[{"type":"strong"}]},{"text":": For secure queries","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"postgres-advanced-patterns","author":"@skillopedia","source":{"stars":8,"repo_name":"a-i--skills","origin_url":"https://github.com/4444j99/a-i--skills/blob/HEAD/skills/development/postgres-advanced-patterns/SKILL.md","repo_owner":"4444j99","body_sha256":"9ec91c151e0a7acb5a6b9ef149133413300baa0b7ca82c65371921e3947b6e00","cluster_key":"c7ce3b2fae26a053cffd153a6627fc0411c7111f47a9ddc74ff47f25f3fa8b5e","clean_bundle":{"format":"clean-skill-bundle-v1","source":"4444j99/a-i--skills/skills/development/postgres-advanced-patterns/SKILL.md","attachments":[{"id":"82287486-9bb4-5e91-933b-168fdf06cebc","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/82287486-9bb4-5e91-933b-168fdf06cebc/attachment.md","path":"references/query-patterns.md","size":5532,"sha256":"f41b26ac24450142510dce07feee5991e85fa0bb2bcb69ac4ef44798c47b8e3b","contentType":"text/markdown; charset=utf-8"}],"bundle_sha256":"c177f36006ccfec4a9cce57f825407e507aa8d484d9b91bf8942903572c35b02","attachment_count":1,"text_attachments":1,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":5,"skill_md_path":"skills/development/postgres-advanced-patterns/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":4},"license":"MIT","version":"v1","category":"data-analytics","metadata":{"source":"affaan-m/everything-claude-code","category":"database","adapted-by":"ai-skills"},"triggers":["user-asks-about-postgres","user-asks-about-database","file-type:*.sql","context:database"],"import_tag":"clean-skills-v1","description":"Advanced PostgreSQL patterns for performance optimization, complex queries, indexing strategies, and database design","organ_affinity":["organ-iii","organ-vi"],"governance_phases":["build"]}},"renderedAt":1782979395835}

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