How would you design a scalable database, and what challenges do you foresee

-- Before scaling: fix the slow query
-- Without index: full table scan on 100M rows
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
  AND txn_date >= '2024-01-01';
-- Seq Scan: 45 seconds

-- Add a composite index
CREATE INDEX idx_txn_account_date
ON transactions (account_id, txn_date);

-- With index: milliseconds
-- Index Scan: 3 ms

package com.bank.config;

import javax.sql.DataSource;

public class ScalableDataSourceConfig {

    private final DataSource primaryDs;   // writes
    private final DataSource replicaDs;   // reads

    public ScalableDataSourceConfig(DataSource primary, DataSource replica) {
        this.primaryDs = primary;
        this.replicaDs = replica;
    }

    public DataSource forWrite() { return primaryDs; }
    public DataSource forRead()  { return replicaDs; }
}

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;

public class CachedBalanceService {

    private static final Logger LOG = Logger.getLogger(
        CachedBalanceService.class.getName());

    private final Connection dbConnection;
    // Simulated Redis cache
    private final java.util.Map<String, BigDecimal> cache =
        new java.util.concurrent.ConcurrentHashMap<>();

    public CachedBalanceService(Connection dbConnection) {
        this.dbConnection = dbConnection;
    }

    public BigDecimal getBalance(String accountId) throws SQLException {
        // Check cache first
        BigDecimal cached = cache.get(accountId);
        if (cached != null) {
            LOG.fine("Cache hit for " + accountId);
            return cached;
        }

        // Cache miss: read from database
        String sql = "SELECT balance FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = dbConnection.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    BigDecimal balance = rs.getBigDecimal("balance");
                    cache.put(accountId, balance);  // cache-aside
                    return balance;
                }
            }
        }
        throw new SQLException("Account not found: " + accountId);
    }

    public void invalidateOnWrite(String accountId) {
        cache.remove(accountId);  // invalidate on write
    }
}

-- Single-shard query (fast): account_id is the shard key
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
-- Routed to Shard 1 only

-- Cross-shard query (slow): no shard key in WHERE
SELECT SUM(balance) FROM accounts WHERE status = 'ACTIVE';
-- Must fan out to ALL shards and merge results

Good shard key: account_id
  - Spreads customers evenly across shards
  - One customer's data lives on one shard
  - Common query (balance by account) hits one shard

Bad shard key: created_at (timestamp)
  - All new inserts go to one shard (latest timestamp)
  - Hot spot: one shard handles all writes
  - Other shards are idle

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.List;
import java.util.logging.Logger;

public class CrossShardQueryService {

    private static final Logger LOG = Logger.getLogger(
        CrossShardQueryService.class.getName());

    private final List<Connection> shardConnections;

    public CrossShardQueryService(List<Connection> shardConnections) {
        this.shardConnections = shardConnections;
    }

    public BigDecimal totalDepositsAcrossShards() throws SQLException {
        BigDecimal total = BigDecimal.ZERO;
        String sql = "SELECT SUM(balance) AS shard_total FROM accounts "
                   + "WHERE status = 'ACTIVE'";

        for (Connection conn : shardConnections) {
            try (PreparedStatement ps = conn.prepareStatement(sql);
                 ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    BigDecimal shardTotal = rs.getBigDecimal("shard_total");
                    if (shardTotal != null) {
                        total = total.add(shardTotal);
                    }
                }
            }
        }
        LOG.info("Total deposits across " + shardConnections.size()
            + " shards: " + total);
        return total;
    }
}

package com.bank.model;

import java.util.UUID;
import java.time.Instant;

public class DistributedIdGenerator {

    /**
     * Generate a time-ordered UUID (UUIDv7-like)
     * that is globally unique across shards.
     */
    public static String generateId() {
        long timestamp = Instant.now().toEpochMilli();
        String uuid = UUID.randomUUID().toString();
        // Prefix with timestamp for ordering
        return String.format("%013d-%s", timestamp, uuid.substring(0, 8));
    }
}