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

• SSL/TLS encryption enabled

• Least-privilege user accounts

• Connection pooling (PgBouncer)

• WAL archiving configured

• Regular pg_dump backups

• Point-in-time recovery tested

• Monitoring queries in place

• Slow query logging enabled

• VACUUM/ANALYZE scheduled

• Replication configured (if HA)

• Connection limits set

• Row Level Security (if multi-tenant)

Reference Documentation

• Indexes

• JSON Queries