From Monolith to Microservices: Choosing the Right Database Strategy

Running Kubernetes for your business workloads offers a cloud-agnostic, highly reliable, and consistent workflow, regardless of your choice of cloud provider or technology stack. Over time, we’ve observed an increasing number of tools being integrated into Kubernetes, including CI tools like Tekton, CD tools like Argo CD, monitoring tools like Grafana and Prometheus, Hadoop for processing large datasets, and MinIO for storing vast amounts of assets in an S3 API-compatible manner. The shift towards Kubernetes isn’t limited to stateless applications such as web frontends, API backends, and reverse proxies. While I’m a proponent of Kubernetes as a comprehensive platform for running workloads, I have reservations about hosting one specific type of workload: production databases.

To clarify, I am not opposed to running databases in Kubernetes for ephemeral environments or classic stages like development or QA. I also acknowledge that hosting a database in Kubernetes is entirely feasible and sometimes necessary due to regulations or contractual agreements. However, I contend that using Kubernetes to run your production database is not the best choice in most cases.

It is indeed possible to run databases like PostgreSQL, MySQL, SQL Server, Oracle, or MongoDB in a Kubernetes cluster. But the question is, should you? Based on my experience, utilizing a cloud-managed service often accelerates time to market, as it eliminates the need to set up the database infrastructure before focusing on the actual work, such as writing code for business requirements. So, why spend time on initial setup and long-term maintenance when you can delegate the responsibility of managing such critical resources to a cloud provider who has more expertise in handling these than a typical engineer who just uses Kubernetes and databases?

With many cloud companies adopting open-source API versions for their database services, vendor lock-in becomes less of a concern. The services often work similarly and use the same open-source database engines, ensuring compatibility and flexibility.

When managing and orchestrating a new greenfield project, I recommend using two Kubernetes clusters: one for development (dev) and one for production (prod). The dev cluster can host all ephemeral environments for QA and development, including stateless apps and databases, as well as CI/CD tooling for deploying stateless apps to the production cluster using tools like Tekton or Argo CD. In the production cluster, only the latest versions of the stateless containers should run, and a cloud-managed database should be utilized, regardless of the specific database tool chosen.

It’s also essential to have a migration tool for running migrations on the production database. The choice of tool depends on the database, and options include using jobs and config maps, Schema Hero, Atlas Operator, or init containers. However, unless a relational database is necessary, I recommend avoiding it in a microservice architecture due to the increased complexity in ensuring high availability during releases, rollbacks, and blue-green deployments. While NoSQL databases are not suitable for every scenario, if you need to query your NoSQL database as if it were a relational database, consider setting up a pipeline or using a tool to move data from your NoSQL database to a relational database solely used for querying. For example, you could use Debezium to capture changes from your MongoDB and then use Kafka consumers to save that data in a PostgreSQL or MySQL database for querying.

What if I cannot Utilize a NO-SQL Database?

For making migrations to a relational database in a microservice suite, the approach depends on the situation. Schema Hero is a powerful tool that allows you to manage your schema in a YAML file, enabling GitOps-style management:

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: airport
  namespace: schemahero-tutorial
spec:
  database: airlinedb
  name: airport
  schema:
    postgres:
      primaryKey: [code]
      columns:
        - name: code
          type: char(4)
        - name: name
          type: varchar(255)
          constraints:
            notNull: true

This is really cool and powerful for those using Postgres, Mysql, Cassandra, Cockroach Db, or Sqlite. However, Schema Hero supports only a limited number of databases, excluding notable ones like Oracle, SQL Server, MariaDB, and H2.

Another option is the Atlas Kubernetes Operator, an open-source DB migration tool that allows you to define migrations in a declarative format:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: myapp
spec:
  urlFrom:
    secretKeyRef:
      key: url
      name: mysql-credentials
  schema:
    sql: |
      create table users (
        id int not null auto_increment,
        name varchar(255) not null,
        email varchar(255) unique not null,
        short_bio varchar(255) not null,
        primary key (id)
      );
  policy:
    lint:
      destructive:
        error: true
    diff:
      skip:
        drop_column: true
  exclude:
    - external_table_managed_elsewhere

Alternatively, you can use a versioned approach for migrations, as shown in this example:

apiVersion: v1
kind: ConfigMap
metadata:
  name: migration-dir
data:
  20230316085611.sql: |
    create table users (
      id int not null auto_increment,
      name varchar(255) not null,
      email varchar(255) unique not null,
      short_bio varchar(255) not null,
      primary key (id)
    );
  20230316085712.sql: |
    alter table users add column age int;
  atlas.sum: |
    h1:sPURLhQRvLU79Dnlaw3aiU4KVkyUVEmW+ekenqu/V2o=
    20230316085611.sql h1:FKUFrD9E4ceSeBZ5owv2c05Ag8rokXGKXp53ZctbocE=
    20230316085712.sql h1:KoUFrE9F5deTeCZ6pwv3d15Bg9rokYGKZp64ZdtcocF=

The corresponding Atlas Migration YAML file would look like this:

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasMigration
metadata:
  name: atlasmigration-sample
spec:
  urlFrom:
    secretKeyRef:
      key: url
      name: db-credentials
  dir:
    configMapRef:
      name: "migration-dir" # ConfigMap name

If you’re looking for a way to perform database migrations in Kubernetes without learning new tooling, you can use Kubernetes config maps and jobs.

To demonstrate, I’ll set up a PostgreSQL cluster in my local Kubernetes cluster on my workstation. You can deploy it wherever you prefer, but if you’re using an external cloud provider, you’ll need to create a secret object with the database password and username. I’m using Helm to quickly create a local database:

helm repo add bitnami https://charts.bitnami.com/bitnami
helm install my-postgresql bitnami/postgresql --version 14.2.3

Next, I used Rancher to create a config map containing the SQL script I wanted to run on my PostgreSQL database:

apiVersion: v1
kind: ConfigMap
metadata:
  name: db-migration-script
  namespace: postgres-statefulset-onepod
data:
  create-db.sql: |
    CREATE DATABASE my_new_database;

I then created a job that used the config map with the SQL script and the PostgreSQL database secret created by the Helm chart:

apiVersion: batch/v1
kind: Job
metadata:
  name: db-migration-job
  namespace: postgres-statefulset-onepod
spec:
  template:
    spec:
      containers:
      - name: db-migration
        image: postgres:latest
        command: ["psql", "-h", "my-postgresql.postgres-statefulset-onepod.svc.cluster.local", "-U", "postgres", "-d", "postgres", "-a", "-f", "/scripts/create-db.sql"]
        volumeMounts:
        - name: script-volume
          mountPath: /scripts
        env:
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: my-postgresql
              key: postgres-password
      volumes:
      - name: script-volume
        configMap:
          name: db-migration-script
      restartPolicy: Never
  backoffLimit: 4

After applying this YAML file to the cluster, I confirmed the migration was successful using pgAdmin.

You can modify this setup to run multiple scripts per config map and execute multiple config maps in a single job:

apiVersion: v1
kind: ConfigMap
metadata:
  name: db-migration-scripts
  namespace: postgres-statefulset-onepod
data:
  create-db.sql: |
    CREATE DATABASE my_new_database;
  create-table.sql: |
    CREATE TABLE example_table (
      id SERIAL PRIMARY KEY,
      name VARCHAR(50)
    );
  insert-data.sql: |
    INSERT INTO example_table (name) VALUES ('Sample Data');

And in the job, you can utilize multiple scripts from the config map and even use another config map for better separation:

apiVersion: batch/v1
kind: Job
metadata:
  name: db-migration-job
  namespace: postgres-statefulset-onepod
spec:
  template:
    spec:
      containers:
      - name: db-migration
        image: postgres:latest
        command: ["/bin/bash", "-c"]
        args:
          - |
            for script in /scripts1/*.sql /scripts2/*.sql; do
              psql -h my-postgresql.postgres-statefulset-onepod.svc.cluster.local -U postgres -d postgres -a -f "$script";
            done
        volumeMounts:
        - name: script-volume1
          mountPath: /scripts1
        - name: script-volume2
          mountPath: /scripts2
        env:
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: my-postgresql
              key: postgres-password
      volumes:
      - name: script-volume1
        configMap:
          name: db-migration-script1
      - name: script-volume2
        configMap:
          name: db-migration-script2
      restartPolicy: Never
  backoffLimit: 4

While this approach is not the best and I still recommend using tools like Schema Hero or Atlas Kubernetes Operator, it’s a viable option for those who can’t use either or simply don’t want to.

Database management in the context of horizontally scaled applications requires careful consideration, especially when it comes to migrations. With horizontally scaled stateless apps that connect to a database, you have two options:

• Accept downtime and errors every time you perform a migration and application update.
• Ensure your schema is backward compatible.

Let’s explore the implications of each option.

If you have new code updates for a microservice, let’s call it the “XYZ” microservice, and it requires migrations to run against its SQL Server database, this could be problematic. If the application must always be available, there will be a window of time during which the database is locked, and the old code may attempt to reference outdated columns, causing errors. These errors will persist until the rollout of the new version of the XYZ microservice is complete.

Alternatively, if you roll out the XYZ microservice updates first and then perform the migrations, you’ll encounter similar issues but in reverse order. The microservice may try to reference new columns that haven’t yet been applied to the database.

A workaround to these challenges is to maintain backward compatibility in your schema. For example, if you need to change a column name from “car” to “carName,” don’t delete the old column. Instead, keep both columns and gradually transition from one to the other, eventually removing the old column long after its creation. This strategy also means that if you’re adding a new column and want to maintain high availability, the migration scripts must be run before the new application rollout.

While there are many other topics to cover, such as incremental column migrations and various high availability strategies for managing databases in a microservice architecture, I want to reiterate an earlier point: If your microservice suite does not require relational databases, consider opting for a NoSQL database like MongoDB, Redis, CouchDB, or ArangoDB as you will not have to think about schemas or backward compatible migrations at all. Avoid databases like DynamoDB or Cosmos DB NoSQL, as they can lock you into a specific cloud vendor.

While my advice is to use only what you need, it’s also true that for most development teams not running a microservice suite and opting for a monolithic architecture, a relational database is likely their best choice if they’re already experienced with it. Relational databases are often the preferred option for managing an application’s state, but they can also be the source of significant headaches during deployments and rollouts that require high availability. This issue isn’t specific to Kubernetes; it can be a problem if you have multiple virtual machines running a monolithic app that connects to the same database and you need high availability during a rollout of a new application update and schema migration. This challenge is present whether your application is monolithic or not. So, if you have a horizontally scaled stateless app that needs to be highly available and connects to your database, you’ll need to address the issue of maintaining high availability.

In Conclusion

Kubernetes has emerged as a powerful platform for managing business workloads, offering a cloud-agnostic and highly reliable workflow. While it excels in integrating a wide range of tools and supporting stateless applications, the decision to run production databases in Kubernetes requires careful consideration. For ephemeral environments and development stages, Kubernetes can be a viable option. However, for production databases, cloud-managed services often provide a more straightforward path to high availability and scalability.

When it comes to database management in a microservice architecture, the choice between relational and NoSQL databases depends on your specific needs and the expertise of your development team. While relational databases are a familiar choice for many, they can introduce challenges in deployments and rollouts. On the other hand, NoSQL databases can offer a more flexible and scalable solution for highly available applications.

Ultimately, the key to successful database management in Kubernetes lies in understanding the trade-offs and aligning your choices with the needs of your business and the capabilities of your team. Whether you choose to embrace Kubernetes for all aspects of your workload management or opt for a hybrid approach, staying informed and adaptable will be crucial in navigating the evolving landscape of cloud-native technologies.