# 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/>