postgresql
π―SkillInstallation
npx skills add g1joshi/agent-skills --skill postgresqlRun it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).
Uses skills.sh Β· Node.js 22.20+
After installing
Ask for related work and the agent uses it on its own, or call it yourself.
/postgresqlInstall by hand
Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.
Open the skill folder β- This project
.claude/skills/postgresql/ - All projects
~/.claude/skills/postgresql/
These paths are for Claude Code. For other tools, the command above puts it in the right place.
SKILL.md
View on GitHub βExpert PostgreSQL assistance covering advanced SQL, JSONB, indexing (B-Tree, GIN, BRIN), query optimization with EXPLAIN ANALYZE, and replication. Use when designing robust relational schemas or tuning performance.
PostgreSQL
PostgreSQL (Postgres) is a powerful, open-source object-relational database system. It is known for its reliability, feature robustness (JSONB, GIS, Full Text Search), and performance.
When to Use
- The Default Advanced Relational Database: The standard, most versatile open-source relational database for modern software engineering.
- Semi-Structured Document Storage (JSONB): Storing, indexing, and querying JSON documents with GIN indexes and JSONPath expressions.
- AI & Vector Embeddings (pgvector): Storing high-dimensional vector embeddings and executing cosine/L2 semantic similarity search.
- Geospatial Applications (PostGIS): Analyzing spatial geometries, geographic boundaries, and GPS coordinates with industry-standard PostGIS.
Quick Start
-- JSONB column usage
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO events (data) VALUES ('{"type": "login", "user": "alice"}');
-- Query JSON directly (indexing supported)
SELECT * FROM events WHERE data->>'user' = 'alice';
Core Concepts
Advanced JSONB & Generalized Inverted Indexes (GIN)
JSONB stores binary decomposed JSON with full indexing support:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
profile JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Create GIN Index over entire JSONB structure
CREATE INDEX idx_users_profile ON users USING GIN (profile);
-- Fast sub-millisecond containment search
SELECT * FROM users
WHERE profile @> '{"role": "admin", "settings": {"notifications": true}}';
pgvector Semantic Similarity Search
Integrates AI vector embeddings directly alongside relational tables:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_embeddings (
id BIGSERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536) -- OpenAI embedding dimension
);
-- Create HNSW index for sub-5ms approximate nearest neighbor search
CREATE INDEX idx_embeddings_hnsw ON document_embeddings
USING hnsw (embedding vector_cosine_ops);
-- Find top 5 most semantically similar documents
SELECT id, content, 1 - (embedding <=> '[0.012, -0.045, ...]') AS similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 5;
Multi-Version Concurrency Control (MVCC) & VACUUM
PostgreSQL retains old row versions on update/delete; the autovacuum daemon reclaims dead tuples:
-- Inspect bloat and dead tuples
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'users';
Common Patterns
JSONB Ingestion with GIN Indexing for Fast Document Queries
Problem: Relational normalization adds too much overhead for variable schema payloads, but standard text search is slow.
Solution:
Use PostgreSQL JSONB data type with GIN index:
CREATE TABLE audit_events (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
metadata JSONB NOT NULL
);
-- GIN index supports fast top-level and nested key existence queries
CREATE INDEX idx_audit_metadata ON audit_events USING GIN (metadata jsonb_path_ops);
-- Fast indexed query matching nested key-value
SELECT * FROM audit_events
WHERE metadata @> '{"action": "LOGIN", "status": "FAILED"}';
Best Practices
Do:
- Use Connection Poolers (PgBouncer): Prevent process-per-connection exhaustion by running PgBouncer in transaction pooling mode.
- Use
TIMESTAMPTZfor Date Columns: Always store timestamps with timezone information (TIMESTAMPTZ), never bareTIMESTAMP. - Create Partial and Expression Indexes: Index only active rows (
WHERE deleted_at IS NULL) to keep index sizes compact. - Run
EXPLAIN (ANALYZE, BUFFERS): Analyze exact buffer hits and query execution plans before deploying new queries.
Don't:
- Use
SERIALfor new tables: Use standard SQL identity columns (GENERATED ALWAYS AS IDENTITY) orUUIDv7. - Disable Autovacuum: Disabling autovacuum causes catastrophic table bloat and transaction ID wraparound emergencies.
- Write long-running transactions: Long open transactions block vacuuming and cause severe table bloat.
Troubleshooting
| Error | Cause | Solution |
|---|---|---|
FATAL: remaining connection slots are reserved for non-replication superuser | Max connections exceeded by application instances. | Deploy PgBouncer connection pooler and tune max_connections. |
Seq Scan on large table in EXPLAIN ANALYZE | Planner chose sequential scan due to stale statistics or missing index. | Run ANALYZE tablename; or create index matching query predicates. |
deadlock detected (ERROR: 40P01) | Concurrent transactions acquired locks in opposing order. | Standardize lock acquisition order across transactions and keep transactions small. |
References
More from this repository10
A template skill providing a starter framework for creating new agent skills with structured sections for triggers, quick start examples, and capability descriptions.
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill