# Postgres

> Use when working with PostgreSQL specifically. Covers indexing, MVCC and vacuum, connection pooling, partitioning, JSONB, replication, and the operational realities that separate Postgres from generic SQL.

- **Type:** Skill
- **Install:** `agentstack add skill-nimadorostkar-claude-skills-collection-postgres`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [nimadorostkar](https://agentstack.voostack.com/s/nimadorostkar)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [nimadorostkar](https://github.com/nimadorostkar)
- **Source:** https://github.com/nimadorostkar/Claude-Skills-collection/tree/main/skills/data/postgres
- **Website:** https://github.com/nimadorostkar/Claude-Skills-collection

## Install

```sh
agentstack add skill-nimadorostkar-claude-skills-collection-postgres
```

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

## About

# PostgreSQL

## Purpose

Operate PostgreSQL well: understand what MVCC costs you, why the table is bloated, why the connection count matters more than you think, and how to change a large table without locking it.

## When to Use

- Designing schemas or indexes for Postgres.
- Diagnosing bloat, slow queries, or lock contention.
- Configuring connection pooling and replication.
- Running a migration on a large table without downtime.

## Capabilities

- Index types: B-tree, GIN, GiST, BRIN, partial, expression, covering.
- MVCC, dead tuples, autovacuum tuning, and transaction ID wraparound.
- Connection pooling with PgBouncer and the pooling modes.
- Partitioning: declarative range and list partitions.
- JSONB indexing and query patterns.
- Streaming replication, replicas, and replication lag.

## Inputs

- The schema, the table sizes, and the query patterns.
- `pg_stat_statements` output for the slow-query question.
- Server configuration and Postgres version.

## Outputs

- Indexes that match the access patterns, with no unused ones.
- Autovacuum settings appropriate to the write volume.
- Migrations that acquire only brief locks.

## Workflow

1. **Find the real slow queries** — `pg_stat_statements` ordered by total time, not by mean. A 30ms query executed a million times costs more than a 4-second query run once.
2. **Index for the access pattern** — Equality columns first in a composite index, then the range or sort column. Add partial indexes for the filters that dominate.
3. **Watch the dead tuples** — Every `UPDATE` writes a new row version and leaves the old one dead. A hot table with default autovacuum settings will bloat and slow down.
4. **Pool the connections** — Each Postgres connection is a process with real memory cost. Beyond a few hundred, performance degrades. PgBouncer in transaction mode is the standard answer.
5. **Migrate without locking** — `CREATE INDEX CONCURRENTLY`. Add columns nullable, backfill in batches, then set defaults and constraints with `NOT VALID` followed by `VALIDATE`.
6. **Set a lock timeout** — Before any DDL: `SET lock_timeout = '3s'`. A migration that waits behind a long transaction will queue every subsequent query behind itself.

## Best Practices

- `ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ` is safe on Postgres 11+ (no table rewrite). Adding a `CHECK` constraint or a foreign key still requires a scan — use `NOT VALID`, then `VALIDATE CONSTRAINT` separately.
- An unused index is a write-amplification tax paid on every insert and update. `pg_stat_user_indexes` shows which have never been scanned. Drop them.
- Autovacuum defaults are tuned for a small database. On a high-write table, lower `autovacuum_vacuum_scale_factor` for that table specifically.
- `idle in transaction` connections hold locks and block vacuum indefinitely. Set `idle_in_transaction_session_timeout`.
- PgBouncer in transaction mode breaks prepared statements, `LISTEN/NOTIFY`, and session-level `SET`. Know this before adopting it, not after.
- Replicas serve read traffic but lag. A read-after-write on a replica will see stale data — route the read to the primary or accept it.

## Examples

**A migration on a large table that takes no meaningful lock:**

```sql
-- Adding a NOT NULL column with a default: safe and instant on PG 11+.
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'USD';

-- A foreign key normally takes a lock and scans the whole table. Split it:
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;                              -- instant: only new rows are checked

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
                                          -- scans, but takes only a SHARE UPDATE
                                          -- EXCLUSIVE lock: writes continue.

-- An index without blocking writes:
SET lock_timeout = '3s';                  -- do not queue behind a long transaction
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  WHERE deleted_at IS NULL;
```

**Finding the queries and the indexes that matter:**

```sql
-- The queries that actually consume the database, by total time.
SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time::numeric, 0) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows / GREATEST(calls, 1) AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Indexes that have never been used: pure write overhead.
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (
  SELECT conindid FROM pg_constraint WHERE contype IN ('p','u')
)
ORDER BY pg_relation_size(indexrelid) DESC;
```

## Notes

- `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block, and it can fail leaving an invalid index behind. Check `pg_index.indisvalid` afterwards and drop any invalid index before retrying.
- Transaction ID wraparound is the failure mode that takes a Postgres cluster fully offline. It only happens when autovacuum has been failing for a long time and being ignored — monitor `age(datfrozenxid)`.
- JSONB is excellent for genuinely schemaless data and a poor substitute for columns. A JSONB field that every query filters on should be a column with an index.

## Source & license

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

- **Author:** [nimadorostkar](https://github.com/nimadorostkar)
- **Source:** [nimadorostkar/Claude-Skills-collection](https://github.com/nimadorostkar/Claude-Skills-collection)
- **License:** MIT
- **Homepage:** https://github.com/nimadorostkar/Claude-Skills-collection

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-nimadorostkar-claude-skills-collection-postgres
- Seller: https://agentstack.voostack.com/s/nimadorostkar
- 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%.