Postgresql

Skill jgamaraalv/ts-dev-kit/skills/postgresql

PostgreSQL 16+ reference for writing queries, designing schemas, managing indexes, and optimizing performance. Use when: (1) writing SQL queries (SELECT, INSERT, UPDATE, DELETE, CTEs, window functions, subqueries), (2) designing or altering table schemas (CREATE TABLE, constraints, sequences, data types), (3) choosing or creating indexes (B-tree, GIN, GiST, BRIN, partial, expression), (4) managing transactions and isolation levels (BEGIN/COMMIT, savepoints, MVCC), (5) working with JSONB data and jsonpath, (6) analyzing query performance with EXPLAIN ANALYZE, or (7) using the psql CLI.From its SKILL.md

Install

npx -y skills add jgamaraalv/ts-dev-kit --skill postgresql

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

15 stars15 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

3.5 KB, as published. Nobody here has run it

PostgreSQL 16+ Reference

Version: 16+. All syntax is standard; most features apply to PostgreSQL 13+.

<quick_reference>

Quick patterns

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

<rules>

Key non-obvious facts

Every statement runs in a transaction. Without 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.
Null values are stored in indexes by B-tree (unlike some other databases). IS NULL can use an index.
SERIAL/BIGSERIAL are shorthand for sequence + default; prefer GENERATED ALWAYS AS IDENTITY (SQL standard).
Default isolation level is Read Committed. SERIALIZABLE prevents all anomalies but may abort transactions.

</rules> <references>

Reference files

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

</references>