Scaling and Optimizing Databases for Microservices: Challenges and Solutions

Introduction

In the digital transformation era, banking systems increasingly rely on microservice-based architectures to ensure flexibility, scalability, and independent development of individual services. However, this approach comes with certain challenges in terms of databases, such as horizontal scaling of stateful services, diverse data requirements of microservices, and choosing the optimal configuration. This article analyzes these problems and offers solutions, best practices, and specific examples.

Problem 1: Difficulty in horizontal scaling of stateful applications

Banking products, such as online banking services, transaction processing, or analytical modules, often utilize traditional relational databases (SQL). These databases are stateful services, which have limitations in vertical scaling (increasing resources) and are quite complex to scale horizontally (distributing the service across multiple servers) while maintaining data consistency.

Solution: Data partitioning (sharding) and federated databases

An effective approach is to divide the database into parts, known as sharding logically. In the banking domain, this can be done based on business units (e.g., retail, corporate), geographical areas, or other criteria. For example, consider an online banking platform where account data is "sharded" based on the last digit of the user ID:

public async Task<Account> GetAccount(int userId)
{
    var shardId = userId % 10;
    using (var connection = new SqlConnection($"ConnectionString_Shard_{shardId}"))
    {
        // Retrieve account from the specific shard
        // ...
    }
}        

In SQL Server, the Federated Database concept is used for partitioning. Each federated database represents a separate shard, containing a subset of data. Federated databases are created using the following syntax:

USE MasterDB
GO

CREATE FEDERATION CustomerFederation
(CustID BIGINT RANGE)
GO        

Next, tables are created in each federated database to store the sharded data. For example:

USE CustomerDB
GO

CREATE TABLE Customers
(
  CustID BIGINT NOT NULL,
  Name NVARCHAR(50),
  Email NVARCHAR(100),
  ...
) FEDERATED ON (CustomerFederation = CustID)        

When inserting data, the FEDERATION_FILTERING=ON parameter is used for automatic routing to the appropriate federated database. When retrieving data, the key (CustID) and the BETWEEN filter operator should be specified for the SQL Server to route the request.

Problem 2: Diverse data requirements of microservices

Banking microservices, such as account management, transactions, and loans, have specific requirements for data structure, access type, and consistency. For example, account balance management requires atomic operations (ACID), while analytical services need fast access to denormalized data. Therefore, one type of database cannot satisfy all requirements.

Solution: Polyglot persistence approach

To address this problem, it is advisable to use "polyglot persistence" - involving different types of databases within one application, based on the requirements of individual microservices. For example, an online payment service will rely on an SQL database, while a deposit analytics module may utilize a NoSQL database (e.g., MongoDB).

For this, an interface for different types of repositories can be created:

public interface ITransactionRepository
{
    Task<Transaction> GetTransaction(int transactionId);
    Task SaveTransaction(Transaction transaction);
}        

And separate implementations for SQL and NoSQL:

public class SqlTransactionRepository : ITransactionRepository 
{
    public async Task<Transaction> GetTransaction(int transactionId)
    {
        using (var connection = new SqlConnection("SqlConnectionString"))
        {
            // Retrieve from SQL
            // ...
        }
    }

    public async Task SaveTransaction(Transaction transaction)
    {
        using (var connection = new SqlConnection("SqlConnectionString"))
        {
            // Save to SQL
            // ...
        }
    }
}

public class MongoTransactionRepository : ITransactionRepository
{
    public async Task<Transaction> GetTransaction(int transactionId)
    {
        var client = new MongoClient("MongoConnectionString");
        // Retrieve from Mongo
        // ...
    }

    public async Task SaveTransaction(Transaction transaction)
    {
        var client = new MongoClient("MongoConnectionString");
        // Save to Mongo  
        // ...
    }
}        

The required repository is initialized via Dependency Injection in the service configuration.

Problem 3: Incorrect determination of database requirements

Often, at the beginning of a project, it is difficult to determine the requirements of each microservice for the database accurately. As a result, the selection and configuration of the database may be suboptimal, leading to performance issues.

Solution: Requirements analysis considering best practices

When designing a microservice, it is essential to perform a detailed analysis of the data model, transactionality, and scalability requirements. Factors to consider include:

- Data structure (e.g., relational, document-oriented, graph)

- Transaction isolation level (e.g., Serializable, Read Committed)

- Consistency requirements (e.g., Strong vs Eventual consistency)

- Load profile (read-heavy or write-heavy)

- Expected data volume and growth dynamics

For example, a microservice for fast transaction processing, such as a payment system, requires an SQL database to ensure transactional integrity (ACID), while the Know Your Customer (KYC) component of the same application may be better suited for a document-oriented NoSQL database to store user profiles.

Problem 4: Data synchronization between microservices

In a microservice architecture, data changes in one service often affect other dependent services. Disruption of data synchronization can lead to an inconsistent state.

For example, in a banking service system, updating a client's contact information in the account management module should also be reflected in the notification sending module, so that the client receives relevant notifications.

Solution: Event-driven architecture and Change Data Capture

To solve this problem, we can implement an event-driven architecture, where data changes are reflected as "domain events" and propagated to interested services.

Events can be published via a message broker (e.g., Apache Kafka, RabbitMQ), to which other services are subscribed. For example:

public async Task UpdateCustomerEmail(int customerId, string newEmail)
{
    // Update email in the database
    // ...

    // Publish CustomerEmailUpdated event
    var emailUpdatedEvent = new CustomerEmailUpdated
    {
        CustomerId = customerId, 
        NewEmail = newEmail
    };

    await _messagePublisher.PublishAsync("customer-events", emailUpdatedEvent);
}        

The notification-sending microservice will subscribe to this channel and receive the event about the email change.

Another approach is to use the Change Data Capture (CDC) mechanism, which tracks changes in the database and transmits them to the relevant microservices. CDC can be built on table triggers or transaction log scanning. This method is particularly effective in read-heavy systems, as it avoids additional load associated with write operations.

For example, in SQL Server, we can create CDC functionality for a specific table:

USE MyDatabase;
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Customers', 
@role_name     = N'cdc_role',  
@supports_net_changes = 1;
GO        

Then, a microservice subscribed to CDC data will periodically check for changes and react accordingly:

public async Task ProcessCustomerChanges()
{
    using (var connection = new SqlConnection("MyCDCConnectionString"))
    {
        connection.Open();

        using (var command = new SqlCommand("SELECT * FROM cdc.dbo_Customers_CT", connection))
        using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                var operation = (string)reader["__$operation"];
                var customerId = (int)reader["CustomerID"];
                
                switch (operation)
                {
                    case "INSERT":
                    case "UPDATE":
                        // Sync changes to the dependent microservice
                        // ...
                        break;
                    case "DELETE":
                        // Handle deletion
                        // ...
                        break;
                }
            }
        }
    }
}        

Problem 5: Data duplication and anomalies

Microservices often need to store a portion of data that "belongs" to another service. This is a kind of compromise between application autonomy and data consistency. Data duplication can cause anomalies and complicate system synchronization.

For example, in an online banking system, customer details such as name and address may be required in many microservices - from profile management to loan applications. Keeping this data in sync is challenging.

Solution: Using APIs between services and a unified data model

A possible solution is to create a separate "profiles" microservice responsible for the client's core data. All other services will retrieve this information via API and store it locally only for transient purposes.

The data model (schema) should be unified and shared by the services to avoid duplication. For this, we can use a schema registry to store all versions of the data structure in Protobuf or AVRO format.

Retrieving customer details from the profiles API:

public async Task<CustomerDetails> GetCustomerDetails(int customerId)
{
    var client = new HttpClient();
    var response = await client.GetAsync($"https://profiles-service/api/customers/{customerId}");
    
    if (response.IsSuccessStatusCode)
    {
        var customerDetails = await response.Content.ReadAsAsync<CustomerDetails>();
        return customerDetails;
    }
    else
    {
        // Handle error
        // ...
    }
}        

Additionally, to ensure eventual data consistency, we can implement the Eventual Consistency pattern, where temporary anomalies are allowed and gradually eliminated by a background process.

Conclusion

Proper design and management of databases for banking microservices is crucial for application scalability and stability. This requires implementing practices such as:

- Data partitioning (sharding) and federated databases for horizontal scalability

- Deep analysis of business requirements and a polyglot persistence approach with mixed types of databases

- Event-driven architecture and Change Data Capture for data synchronization

- Introducing API interfaces between services and a unified data model to avoid duplication

Selecting and adapting these strategies for individual business scenarios is essential for the smooth functioning of the service. This is facilitated by continuous monitoring and optimization of application and infrastructure development.

Ultimately, adopting modern techniques for storing and managing data in banking microservices ensures system reliability, flexibility, full compliance with business requirements, and market competitiveness. This is achieved through a deep understanding of architectural and infrastructural challenges and an adequate response to them, which this article will help with.