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.
How do I install this agent skill?
npx skills add https://github.com/partme-ai/full-stack-skills --skill postgresqlIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is a placeholder for PostgreSQL guidance and contains no executable code or dangerous instructions.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
- Runlayerwarn
2/2 files flagged
What does this agent skill do?
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
- Identify the task - Schema design, query writing, optimization, or administration
- Write the SQL - Use the patterns and examples below
- Analyze performance - Run EXPLAIN ANALYZE on slow queries
- Apply best practices - Index strategy, VACUUM, partitioning as needed
Quick-Start Example: Table with Index and Query
-- 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
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
Best Practices
- Index strategically - Create indexes for WHERE/JOIN columns; use partial indexes for filtered queries
- Run VACUUM regularly - Prevent table bloat; configure autovacuum thresholds for high-write tables
- Partition large tables - Use range partitioning on timestamp columns for tables over 100M rows
- Use ROLE/GRANT - Grant least privilege; never use superuser for application connections
- Backup and verify - Use
pg_dumpor WAL archiving; test restore procedures regularly
Keywords
postgresql, postgres, psql, SQL, JSONB, full-text search, CTE, window function, 关系型数据库, 索引, 复制, EXPLAIN ANALYZE, pg_dump, partitioning
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/partme-ai/full-stack-skills/postgresql">View postgresql on skillZs</a>