SQL Scaling Patterns - Learning Module

Loading content...

0/273

Functional Partitioning

Dividing the Monolith

Before you shard—before you split a single table across multiple databases—there's a simpler, less invasive scaling strategy: functional partitioning. This approach separates your database not by rows, but by purpose. Instead of one massive database containing users, orders, products, analytics, and session data, you create multiple databases, each owning a distinct domain.

Functional partitioning is the database equivalent of the microservices movement in application architecture. It enables independent scaling, isolated failure domains, and team autonomy—while avoiding the crushing complexity of distributed transactions and cross-partition queries that full sharding demands.

What You Will Learn

By the end of this page, you will understand what functional partitioning is and when to use it, how to identify partition boundaries using domain-driven design principles, strategies for handling cross-partition queries and data integrity, migration patterns from monolithic to partitioned databases, and the trade-offs compared to other scaling approaches.

What Is Functional Partitioning?

Functional partitioning (also called vertical partitioning in some contexts, though that term has other meanings) divides a database by business domain or feature area. Each partition is a complete, independent database responsible for a specific set of related tables.

The Anatomy of a Partitioned Architecture

Consider a typical e-commerce application with a monolithic database:

monolith_to_partitioned.md
Architecture
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
BEFORE: Monolithic Database
═══════════════════════════════════════════════════════════════
┌────────────────────────────────────────────────────────────┐
│                     MAIN DATABASE                          │
│                                                            │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐      │
│  │    users     │  │   products   │  │    orders    │      │
│  │   profiles   │  │   categories │  │  order_items │      │
│  │   sessions   │  │   inventory  │  │   payments   │      │
│  │ preferences  │  │   reviews    │  │   shipments  │      │
│  └──────────────┘  └──────────────┘  └──────────────┘      │
│                                                            │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐      │
│  │   analytics  │  │   messages   │  │    search    │      │
│  │   events     │  │   threads    │  │   indices    │      │
│  │   metrics    │  │    inbox     │  │    facets    │      │
│  └──────────────┘  └──────────────┘  └──────────────┘      │
└────────────────────────────────────────────────────────────┘
                          │
                          │ Functional Partitioning
                          ▼
 
AFTER: Functionally Partitioned Databases
═══════════════════════════════════════════════════════════════
┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  USER_DB     │  │  CATALOG_DB  │  │  ORDER_DB    │
│              │  │              │  │              │
│  users       │  │  products    │  │  orders      │
│  profiles    │  │  categories  │  │  order_items │
│  preferences │  │  inventory   │  │  payments    │
│  sessions    │  │  reviews     │  │  shipments   │
└──────────────┘  └──────────────┘  └──────────────┘
 
┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│ ANALYTICS_DB │  │ MESSAGING_DB │  │  SEARCH_DB   │
│              │  │              │  │              │
│  events      │  │  messages    │  │  Elasticsearch│
│  metrics     │  │  threads     │  │  (different  │
│  reports     │  │  inbox       │  │   tech stack)│
└──────────────┘  └──────────────┘  └──────────────┘

Key Characteristics

Domain Ownership: Each partition owns a complete domain. The Order database contains everything about orders—no foreign keys pointing to user tables in another database.

Independent Scaling: The Order database can be scaled (more replicas, bigger hardware) independently of the User database. High order volume doesn't impact user authentication.

Technology Flexibility: Different partitions can use different technologies. Analytics might use ClickHouse, search uses Elasticsearch, while transactional data remains in PostgreSQL.

Isolated Failure Domains: If the Messaging database goes down, users can still browse products and place orders. The blast radius of failures is limited.

Functional Partitioning vs. Sharding

Don't confuse functional partitioning with sharding. Sharding splits a single table horizontally (e.g., users with ID 1-1M on shard 1, 1M-2M on shard 2). Functional partitioning splits by domain (users on one database, orders on another). Functional partitioning is simpler because individual tables remain intact.

Identifying Partition Boundaries

The hardest part of functional partitioning is deciding where to draw the lines. Poor boundaries create cross-partition dependencies that negate the scaling benefits. Good boundaries create isolated, cohesive units.

Domain-Driven Design Alignment

The best partition boundaries often align with bounded contexts from Domain-Driven Design (DDD):

User Context: Authentication, authorization, profiles, preferences
Catalog Context: Products, categories, pricing, inventory
Order Context: Carts, orders, fulfillment, returns
Payment Context: Payment methods, transactions, fraud detection
Communication Context: Notifications, emails, in-app messages

Each bounded context represents a coherent domain with its own ubiquitous language and minimal cross-context dependencies.

Analyzing Data Access Patterns

Beyond DDD theory, analyze how your application actually accesses data:

access_pattern_analysis.sql
PostgreSQL
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
-- Analyze JOIN patterns to identify natural partition boundaries
 
-- 1. Find tables that are frequently JOINed together
-- These should likely stay in the same partition
WITH query_stats AS (
    SELECT 
        query,
        calls,
        total_time
    FROM pg_stat_statements
    WHERE query ILIKE '%JOIN%'
),
parsed_tables AS (
    SELECT 
        query,
        calls,
        (regexp_matches(query, 'FROM\s+([\w.]+)', 'gi'))[1] AS from_table,
        (regexp_matches(query, 'JOIN\s+([\w.]+)', 'gi'))[1] AS join_table
    FROM query_stats
)
SELECT 
    from_table,
    join_table,
    SUM(calls) AS join_frequency
FROM parsed_tables
WHERE from_table IS NOT NULL AND join_table IS NOT NULL
GROUP BY from_table, join_table
ORDER BY join_frequency DESC
LIMIT 20;
 
-- 2. Find foreign key relationships (structural dependencies)
SELECT 
    tc.table_name AS referencing_table,
    ccu.table_name AS referenced_table,
    kcu.column_name AS fk_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'
ORDER BY referencing_table, referenced_table;
 
-- 3. Identify table clusters by access time patterns
-- Tables accessed together suggest same partition
SELECT 
    relname AS table_name,
    seq_scan + idx_scan AS total_reads,
    n_tup_ins + n_tup_upd + n_tup_del AS total_writes,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY total_reads DESC;

Signs of a Good Partition Boundary

•Low cross-boundary JOIN frequency — Tables within a partition are frequently JOINed; cross-partition JOINs are rare.
•Clear ownership — Each partition has a distinct team or service owner. No ambiguity about who maintains what.
•Independent transaction boundaries — Most transactions complete within a single partition. Cross-partition transactions are exceptional.
•Distinct scaling characteristics — Partitions have different read/write ratios, growth rates, or latency requirements.
•Failure isolation — If one partition fails, others continue functioning with minimal degradation.

Handling Cross-Partition Queries

Once you partition your database, some queries that previously used JOINs must now be handled differently. This is the primary complexity tax of functional partitioning.

Example: Order Details with User Name

Before partitioning (single database):

SELECT o.*, u.name AS customer_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.id = 12345;

After partitioning (order_db and user_db are separate): This JOIN is no longer possible. The application must:

Query order_db for the order
Extract user_id from the result
Query user_db for the user name
Combine results in application code

Cross-Partition Query Strategies

Strategies for Cross-Partition Data Access
Strategy	Description	Pros	Cons
Application-Level Joins	Fetch from each DB sequentially, join in application	Simple, flexible	N+1 queries, higher latency
Data Denormalization	Copy essential data to avoid cross-partition lookups	Fast reads, no cross-queries	Data duplication, sync complexity
API Composition	Each partition exposes an API; gateway composes responses	Clean service boundaries	Network overhead, complexity
Read-Optimized Views (CQRS)	Maintain materialized views that pre-join data	Read performance	Eventual consistency, storage cost
Foreign Data Wrappers	SQL-level federation (PostgreSQL FDW)	Transparent SQL access	Performance overhead, limited optimization

cross_partition_patterns.ts
TypeScript
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
// Pattern 1: Application-Level Join
// Simple but can have performance issues with many entities
 
interface Order {
    id: string;
    userId: string;
    items: OrderItem[];
    total: number;
}
 
interface User {
    id: string;
    name: string;
    email: string;
}
 
interface OrderWithCustomer extends Order {
    customerName: string;
    customerEmail: string;
}
 
async function getOrderWithCustomer(
    orderId: string,
    orderDb: OrderDatabase,
    userDb: UserDatabase
): Promise<OrderWithCustomer> {
    // Step 1: Fetch order from order database
    const order = await orderDb.getOrder(orderId);
    
    // Step 2: Fetch user from user database
    const user = await userDb.getUser(order.userId);
    
    // Step 3: Combine in application
    return {
        ...order,
        customerName: user.name,
        customerEmail: user.email,
    };
}
 
// Pattern 2: Batch fetching to avoid N+1
async function getOrdersWithCustomers(
    orderIds: string[],
    orderDb: OrderDatabase,
    userDb: UserDatabase
): Promise<OrderWithCustomer[]> {
    // Step 1: Fetch all orders in single query
    const orders = await orderDb.getOrders(orderIds);
    
    // Step 2: Collect unique user IDs
    const userIds = [...new Set(orders.map(o => o.userId))];
    
    // Step 3: Batch fetch all users in single query
    const users = await userDb.getUsersByIds(userIds);
    const userMap = new Map(users.map(u => [u.id, u]));
    
    // Step 4: Merge results
    return orders.map(order => {
        const user = userMap.get(order.userId)!;
        return {
            ...order,
            customerName: user.name,
            customerEmail: user.email,
        };
    });
}
 
// Pattern 3: Denormalization - store customer name in order
interface DenormalizedOrder {
    id: string;
    userId: string;
    customerName: string;  // Denormalized from user table
    items: OrderItem[];
    total: number;
}
 
async function createOrder(
    orderData: CreateOrderInput,
    orderDb: OrderDatabase,
    userDb: UserDatabase
): Promise<DenormalizedOrder> {
    // Fetch user at order creation time
    const user = await userDb.getUser(orderData.userId);
    
    // Store denormalized data
    return orderDb.createOrder({
        ...orderData,
        customerName: user.name,  // Denormalized copy
    });
}
 
// When user updates their name, we need to sync
async function handleUserNameUpdate(
    userId: string,
    newName: string,
    orderDb: OrderDatabase
): Promise<void> {
    // Background job to update denormalized data
    await orderDb.updateCustomerNameForUser(userId, newName);
}

Denormalization Trade-offs

Denormalization eliminates cross-partition queries but introduces data synchronization complexity. When a user changes their name, you must update it in all partitions that store a copy. This requires reliable change propagation (events, CDC) and acceptance of eventual consistency.

Cross-Partition Transactions

When a business operation spans multiple partitions, you lose ACID transactions. This is perhaps the most significant challenge of functional partitioning.

Example: Place Order Operation

Placing an order might require:

User DB: Verify user is active, check loyalty points
Catalog DB: Verify products exist, check inventory
Order DB: Create order record
Payment DB: Process payment
Catalog DB: Decrement inventory
User DB: Add loyalty points

In a monolithic database, this is a single transaction: all succeed or all fail. With partitioned databases, you need distributed coordination.

The Saga Pattern

The most common solution is the Saga pattern: a sequence of local transactions with compensating actions for rollback.

order_saga.ts
TypeScript
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
/**
 * Order placement saga with compensating transactions
 * 
 * Each step is a local transaction. If any step fails,
 * previously completed steps are compensated (rolled back).
 */
 
interface SagaStep<T> {
    name: string;
    execute: () => Promise<T>;
    compensate: () => Promise<void>;
}
 
class OrderPlacementSaga {
    private completedSteps: SagaStep<any>[] = [];
    
    async execute(orderRequest: OrderRequest): Promise<OrderResult> {
        try {
            // Step 1: Reserve inventory (Catalog DB)
            const reservation = await this.executeStep({
                name: 'reserve_inventory',
                execute: async () => {
                    return await catalogDb.reserveInventory(
                        orderRequest.items
                    );
                },
                compensate: async () => {
                    await catalogDb.releaseInventory(
                        orderRequest.items,
                        reservation.id
                    );
                },
            });
            
            // Step 2: Create pending order (Order DB)
            const order = await this.executeStep({
                name: 'create_order',
                execute: async () => {
                    return await orderDb.createOrder({
                        ...orderRequest,
                        status: 'pending',
                        reservationId: reservation.id,
                    });
                },
                compensate: async () => {
                    await orderDb.cancelOrder(order.id, 'saga_rollback');
                },
            });
            
            // Step 3: Process payment (Payment DB)
            const payment = await this.executeStep({
                name: 'process_payment',
                execute: async () => {
                    return await paymentDb.processPayment({
                        orderId: order.id,
                        amount: orderRequest.total,
                        paymentMethod: orderRequest.paymentMethod,
                    });
                },
                compensate: async () => {
                    await paymentDb.refundPayment(payment.id);
                },
            });
            
            // Step 4: Confirm inventory deduction (Catalog DB)
            await this.executeStep({
                name: 'confirm_inventory',
                execute: async () => {
                    return await catalogDb.confirmReservation(reservation.id);
                },
                compensate: async () => {
                    // Inventory was already committed, 
                    // need to restore it
                    await catalogDb.restoreInventory(orderRequest.items);
                },
            });
            
            // Step 5: Update order status (Order DB)
            await this.executeStep({
                name: 'confirm_order',
                execute: async () => {
                    return await orderDb.updateOrderStatus(
                        order.id, 
                        'confirmed'
                    );
                },
                compensate: async () => {
                    // No compensation needed - previous steps 
                    // handle order state
                },
            });
            
            // Step 6: Add loyalty points (User DB) - eventual
            await this.executeStep({
                name: 'add_loyalty_points',
                execute: async () => {
                    return await userDb.addLoyaltyPoints(
                        orderRequest.userId,
                        calculatePoints(orderRequest.total)
                    );
                },
                compensate: async () => {
                    await userDb.deductLoyaltyPoints(
                        orderRequest.userId,
                        calculatePoints(orderRequest.total)
                    );
                },
            });
            
            return { success: true, orderId: order.id };
            
        } catch (error) {
            // Saga failed - compensate all completed steps in reverse
            await this.rollback();
            throw new SagaFailedError(error, this.completedSteps);
        }
    }
    
    private async executeStep<T>(step: SagaStep<T>): Promise<T> {
        const result = await step.execute();
        this.completedSteps.push(step);
        return result;
    }
    
    private async rollback(): Promise<void> {
        // Compensate in reverse order
        for (const step of this.completedSteps.reverse()) {
            try {
                await step.compensate();
            } catch (compensationError) {
                // Log and alert - compensation failure needs manual intervention
                console.error(
                    `Compensation failed for ${step.name}`,
                    compensationError
                );
            }
        }
    }
}

Saga Pattern Considerations

•Eventual Consistency — During saga execution, the system is in an inconsistent state. Inventory may be reserved but order not yet created. Design UIs and APIs to handle intermediate states.
•Compensation Failures — What if a compensation step fails? You need idempotent compensations, retry logic, and ultimately human intervention for stuck sagas.
•Ordering and Visibility — Other transactions may see partial state. A user might see 'order created' before payment is confirmed.
•Complexity — Sagas add significant complexity. Each operation needs forward and backward logic. Testing all failure paths is challenging.

Migration Strategies

Migrating from a monolithic database to a partitioned architecture is a high-risk operation. The key is incremental, reversible migration with extensive validation at each step.

The Strangler Fig Pattern

Inspired by strangler fig plants that gradually envelop trees, this pattern gradually migrates functionality to new databases while maintaining the monolith:

Create new partition database — Set up the new database with the target schema
Dual-write — Write to both old and new databases
Validate consistency — Compare data between databases regularly
Migrate reads — Gradually shift read traffic to new database
Cut over writes — Stop writing to old location
Deprecate old tables — Archive and eventually drop old tables

dual_write_migration.ts
TypeScript
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
/**
 * Dual-write service for gradual database migration
 * 
 * During migration, writes go to both old and new databases.
 * Reads can be toggled between sources for validation.
 */
 
interface MigrationConfig {
    enableDualWrite: boolean;
    readFromNew: boolean;  // Feature flag for gradual cutover
    validateOnRead: boolean;
}
 
class DualWriteUserService {
    constructor(
        private legacyDb: LegacyMonolithDatabase,
        private newUserDb: UserDatabase,
        private config: MigrationConfig
    ) {}
    
    async createUser(userData: CreateUserInput): Promise<User> {
        // Always write to legacy first (source of truth during migration)
        const legacyUser = await this.legacyDb.users.create(userData);
        
        if (this.config.enableDualWrite) {
            try {
                // Write to new database
                await this.newUserDb.createUser({
                    ...userData,
                    id: legacyUser.id,  // Use same ID for correlation
                });
            } catch (error) {
                // Log but don't fail - new DB is not source of truth yet
                console.error('Dual-write to new DB failed', error);
                await this.alertOps('dual_write_failure', {
                    userId: legacyUser.id,
                    error,
                });
            }
        }
        
        return legacyUser;
    }
    
    async updateUser(userId: string, updates: UserUpdates): Promise<User> {
        // Update legacy first
        const updatedUser = await this.legacyDb.users.update(userId, updates);
        
        if (this.config.enableDualWrite) {
            try {
                await this.newUserDb.updateUser(userId, updates);
            } catch (error) {
                console.error('Dual-write update failed', error);
                // Queue for reconciliation
                await this.queueReconciliation(userId);
            }
        }
        
        return updatedUser;
    }
    
    async getUser(userId: string): Promise<User> {
        if (this.config.readFromNew) {
            const newUser = await this.newUserDb.getUser(userId);
            
            if (this.config.validateOnRead) {
                // Shadow validation: compare with legacy
                const legacyUser = await this.legacyDb.users.findById(userId);
                await this.validateConsistency(legacyUser, newUser);
            }
            
            return newUser;
        } else {
            return await this.legacyDb.users.findById(userId);
        }
    }
    
    private async validateConsistency(
        legacy: User | null,
        newDb: User | null
    ): Promise<void> {
        if (!legacy && !newDb) return;
        
        if (!legacy || !newDb || !this.deepEquals(legacy, newDb)) {
            await this.logInconsistency({
                legacy,
                newDb,
                timestamp: new Date(),
            });
        }
    }
    
    private deepEquals(a: User, b: User): boolean {
        // Compare relevant fields
        return a.id === b.id &&
               a.email === b.email &&
               a.name === b.name &&
               // ... other fields
               true;
    }
}
 
// Reconciliation job for fixing inconsistencies
async function reconcileUserData(
    legacyDb: LegacyMonolithDatabase,
    newDb: UserDatabase
): Promise<ReconciliationReport> {
    const inconsistencies: Inconsistency[] = [];
    
    // Iterate through legacy records
    const cursor = legacyDb.users.cursor();
    
    for await (const legacyUser of cursor) {
        const newUser = await newDb.getUser(legacyUser.id);
        
        if (!newUser) {
            // Missing in new DB
            await newDb.createUser(legacyUser);
            inconsistencies.push({
                type: 'missing',
                id: legacyUser.id,
                resolution: 'created',
            });
        } else if (!deepEquals(legacyUser, newUser)) {
            // Data mismatch - legacy is source of truth
            await newDb.updateUser(legacyUser.id, legacyUser);
            inconsistencies.push({
                type: 'mismatch',
                id: legacyUser.id,
                resolution: 'updated_from_legacy',
            });
        }
    }
    
    return { inconsistencies, total: cursor.count };
}

Feature Flags Are Essential

Use feature flags to control every stage of migration. The ability to instantly roll back read traffic to the legacy database—without a deployment—is critical when issues arise. Expect issues; plan for instant rollback.

Operational Considerations

Operating partitioned databases introduces new operational challenges compared to a single database.

Connection Management

Applications now need connections to multiple databases. This compounds connection pool management:

A service talking to 5 partitions, with 10 connections each = 50 connections
With 10 application instances = 500 database connections total
Some partitions may be starved while others are over-allocated

Strategies:

Use connection poolers (PgBouncer, ProxySQL) per partition
Implement lazy connection acquisition—only connect when needed
Monitor connection utilization and alert on imbalances

Monitoring and Observability

With multiple databases, you need:

Per-partition metrics: Query performance, replication lag, storage utilization
Cross-partition correlation: Trace IDs that span partitions for request tracing
Aggregate dashboards: Overall system health view across all partitions

Operational Challenges

•Multiple backup schedules and retention policies
•Schema migration coordination across partitions
•More databases to patch and upgrade
•Increased total storage cost (some duplication)
•More complex disaster recovery planning
•Connection pool management at scale

Operational Benefits

•Smaller backups per partition (faster restore)
•Independent maintenance windows
•Failures isolated to affected partition
•Teams can optimize their partition independently
•Technology choices per use case
•Easier capacity planning per domain

Schema Management

With a monolithic database, one migration tool manages everything. With partitions, you have options:

Unified schema management: One tool (e.g., Flyway, Alembic) managing all partitions. Simple but tightly couples partitions.
Per-partition schema management: Each partition has its own migrations repo and deployment pipeline. More operational overhead but complete independence.
Hybrid approach: Common libraries for shared patterns; partition-specific migrations for domain logic.

For most organizations, per-partition management aligns better with the team autonomy goals of partitioning.

When to Use Functional Partitioning

Functional partitioning isn't always the right choice. Use these criteria to evaluate:

Good Candidates for Functional Partitioning

•Clear domain boundaries exist — Your business naturally decomposes into distinct areas with limited cross-domain queries.
•Different scaling needs per domain — One area has 10x the read volume or storage needs of others.
•Team structure matches domains — Different teams own different domains and want autonomy.
•Independent deployment desired — Teams want to evolve their data model without coordinating with others.
•Technology diversity needed — One domain benefits from specialized storage (time-series, graph, search) while others need traditional SQL.
•Failure isolation is critical — The business can tolerate degraded functionality rather than complete outage.

Poor Candidates for Functional Partitioning

•Highly interconnected data model — Every table references every other. Cross-partition queries would dominate.
•Strong ACID requirements across domains — Business logic requires atomic operations spanning what would be partition boundaries.
•Small team / single service — The operational complexity isn't justified for a small team managing everything.
•Read-heavy with complex aggregations — Analytical queries joining all domain data are common. Partitioning would break these.
•Regulatory requirements for unified audit — Compliance may require transactional consistency across all data.

The Principal Engineer's Perspective

Functional partitioning is a sociotechnical decision as much as a technical one. If your organization has clear domain teams who want autonomy, partitioning enables that. If you have one team managing everything, the coordination overhead may exceed the benefits. Match your database architecture to your organization structure—Conway's Law applies to databases too.

Summary: Functional Partitioning

Let's consolidate the key insights from our exploration of functional partitioning:

Key Takeaways

•Partition by domain, not by row — Functional partitioning separates databases by business function, keeping tables intact but isolated.
•Boundaries should align with bounded contexts — Use DDD principles and query pattern analysis to identify natural partition points.
•Cross-partition queries require application logic — JOINs across partitions don't work. Use application-level joins, denormalization, or CQRS patterns.
•Cross-partition transactions need sagas — Replace ACID transactions with saga patterns and compensating transactions.
•Migrate incrementally with dual-writes — Use the strangler fig pattern with extensive validation before cutting over.
•Operational complexity increases — More databases mean more to monitor, backup, patch, and coordinate. Ensure the benefits justify this cost.

What's Next:

When even functional partitioning isn't sufficient—typically when a single partition's table grows beyond what one database can handle—you need to split tables horizontally. This is application-level sharding, the most powerful but also most complex scaling strategy for SQL databases.

Page Complete

You now understand functional partitioning—dividing databases by domain to achieve independent scaling, failure isolation, and team autonomy. This strategy bridges the gap between simple vertical scaling and the complexity of full sharding.