skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
partme-ai/full-stack-skills85 installs

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 postgresql
view source ↗

Is 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

  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

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>