The correct order of database scaling steps, and why sharding is last

Scaling steps by cost and complexity:

 Step  | Infrastructure Cost | Code Change | Reversible? | Risk
-------|--------------------|--------------|--------------|---------
 Index | Zero               | None         | Yes          | None
 Scale | Hardware cost      | None         | Yes          | Low
 Replica | Moderate         | Routing      | Yes          | Low
 Cache | Moderate           | Cache logic  | Yes          | Medium
 CQRS  | Significant        | Architecture | Hard         | Medium
 Shard | Very high          | Everything   | No           | High

-- Before: sequential scan on 10 million rows
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE account_id = 'SAV2001';
-- Seq Scan: 8000ms

-- Fix: add index
CREATE INDEX idx_txn_account ON transactions(account_id);

-- After: index scan
-- Index Scan: 0.5ms (16,000x faster)

package com.bank.maintenance;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class IndexOptimizationService {

    private final DataSource dataSource;

    public IndexOptimizationService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void addMissingIndex() throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE INDEX CONCURRENTLY idx_txn_acct_date "
                    + "ON transactions(account_id, created_at)");
            System.out.println("Index created without locking the table");
        }
    }
}

package com.bank.config;

import javax.sql.DataSource;

public class ReadWriteRoutingConfig {

    private final DataSource primaryDataSource;  // For writes
    private final DataSource replicaDataSource;  // For reads

    public ReadWriteRoutingConfig(DataSource primary, DataSource replica) {
        this.primaryDataSource = primary;
        this.replicaDataSource = replica;
    }

    // Write operations go to primary
    public DataSource getWriteDataSource() {
        return primaryDataSource;
    }

    // Read operations go to replica
    // Caveat: replication lag means reads may be slightly stale
    public DataSource getReadDataSource() {
        return replicaDataSource;
    }
}

package com.bank.cache;

import redis.clients.jedis.Jedis;
import java.math.BigDecimal;

public class BalanceCacheService {

    private final Jedis redis;

    public BalanceCacheService(Jedis redis) {
        this.redis = redis;
    }

    public BigDecimal getBalance(String accountId) {
        String cached = redis.get("balance:" + accountId);
        if (cached != null) {
            return new BigDecimal(cached);  // Cache hit: microseconds
        }
        return null;  // Cache miss: fall through to database
    }

    public void cacheBalance(String accountId, BigDecimal balance) {
        redis.setex("balance:" + accountId, 60, balance.toPlainString());
    }

    public void invalidateBalance(String accountId) {
        redis.del("balance:" + accountId);  // Invalidate on write
    }
}

package com.bank.cqrs;

import java.math.BigDecimal;

// Command side: writes to PostgreSQL (ACID)
public class TransferCommandHandler {
    public void handle(String from, String to, BigDecimal amount) {
        // Debit and credit in a single ACID transaction
        // Publish event: TransferCompleted
    }
}

// Query side: reads from a denormalized read store
public class AccountQueryHandler {
    public BigDecimal getBalance(String accountId) {
        // Read from a denormalized view or separate read database
        // Optimized for the specific query pattern
        return BigDecimal.ZERO;
    }
}

package com.bank.sharding;

public class ShardingChallenges {

    // 1. Cross-shard joins are gone
    // "Show total balance across all accounts" now requires
    // fan-out to every shard and application-side aggregation

    // 2. Cross-shard transactions need 2PC or Sagas
    // Transfer between accounts on different shards:
    // BEGIN on Shard A: debit SAV2001
    // BEGIN on Shard B: credit SAV2002
    // Both must commit or both roll back → 2PC or Saga

    // 3. Choosing a bad shard key creates hot spots
    // Shard by city? Mumbai gets 40% of traffic
    // Re-sharding moves enormous amounts of data

    // 4. Global unique constraints and auto-increment IDs break
    // Need distributed ID generation (Snowflake, UUIDv7)

    // 5. Schema migrations must be coordinated across all shards

    // 6. Operationally: N databases to monitor, backup, and maintain

    // 7. It is effectively a one-way door: un-sharding is a major project
}

Scaling dimension:

  Step           | Scales Reads | Scales Writes
  Index          | Yes          | Yes (less I/O)
  Scale up       | Yes          | Yes
  Replica        | Yes          | No (primary only)
  Cache          | Yes          | No
  CQRS           | Yes          | Indirectly
  Shard          | Yes          | YES (main purpose)

Sharding becomes necessary specifically when:
  - Write throughput exceeds a single node
  - Raw data volume exceeds a single node's storage
  - NOT when reads are the bottleneck (replicas + cache solve that)