Full-stack-skills 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.

install

source · Clone the upstream repo

git clone https://github.com/partme-ai/full-stack-skills

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/partme-ai/full-stack-skills "$T" && mkdir -p ~/.claude/skills && cp -r "$T/skills/database-skills/postgresql" ~/.claude/skills/partme-ai-full-stack-skills-postgresql && rm -rf "$T"

manifest: skills/database-skills/postgresql/SKILL.md

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_dump
```
or WAL archiving; test restore procedures regularly

Keywords

postgresql, postgres, psql, SQL, JSONB, full-text search, CTE, window function, 关系型数据库, 索引, 复制, EXPLAIN ANALYZE, pg_dump, partitioning