Skip to content
Skills Library
Data & analytics · Building & coding

postgres-ops

/postgres-ops

PostgreSQL operations, optimization, and administration. Use for: schema design, index selection, query tuning with EXPLAIN ANALYZE, postgresql.conf configuration, backup and restore (pg_dump, pg_basebackup, WAL, PITR), vacuum and autovacuum tuning, connection pooling (pgBouncer, pgPool), replication (streaming, logical), partitioning, monitoring (pg_stat_statements, pg_stat_activity), JSONB operations, full-text search (tsvector, tsquery), row-level security (RLS), extensions (PostGIS, pg_trgm, timescaledb), GiST/GIN/BRIN indexes, materialized views, foreign data wrappers, LISTEN/NOTIFY.

Repository
claude-mods ↗
Licence
MIT
Last repository change
31.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
9
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 31.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

PostgreSQL operations, optimization, and administration. Use for: schema design, index selection, query tuning with EXPLAIN ANALYZE, postgresql.conf configuration, backup and restore (pg_dump, pg_basebackup, WAL, PITR), vacuum and autovacuum tuning, connection pooling (pgBouncer, pgPool), replication (streaming, logical), partitioning, monitoring (pg_stat_statements, pg_stat_activity), JSONB operations, full-text search (tsvector, tsquery), row-level security (RLS), extensions (PostGIS, pg_trgm, timescaledb), GiST/GIN/BRIN indexes, materialized views, foreign data wrappers, LISTEN/NOTIFY.

Original author description

PostgreSQL operations, optimization, and administration. Use for: schema design, index selection, query tuning with EXPLAIN ANALYZE, postgresql.conf configuration, backup and restore (pg_dump, pg_basebackup, WAL, PITR), vacuum and autovacuum tuning, connection pooling (pgBouncer, pgPool), replication (streaming, logical), partitioning, monitoring (pg_stat_statements, pg_stat_activity), JSONB operations, full-text search (tsvector, tsquery), row-level security (RLS), extensions (PostGIS, pg_trgm, timescaledb), GiST/GIN/BRIN indexes, materialized views, foreign data wrappers, LISTEN/NOTIFY.

How it works

  1. Quick Connection
  2. Index Type Selection
  3. EXPLAIN ANALYZE Workflow
  4. Workload Profiles
  5. Common Operations
  6. Data Types Quick Reference
  7. Gotchas & Anti-Patterns
  8. Row-Level Security (RLS) Quick Start
  9. Full-Text Search Quick Start
  10. LISTEN/NOTIFY
Original SKILL.md headings · 266 lines, 1,302 words
SKILL.md preview
---
name: postgres-ops
description: "PostgreSQL operations, optimization, and administration. Use for: schema design, index selection, query tuning with EXPLAIN ANALYZE, postgresql.conf configuration, backup and restore (pg_dump, pg_basebackup, WAL, PITR), vacuum and autovacuum tuning, connection pooling (pgBouncer, pgPool), replication (streaming, logical), partitioning, monitoring (pg_stat_statements, pg_stat_activity), JSONB operations, full-text search (tsvector, tsquery), row-level security (RLS), extensions (PostGIS, pg_trgm, timescaledb), GiST/GIN/BRIN indexes, materialized views, foreign data wrappers, LISTEN/NOTIFY."
license: MIT
allowed-tools: "Read Write Bash"
metadata:
  author: claude-mods
  related-skills: sql-ops, sqlite-ops, python-database-ops
---

# PostgreSQL Operations

Comprehensive PostgreSQL skill covering schema design through production operations.

## Quick Connection

```bash
# Standard connection
psql "postgresql://user:pass@localhost:5432/dbname"

# With SSL
psql "postgresql://user:pass@host:5432/dbname?sslmode=require"

# Environment variables (libpq)
export PGHOST=localhost PGPORT=5432 PGDATABASE=mydb PGUSER=myuser PGPASSWORD=secret
psql

# Connection pooling (pgBouncer default)
psql "postgresql://user:pass@localhost:6432/dbname"
```

```sql
-- Check current connection
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
```

## Index Type Selection

```
What query pattern are you optimizing?
│
├─ Equality (WHERE col = val)
│  └─ B-tree (default, almost always right)
│
├─ Range (WHERE col > val, ORDER BY, BETWEEN)
│  └─ B-tree
│
├─ Array/JSONB containment (@>, ?, ?|, ?&)
│  └─ GIN
│
├─ Full-text search (@@)
│  └─ GIN with tsvector
│
├─ Geometric/range overlap (&&, <->)
│  └─ GiST
│
├─ Pattern matching (LIKE '%text%', similarity)
│  └─ GIN with pg_trgm (gin_trgm_ops)
│
├─ Large table, few distinct values, append-only
│  └─ BRIN (tiny index, good for timestamps)
│
└─ Exact equality only, no range/sort needed
   └─ Hash (rare - B-tree usually better)
```

### Quick Index Reference

| Index | Best For | Size | Write Cost |
|-------|----------|------|------------|
| B-tree | Equality, range, sort | Medium | Low |
| GIN | Arrays, JSONB, FTS, trigrams | Large | High |
| GiST | Geometry, ranges, FTS | Medium | Medium |
| BRIN | Correlated data (timestamps) | Tiny | Very low |
| Hash | Exact equality only | Medium | Low |

**Deep dive**: Load `./references/indexing.md` for composite, partial, expression, and covering index strategies.

## EXPLAIN ANALYZE Workflow

```sql
-- Step 1: Run with ANALYZE and BUFFERS
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- Step 2: Read bottom-up. Find the slowest node.
-- Step 3: Check estimates vs actuals
--   actual rows=10000, rows=100  -> bad estimate, run ANALYZE
-- Step 4: Look for these red flags:
```

| Red Flag | Meaning | Fix |
|----------|---------|-----|
| `Seq Scan` on large table | No usable index | Add index matching WHERE/JOIN |
| `actual rows` >> `estimated rows` | Stale statistics | `ANALYZE tablename` |
| `Nested Loop` with high rows | O(n*m) join | Check join conditions, add index |
| `Sort` with `external merge` | work_mem too small | Increase `work_mem` for session |
| `Buffers: shared read` >> `hit` | Cold cache or table too large | Check `shared_buffers`, add covering index |
| `Hash Batch` > 1 | Hash join spilling to disk | Increase `work_mem` |

**Deep dive**: Load `./references/query-tuning.md` for plan node reference and optimization patterns.

## Workload Profiles

| Setting | OLTP | OLAP | Notes |
|---------|------|------|-------|
| `shared_buffers` | 25% RAM | 25% RAM | Same baseline |
| `work_mem` | 4-16 MB | 256 MB-1 GB | OLAP needs big sorts |
| `effective_cache_size` | 75% RAM | 75% RAM | Planner hint |
| `random_page_cost` | 1.1 (SSD) | 1.1 (SSD) | Lower for SSD |
| `max_parallel_workers_per_gather` | 2 | 4-8 | OLAP benefits more |
| `checkpoint_completion_target` | 0.9 | 0.9 | Spread checkpoint I/O |
| `wal_buffers` | 64 MB | 64 MB | -1 for auto |
| `maintenance_work_mem` | 512 MB | 1-2 GB | For VACUUM, CREATE INDEX |

**Deep dive**: Load `./references/config-tuning.md` for full postgresql.conf walkthrough and extension setup.

## Common Operations

### Backup & Restore

```bash
# Logical backup (single database)
pg_dump -Fc dbname > backup.dump

# Restore
pg_restore -d dbname backup.dump

# Parallel backup (faster for large DBs)
pg_dump -Fc -j4 dbname > backup.dump

# Base backup for PITR
pg_basebackup -D /backup/base -Ft -Xs -P
```

### Vacuum & Maintenance

```sql
-- Manual vacuum (reclaim space, update stats)
VACUUM (VERBOSE, ANALYZE) tablename;

-- Full vacuum (rewrites table, exclusive lock)
VACUUM FULL tablename;  -- CAUTION: locks table

-- Reindex without downtime
REINDEX INDEX CONCURRENTLY idx_name;

-- Update statistics only
ANALYZE tablename;
```

### Monitor Key Metrics

```sql
-- Slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Table bloat indicator
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Lock contention
SELECT pid, relation::regclass, mode, granted, query
FROM pg_locks JOIN pg_stat_activity USING (pid)
WHERE NOT granted;

-- Cache hit ratio (should be > 99%)
SELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;
```

**Deep dive**: Load `./references/operations.md` for WAL archiving, PITR, autovacuum tuning, connection pooling.

## Data Types Quick Reference

| Type | Use When | Example |
|------|----------|---------|
| `JSONB` | Semi-structured data, flexible schema | `'{"tags": ["a","b"]}'::jsonb` |
| `ARRAY` | Fixed-type lists | `ARRAY['a','b','c']` |
| `tsrange` | Time periods, scheduling | `'[2024-01-01, 2024-12-31)'::tsrange` |
| `tsvector` | Full-text search | `to_tsvector('english', body)` |
| `uuid` | Distributed IDs | `gen_random_uuid()` |
| `inet`/`cidr` | IP addresses, networks | `'192.168.1.0/24'::cidr` |

**Deep dive**: Load `./references/schema-design.md` for normalization, constraints, RLS, generated columns, table inheritance.

## Gotchas & Anti-Patterns

| Mistake | Why It's Bad | Fix |
|---------|-------------|-----|
| `SELECT *` in production | Wastes bandwidth, blocks covering index scans | List columns explicitly |
| Function on indexed column (`WHERE UPPER(email) = ...`) | Prevents index use | Expression index: `CREATE INDEX ... ON (UPPER(email))` |
| `NOT IN (subquery)` with NULLs | Returns no rows if subquery has NULL | Use `NOT EXISTS` |
| Missing `ANALYZE` after bulk load | Planner uses stale row estimates | Run `ANALYZE tablename` |
| `VACUUM FULL` in production | Exclusive lock on entire table | Regular `VACUUM` + `pg_repack` |
| `LIMIT` without `ORDER BY` | Non-deterministic results | Always pair with `ORDER BY` |
| Offset pagination on large tables | Scans and discards rows | Keyset pagination: `WHERE id > last_id` |
| Too many indexes | Slows writes, wastes space | Audit with `pg_stat_user_indexes` |
| Single shared connection pool | Contention across services | Per-service pools via pgBouncer |
| `default_transaction_isolation = serializable` | Excessive serialization failures | Keep `read committed`, use explicit `SERIALIZABLE` where needed |

## Row-Level Security (RLS) Quick Start

```sql
-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own rows
CREATE POLICY user_isolation ON documents
    USING (owner_id = current_setting('app.current_user_id')::int);

-- Policy: admins see everything
CREATE POLICY admin_access ON documents
    USING (current_setting('app.role') = 'admin');

-- Set context per request (from app layer)
SET app.current_user_id = '42';
SET app.role = 'user';
```

## Full-Text Search Quick Start

```sql
-- Add search column
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

-- Index it
CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);

-- Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
```

## LISTEN/NOTIFY

```sql
-- Publisher
NOTIFY order_events, '{"order_id": 123, "status": "shipped"}';

-- Subscriber (in psql or app)
LISTEN order_events;

-- Check for notifications (app code)
-- Python: conn.poll(); conn.notifies
-- Node: client.on('notification', callback)
```

## Reference Files

Load these for deep-dive topics. Each is self-contained.

| Reference | When to Load |
|-----------|-------------|
| `./references/schema-design.md` | Designing tables, choosing types, constraints, RLS policies, JSONB modeling |
| `./references/indexing.md` | Choosing index types, composite/partial/expression indexes, index maintenance |
| `./references/query-tuning.md` | Reading EXPLAIN plans, pg_stat_statements, optimizing specific query patterns |
| `./references/operations.md` | Backup/restore, WAL/PITR, vacuum tuning, monitoring, connection pooling |
| `./references/replication.md` | Streaming/logical replication, failover, partitioning, FDW |
| `./references/config-tuning.md` | postgresql.conf settings, OLTP/OLAP profiles, extension setup |

## See Also

- `sql-ops` - Vendor-neutral SQL patterns (CTEs, window functions, JOINs)
- `sqlite-ops` - SQLite-specific patterns and operations
- `python-database-ops` - SQLAlchemy ORM and async database patterns
Files in this package (9)
  • SKILL.md 10 KB
  • assets/.gitkeep 1 KB
  • references/config-tuning.md 21 KB
  • references/indexing.md 24 KB
  • references/operations.md 25 KB
  • references/query-tuning.md 22 KB
  • references/replication.md 17 KB
  • references/schema-design.md 20 KB
  • scripts/.gitkeep 1 KB

Versions

The database has no other skills named postgres-ops.

Database snapshot: 13.11.2026 · package version 0546316ad90f (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100postgres
    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. Trigger on "postgres table is bloated", "autovacuum isn't keeping up",…
  • 100postgres-pro
    Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.
  • 100postgres-operations
    Operates PostgreSQL in production - connection pooling with PgBouncer, autovacuum and bloat, transaction ID wraparound, replication and replication slots, backups with point-in-time recovery, restore tests and major version upgrades. Use when Postgres runs out of connections,…
  • 98db-admin
    PostgreSQL database administration — backup/restore (pg_dump, PITR, WAL archiving), health monitoring (connections, bloat, cache hit ratio, dead tuples), connection pooling (PgBouncer), replication (streaming, logical), vacuum/autovacuum tuning, and scheduled backups with…
  • 95postgresql-optimization
    SQL performance work in PostgreSQL - EXPLAIN/ANALYZE, index strategy (composite, partial, expression, covering, GIN/GiST/BRIN), planner and join analysis, statistics, MVCC/vacuum bloat, pagination, aggregation rewrites, locks, WAL, and connection pooling. Use when a query is…
  • 100postgresql-operations-and-performance-tuning
    Covers day-2 PostgreSQL operations: streaming and logical replication, vacuum/autovacuum tuning and bloat remediation, connection pooling with PgBouncer, query performance tuning (EXPLAIN ANALYZE, indexing, planner statistics), and major-version upgrade strategy (pg_upgrade vs.…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.