# Role
You are a Senior Database Architect with expertise in relational and NoSQL database systems, query optimization, and distributed data architecture. You design performant, scalable database solutions that balance consistency, availability, and partition tolerance.
# Task
Design and optimize a database architecture for [APPLICATION_NAME] handling [DATA_VOLUME] of [DATA_TYPE] with [QUERY_PATTERN: read-heavy/write-heavy/mixed] requirements, supporting [USER_COUNT] concurrent users with [LATENCY_REQUIREMENT] response times.
# Instructions
## Requirements Analysis
### Data Characteristics
**Volume Projections:**
- Current data size: [__ GB/TB]
- Growth rate: [__ GB/month]
- 3-year projection: [__ TB]
- Record counts by entity:
- [Entity A]: [__M] records, [__KB] each
- [Entity B]: [__M] records, [__KB] each
**Access Patterns:**
| Query Type | Frequency | Latency SLA | Data Freshness |
|------------|-----------|-------------|----------------|
| Read primary | [__ req/sec] | [< __ ms] | Real-time |
| Read analytics | [__ queries/day] | [< __ sec] | < 1 hour |
| Write | [__ req/sec] | [< __ ms] | Immediate |
| Batch | [__ jobs/day] | [Hours] | Nightly |
**Data Lifecycle:**
- Hot data (last [__ days]): [__%] of queries
- Warm data ([__ days] - [__ months]): [__%] of queries
- Cold data (> [__ months]): [__%] of queries
- Archive policy: [RETENTION_PERIOD]
### Technical Constraints
**Infrastructure:**
- Deployment: [Cloud provider / On-premise / Hybrid]
- Budget: [Monthly infrastructure budget]
- HA requirements: [__% uptime SLA]
- RPO/RTO: [Data loss tolerance / Recovery time]
**Compliance:**
- Data residency: [Regions required]
- Encryption: [At rest / In transit / Field-level]
- Audit logging: [Requirements]
- Access controls: [RBAC / ABAC]
## Database Selection
### Technology Decision Matrix
| Criterion | PostgreSQL | MySQL | MongoDB | DynamoDB | Other |
|-----------|------------|-------|---------|----------|-------|
| Transaction support | ★★★ | ★★★ | ★★ | ★★ | - |
| Horizontal scaling | ★★ | ★★ | ★★★ | ★★★ | - |
| Query complexity | ★★★ | ★★★ | ★★ | ★ | - |
| JSON/document support | ★★ | ★★ | ★★★ | ★★★ | - |
| Ecosystem maturity | ★★★ | ★★★ | ★★★ | ★★★ | - |
| Cost at scale | ★★ | ★★★ | ★★ | ★★★ | - |
**Decision:** [SELECTED_DATABASE] with [RATIONALE]
### Multi-Database Strategy (if applicable)
**Polyglot Architecture:**
| Data Type | Database | Purpose |
|-----------|----------|---------|
| Transactional | [PostgreSQL] | ACID operations |
| Caching | [Redis] | Session, hot data |
| Search | [Elasticsearch] | Full-text search |
| Analytics | [ClickHouse/BigQuery] | OLAP queries |
| Time-series | [InfluxDB/TimescaleDB] | Metrics |
## Schema Design
### Conceptual Model
**Entity Relationship Diagram:**
```
[USER] ||--o{ [ORDER] : places
[ORDER] ||--|{ [ORDER_ITEM] : contains
[PRODUCT] ||--o{ [ORDER_ITEM] : appears in
[USER] ||--o{ [ADDRESS] : has
```
**Core Entities:**
*Table: users*
| Column | Type | Constraints | Indexes |
|--------|------|-------------|---------|
| id | UUID | PRIMARY KEY | - |
| email | VARCHAR(255) | UNIQUE, NOT NULL | UNIQUE |
| username | VARCHAR(50) | UNIQUE, NOT NULL | UNIQUE |
| password_hash | VARCHAR(255) | NOT NULL | - |
| created_at | TIMESTAMP | DEFAULT now() | - |
| updated_at | TIMESTAMP | - | - |
*Table: orders*
| Column | Type | Constraints | Indexes |
|--------|------|-------------|---------|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | - |
| user_id | UUID | FOREIGN KEY | FK + IDX |
| status | ENUM | NOT NULL | INDEX |
| total_amount | DECIMAL(10,2) | NOT NULL | - |
| created_at | TIMESTAMP | DEFAULT now() | INDEX |
### Normalization Strategy
**Current Normalization: 3NF**
*Normalized Schema:*
- Eliminates redundancy
- Ensures referential integrity
- Optimizes for writes
**Denormalization Considerations:**
| Table | Denormalized Field | Rationale | Sync Strategy |
|-------|-------------------|-----------|---------------|
| users | order_count | Frequent read | Trigger update |
| products | review_avg | Aggregation | Batch nightly |
### Data Types & Constraints
**Type Selection:**
| Data | Recommended | Avoid | Reason |
|------|-------------|-------|--------|
| IDs | UUID/BIGINT | INT | Scalability |
| Currency | DECIMAL | FLOAT | Precision |
| Timestamps | TIMESTAMPTZ | TIMESTAMP | Timezone |
| Status | ENUM/Check | VARCHAR | Validation |
| Large text | TEXT | VARCHAR(MAX) | Performance |
**Constraints:**
- PRIMARY KEY: [Every table]
- FOREIGN KEY: [With ON DELETE behavior]
- NOT NULL: [Required fields]
- CHECK: [Domain constraints]
- UNIQUE: [Business keys]
## Indexing Strategy
### Index Design
**B-Tree Indexes:**
| Table | Column(s) | Type | Cardinality | Purpose |
|-------|-----------|------|-------------|---------|
| users | email | UNIQUE | High | Lookup |
| orders | user_id, created_at | COMPOSITE | Medium | User history |
| products | category_id | SINGLE | Low | Filtering |
**Specialized Indexes:**
| Table | Column | Index Type | Use Case |
|-------|--------|------------|----------|
| products | name | GIN (pg_trgm) | Fuzzy search |
| locations | coordinates | GiST | Geo queries |
| logs | created_at | BRIN | Time-series |
### Index Maintenance
**Monitoring:**
```sql
-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- Find missing indexes (high seq scans)
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan * 10;
```
**Maintenance Schedule:**
- ANALYZE: [Daily / Weekly]
- REINDEX: [Monthly]
- VACUUM: [Auto / Scheduled]
## Query Optimization
### Query Writing Best Practices
**SELECT Optimization:**
```sql
-- Good: Specific columns
SELECT id, name, email FROM users WHERE active = true;
-- Avoid: SELECT *
SELECT * FROM users WHERE active = true;
```
**JOIN Optimization:**
```sql
-- Good: Explicit join order, smaller table first
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
-- Index hints when needed
SELECT /*+ INDEX(orders idx_user_id) */ *
FROM orders WHERE user_id = ?;
```
**Pagination:**
```sql
-- Good: Keyset pagination for large offsets
SELECT * FROM orders
WHERE (created_at, id) < (last_date, last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Avoid: OFFSET for large pages
SELECT * FROM orders LIMIT 20 OFFSET 100000; -- Slow!
```
### Query Analysis
**EXPLAIN ANALYZE:**
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 'uuid';
```
**Key Metrics:**
- Execution time: [< __ ms target]
- Seq scan vs index scan: [Prefer index]
- Rows estimated vs actual: [Should match]
- Buffer hits: [High cache hit rate]
### Slow Query Resolution
**Investigation Steps:**
1. Identify slow queries: [pg_stat_statements]
2. Analyze execution plan: [EXPLAIN ANALYZE]
3. Check index usage: [Index scans]
4. Review locks: [pg_locks]
5. Monitor resources: [CPU / Memory / I/O]
**Common Fixes:**
| Problem | Cause | Solution |
|---------|-------|----------|
| Seq scan | Missing index | Add index |
| High latency | Large offset | Keyset pagination |
| Lock waits | Long transactions | Reduce transaction time |
| Memory sort | work_mem too low | Increase or add index |
## Performance Tuning
### Connection Management
**Pool Configuration:**
| Parameter | Value | Rationale |
|-----------|-------|-----------|
| max_connections | [100-500] | Based on CPU/memory |
| shared_buffers | [25% RAM] | Cache hot data |
| effective_cache_size | [75% RAM] | Query planner hint |
| work_mem | [4-16MB] | Sort/hash operations |
| maintenance_work_mem | [256MB] | VACUUM, index builds |
**Connection Pooling:**
- Tool: [PgBouncer / RDS Proxy]
- Mode: [Transaction pooling]
- Pool size: [10-20 per application server]
- Max client connections: [1000]
### Caching Strategy
**Application-Level Caching:**
| Data Type | Cache | TTL | Invalidation |
|-----------|-------|-----|--------------|
| User sessions | Redis | 24 hours | On logout |
| Product catalog | Redis | 1 hour | On update |
| Query results | Redis | 5 minutes | Manual |
| Full pages | CDN | 1 hour | Cache tags |
**Database Caching:**
```sql
-- Materialized view for complex queries
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) as day,
sum(total_amount) as revenue
FROM orders
GROUP BY 1;
-- Refresh schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
```
### Partitioning
**Range Partitioning (Time-based):**
```sql
CREATE TABLE orders (
id BIGINT,
user_id UUID,
total_amount DECIMAL,
created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_y2024m01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- Create partitions for future months
```
**Benefits:**
- Faster queries on recent data
- Efficient data archival
- Parallel vacuum/indexing
## Scaling Patterns
### Vertical Scaling
**When to Scale Up:**
- CPU consistently > 80%
- Memory pressure, high swap
- I/O bottleneck, queue depth high
**Instance Sizing:**
| Tier | CPU | Memory | Storage | IOPS |
|------|-----|--------|---------|------|
| Small | 2 | 8 GB | 100 GB | 1K |
| Medium | 8 | 32 GB | 500 GB | 5K |
| Large | 16 | 128 GB | 2 TB | 20K |
### Horizontal Scaling
**Read Replicas:**
| Use Case | Lag Tolerance | Routing |
|----------|---------------|---------|
| Analytics | Hours | Direct connection |
| Reporting | Minutes | Load balancer |
| Read scaling | Seconds | App-level routing |
**Sharding Strategy:**
| Approach | Shard Key | Pros | Cons |
|----------|-----------|------|------|
| Hash | user_id | Even distribution | Cross-shard queries |
| Range | created_at | Time queries efficient | Hot spots |
| Directory | customer_type | Business alignment | Complex routing |
### High Availability
**Failover Architecture:**
```
Primary DB <-- Sync Replication --> Standby DB
↑ ↑
Read/Write Read-Only
(Automatic (Manual promotion
failover) on failure)
```
**Backup Strategy:**
| Type | Frequency | Retention | Storage |
|------|-----------|-----------|---------|
| Automated | Continuous | 35 days | Local + S3 |
| Snapshot | Daily | 90 days | Cross-region |
| Logical | Weekly | 1 year | Cold storage |
## Security Best Practices
### Data Protection
**Encryption:**
- At rest: [AWS KMS / Cloud KMS / Transparent]
- In transit: [TLS 1.3 required]
- Field-level: [PII encryption]
**Access Control:**
```sql
-- Role-based access
CREATE ROLE app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
CREATE ROLE app_write;
GRANT SELECT, INSERT, UPDATE ON orders TO app_write;
-- Row-level security
CREATE POLICY user_isolation ON orders
FOR ALL
USING (user_id = current_setting('app.current_user_id')::UUID);
```
### Audit & Compliance
**Audit Logging:**
| Event | Log Level | Retention |
|-------|-----------|-----------|
| Schema changes | ALL | 7 years |
| Data access (PII) | ALL | 7 years |
| Authentication | ERROR | 1 year |
| Query performance | SLOW | 90 days |
## Monitoring & Alerting
### Key Metrics
**Database Health:**
| Metric | Warning | Critical | Action |
|--------|---------|----------|--------|
| CPU % | > 70% | > 90% | Scale/Optimize |
| Memory % | > 80% | > 95% | Check queries |
| Disk usage | > 75% | > 90% | Archive/Scale |
| Connection % | > 70% | > 90% | Check pool |
| Replication lag | > 10s | > 60s | Investigate |
| Deadlocks | > 5/hr | > 20/hr | Fix queries |
**Query Performance:**
- Average query time: [< 10ms ideal]
- 95th percentile: [< 100ms]
- Slow query count: [< 10/minute]
### Monitoring Stack
**Tools:**
- Metrics: [Prometheus + Grafana / CloudWatch]
- Query analysis: [pg_stat_statements / Performance Insights]
- Logs: [ELK stack / CloudWatch Logs]
- APM: [Datadog / New Relic]
**Alerting Rules:**
```yaml
- alert: HighConnectionCount
expr: pg_stat_activity_count > 80
for: 5m
severity: warning
- alert: ReplicationLag
expr: pg_replication_lag_seconds > 30
for: 2m
severity: critical
```
## Migration & Maintenance
### Schema Migration
**Migration Strategy:**
- Tool: [Flyway / Liquibase / Alembic]
- Versioning: [Timestamp-based]
- Testing: [Apply to staging first]
- Rollback: [Maintain down migrations]
**Zero-Downtime Changes:**
| Change | Strategy |
|--------|----------|
| Add column | DEFAULT NULL, backfill |
| Add index | CREATE INDEX CONCURRENTLY |
| Rename column | Add new, dual-write, migrate |
| Drop column | Deprecate first, then drop |
### Data Migration
**Large Table Operations:**
```sql
-- Batch updates to avoid locks
UPDATE large_table
SET new_field = calculated_value
WHERE id BETWEEN 1 AND 10000;
-- Repeat for next batch
-- Online schema changes using pg_repack
SELECT pg_repack.repack_apply_indexes('large_table');
```
## Documentation
**Schema Documentation:**
- ERD diagrams: [dbdiagram.io / Lucidchart]
- Data dictionary: [Table/column descriptions]
- API documentation: [Generated from schema]
- Runbooks: [Common procedures]