mcpField
P
postgresql

postgresql

github.com/partme-aiData & databases71885 installsUpdated 4w ago
Repository

About postgresql

postgresql is a skill guide for PostgreSQL developers and administrators. It covers SQL, database design, performance analysis, and operational tasks, with references to PostgreSQL-specific features and practices.

Key features

  1. 01Table and index design
  2. 02SQL query writing and optimization
  3. 03JSONB, full-text search, and pgvector
  4. 04EXPLAIN ANALYZE performance analysis
  5. 05User roles, backups, and replication

Use cases

  1. 01Design tables, constraints, triggers, and functions
  2. 02Optimize joins, CTEs, and aggregate queries
  3. 03Analyze slow queries and tune indexes
  4. 04Configure roles, permissions, and backups

Install

Copy the command and run it in a terminal.

$npx skills add https://github.com/partme-ai/full-stack-skills --skill postgresql

After installing, type/postgresqlin Claude Code to confirm it loaded.

Bundled filesLICENSE.txt

Details

From the project README

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
-- 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

  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, partitioning

Comments

Markdown supported