# Postgres Optimization

PostgreSQL optimization including indexes, query plans, partitioning, JSONB operations...

- Repository: https://github.com/rohitg00/awesome-claude-code-toolkit (2800 stars)
- Category: Engineering
- License: Apache-2.0
- Last updated: 2026-05-12
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add rohitg00/awesome-claude-code-toolkit --skill postgres-optimization -g -a claude-code -y`
Codex: `npx skills add rohitg00/awesome-claude-code-toolkit --skill postgres-optimization -g -a codex -y`
Cursor: `npx skills add rohitg00/awesome-claude-code-toolkit --skill postgres-optimization -g -a cursor -y`
Gemini CLI: `npx skills add rohitg00/awesome-claude-code-toolkit --skill postgres-optimization -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add rohitg00/awesome-claude-code-toolkit`

## Description

PostgreSQL optimization including indexes, query plans, partitioning, JSONB operations, and connection pooling

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# PostgreSQL Optimization

## Index Strategies

```sql
-- B-tree index for equality and range queries (default)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index (column order matters: equality columns first, range last)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- Covering index (avoids table lookup entirely)
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name, avatar_url);

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GiST index for full-text search
CREATE INDEX idx_articles_search ON articles USING GiST (
  to_tsvector('english', title || ' ' || body)
);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table (col);
```

## Reading Query Plans

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 20;
```

Key things to look for in the plan:
- `Seq Scan` on large tables indicates a missing index
- `Nested Loop` with high row estimates suggests missing join index
- `Sort` without `Index Scan` means the sort is happening in memory/disk
- `Buffers: shared hit` vs `shared read` shows cache efficiency

## Partitioning

```sql
CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type  TEXT NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

The rest: https://github.com/rohitg00/awesome-claude-code-toolkit/blob/HEAD/skills/postgres-optimization/SKILL.md