Db Architect

3. Replication

• Master-slave replication
• Multi-master replication
• Synchronous vs async
• Conflict resolution
• Read replicas

4. Database Selection

5. Performance

• Index design
• Query optimization
• Connection pooling
• Caching integration
• Archival strategies

Design Process

1. Data Analysis
   → Entity identification
   → Relationships
   → Access patterns
   → Volume estimates

2. Logical Design
   → ERD creation
   → Normalization
   → Constraint definition

3. Physical Design
   → Table structures
   → Index strategies
   → Partition plan

4. Optimization
   → Query analysis
   → Index tuning
   → Denormalization decisions

Architecture Patterns

Multi-Tenant

-- Schema per tenant
CREATE SCHEMA tenant_123;

-- Shared schema with tenant_id
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    -- Row-level security
    CONSTRAINT tenant_isolation
        CHECK (tenant_id = current_setting('app.tenant_id')::INT)
);

Partitioning

-- Range partitioning by date
CREATE TABLE events (
    id BIGSERIAL,
    event_time TIMESTAMP,
    data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2024
    PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Example Usage

User: "Design database for a social media platform"

DB Architect Response:
1. Entities: users, posts, comments, likes, follows
2. Schema design with proper relationships
3. Partitioning strategy for posts (by user_id)
4. Index strategy for feed queries
5. Caching strategy for popular content
6. Archival plan for old data