Engineering Advanced PostgreSQLpgvectorDatabase

Postgres Pro

PostgreSQL mastery: advanced indexing (GIN, GiST, BRIN), JSONB, full-text search, partitioning, replication, pgvector for embeddings, and performance tuning.

Expert-level PostgreSQL skill covering the techniques that separate good schemas from bulletproof ones. Knows when to use GIN vs GiST vs BRIN indexes, how to tune `work_mem` and `shared_buffers` for your workload, how to design partition strategies that don't break queries, and how to extend Postgres with pgvector for AI-native applications.

Added Mar 20, 2026

$npx skills add johnefemer/skillfish --skill postgres-pro

Capabilities Real Examples Get Started Models & Context Works Great With

What This Skill Can Do

Concrete capabilities you get when you install this skill.

Design optimal indexes for complex query patterns including GIN, GiST, and partial indexes

Tune PostgreSQL configuration for OLTP, OLAP, and mixed workloads

Implement table partitioning with range, list, and hash strategies

Build vector similarity search with pgvector and HNSW indexing

Diagnose slow queries using EXPLAIN ANALYZE, pg_stat_statements, and auto_explain

Set up logical replication, read replicas, and failover with Patroni or pg_auto_failover

Real Conversations

See how the Postgres Pro skill responds to actual requests. These are representative examples of what your AI agent will produce.

You

This query takes 4 seconds on 10 million rows. I need it under 100ms:

SELECT * FROM articles WHERE content ILIKE '%machine learning%' ORDER BY created_at DESC LIMIT 20;

Agent

ILIKE '%...%' with a leading wildcard disables all index scans — it's a full sequential scan every time. Two-step fix:

Step 1 — Add a tsvector column and GIN index

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) STORED;

CREATE INDEX articles_search_gin ON articles USING GIN(search_vector);

Step 2 — Rewrite the query

SELECT *, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'machine & learning') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Performance: GIN index on tsvector reduces this from 4s sequential scan to ~5-15ms. The GENERATED ALWAYS AS ... STORED keeps the vector current automatically.

If you also filter by date range, add a composite index:

CREATE INDEX articles_date_gin ON articles USING BRIN(created_at);

BRIN on append-heavy time-series columns costs almost no storage and prunes partitions efficiently.

Tradeoff: tsvector doesn't support prefix matching or fuzzy search. For that, use pg_trgm with a GIN trigram index instead.

You

I set up pgvector with an HNSW index for semantic search, but EXPLAIN shows a sequential scan on every query. Index exists and has 500k rows.

Agent

The query planner ignores HNSW when it thinks a seqscan is cheaper — usually because of a missing cast, wrong operator class, or a filter that prevents index use. Let's diagnose:

Check 1 — Correct operator class

-- Must use <=> (cosine), <-> (L2), or <#> (inner product)
SELECT * FROM items ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector LIMIT 10;
-- NOT: WHERE embedding = ... or ORDER BY ABS(embedding - ...)

Check 2 — Verify index was created with the right op class

\d+ items  -- look for: USING hnsw (embedding vector_cosine_ops)

If you see vector_l2_ops but query uses <=>, the index won't match.

Check 3 — enable_seqscan test

SET enable_seqscan = off;
EXPLAIN SELECT * FROM items ORDER BY embedding <=> '[...]'::vector LIMIT 10;

If the planner still avoids the index with seqscan off, you have an operator class mismatch.

Fix — recreate with correct op class

DROP INDEX items_embedding_hnsw;
CREATE INDEX items_embedding_hnsw ON items
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Also check: Is there a WHERE clause filtering rows before the ORDER BY? pgvector HNSW doesn't support pre-filtering — use a CTE or post-filter instead.

Get Started

How to install and use this skill in your preferred environment.

Skills are designed for AI coding agents (Claude Code, Cursor, Windsurf) and IDE-based workflows where the agent can read files, run scripts, and act on your codebase.

Models & Context

Which AI models and context windows work best with this skill.

Recommended Models

Works well with Claude Sonnet, GPT-4o, or Gemini 2.5 Pro. Deep PostgreSQL internals benefit from larger context to hold schema, query plans, and config together.

Context Window

SKILL.md is ~9KB. Complex tuning sessions with EXPLAIN output, schema, and config benefit from 64K+ context.

Pro tips for best results

1

Be specific

Include numbers — users, budget, RPS — so the skill can size the architecture.

2

Share constraints

Compliance needs, team size, and existing stack all improve the output.

3

Iterate

Start with a high-level design, then ask follow-ups for IaC, cost analysis, or security review.

4

Combine skills

Pair with companion skills below for end-to-end coverage.

Works Great With

These skills complement Postgres Pro for end-to-end coverage. Install them together for better results.

Database Designer

Schema analysis, ERD generation, index optimization, and migrations.

DatabaseSchema

Senior Backend

REST APIs, database optimization, authentication, and microservices.

BackendAPIs

Senior Data Engineer

Data pipelines, ETL/ELT systems, Spark, Airflow, dbt, and Kafka.

DataPipelines

Performance Profiler

Node/Python/Go profiling, bundle analysis, and load testing.

PerformanceProfiling

$ skillfish add johnefemer/skillfish --all # install all skills at once

Ready to try Postgres Pro?

Install the skill and start getting expert-level guidance in your workflow — any agent, any IDE.