postgres-ops skill
Performs PostgreSQL database administration, schema design, query optimization, and production operations.
- GitHub stars
- 81
- Files
- 9
- Last commit
- Nov 7, 2026
- License
- MIT
- Allowed tools
- 3
What does the postgres-ops skill do?
postgres-ops performs PostgreSQL database administration, schema design, query optimization, and production operations.
Summary generated from the skill's SKILL.md.
About the postgres-ops skill
Managing PostgreSQL databases from within an AI agent is what this skill enables for database administrators and developers. It provides a comprehensive toolkit for handling everything from initial schema design and index selection to complex production operations like backup and restore. An agent should load this skill when it needs to perform query tuning using EXPLAIN ANALYZE, manage vacuum and autovacuum settings, or configure connection pooling with tools like pgBouncer and pgPool.
The skill supports advanced database features including row-level security, full-text search with tsvector, and JSONB operations. It also assists with maintenance tasks such as using pg_dump for logical backups, pg_basebackup for PITR, and monitoring system health through pg_stat_statements and pg_stat_activity. By utilizing read, write, and bash tools, the agent can execute direct SQL commands and system-level operations to optimize performance for both OLTP and OLAP workloads.
What an agent can do with it
- 1Optimize slow database queriesAnalyze a query execution plan to identify bottlenecks like sequential scans or high memory usage.
- 2Perform database backups and recoveryExecute logical or parallel backups to ensure data durability and enable point-in-time recovery.
- 3Implement fine-grained data securityConfigure row-level security policies to restrict data access based on user roles or IDs.
Written from the skill's SKILL.md. Every use case is checked against a quote from it.
How to install the postgres-ops skill
Each command copies the skill folder into the client's skills directory. Restart the client or start a new session to load it.Install the postgres-ops skill in Plugin
/plugin marketplace add 0xDarkMatter/claude-mods /plugin install claude-mods@claude-mods
Install the postgres-ops skill in Claude Code
~/.claude/skills/postgres-ops/SKILL.md
$ npx degit 0xDarkMatter/claude-mods/skills/postgres-ops ~/.claude/skills/postgres-opsInstall the postgres-ops skill in Claude Code (project)
.claude/skills/postgres-ops/SKILL.md
$ npx degit 0xDarkMatter/claude-mods/skills/postgres-ops .claude/skills/postgres-opsInstall the postgres-ops skill in Codex
~/.agents/skills/postgres-ops/SKILL.md
$ npx degit 0xDarkMatter/claude-mods/skills/postgres-ops ~/.agents/skills/postgres-opsInstall the postgres-ops skill in Gemini CLI
~/.gemini/skills/postgres-ops/SKILL.md
$ npx degit 0xDarkMatter/claude-mods/skills/postgres-ops ~/.gemini/skills/postgres-opsInstall the postgres-ops skill in Claude.ai
npx degit 0xDarkMatter/claude-mods/skills/postgres-ops postgres-ops && zip -r postgres-ops.zip postgres-ops # claude.ai → Customize → Skills → + → Create skill → Upload a skill → postgres-ops.zip
Automated checks
Ran Nov 15, 2026Full report →Not verified yet. A skill needs a score of 80, no serious finding, and enough data for us to check. How checks work · What to fix for more points
- Source repository linked and reachable0xdarkmatter/claude-mods/skills/postgres-ops is public and reachablegithub.com/0xdarkmatter/claude-mods/tree/HEAD/skills/postgre ↗Pass
- Open source license (OSI)MITspdx.org/licenses/MIT.html ↗Pass
- Commit or release in the last yearLast commit 2026-11-07github.com/0xdarkmatter/claude-mods/commits ↗Pass
- No download-and-run or credential theft in scriptsNo scripts in the skill folderPass
- No hidden instructions in SKILL.mdNo override phrases, safeguard switches, credential requests or hidden charactersgithub.com/0xdarkmatter/claude-mods/blob/HEAD/skills/postgre ↗Pass
- Pre-approved tools are scopedBash lets the agent run any shell command without askinggithub.com/0xdarkmatter/claude-mods/blob/HEAD/skills/postgre ↗Finding
- SKILL.md follows the Agent Skills formatValid name and description; says when to use it; 275 linesgithub.com/0xdarkmatter/claude-mods/blob/HEAD/skills/postgre ↗Pass
SKILL.md
View on GitHubPostgreSQL Operations
Comprehensive PostgreSQL skill covering schema design through production operations.
Quick Connection
# 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"
-- 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
-- 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
# 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
-- 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
-- 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
-- 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
-- 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
-- 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 operationspython-database-ops- SQLAlchemy ORM and async database patterns
Files in this skill
9postgres-ops skill questions
How do I install the postgres-ops skill in Claude Code?
Run npx degit 0xDarkMatter/claude-mods/skills/postgres-ops ~/.claude/skills/postgres-ops. Claude Code loads skills from ~/.claude/skills (all projects) or .claude/skills (one project) and uses a skill when its description matches the task.
Which clients can use the postgres-ops skill?
Any client that reads the Agent Skills format: Claude Code, Codex, Gemini CLI and claude.ai (upload as a ZIP). Scripts in the skill run only where the client allows code execution.
How can I optimize a slow query in PostgreSQL?
Use the EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) command to identify slow nodes, check for stale statistics by running ANALYZE, and look for red flags like Seq Scans on large tables.
How do I perform a logical backup of a single database?
You can use the pg_dump command with the custom format flag: pg_dump -Fc dbname > backup.dump.
How can I implement row-level security?
Enable RLS on the table using ALTER TABLE and then create policies using the CREATE POLICY command to define access rules.