340 lines
7.2 KiB
Markdown
340 lines
7.2 KiB
Markdown
# Database Quick Reference
|
|
|
|
## Health Check Commands
|
|
|
|
```bash
|
|
# Full health check
|
|
cd backend && python check_database_health.py
|
|
|
|
# Re-run optimization (if needed)
|
|
cd backend && python optimize_database.py
|
|
```
|
|
|
|
## Common PostgreSQL Commands
|
|
|
|
```bash
|
|
# Connect to database
|
|
psql -U techzone_user -d techzone
|
|
|
|
# List tables
|
|
\dt
|
|
|
|
# Describe table
|
|
\d table_name
|
|
|
|
# List indexes
|
|
\di
|
|
|
|
# Check database size
|
|
\l+
|
|
|
|
# Exit
|
|
\q
|
|
```
|
|
|
|
## Useful SQL Queries
|
|
|
|
### Index Information
|
|
|
|
```sql
|
|
-- List all indexes
|
|
SELECT schemaname, tablename, indexname, indexdef
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public'
|
|
ORDER BY tablename, indexname;
|
|
|
|
-- Check index usage
|
|
SELECT schemaname, tablename, indexname, idx_scan
|
|
FROM pg_stat_user_indexes
|
|
WHERE schemaname = 'public'
|
|
ORDER BY idx_scan DESC;
|
|
|
|
-- Find unused indexes
|
|
SELECT schemaname, tablename, indexname
|
|
FROM pg_stat_user_indexes
|
|
WHERE idx_scan = 0
|
|
AND schemaname = 'public'
|
|
AND indexname NOT LIKE '%_pkey';
|
|
```
|
|
|
|
### Performance Analysis
|
|
|
|
```sql
|
|
-- Table sizes
|
|
SELECT
|
|
tablename,
|
|
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
|
|
FROM pg_tables
|
|
WHERE schemaname = 'public'
|
|
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
|
|
|
|
-- Find slow queries (requires pg_stat_statements)
|
|
SELECT
|
|
query,
|
|
calls,
|
|
total_exec_time,
|
|
mean_exec_time,
|
|
max_exec_time
|
|
FROM pg_stat_statements
|
|
ORDER BY mean_exec_time DESC
|
|
LIMIT 10;
|
|
|
|
-- Check for sequential scans
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
seq_scan,
|
|
seq_tup_read,
|
|
idx_scan
|
|
FROM pg_stat_user_tables
|
|
WHERE schemaname = 'public'
|
|
ORDER BY seq_scan DESC;
|
|
```
|
|
|
|
### Data Integrity
|
|
|
|
```sql
|
|
-- Check for orphaned records (products without category)
|
|
SELECT id, name
|
|
FROM products
|
|
WHERE category_id IS NOT NULL
|
|
AND category_id NOT IN (SELECT id FROM categories);
|
|
|
|
-- Verify foreign key constraints
|
|
SELECT
|
|
tc.table_name,
|
|
kcu.column_name,
|
|
ccu.table_name AS foreign_table,
|
|
ccu.column_name AS foreign_column
|
|
FROM information_schema.table_constraints tc
|
|
JOIN information_schema.key_column_usage kcu
|
|
ON tc.constraint_name = kcu.constraint_name
|
|
JOIN information_schema.constraint_column_usage ccu
|
|
ON ccu.constraint_name = tc.constraint_name
|
|
WHERE tc.constraint_type = 'FOREIGN KEY';
|
|
|
|
-- Check constraint violations
|
|
SELECT conname, conrelid::regclass AS table_name
|
|
FROM pg_constraint
|
|
WHERE contype = 'c';
|
|
```
|
|
|
|
### Maintenance
|
|
|
|
```sql
|
|
-- Manual vacuum analyze
|
|
VACUUM ANALYZE;
|
|
|
|
-- Analyze specific table
|
|
ANALYZE products;
|
|
|
|
-- Reindex table
|
|
REINDEX TABLE products;
|
|
|
|
-- Check table bloat
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
|
|
n_dead_tup,
|
|
n_live_tup
|
|
FROM pg_stat_user_tables
|
|
WHERE schemaname = 'public'
|
|
ORDER BY n_dead_tup DESC;
|
|
```
|
|
|
|
## Database Backup & Restore
|
|
|
|
### Backup
|
|
|
|
```bash
|
|
# Full database backup
|
|
pg_dump -U techzone_user -d techzone > backup_$(date +%Y%m%d).sql
|
|
|
|
# Compressed backup
|
|
pg_dump -U techzone_user -d techzone | gzip > backup_$(date +%Y%m%d).sql.gz
|
|
|
|
# Specific table backup
|
|
pg_dump -U techzone_user -d techzone -t products > products_backup.sql
|
|
|
|
# Schema only (no data)
|
|
pg_dump -U techzone_user -d techzone --schema-only > schema.sql
|
|
```
|
|
|
|
### Restore
|
|
|
|
```bash
|
|
# Restore full database
|
|
psql -U techzone_user -d techzone < backup.sql
|
|
|
|
# Restore from compressed backup
|
|
gunzip -c backup.sql.gz | psql -U techzone_user -d techzone
|
|
|
|
# Restore specific table
|
|
psql -U techzone_user -d techzone < products_backup.sql
|
|
```
|
|
|
|
## Performance Tuning
|
|
|
|
### Query Optimization
|
|
|
|
```sql
|
|
-- Explain query plan
|
|
EXPLAIN SELECT * FROM products WHERE category = 'electronics';
|
|
|
|
-- Explain with execution details
|
|
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'electronics';
|
|
|
|
-- Check if index is used
|
|
EXPLAIN (FORMAT JSON) SELECT * FROM products WHERE is_active = true;
|
|
```
|
|
|
|
### Index Management
|
|
|
|
```sql
|
|
-- Create index
|
|
CREATE INDEX idx_name ON table(column);
|
|
|
|
-- Create composite index
|
|
CREATE INDEX idx_name ON table(col1, col2);
|
|
|
|
-- Create partial index
|
|
CREATE INDEX idx_name ON table(column) WHERE condition;
|
|
|
|
-- Drop unused index
|
|
DROP INDEX IF EXISTS idx_name;
|
|
```
|
|
|
|
## Monitoring Checklist
|
|
|
|
### Daily
|
|
|
|
- [ ] Check connection count
|
|
- [ ] Monitor disk space
|
|
- [ ] Review error logs
|
|
|
|
### Weekly
|
|
|
|
- [ ] Run health check script
|
|
- [ ] Check slow queries
|
|
- [ ] Verify backup success
|
|
- [ ] Review index usage
|
|
|
|
### Monthly
|
|
|
|
- [ ] Full vacuum analyze
|
|
- [ ] Check table bloat
|
|
- [ ] Review and optimize slow queries
|
|
- [ ] Update table statistics
|
|
- [ ] Test restore procedure
|
|
|
|
## Troubleshooting
|
|
|
|
### High CPU Usage
|
|
|
|
```sql
|
|
-- Find active queries
|
|
SELECT pid, usename, state, query
|
|
FROM pg_stat_activity
|
|
WHERE state = 'active';
|
|
|
|
-- Kill long-running query
|
|
SELECT pg_cancel_backend(pid); -- Graceful
|
|
SELECT pg_terminate_backend(pid); -- Forceful
|
|
```
|
|
|
|
### Locked Tables
|
|
|
|
```sql
|
|
-- Find blocking queries
|
|
SELECT
|
|
blocked_locks.pid AS blocked_pid,
|
|
blocked_activity.usename AS blocked_user,
|
|
blocking_locks.pid AS blocking_pid,
|
|
blocking_activity.usename AS blocking_user,
|
|
blocked_activity.query AS blocked_statement,
|
|
blocking_activity.query AS blocking_statement
|
|
FROM pg_catalog.pg_locks blocked_locks
|
|
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
|
|
JOIN pg_catalog.pg_locks blocking_locks
|
|
ON blocking_locks.locktype = blocked_locks.locktype
|
|
AND blocking_locks.relation = blocked_locks.relation
|
|
AND blocking_locks.pid != blocked_locks.pid
|
|
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
|
|
WHERE NOT blocked_locks.granted;
|
|
```
|
|
|
|
### Connection Issues
|
|
|
|
```sql
|
|
-- Check connection limits
|
|
SHOW max_connections;
|
|
|
|
-- Current connections
|
|
SELECT count(*) FROM pg_stat_activity;
|
|
|
|
-- Connections by database
|
|
SELECT datname, count(*)
|
|
FROM pg_stat_activity
|
|
GROUP BY datname;
|
|
```
|
|
|
|
## Index Types Reference
|
|
|
|
| Type | Use Case | Example |
|
|
|------|----------|---------|
|
|
| **B-tree** (default) | Equality, range queries | `CREATE INDEX idx ON products(price);` |
|
|
| **Hash** | Equality only | `CREATE INDEX idx USING hash ON users(email);` |
|
|
| **GiST** | Full-text, geometric | `CREATE INDEX idx USING gist ON docs(body);` |
|
|
| **GIN** | Array, JSONB | `CREATE INDEX idx USING gin ON products(specs);` |
|
|
| **Partial** | Filtered subset | `CREATE INDEX idx ON products(price) WHERE is_active;` |
|
|
| **Composite** | Multiple columns | `CREATE INDEX idx ON products(category, price);` |
|
|
|
|
## Emergency Procedures
|
|
|
|
### Database Won't Start
|
|
|
|
```bash
|
|
# Check logs
|
|
tail -f /var/log/postgresql/postgresql-14-main.log
|
|
|
|
# Check disk space
|
|
df -h
|
|
|
|
# Verify permissions
|
|
ls -la /var/lib/postgresql/14/main/
|
|
```
|
|
|
|
### Database Corruption
|
|
|
|
```bash
|
|
# Check for corruption
|
|
pg_checksums --check -D /var/lib/postgresql/14/main/
|
|
|
|
# Restore from backup
|
|
psql -U techzone_user -d techzone < latest_backup.sql
|
|
```
|
|
|
|
### Out of Disk Space
|
|
|
|
```sql
|
|
-- Find largest tables
|
|
SELECT pg_size_pretty(pg_total_relation_size(oid)) AS size, relname
|
|
FROM pg_class
|
|
ORDER BY pg_total_relation_size(oid) DESC
|
|
LIMIT 10;
|
|
|
|
-- Vacuum to reclaim space
|
|
VACUUM FULL;
|
|
```
|
|
|
|
## Contact & Resources
|
|
|
|
**Documentation**: See [DATABASE_OPTIMIZATION_REPORT.md](DATABASE_OPTIMIZATION_REPORT.md)
|
|
|
|
**Health Check**: `cd backend && python check_database_health.py`
|
|
|
|
**PostgreSQL Docs**: <https://www.postgresql.org/docs/>
|
|
|
|
**SQLAlchemy Docs**: <https://docs.sqlalchemy.org/>
|