postgres-pro

Name: postgres-pro
Author: Jeffallan

5.7kinstalls971forks

How to install postgres-pro?

jeffallan/claude-skills/postgres-pro

$npx -y skills add Jeffallan/claude-skills --skill postgres-pro

Installs into the current project.

Summary

Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Pro
2
3	Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.
4
5	## When to Use This Skill
6
7	- Analyzing and optimizing slow queries with EXPLAIN
8	- Implementing JSONB storage and indexing strategies
9	- Setting up streaming or logical replication
10	- Configuring and using PostgreSQL extensions
11	- Tuning VACUUM, ANALYZE, and autovacuum
12	- Monitoring database health with pg_stat views
13	- Designing indexes for optimal performance
14
15	## Core Workflow
16
17	1. Analyze performance — Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks
18	2. Design indexes — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying
19	3. Optimize queries — Rewrite inefficient queries, run `ANALYZE` to refresh statistics
20	4. Setup replication — Streaming or logical based on requirements; monitor lag continuously
21	5. Monitor and maintain — Track VACUUM, bloat, and autovacuum via `pg_stat` views; verify improvements after each change
22
23	### End-to-End Example: Slow Query → Fix → Verification
24
25	```sql
26	-- Step 1: Identify slow queries
27	SELECT query, mean_exec_time, calls
28	FROM pg_stat_statements
29	ORDER BY mean_exec_time DESC
30	LIMIT 10;
31
32	-- Step 2: Analyze a specific slow query
33	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
34	SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
35	-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets
36
37	-- Step 3: Create a targeted index
38	CREATE INDEX CONCURRENTLY idx_orders_customer_status
39	ON orders (customer_id, status)
40	WHERE status = 'pending'; -- partial index reduces size
41
42	-- Step 4: Verify the index is used
43	EXPLAIN (ANALYZE, BUFFERS)
44	SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
45	-- Confirm: Index Scan on idx_orders_customer_status, lower actual time
46
47	-- Step 5: Update statistics if needed after bulk changes
48	ANALYZE orders;
49	```
50
51	## Reference Guide
52
53	Load detailed guidance based on context:
54
55	\| Topic \| Reference \| Load When \|
56	\|-------\|-----------\|-----------\|
57	\| Performance \| `references/performance.md` \| EXPLAIN ANALYZE, indexes, statistics, query tuning \|
58	\| JSONB \| `references/jsonb.md` \| JSONB operators, indexing, GIN indexes, containment \|
59	\| Extensions \| `references/extensions.md` \| PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements \|
60	\| Replication \| `references/replication.md` \| Streaming replication, logical replication, failover \|
61	\| Maintenance \| `references/maintenance.md` \| VACUUM, ANALYZE, pg_stat views, monitoring, bloat \|
62
63	## Common Patterns
64
65	### JSONB — GIN Index and Query
66
67	```sql
68	-- Create GIN index for containment queries
69	CREATE INDEX idx_events_payload ON events USING GIN (payload);
70
71	-- Efficient JSONB containment query (uses GIN index)
72	SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';
73
74	-- Extract nested value
75	SELECT payload->>'user_id', payload->'meta'->>'ip'
76	FROM events
77	WHERE payload @> '{"type": "login"}';
78	```
79
80	### VACUUM and Bloat Monitoring
81
82	```sql
83	-- Check tables with high dead tuple counts
84	SELECT relname, n_dead_tup, n_live_tup,
85	round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
86	last_autovacuum
87	FROM pg_stat_user_tables
88	ORDER BY n_dead_tup DESC
89	LIMIT 20;
90
91	-- Manually vacuum a high-churn table and verify
92	VACUUM (ANALYZE, VERBOSE) orders;
93	```
94
95	### Replication Lag Monitoring
96
97	```sql
98	-- On primary: check standby lag
99	SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
100	(sent_lsn - replay_lsn) AS replication_lag_bytes
101	FROM pg_stat_replication;
102	```
103
104	## Constraints
105
106	### MUST DO
107	- Use `EXPLAIN (ANALYZE, BUFFERS)` for query optimization
108	- Verify indexes are actually used with `EXPLAIN` before and after creation
109	- Use `CREATE INDEX CONCURRENTLY` to avoid table locks in production
110	- Run `ANALYZE` after bulk data changes to refresh statistics
111	- Monitor autovacuum; tune `autovacuum_vacuum_scale_factor` for high-churn tables
112	- Use connection pooling (pgBouncer, pgPool)
113	- Monitor replication lag via `pg_stat_replication`
114	- Use prepared statements to prevent SQL injection
115	- Use `uuid` type for UUIDs, not `text`
116
117	### MUST NOT DO
118	- Disable autovacuum globally
119	- Create indexes without first analyzing query patterns
120	- Use `SELECT *` in production queries
121	- Ignore replication lag alerts
122	- Skip VACUUM on hi

People who liked this also viewed

6 picks

Type

postgres-pro

5.7kinstalls971forks

View on GitHub

How to install postgres-pro?

jeffallan/claude-skills/postgres-pro

$npx -y skills add Jeffallan/claude-skills --skill postgres-pro

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Pro
2
3	Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.
4
5	## When to Use This Skill
6
7	- Analyzing and optimizing slow queries with EXPLAIN
8	- Implementing JSONB storage and indexing strategies
9	- Setting up streaming or logical replication
10	- Configuring and using PostgreSQL extensions
11	- Tuning VACUUM, ANALYZE, and autovacuum
12	- Monitoring database health with pg_stat views
13	- Designing indexes for optimal performance
14
15	## Core Workflow
16
17	1. Analyze performance — Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks
18	2. Design indexes — Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying
19	3. Optimize queries — Rewrite inefficient queries, run `ANALYZE` to refresh statistics
20	4. Setup replication — Streaming or logical based on requirements; monitor lag continuously
21	5. Monitor and maintain — Track VACUUM, bloat, and autovacuum via `pg_stat` views; verify improvements after each change
22
23	### End-to-End Example: Slow Query → Fix → Verification
24
25	```sql
26	-- Step 1: Identify slow queries
27	SELECT query, mean_exec_time, calls
28	FROM pg_stat_statements
29	ORDER BY mean_exec_time DESC
30	LIMIT 10;
31
32	-- Step 2: Analyze a specific slow query
33	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
34	SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
35	-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets
36
37	-- Step 3: Create a targeted index
38	CREATE INDEX CONCURRENTLY idx_orders_customer_status
39	ON orders (customer_id, status)
40	WHERE status = 'pending'; -- partial index reduces size
41
42	-- Step 4: Verify the index is used
43	EXPLAIN (ANALYZE, BUFFERS)
44	SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
45	-- Confirm: Index Scan on idx_orders_customer_status, lower actual time
46
47	-- Step 5: Update statistics if needed after bulk changes
48	ANALYZE orders;
49	```
50
51	## Reference Guide
52
53	Load detailed guidance based on context:
54
55	\| Topic \| Reference \| Load When \|
56	\|-------\|-----------\|-----------\|
57	\| Performance \| `references/performance.md` \| EXPLAIN ANALYZE, indexes, statistics, query tuning \|
58	\| JSONB \| `references/jsonb.md` \| JSONB operators, indexing, GIN indexes, containment \|
59	\| Extensions \| `references/extensions.md` \| PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements \|
60	\| Replication \| `references/replication.md` \| Streaming replication, logical replication, failover \|
61	\| Maintenance \| `references/maintenance.md` \| VACUUM, ANALYZE, pg_stat views, monitoring, bloat \|
62
63	## Common Patterns
64
65	### JSONB — GIN Index and Query
66
67	```sql
68	-- Create GIN index for containment queries
69	CREATE INDEX idx_events_payload ON events USING GIN (payload);
70
71	-- Efficient JSONB containment query (uses GIN index)
72	SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';
73
74	-- Extract nested value
75	SELECT payload->>'user_id', payload->'meta'->>'ip'
76	FROM events
77	WHERE payload @> '{"type": "login"}';
78	```
79
80	### VACUUM and Bloat Monitoring
81
82	```sql
83	-- Check tables with high dead tuple counts
84	SELECT relname, n_dead_tup, n_live_tup,
85	round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
86	last_autovacuum
87	FROM pg_stat_user_tables
88	ORDER BY n_dead_tup DESC
89	LIMIT 20;
90
91	-- Manually vacuum a high-churn table and verify
92	VACUUM (ANALYZE, VERBOSE) orders;
93	```
94
95	### Replication Lag Monitoring
96
97	```sql
98	-- On primary: check standby lag
99	SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
100	(sent_lsn - replay_lsn) AS replication_lag_bytes
101	FROM pg_stat_replication;
102	```
103
104	## Constraints
105
106	### MUST DO
107	- Use `EXPLAIN (ANALYZE, BUFFERS)` for query optimization
108	- Verify indexes are actually used with `EXPLAIN` before and after creation
109	- Use `CREATE INDEX CONCURRENTLY` to avoid table locks in production
110	- Run `ANALYZE` after bulk data changes to refresh statistics
111	- Monitor autovacuum; tune `autovacuum_vacuum_scale_factor` for high-churn tables
112	- Use connection pooling (pgBouncer, pgPool)
113	- Monitor replication lag via `pg_stat_replication`
114	- Use prepared statements to prevent SQL injection
115	- Use `uuid` type for UUIDs, not `text`
116
117	### MUST NOT DO
118	- Disable autovacuum globally
119	- Create indexes without first analyzing query patterns
120	- Use `SELECT *` in production queries
121	- Ignore replication lag alerts
122	- Skip VACUUM on hi

People who liked this also viewed

6 picks

Type