# 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) managin…

- **Type:** Skill
- **Install:** `agentstack add skill-jgamaraalv-ts-dev-kit-postgresql`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [jgamaraalv](https://agentstack.voostack.com/s/jgamaraalv)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [jgamaraalv](https://github.com/jgamaraalv)
- **Source:** https://github.com/jgamaraalv/ts-dev-kit/tree/main/skills/postgresql

## Install

```sh
agentstack add skill-jgamaraalv-ts-dev-kit-postgresql
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# PostgreSQL 16+ Reference

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

## Quick patterns

```sql
-- 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 = ;
```

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

## 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](references/queries.md)           | Writing or debugging any query         |
| CREATE TABLE, ALTER TABLE, constraints | [references/ddl-schema.md](references/ddl-schema.md)     | Designing or modifying schemas         |
| Index types, creation, strategy        | [references/indexes.md](references/indexes.md)           | Adding indexes or fixing slow queries  |
| Transactions, savepoints, isolation    | [references/transactions.md](references/transactions.md) | Concurrency, locking, isolation issues |
| JSONB operators, GIN, jsonpath         | [references/jsonb.md](references/jsonb.md)               | Working with JSON/JSONB columns        |
| EXPLAIN output, VACUUM, stats          | [references/performance.md](references/performance.md)   | Query tuning or performance analysis   |
| psql meta-commands                     | [references/psql-cli.md](references/psql-cli.md)         | Working interactively in psql          |

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [jgamaraalv](https://github.com/jgamaraalv)
- **Source:** [jgamaraalv/ts-dev-kit](https://github.com/jgamaraalv/ts-dev-kit)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-jgamaraalv-ts-dev-kit-postgresql
- Seller: https://agentstack.voostack.com/s/jgamaraalv
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.