From ts-dev-kit
PostgreSQL 16+ reference for writing SQL queries (SELECT, CTEs, windows), designing schemas, creating indexes (B-tree, GIN), managing transactions, using JSONB, analyzing with EXPLAIN ANALYZE, and psql CLI.
How this skill is triggered — by the user, by Claude, or both
Slash command
/ts-dev-kit:postgresqlThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Version: **16+**. All syntax is standard; most features apply to PostgreSQL 13+.
Version: 16+. All syntax is standard; most features apply to PostgreSQL 13+.
<quick_reference>
-- Check running queries
SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle';
-- Explain a slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- List table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class
WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;
-- Kill a blocking query
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <pid>;
</quick_reference>
BEGIN, each statement auto-commits.jsonb stores parsed binary (faster queries); json stores raw text (exact input preserved). Prefer jsonb.LIKE 'foo%' can use B-tree; LIKE '%foo' cannot — use pg_trgm GIN for suffix search.CREATE INDEX CONCURRENTLY avoids table lock but cannot run inside a transaction block.EXPLAIN without ANALYZE shows the planner's estimate. Always use EXPLAIN (ANALYZE, BUFFERS) for real data.IS NULL can use an index.SERIAL/BIGSERIAL are shorthand for sequence + default; prefer GENERATED ALWAYS AS IDENTITY (SQL standard).SERIALIZABLE prevents all anomalies but may abort transactions.Load the relevant file when working on a specific topic:
| Topic | File | When to read |
|---|---|---|
| SELECT, JOINs, CTEs, window functions | references/queries.md | Writing or debugging any query |
| CREATE TABLE, ALTER TABLE, constraints | references/ddl-schema.md | Designing or modifying schemas |
| Index types, creation, strategy | references/indexes.md | Adding indexes or fixing slow queries |
| Transactions, savepoints, isolation | references/transactions.md | Concurrency, locking, isolation issues |
| JSONB operators, GIN, jsonpath | references/jsonb.md | Working with JSON/JSONB columns |
| EXPLAIN output, VACUUM, stats | references/performance.md | Query tuning or performance analysis |
| psql meta-commands | references/psql-cli.md | Working interactively in psql |
npx claudepluginhub jgamaraalv/ts-dev-kit --plugin ts-dev-kitGuides PostgreSQL operations: schema design, index selection (B-tree/GIN/GiST/BRIN), query tuning (EXPLAIN ANALYZE), backups (pg_dump/pg_basebackup), replication, vacuum tuning, monitoring (pg_stat_statements), RLS, extensions (PostGIS/timescaledb).
Reference for PostgreSQL query optimization, schema design, indexing strategies, and Row Level Security. Based on Supabase best practices.
Optimizes PostgreSQL queries, configures replication, uses extensions, and tunes VACUUM. Use for EXPLAIN analysis, JSONB operations, and performance monitoring.