PostgreSQL Core Knowledge
Deep Knowledge: Use mcp__documentation__fetch_docs with technology: postgresql for comprehensive documentation.
Table Definition
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);
Common Queries
-- Select with pagination
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- Join
SELECT u.*, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id = $1;
-- Aggregate
SELECT
DATE_TRUNC('day', created_at) as day,
COUNT(*) as count
FROM users
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day DESC;
-- Upsert
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
Data Types
| Type |
Use For |
SERIAL |
Auto-increment IDs |
UUID |
Unique identifiers |
VARCHAR(n) |
Variable strings |
TEXT |
Long text |
JSONB |
JSON data (indexed) |
TIMESTAMP |
Date and time |
BOOLEAN |
True/false |
Performance
- Use
EXPLAIN ANALYZE to check queries
- Add indexes for WHERE/JOIN columns
- Use
JSONB over JSON for queries
- Partial indexes for filtered queries
When NOT to Use This Skill
- MySQL-specific syntax - Use
mysql skill for MySQL databases (AUTO_INCREMENT, GROUP_CONCAT)
- NoSQL operations - Use
mongodb or redis skills for document/key-value stores
- Oracle PL/SQL - Use
plsql skill for Oracle-specific procedural code
- SQL Server T-SQL - Use
tsql skill for SQL Server-specific features
- ORM abstractions - Use framework-specific skills (Prisma, TypeORM, Spring Data JPA)
Anti-Patterns
| Anti-Pattern |
Issue |
Solution |
SELECT * in production |
Transfers unnecessary data, breaks when schema changes |
Specify needed columns explicitly |
Missing WHERE on UPDATE/DELETE |
Modifies all rows unintentionally |
Always include WHERE clause, use transactions |
| Missing indexes on JOIN/WHERE columns |
Full table scans, slow queries |
Add indexes on frequently queried columns |
| Using functions on indexed columns |
Prevents index usage: WHERE UPPER(email) = 'X' |
Use functional indexes or change query |
LIKE '%pattern' |
Cannot use index, full scan |
Use LIKE 'pattern%' or full-text search |
Missing LIMIT on large tables |
Can crash application, memory issues |
Always paginate results |
| Storing comma-separated values |
Cannot query efficiently, violates normalization |
Use array type or junction table |
| Missing foreign keys |
Data integrity issues, orphaned records |
Define proper FK constraints |
| N+1 query problem |
One query per row in loop |
Use JOINs or batch queries |
| Long-running transactions |
Locks resources, blocks other queries |
Keep transactions short, use appropriate isolation |
Quick Troubleshooting
| Problem |
Diagnostic |
Fix |
| Slow queries |
EXPLAIN ANALYZE query |
Add indexes, rewrite query, update statistics |
| High CPU usage |
pg_stat_statements to find slow queries |
Optimize top queries, add connection pooling |
| Connection limit reached |
SELECT count(*) FROM pg_stat_activity |
Increase max_connections, use PgBouncer |
| Lock contention |
SELECT * FROM pg_locks WHERE NOT granted |
Reduce transaction time, use lower isolation |
| Disk space full |
SELECT pg_size_pretty(pg_database_size('mydb')) |
VACUUM, archive old data, increase storage |
| Replication lag |
Check pg_stat_replication |
Increase resources, tune checkpoint settings |
| Cache hit ratio < 95% |
SELECT sum(blks_hit)/sum(blks_hit+blks_read) FROM pg_stat_database |
Increase shared_buffers |
| Dead tuples accumulating |
SELECT n_dead_tup FROM pg_stat_user_tables |
Run VACUUM, tune autovacuum |
Production Readiness
Security Configuration
-- Create application user with limited privileges
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Row Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_tenant_isolation ON users
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Force SSL connections
-- In postgresql.conf:
-- ssl = on
-- ssl_cert_file = '/path/to/server.crt'
-- ssl_key_file = '/path/to/server.key'
-- Connection with SSL
psql "postgresql://user:pass@host:5432/db?sslmode=require"
Connection Pooling (PgBouncer)
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
Backup & Recovery
# Continuous archiving (WAL)
# postgresql.conf:
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
wal_level = replica
# Full backup with pg_basebackup
pg_basebackup -D /backup/base -Ft -Xs -P -U replication
# Point-in-time recovery (recovery.signal)
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-01-15 10:00:00'
Performance Tuning
-- postgresql.conf recommendations (for 16GB RAM server)
shared_buffers = 4GB # 25% of RAM
effective_cache_size = 12GB # 75% of RAM
maintenance_work_mem = 1GB
work_mem = 64MB
wal_buffers = 64MB
max_connections = 200
checkpoint_completion_target = 0.9
random_page_cost = 1.1 # SSD
-- Query optimization
SET log_min_duration_statement = 1000; -- Log queries > 1s
ANALYZE; -- Update statistics
Monitoring Metrics
| Metric |
Alert Threshold |
| Connection count |
> 80% max_connections |
| Cache hit ratio |
< 95% |
| Replication lag |
> 1MB or > 10s |
| Dead tuples ratio |
> 10% |
| Long-running transactions |
> 5 minutes |
| Lock wait events |
> 10/min |
Monitoring Queries
-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
-- Cache hit ratio
SELECT
round(100 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) as cache_hit_ratio
FROM pg_stat_database;
-- Table bloat (dead tuples)
SELECT schemaname, relname, n_dead_tup, n_live_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';
-- Replication lag
SELECT client_addr, state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;
High Availability
# Patroni configuration for HA
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
etcd:
hosts: etcd1:2379,etcd2:2379,etcd3:2379
postgresql:
listen: 0.0.0.0:5432
data_dir: /data/postgres
parameters:
max_connections: 200
shared_buffers: 4GB
Checklist
Reference Documentation
1---2name: postgresql3description: |4 PostgreSQL relational database. Covers SQL queries, indexes,5 constraints, and performance. Use when working with PostgreSQL.67 USE WHEN: user mentions "postgres", "postgresql", "pg_", asks about "JSONB queries",8 "window functions", "recursive CTE", "row level security", "full text search",9 "partitioning", "pgBouncer", "replication"1011 DO NOT USE FOR: MySQL syntax - use `mysql` instead, MongoDB - use `mongodb` instead,12 Oracle PL/SQL - use `plsql` instead, SQL Server T-SQL - use `tsql` instead13allowed-tools: Read, Grep, Glob, Write, Edit14metadata:15 source: https://skillmd.com/skills/claude-dev-suite/postgresql16---1718# PostgreSQL Core Knowledge1920> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `postgresql` for comprehensive documentation.2122## Table Definition2324```sql25CREATE TABLE users (26 id SERIAL PRIMARY KEY,27 name VARCHAR(100) NOT NULL,28 email VARCHAR(255) UNIQUE NOT NULL,29 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,30 is_active BOOLEAN DEFAULT TRUE31);3233CREATE INDEX idx_users_email ON users(email);34CREATE INDEX idx_users_created ON users(created_at DESC);35```3637## Common Queries3839```sql40-- Select with pagination41SELECT * FROM users42WHERE is_active = TRUE43ORDER BY created_at DESC44LIMIT 20 OFFSET 0;4546-- Join47SELECT u.*, p.title48FROM users u49LEFT JOIN posts p ON p.user_id = u.id50WHERE u.id = $1;5152-- Aggregate53SELECT54 DATE_TRUNC('day', created_at) as day,55 COUNT(*) as count56FROM users57GROUP BY DATE_TRUNC('day', created_at)58ORDER BY day DESC;5960-- Upsert61INSERT INTO users (email, name)62VALUES ($1, $2)63ON CONFLICT (email)64DO UPDATE SET name = EXCLUDED.name;65```6667## Data Types6869| Type | Use For |70|------|---------|71| `SERIAL` | Auto-increment IDs |72| `UUID` | Unique identifiers |73| `VARCHAR(n)` | Variable strings |74| `TEXT` | Long text |75| `JSONB` | JSON data (indexed) |76| `TIMESTAMP` | Date and time |77| `BOOLEAN` | True/false |7879## Performance8081- Use `EXPLAIN ANALYZE` to check queries82- Add indexes for WHERE/JOIN columns83- Use `JSONB` over `JSON` for queries84- Partial indexes for filtered queries8586## When NOT to Use This Skill8788- **MySQL-specific syntax** - Use `mysql` skill for MySQL databases (AUTO_INCREMENT, GROUP_CONCAT)89- **NoSQL operations** - Use `mongodb` or `redis` skills for document/key-value stores90- **Oracle PL/SQL** - Use `plsql` skill for Oracle-specific procedural code91- **SQL Server T-SQL** - Use `tsql` skill for SQL Server-specific features92- **ORM abstractions** - Use framework-specific skills (Prisma, TypeORM, Spring Data JPA)9394## Anti-Patterns9596| Anti-Pattern | Issue | Solution |97|--------------|-------|----------|98| `SELECT *` in production | Transfers unnecessary data, breaks when schema changes | Specify needed columns explicitly |99| Missing `WHERE` on UPDATE/DELETE | Modifies all rows unintentionally | Always include WHERE clause, use transactions |100| Missing indexes on JOIN/WHERE columns | Full table scans, slow queries | Add indexes on frequently queried columns |101| Using functions on indexed columns | Prevents index usage: `WHERE UPPER(email) = 'X'` | Use functional indexes or change query |102| `LIKE '%pattern'` | Cannot use index, full scan | Use `LIKE 'pattern%'` or full-text search |103| Missing `LIMIT` on large tables | Can crash application, memory issues | Always paginate results |104| Storing comma-separated values | Cannot query efficiently, violates normalization | Use array type or junction table |105| Missing foreign keys | Data integrity issues, orphaned records | Define proper FK constraints |106| N+1 query problem | One query per row in loop | Use JOINs or batch queries |107| Long-running transactions | Locks resources, blocks other queries | Keep transactions short, use appropriate isolation |108109## Quick Troubleshooting110111| Problem | Diagnostic | Fix |112|---------|------------|-----|113| Slow queries | `EXPLAIN ANALYZE query` | Add indexes, rewrite query, update statistics |114| High CPU usage | `pg_stat_statements` to find slow queries | Optimize top queries, add connection pooling |115| Connection limit reached | `SELECT count(*) FROM pg_stat_activity` | Increase max_connections, use PgBouncer |116| Lock contention | `SELECT * FROM pg_locks WHERE NOT granted` | Reduce transaction time, use lower isolation |117| Disk space full | `SELECT pg_size_pretty(pg_database_size('mydb'))` | VACUUM, archive old data, increase storage |118| Replication lag | Check `pg_stat_replication` | Increase resources, tune checkpoint settings |119| Cache hit ratio < 95% | `SELECT sum(blks_hit)/sum(blks_hit+blks_read) FROM pg_stat_database` | Increase shared_buffers |120| Dead tuples accumulating | `SELECT n_dead_tup FROM pg_stat_user_tables` | Run VACUUM, tune autovacuum |121122## Production Readiness123124### Security Configuration125126```sql127-- Create application user with limited privileges128CREATE USER app_user WITH PASSWORD 'secure_password';129GRANT CONNECT ON DATABASE mydb TO app_user;130GRANT USAGE ON SCHEMA public TO app_user;131GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;132GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;133134-- Row Level Security (RLS)135ALTER TABLE users ENABLE ROW LEVEL SECURITY;136CREATE POLICY users_tenant_isolation ON users137 USING (tenant_id = current_setting('app.tenant_id')::uuid);138139-- Force SSL connections140-- In postgresql.conf:141-- ssl = on142-- ssl_cert_file = '/path/to/server.crt'143-- ssl_key_file = '/path/to/server.key'144```145146```sql147-- Connection with SSL148psql "postgresql://user:pass@host:5432/db?sslmode=require"149```150151### Connection Pooling (PgBouncer)152153```ini154# pgbouncer.ini155[databases]156mydb = host=127.0.0.1 port=5432 dbname=mydb157158[pgbouncer]159listen_addr = 0.0.0.0160listen_port = 6432161auth_type = md5162auth_file = /etc/pgbouncer/userlist.txt163pool_mode = transaction164max_client_conn = 1000165default_pool_size = 20166min_pool_size = 5167reserve_pool_size = 5168```169170### Backup & Recovery171172```bash173# Continuous archiving (WAL)174# postgresql.conf:175archive_mode = on176archive_command = 'cp %p /backup/wal/%f'177wal_level = replica178179# Full backup with pg_basebackup180pg_basebackup -D /backup/base -Ft -Xs -P -U replication181182# Point-in-time recovery (recovery.signal)183restore_command = 'cp /backup/wal/%f %p'184recovery_target_time = '2024-01-15 10:00:00'185```186187### Performance Tuning188189```sql190-- postgresql.conf recommendations (for 16GB RAM server)191shared_buffers = 4GB # 25% of RAM192effective_cache_size = 12GB # 75% of RAM193maintenance_work_mem = 1GB194work_mem = 64MB195wal_buffers = 64MB196max_connections = 200197checkpoint_completion_target = 0.9198random_page_cost = 1.1 # SSD199200-- Query optimization201SET log_min_duration_statement = 1000; -- Log queries > 1s202ANALYZE; -- Update statistics203```204205### Monitoring Metrics206207| Metric | Alert Threshold |208|--------|-----------------|209| Connection count | > 80% max_connections |210| Cache hit ratio | < 95% |211| Replication lag | > 1MB or > 10s |212| Dead tuples ratio | > 10% |213| Long-running transactions | > 5 minutes |214| Lock wait events | > 10/min |215216### Monitoring Queries217218```sql219-- Active connections220SELECT count(*) FROM pg_stat_activity WHERE state = 'active';221222-- Cache hit ratio223SELECT224 round(100 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) as cache_hit_ratio225FROM pg_stat_database;226227-- Table bloat (dead tuples)228SELECT schemaname, relname, n_dead_tup, n_live_tup,229 round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio230FROM pg_stat_user_tables231WHERE n_dead_tup > 1000232ORDER BY n_dead_tup DESC;233234-- Long-running queries235SELECT pid, now() - pg_stat_activity.query_start AS duration, query236FROM pg_stat_activity237WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';238239-- Replication lag240SELECT client_addr, state,241 pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,242 pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag243FROM pg_stat_replication;244```245246### High Availability247248```yaml249# Patroni configuration for HA250scope: postgres-cluster251name: node1252253restapi:254 listen: 0.0.0.0:8008255256etcd:257 hosts: etcd1:2379,etcd2:2379,etcd3:2379258259postgresql:260 listen: 0.0.0.0:5432261 data_dir: /data/postgres262 parameters:263 max_connections: 200264 shared_buffers: 4GB265```266267### Checklist268269- [ ] SSL/TLS encryption enabled270- [ ] Least-privilege user accounts271- [ ] Connection pooling (PgBouncer)272- [ ] WAL archiving configured273- [ ] Regular pg_dump backups274- [ ] Point-in-time recovery tested275- [ ] Monitoring queries in place276- [ ] Slow query logging enabled277- [ ] VACUUM/ANALYZE scheduled278- [ ] Replication configured (if HA)279- [ ] Connection limits set280- [ ] Row Level Security (if multi-tenant)281282## Reference Documentation283- [Indexes](quick-ref/indexes.md)284- [JSON Queries](quick-ref/json.md)