postgresql
Guides PostgreSQL development including table design, indexing, constraints, PL/pgSQL, JSONB, full-text search, window functions, CTEs, EXPLAIN ANALYZE tuning, backup/restore, replication, and extensions like pgvector. Use when the user needs to write or optimize PostgreSQL queries, design schemas, or manage PostgreSQL databases.
Best use case
postgresql is best used when you need a repeatable AI agent workflow instead of a one-off prompt. It is especially useful for teams working in multi. Guides PostgreSQL development including table design, indexing, constraints, PL/pgSQL, JSONB, full-text search, window functions, CTEs, EXPLAIN ANALYZE tuning, backup/restore, replication, and extensions like pgvector. Use when the user needs to write or optimize PostgreSQL queries, design schemas, or manage PostgreSQL databases.
Guides PostgreSQL development including table design, indexing, constraints, PL/pgSQL, JSONB, full-text search, window functions, CTEs, EXPLAIN ANALYZE tuning, backup/restore, replication, and extensions like pgvector. Use when the user needs to write or optimize PostgreSQL queries, design schemas, or manage PostgreSQL databases.
Users should expect a more consistent workflow output, faster repeated execution, and less time spent rewriting prompts from scratch.
Practical example
Example input
Use the "postgresql" skill to help with this workflow task. Context: Guides PostgreSQL development including table design, indexing, constraints, PL/pgSQL, JSONB, full-text search, window functions, CTEs, EXPLAIN ANALYZE tuning, backup/restore, replication, and extensions like pgvector. Use when the user needs to write or optimize PostgreSQL queries, design schemas, or manage PostgreSQL databases.
Example output
A structured workflow result with clearer steps, more consistent formatting, and an output that is easier to reuse in the next run.
When to use this skill
- Use this skill when you want a reusable workflow rather than writing the same prompt again and again.
When not to use this skill
- Do not use this when you only need a one-off answer and do not need a reusable workflow.
- Do not use it if you cannot install or maintain the related files, repository context, or supporting tools.
Installation
Claude Code / Cursor / Codex
Manual Installation
- Download SKILL.md from GitHub
- Place it in
.claude/skills/postgresql/SKILL.mdinside your project - Restart your AI agent — it will auto-discover the skill
How postgresql Compares
| Feature / Agent | postgresql | Standard Approach |
|---|---|---|
| Platform Support | Not specified | Limited / Varies |
| Context Awareness | High | Baseline |
| Installation Complexity | Unknown | N/A |
Frequently Asked Questions
What does this skill do?
Guides PostgreSQL development including table design, indexing, constraints, PL/pgSQL, JSONB, full-text search, window functions, CTEs, EXPLAIN ANALYZE tuning, backup/restore, replication, and extensions like pgvector. Use when the user needs to write or optimize PostgreSQL queries, design schemas, or manage PostgreSQL databases.
Where can I find the source code?
You can find the source code on GitHub using the link provided at the top of the page.
SKILL.md Source
## When to use this skill
Use this skill whenever the user wants to:
- Design tables, indexes, constraints, triggers, or PL/pgSQL functions
- Write or optimize SQL queries (joins, CTEs, window functions, aggregations)
- Use PostgreSQL-specific features (JSONB, full-text search, array types, pgvector)
- Manage users, roles, and permissions with psql
- Configure backup (pg_dump), replication, or performance tuning (EXPLAIN ANALYZE)
## How to use this skill
### Workflow
1. **Identify the task** - Schema design, query writing, optimization, or administration
2. **Write the SQL** - Use the patterns and examples below
3. **Analyze performance** - Run EXPLAIN ANALYZE on slow queries
4. **Apply best practices** - Index strategy, VACUUM, partitioning as needed
### Quick-Start Example: Table with Index and Query
```sql
-- Create a table with constraints
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','shipped','delivered')),
total NUMERIC(10,2) NOT NULL,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Create an index for common queries
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
-- Query with CTE and window function
WITH monthly_totals AS (
SELECT customer_id,
date_trunc('month', created_at) AS month,
SUM(total) AS month_total
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id, date_trunc('month', created_at)
)
SELECT customer_id, month, month_total,
LAG(month_total) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month
FROM monthly_totals;
```
### Performance Analysis
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
```
## Best Practices
1. **Index strategically** - Create indexes for WHERE/JOIN columns; use partial indexes for filtered queries
2. **Run VACUUM regularly** - Prevent table bloat; configure autovacuum thresholds for high-write tables
3. **Partition large tables** - Use range partitioning on timestamp columns for tables over 100M rows
4. **Use ROLE/GRANT** - Grant least privilege; never use superuser for application connections
5. **Backup and verify** - Use `pg_dump` or WAL archiving; test restore procedures regularly
## Keywords
postgresql, postgres, psql, SQL, JSONB, full-text search, CTE, window function, 关系型数据库, 索引, 复制, EXPLAIN ANALYZE, pg_dump, partitioningRelated Skills
vant-vue3
Provides structured guidance for Vant of Vue 3.0. Use when the user needs Vant with Vue 3, asks about mobile UI components such as Button, Cell, Form, Dialog, Toast, Popup, ConfigProvider, theme customization, project setup, or wants to implement mobile-first interfaces with vant or van- components.
layui-vue3
Provides comprehensive guidance for Layui Vue component library including components, layer dialogs, and utilities. Use when the user asks about Layui Vue, needs to use Layui components in Vue 3, or implement UI components.
element-plus-vue3
Provides comprehensive guidance for Element Plus Vue 3 component library including installation, components, themes, internationalization, and API reference. Use when the user asks about Element Plus for Vue 3, needs to build Vue 3 applications with Element Plus, or customize component styles.
bootstrap-vue3
Provides comprehensive guidance for Bootstrap Vue 3 component library including Bootstrap components, grid system, utilities, and Vue 3 integration. Use when the user asks about Bootstrap Vue 3, needs to use Bootstrap components in Vue 3, or implement responsive layouts.
vuex-vue2
Provides comprehensive guidance for Vuex 2.x state management in Vue 2 applications including state, mutations, actions, getters, modules, and plugins. Use when the user asks about Vuex for Vue 2, needs to manage state in Vue 2 applications, or implement Vuex patterns.
vue3
Guidance for Vue 3 using the official guide and API reference. Use when the user needs Vue 3 concepts, patterns, or API details to build components, apps, and tooling.
vue2
Provides comprehensive guidance for Vue 2.x development including Options API, components, directives, lifecycle hooks, computed properties, watchers, Vuex state management, and Vue Router. Use when the user asks about Vue 2, needs to create Vue 2 components, implement reactive data binding, handle component communication, or work with Vue 2 ecosystem tools.
vue-router
Provides comprehensive guidance for Vue Router including route configuration, navigation, dynamic routes, nested routes, route guards, programmatic navigation, and route meta. Use when the user asks about Vue Router, needs to set up routing, implement navigation guards, handle route parameters, or manage route transitions.
vue-router-v4
Provides comprehensive guidance for Vue Router v4 including route configuration, navigation, nested routes, route guards, and Vue 3 integration. Use when the user asks about Vue Router v4, needs to set up routing for Vue 3 applications, implement navigation guards, or work with Vue Router v4 features.
vue-router-v3
Guidance for Vue Router v3 using the official Installation, Guide, and API docs. Use when users need routing setup, navigation patterns, or API details for Vue 2 projects.
pinia
Provides comprehensive guidance for Pinia state management including stores, state, getters, actions, plugins, and TypeScript support. Use when the user asks about Pinia, needs to manage application state, create stores, implement state persistence, or migrate from Vuex.
vscode-project-init
Scaffold a new VS Code extension project using TypeScript via Yeoman generator (yo code), creating src/extension.ts entry point and package.json manifest. Use when the user wants to start a new VS Code extension project from scratch.