# Postgres Patterns

> When to activate: PostgreSQL, JSONB, pg, psql, indexes, CTEs, window functions, partitioning, VACUUM, pg_stat, postgres

- Skill: `mattakushi432/postgres-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/postgres-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/postgres-patterns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/postgres-patterns

---

# PostgreSQL Patterns

## JSONB Queries

```sql
-- JSONB containment and path operators
SELECT * FROM events WHERE payload @> '{"type": "click"}';
SELECT payload->>'user_id' AS user_id FROM events;
SELECT payload#>>'{meta,source}' AS source FROM events;

-- JSONB indexes
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);
CREATE INDEX idx_events_type ON events ((payload->>'type'));

-- JSONB update
UPDATE events SET payload = payload || '{"processed": true}' WHERE id = 1;
UPDATE events SET payload = payload - 'temp_field';
```

## Index Types

```sql
-- B-tree (default) — equality & range
CREATE INDEX ON orders (created_at DESC);

-- GIN — JSONB, arrays, full-text
CREATE INDEX ON articles USING GIN (to_tsvector('english', body));
CREATE INDEX ON products USING GIN (tags);

-- GiST — geometric, range types, full-text
CREATE INDEX ON locations USING GIST (coordinates);
CREATE INDEX ON reservations USING GIST (during); -- tsrange

-- BRIN — large sequential tables (logs, time-series)
CREATE INDEX ON logs USING BRIN (created_at) WITH (pages_per_range = 128);

-- Partial index
CREATE INDEX ON orders (user_id) WHERE status = 'pending';

-- Covering index (INCLUDE)
CREATE INDEX ON orders (user_id) INCLUDE (total, status);
```

## CTEs and Window Functions

```sql
-- Recursive CTE (org hierarchy)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth;

-- Window functions
SELECT
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount
FROM transactions;

-- DISTINCT ON (keep first per group)
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM orders ORDER BY user_id, created_at DESC;
```

## Partitioning

```sql
-- Range partitioning by month
CREATE TABLE events (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Auto-create partitions with pg_partman
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');
```

## VACUUM and Maintenance

```sql
-- Check bloat
SELECT relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

-- Force vacuum analyze
VACUUM (ANALYZE, VERBOSE) orders;

-- Check autovacuum settings per table
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
```

## pg_stat Queries

```sql
-- Slow queries
SELECT query, calls, total_exec_time/calls AS avg_ms,
  rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- Index usage
SELECT relname, indexrelname, idx_scan, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

-- Table sizes
SELECT relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- Active connections
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Blocking queries
SELECT pid, query, wait_event_type, wait_event
FROM pg_stat_activity WHERE wait_event IS NOT NULL;
```

## Performance Tips

- Use `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` for detailed plans
- Set `work_mem` per session for sort-heavy queries: `SET work_mem = '256MB'`
- Use `connection_limit` on roles, `PgBouncer` for pooling
- `shared_buffers` = 25% RAM; `effective_cache_size` = 75% RAM
- Prefer `COPY` over `INSERT` for bulk loads
- Use `UNLOGGED TABLE` for ephemeral staging data