From postgres-pro
Advanced PostgreSQL: indexing, CTEs, window functions, partitioning, full-text search. Use when optimizing or reviewing PostgreSQL schemas and queries.
How this skill is triggered — by the user, by Claude, or both
Slash command
/postgres-pro:postgres-proThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Use PostgreSQL at a senior DBA level. Covers advanced indexing strategies, query optimization, window functions, CTEs, full-text search, partitioning, and migration safety.
Use PostgreSQL at a senior DBA level. Covers advanced indexing strategies, query optimization, window functions, CTEs, full-text search, partitioning, and migration safety.
Use this when:
Use this ESPECIALLY when:
Don't skip when:
-- B-tree (default) — equality + range queries
CREATE INDEX idx_projects_owner_id ON projects(owner_id);
-- Composite B-tree — multi-column filters
CREATE INDEX idx_projects_owner_status_created
ON projects(owner_id, status, created_at DESC);
-- Partial index — filtered subset
CREATE INDEX idx_active_projects
ON projects(owner_id, created_at DESC)
WHERE status = 'active';
-- Covering index — avoid heap lookups
CREATE INDEX idx_projects_list
ON projects(owner_id, status, created_at DESC)
INCLUDE (name, description);
-- GIN — full-text search, JSONB, arrays
CREATE INDEX idx_projects_search
ON projects USING GIN(to_tsvector('english', name || ' ' || description));
-- BRIN — large, sequential tables (logs, time-series)
CREATE INDEX idx_audit_logs_created
ON audit_logs USING BRIN(created_at)
WITH (pages_per_range = 32);
-- Hash — equality only (rarely needed)
CREATE INDEX idx_users_email_hash
ON users USING HASH(email);
-- Check query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM projects WHERE owner_id = 'uuid-here';
-- Common anti-patterns:
-- ❌ Sequential scan on large table
-- ✅ Add appropriate index
-- ❌ N+1 queries in app code
-- ✅ JOIN + prefetch
-- ❌ SELECT * when only id needed
-- ✅ SELECT specific columns
-- ❌ COUNT(*) on every page load
-- ✅ Approximate count or cached count
-- ❌ Filtering on function(column)
-- ✅ Index on expression or generated column
-- Recursive CTE — hierarchy traversal
WITH RECURSIVE project_tree AS (
-- Anchor: top-level tasks
SELECT id, parent_id, title, 1 AS depth
FROM tasks
WHERE project_id = 'uuid' AND parent_id IS NULL
UNION ALL
-- Recursive: child tasks
SELECT t.id, t.parent_id, t.title, pt.depth + 1
FROM tasks t
JOIN project_tree pt ON t.parent_id = pt.id
)
SELECT * FROM project_tree ORDER BY depth, title;
-- Multi-step CTE — clear data pipeline
WITH
project_stats AS (
SELECT project_id, COUNT(*) AS task_count
FROM tasks GROUP BY project_id
),
top_projects AS (
SELECT p.*, ps.task_count
FROM projects p
JOIN project_stats ps ON p.id = ps.project_id
ORDER BY ps.task_count DESC
LIMIT 10
)
SELECT * FROM top_projects;
-- Running total
SELECT
created_at,
amount,
SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM transactions
WHERE project_id = 'uuid';
-- Rank within group
SELECT
id, name, status, created_at,
ROW_NUMBER() OVER (
PARTITION BY status
ORDER BY created_at DESC
) AS status_rank
FROM projects
WHERE owner_id = 'uuid';
-- Moving average
SELECT
created_at,
value,
AVG(value) OVER (
ORDER BY created_at
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM metrics
WHERE project_id = 'uuid';
-- Add tsvector column
ALTER TABLE projects ADD COLUMN search_vector tsvector;
-- Populate via trigger
CREATE FUNCTION projects_search_update() RETURNS trigger AS $$
BEGIN
NEW.search_vector := to_tsvector('english',
COALESCE(NEW.name, '') || ' ' || COALESCE(NEW.description, '')
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_projects_search
BEFORE INSERT OR UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION projects_search_update();
-- GIN index on tsvector
CREATE INDEX idx_projects_search ON projects USING GIN(search_vector);
-- Query
SELECT id, name,
ts_rank(search_vector, query) AS rank
FROM projects, plainto_tsquery('english', 'project management') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- Range partitioning by date
CREATE TABLE audit_logs (
id UUID NOT NULL,
action TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE audit_logs_2025_q1
PARTITION OF audit_logs
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE audit_logs_2025_q2
PARTITION OF audit_logs
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
-- Partition pruning happens automatically
EXPLAIN SELECT * FROM audit_logs
WHERE created_at BETWEEN '2025-02-01' AND '2025-02-28';
-- Only scans audit_logs_2025_q1
-- ✅ Additive: new column with default (no downtime)
ALTER TABLE projects ADD COLUMN priority INTEGER DEFAULT 0;
-- ✅ Expand-contract: rename column safely
-- Phase 1: Add new column + sync trigger
ALTER TABLE projects ADD COLUMN display_name TEXT;
UPDATE projects SET display_name = name WHERE display_name IS NULL;
CREATE FUNCTION sync_name() RETURNS trigger AS $$ ... $$;
-- Phase 2: Drop old column (separate deploy, after all code updated)
ALTER TABLE projects DROP COLUMN name;
-- ✅ NOT VALID: add constraint without table lock
ALTER TABLE projects ADD CONSTRAINT projects_status_check
CHECK (status IN ('active', 'archived'))
NOT VALID;
-- Later: VALIDATE CONSTRAINT (no lock)
ALTER TABLE projects VALIDATE CONSTRAINT projects_status_check;
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Implements vector databases with Pinecone, Weaviate, Qdrant, Milvus, pgvector for semantic search, RAG, recommendations, and similarity systems. Optimizes embeddings, indexing, and hybrid search.
npx claudepluginhub haj1t/senior-dev-squad-skills --plugin postgres-pro