Future Proofing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

"""
Database Abstraction Layer Patterns
 
Demonstrates abstraction approaches that enable database portability
and future migration flexibility.
"""
 
from abc import ABC, abstractmethod
from typing import List, Dict, Optional, Any
from dataclasses import dataclass
from datetime import datetime
 
 
@dataclass
class User:
    """Domain entity - database-agnostic"""
    id: str
    email: str
    name: str
    created_at: datetime
    metadata: Dict[str, Any]
 
 
class UserRepository(ABC):
    """
    Repository interface - defines what operations are needed,
    not how they're implemented. Enables swapping implementations.
    """
    
    @abstractmethod
    async def find_by_id(self, user_id: str) -> Optional[User]:
        pass
    
    @abstractmethod
    async def find_by_email(self, email: str) -> Optional[User]:
        pass
    
    @abstractmethod
    async def save(self, user: User) -> User:
        pass
    
    @abstractmethod
    async def delete(self, user_id: str) -> bool:
        pass
    
    @abstractmethod
    async def list_all(self, limit: int = 100, offset: int = 0) -> List[User]:
        pass
 
 
class PostgresUserRepository(UserRepository):
    """PostgreSQL implementation of UserRepository"""
    
    def __init__(self, connection_pool):
        self.pool = connection_pool
    
    async def find_by_id(self, user_id: str) -> Optional[User]:
        async with self.pool.acquire() as conn:
            row = await conn.fetchrow(
                "SELECT id, email, name, created_at, metadata FROM users WHERE id = $1",
                user_id
            )
            return self._row_to_user(row) if row else None
    
    async def save(self, user: User) -> User:
        async with self.pool.acquire() as conn:
            await conn.execute("""
                INSERT INTO users (id, email, name, created_at, metadata)
                VALUES ($1, $2, $3, $4, $5)
                ON CONFLICT (id) DO UPDATE SET
                    email = EXCLUDED.email,
                    name = EXCLUDED.name,
                    metadata = EXCLUDED.metadata
            """, user.id, user.email, user.name, user.created_at, user.metadata)
        return user
    
    # ... other method implementations
    
    def _row_to_user(self, row) -> User:
        return User(
            id=row['id'],
            email=row['email'],
            name=row['name'],
            created_at=row['created_at'],
            metadata=row['metadata']
        )
 
 
class MongoUserRepository(UserRepository):
    """MongoDB implementation - same interface, different storage"""
    
    def __init__(self, database):
        self.collection = database.users
    
    async def find_by_id(self, user_id: str) -> Optional[User]:
        doc = await self.collection.find_one({"_id": user_id})
        return self._doc_to_user(doc) if doc else None
    
    async def save(self, user: User) -> User:
        await self.collection.update_one(
            {"_id": user.id},
            {"$set": {
                "email": user.email,
                "name": user.name,
                "created_at": user.created_at,
                "metadata": user.metadata
            }},
            upsert=True
        )
        return user
    
    # ... other method implementations
    
    def _doc_to_user(self, doc) -> User:
        return User(
            id=doc['_id'],
            email=doc['email'],
            name=doc['name'],
            created_at=doc['created_at'],
            metadata=doc.get('metadata', {})
        )
 
 
class CachedUserRepository(UserRepository):
    """
    Decorator pattern - adds caching without changing interface.
    Demonstrates how abstraction enables cross-cutting concerns.
    """
    
    def __init__(self, delegate: UserRepository, cache):
        self.delegate = delegate
        self.cache = cache
        self.ttl_seconds = 300
    
    async def find_by_id(self, user_id: str) -> Optional[User]:
        # Check cache first
        cache_key = f"user:{user_id}"
        cached = await self.cache.get(cache_key)
        if cached:
            return User(**cached)
        
        # Fallback to delegate
        user = await self.delegate.find_by_id(user_id)
        if user:
            await self.cache.set(cache_key, user.__dict__, ex=self.ttl_seconds)
        return user
    
    async def save(self, user: User) -> User:
        # Invalidate cache, then save
        await self.cache.delete(f"user:{user.id}")
        return await self.delegate.save(user)
    
    # ... other methods delegate and manage cache appropriately
 
 
# Dependency Injection configuration
class DatabaseConfig:
    """
    Factory that creates appropriate repository implementation
    based on configuration. Switching database is config change, not code change.
    """
    
    @staticmethod
    async def create_user_repository(config: Dict) -> UserRepository:
        db_type = config.get('database_type', 'postgres')
        
        if db_type == 'postgres':
            pool = await create_postgres_pool(config['postgres'])
            repo = PostgresUserRepository(pool)
        elif db_type == 'mongodb':
            client = await create_mongo_client(config['mongodb'])
            repo = MongoUserRepository(client.database)
        else:
            raise ValueError(f"Unknown database type: {db_type}")
        
        # Add caching layer if configured
        if config.get('enable_cache'):
            cache = await create_redis_client(config['redis'])
            repo = CachedUserRepository(repo, cache)
        
        return repo
 
 
# Usage: Application code is database-agnostic
class UserService:
    """Business logic - has no idea what database is used"""
    
    def __init__(self, user_repo: UserRepository):
        self.users = user_repo  # Only knows the interface
    
    async def get_user_profile(self, user_id: str) -> Optional[Dict]:
        user = await self.users.find_by_id(user_id)
        if not user:
            return None
        return {
            'name': user.name,
            'email': user.email,
            'member_since': user.created_at.isoformat()
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

-- Expand-Contract Pattern: Renaming a Column Safely
-- This demonstrates zero-downtime schema evolution
 
-- ============================================
-- PHASE 1: EXPAND
-- Add new column alongside old, sync data
-- Duration: Deploy immediately
-- ============================================
 
-- Add the new column (nullable initially)
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
 
-- Create trigger to keep old and new in sync during transition
CREATE OR REPLACE FUNCTION sync_email_columns()
RETURNS TRIGGER AS $$
BEGIN
    -- Sync from old to new
    IF NEW.email IS DISTINCT FROM OLD.email THEN
        NEW.customer_email := NEW.email;
    END IF;
    -- Sync from new to old
    IF NEW.customer_email IS DISTINCT FROM OLD.customer_email THEN
        NEW.email := NEW.customer_email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_email_sync
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_email_columns();
 
-- Backfill existing data
UPDATE orders SET customer_email = email WHERE customer_email IS NULL;
 
-- Add index on new column
CREATE INDEX CONCURRENTLY idx_orders_customer_email ON orders(customer_email);
 
-- ============================================
-- PHASE 2: MIGRATE CODE
-- Update application to read from new column
-- Old code continues working (reads old column)
-- Duration: Deploy application changes
-- ============================================
 
-- Application code transitions:
-- v1: READ email, WRITE email
-- v2: READ customer_email, WRITE both (for rollback safety)
-- v3: READ customer_email, WRITE customer_email only
 
-- ============================================
-- PHASE 3: CONTRACT
-- Remove old column after all code is updated
-- Duration: After confidence period (days/weeks)
-- ============================================
 
-- Remove the sync trigger
DROP TRIGGER orders_email_sync ON orders;
DROP FUNCTION sync_email_columns();
 
-- Remove old column (only after all readers are updated)
ALTER TABLE orders DROP COLUMN email;
 
-- Rename new column to final name if desired
ALTER TABLE orders RENAME COLUMN customer_email TO email;
 
-- ============================================
-- ROLLBACK CAPABILITY AT EACH PHASE
-- ============================================
 
-- Phase 1 rollback: 
--   ALTER TABLE orders DROP COLUMN customer_email;
--   DROP TRIGGER/FUNCTION...
--   (No data loss - old column still primary)
 
-- Phase 2 rollback:
--   Deploy v1 application code
--   (Both columns exist, sync trigger keeps them aligned)
 
-- Phase 3 rollback:
--   Not easily reversible - this is the point of no return
--   (Hence the "confidence period" before Phase 3)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

# Vendor Lock-in Decision Record
 
## Decision: Use Oracle Partitioning for Large Transaction Tables
 
### Date: 2024-01-15
### Decision Owner: Database Architecture Team
### Status: APPROVED
 
## Context
The orders table is projected to exceed 500GB within 12 months. 
Queries against recent orders must remain sub-100ms. Historical 
queries can tolerate longer latencies.
 
## Options Considered
 
### Option A: Table Partitioning (Oracle Feature)
- Automatic partition management and pruning
- Mature, well-documented feature
- Requires Oracle Enterprise Edition license
- **Lock-in Impact: MODERATE** - Partitioning syntax is Oracle-specific
 
### Option B: Application-Level Sharding
- Portable across databases
- Significant application complexity
- No additional licensing
- **Lock-in Impact: LOW** - Standard SQL
 
### Option C: Time-Series Database for Historical
- Separate hot/cold data stores
- Operational complexity increases
- Mixed technology stack
- **Lock-in Impact: LOW** - Standard interfaces
 
## Decision
**Chose Option A: Oracle Partitioning**
 
## Rationale
- Feature is mature and well-supported
- Team has existing Oracle expertise
- Migration cost is acceptable given Oracle investment horizon
- Partition pruning optimization is significant performance benefit
- Application complexity of Option B deemed higher risk
 
## Lock-in Implications
- Requires Oracle Enterprise Edition ($X/core/year)
- Partition DDL is Oracle-specific syntax
- Migration would require:
  - Rebuild tables without partitioning OR
  - Implement application-level date filtering
  - Estimated effort: 2-4 weeks engineering
 
## Mitigation
- Document partitioning scheme clearly  
- Abstract partition maintenance in operations runbooks
- Include in bi-annual migration cost assessment
- Ensure export procedures can extract data by partition
 
## Review Date: 2025-01
### Re-review if:
- Oracle licensing costs increase significantly
- Cloud migration timeline accelerates
- PostgreSQL partitioning matures

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

# Database Technology Radar - Q1 2024
# Last Updated: 2024-01-15
# Owner: Database Architecture Team
 
adopt:
  - name: PostgreSQL 15+
    category: RDBMS
    notes: Default choice for new relational workloads
    since: 2022-Q2
    
  - name: Redis 7+
    category: Caching/KV
    notes: Session cache, rate limiting, pub/sub
    since: 2021-Q1
    
  - name: AWS RDS / Azure SQL
    category: Managed Services
    notes: Preferred over self-managed for most workloads
    since: 2023-Q1
 
trial:
  - name: CockroachDB
    category: NewSQL
    notes: Evaluating for globally distributed workloads
    since: 2023-Q3
    use_cases: ["geo-distributed apps", "global consistency needs"]
    contact: "@db-architecture"
    
  - name: Serverless Aurora
    category: Managed Services
    notes: Cost-effective for variable workloads; evaluating scale-to-zero
    since: 2023-Q4
    use_cases: ["dev environments", "variable traffic apps"]
    
  - name: TimescaleDB
    category: Time-Series
    notes: PostgreSQL extension for IoT/metrics; replaces InfluxDB
    since: 2023-Q2
    use_cases: ["application metrics", "IoT data"]
 
assess:
  - name: Vector Databases (Pinecone, Milvus)
    category: AI/ML
    notes: Required for AI features; evaluating options
    since: 2023-Q4
    research_owner: "@ml-platform"
    
  - name: Neon / PlanetScale
    category: Serverless
    notes: Interesting database branching capabilities
    since: 2024-Q1
    
  - name: DuckDB
    category: Embedded Analytics
    notes: Potential for in-process analytical queries
    since: 2023-Q4
 
hold:
  - name: MongoDB Atlas (new projects)
    category: Document Store
    notes: "Existing deployments maintained; new document needs review PostgreSQL JSONB first"
    since: 2023-Q3
    rationale: "Consolidation on PostgreSQL for simpler operations"
    
  - name: Self-managed databases (production)
    category: Operations
    notes: "Use managed services unless specific requirement for self-managed"
    since: 2022-Q4
    
  - name: Oracle (new projects)
    category: RDBMS
    notes: "Cost prohibitive for new projects; PostgreSQL preferred"
    since: 2023-Q1
    migration_plan: "Existing Oracle workloads assessed for migration individually"
 
upcoming_reviews:
  - technology: PostgreSQL 16 Features
    review_date: 2024-Q2
    owner: "@db-architecture"
    
  - technology: AlloyDB
    review_date: 2024-Q2
    owner: "@cloud-team"
 
retired:
  - name: MySQL 5.7
    date: 2023-Q4
    replacement: "PostgreSQL or MySQL 8.0"
    
  - name: Elasticsearch (self-managed)
    date: 2023-Q2
    replacement: "Elastic Cloud or OpenSearch"