🎯

postgresql

🎯Skill
g1joshi/agent-skills2 Installs9Last Updated Feb 10, 2026

Installation

$npx skills add g1joshi/agent-skills --skill postgresql

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

Run it (Claude Code)/postgresql
Install 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.

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 TIMESTAMPTZ for Date Columns: Always store timestamps with timezone information (TIMESTAMPTZ), never bare TIMESTAMP.
  • 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 SERIAL for new tables: Use standard SQL identity columns (GENERATED ALWAYS AS IDENTITY) or UUIDv7.
  • 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

ErrorCauseSolution
FATAL: remaining connection slots are reserved for non-replication superuserMax connections exceeded by application instances.Deploy PgBouncer connection pooler and tune max_connections.
Seq Scan on large table in EXPLAIN ANALYZEPlanner 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