Challenges of sharding

package com.bank.sharding;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;

public class CrossShardQueryService {

    private final List<DataSource> shardDataSources;

    public CrossShardQueryService(List<DataSource> shardDataSources) {
        this.shardDataSources = shardDataSources;
    }

    // "Total balance across ALL accounts" requires scatter-gather
    public BigDecimal getTotalBalanceAcrossAllShards() throws SQLException {
        BigDecimal total = BigDecimal.ZERO;
        for (DataSource ds : shardDataSources) {
            try (Connection conn = ds.getConnection();
                 PreparedStatement ps = conn.prepareStatement(
                         "SELECT SUM(balance) FROM accounts")) {
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next() && rs.getBigDecimal(1) != null) {
                        total = total.add(rs.getBigDecimal(1));
                    }
                }
            }
        }
        return total;  // Aggregated across all shards in application
    }

    // Mitigation: denormalize, co-locate related data on same shard,
    // or use a separate analytics/search store for cross-shard queries
}

package com.bank.sharding;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

public class CrossShardTransferService {

    private final DataSource shardA;
    private final DataSource shardB;

    public CrossShardTransferService(DataSource shardA, DataSource shardB) {
        this.shardA = shardA;
        this.shardB = shardB;
    }

    // Saga pattern: local transactions with compensating actions
    public void transferAcrossShards(String fromAccount, String toAccount,
                                      BigDecimal amount) throws SQLException {
        // Step 1: Debit on Shard A (local transaction)
        try (Connection connA = shardA.getConnection()) {
            connA.setAutoCommit(false);
            try {
                debit(connA, fromAccount, amount);
                connA.commit();
            } catch (SQLException e) {
                connA.rollback();
                throw new RuntimeException("Debit failed, no compensation needed", e);
            }
        }

        // Step 2: Credit on Shard B (local transaction)
        try (Connection connB = shardB.getConnection()) {
            connB.setAutoCommit(false);
            try {
                credit(connB, toAccount, amount);
                connB.commit();
            } catch (SQLException e) {
                connB.rollback();
                // Step 3: Compensate - re-credit Shard A
                compensateDebit(fromAccount, amount);
                throw new RuntimeException("Credit failed, debit compensated", e);
            }
        }
    }

    private void debit(Connection conn, String accountId, BigDecimal amount)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE account_id = ?")) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.executeUpdate();
        }
    }

    private void credit(Connection conn, String accountId, BigDecimal amount)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE account_id = ?")) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.executeUpdate();
        }
    }

    private void compensateDebit(String accountId, BigDecimal amount)
            throws SQLException {
        try (Connection connA = shardA.getConnection()) {
            connA.setAutoCommit(false);
            credit(connA, accountId, amount);  // Reverse the debit
            connA.commit();
        }
    }
}

package com.bank.sharding;

public class ReferentialIntegrityChallenges {

    // Single database:
    // FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    // Database enforces: no dangling references

    // Sharded:
    // accounts on Shard A, customers on Shard B
    // No foreign key enforcement across shards!
    // Application must verify references manually
    // Background reconciliation job checks for orphans

    // Mitigation: co-locate related data on the same shard
    // If customer + their accounts are on the same shard,
    // foreign key within that shard still works
}

package com.bank.sharding;

public class OperationalComplexity {

    // With 10 shards:
    // - 10 databases to monitor (disk, CPU, connections, replication lag)
    // - 10 separate backup schedules
    // - 10 sets of WAL archiving to verify
    // - 10 databases to patch and upgrade
    // - 10 databases to check for slow queries
    // - Configuration must be consistent across all 10
    // - Debugging: "which shard is this row on?"
    // - Capacity planning: each shard may grow differently

    // With 1 database:
    // - 1 of everything above
    // - Much simpler

    // The operational cost is permanent and grows with shard count
}

package com.bank.sharding;

public class DataCoLocation {

    // Co-locate customer + accounts + transactions on the same shard
    // Shard key: customer_id
    //
    // Customer C001: Shard 2
    //   Account SAV2001: Shard 2 (same shard)
    //   Transactions for SAV2001: Shard 2 (same shard)
    //
    // Query: "All of Rajesh's transactions" → single shard
    // Transfer between Rajesh's own accounts → single shard
    //
    // Transfer between Rajesh (Shard 2) and Priya (Shard 0)
    //   → cross-shard, needs Saga
    //   → but this is less common than within-customer operations
}