Exploring common patterns for refactoring relational database models as part of a microservices architecture

Introduction

There is no shortage of books, articles, tutorials, and presentations on migrating existing monolithic applications to microservices, nor designing new applications using a microservices architecture. It has been one of the most popular IT topics for the last several years. Unfortunately, monolithic architectures often have equally monolithic database models. As organizations evolve from monolithic to microservices architectures, refactoring the application’s database model is often overlooked or deprioritized. Similarly, as organizations develop new microservices-based applications, they frequently neglect to apply a similar strategy to their databases.

The following post will examine several basic patterns for refactoring relational databases for microservices-based applications.

Terminology

Monolithic Architecture

A monolithic architecture is “the traditional unified model for the design of a software program. Monolithic, in this context, means composed all in one piece.” (TechTarget). A monolithic application “has all or most of its functionality within a single process or container, and it’s componentized in internal layers or libraries” (Microsoft). A monolith is usually built, deployed, and upgraded as a single unit of code.

Microservices Architecture

A microservices architecture (aka microservices) refers to “an architectural style for developing applications. Microservices allow a large application to be separated into smaller independent parts, with each part having its own realm of responsibility” (Google Cloud).

According to microservices.io, the advantages of microservices include:

• Highly maintainable and testable
• Loosely coupled
• Independently deployable
• Organized around business capabilities
• Owned by a small team
• Enables rapid, frequent, and reliable delivery
• Allows an organization to [more easily] evolve its technology stack

Database

A database is “an organized collection of structured information, or data, typically stored electronically in a computer system” (Oracle). There are many types of databases. The most common database engines include relational, NoSQL, key-value, document, in-memory, graph, time series, wide column, and ledger.

PostgreSQL

In this post, we will use PostgreSQL (aka Postgres), a popular open-source object-relational database. A relational database is “a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Tables are used to hold information about the objects to be represented in the database” (AWS).

Amazon RDS for PostgreSQL

We will use the fully managed Amazon RDS for PostgreSQL in this post. Amazon RDS makes it easy to set up, operate, and scale PostgreSQL deployments in the cloud. With Amazon RDS, you can deploy scalable PostgreSQL deployments in minutes with cost-efficient and resizable hardware capacity. In addition, Amazon RDS offers multiple versions of PostgreSQL, including the latest version used for this post, 14.2.

The patterns discussed here are not specific to Amazon RDS for PostgreSQL. There are many options for using PostgreSQL on the public cloud or within your private data center. Alternately, you could choose Amazon Aurora PostgreSQL-Compatible Edition, Google Cloud’s Cloud SQL for PostgreSQL, Microsoft’s Azure Database for PostgreSQL, ElephantSQL, or your own self-manage PostgreSQL deployed to bare metal servers, virtual machine (VM), or container.

Database Refactoring Patterns

There are many ways in which a relational database, such as PostgreSQL, can be refactored to optimize efficiency in microservices-based application architectures. As stated earlier, a database is an organized collection of structured data. Therefore, most refactoring patterns reorganize the data to optimize for an organization’s functional requirements, such as database access efficiency, performance, resilience, security, compliance, and manageability.

The basic building block of Amazon RDS is the DB instance, where you create your databases. You choose the engine-specific characteristics of the DB instance when you create it, such as storage capacity, CPU, memory, and EC2 instance type on which the database server runs. A single Amazon RDS database instance can contain multiple databases. Those databases contain numerous object types, including tables, views, functions, procedures, and types. Tables and other object types are organized into schemas. These hierarchal constructs — instances, databases, schemas, and tables — can be arranged in different ways depending on the requirements of the database data producers and consumers.

Sample Database

To demonstrate different patterns, we need data. Specifically, we need a database with data. Conveniently, due to the popularity of PostgreSQL, there are many available sample databases, including the Pagila database. I have used it in many previous articles and demonstrations. The Pagila database is available for download from several sources.

The Pagila database represents a DVD rental business. The database is well-built, small, and adheres to a third normal form (3NF) database schema design. The Pagila database has many objects, including 1 schema, 15 tables, 1 trigger, 7 views, 8 functions, 1 domain, 1 type, 1 aggregate, and 13 sequences. Pagila’s tables contain between 2 and 16K rows.

Pattern 1: Single Schema

Pattern 1: Single Schema is one of the most basic database patterns. There is one database instance containing a single database. That database has a single schema containing all tables and other database objects.

As organizations begin to move from monolithic to microservices architectures, they often retain their monolithic database architecture for some time.

Frequently, the monolithic database’s data model is equally monolithic, lacking proper separation of concerns using simple database constructs such as schemas. The Pagila database is an example of this first pattern. The Pagila database has a single schema containing all database object types, including tables, functions, views, procedures, sequences, and triggers.

To create a copy of the Pagila database, we can use pg_restore to restore any of several publically available custom-format database archive files. If you already have the Pagila database running, simply create a copy with pg_dump.

	# set postgres environment variables
	# ** CHANGE ME **
	export PGHOST="postgres1.abcxyzdef.us-east-1.rds.amazonaws.com"
	export PGPORT=5432
	export PGDATABASE="postgres"
	export PGUSER="admin"
	export PGPASSWORD="change_me!"
	# create new v1 of pagila database
	export PGDATABASE="postgres"
	psql -c "CREATE DATABASE pagila_v1;"
	# restore original version of pagila database
	pg_restore -d pagila_v1 pagila.dump
	# confirm pagila tables in public schema
	export PGDATABASE="pagila_v1"
	psql -c "\dt"

view raw create_pagila_v1.sh hosted with ❤ by GitHub

Below we see the table layout of the Pagila database, which contains the single, default public schema.

-----------+----------+--------+------------
 Instance  | Database | Schema | Table
-----------+----------+--------+------------
 postgres1 | pagila   | public | actor
 postgres1 | pagila   | public | address
 postgres1 | pagila   | public | category
 postgres1 | pagila   | public | city 
 postgres1 | pagila   | public | country
 postgres1 | pagila   | public | customer
 postgres1 | pagila   | public | film 
 postgres1 | pagila   | public | film_actor
 postgres1 | pagila   | public | film_category
 postgres1 | pagila   | public | inventory
 postgres1 | pagila   | public | language
 postgres1 | pagila   | public | payment
 postgres1 | pagila   | public | rental
 postgres1 | pagila   | public | staff
 postgres1 | pagila   | public | store

Using a single schema to house all tables, especially the public schema is generally considered poor database design. As a database grows in complexity, creating, organizing, managing, and securing dozens, hundreds, or thousands of database objects, including tables, within a single schema becomes impossible. For example, given a single schema, the only way to organize large numbers of database objects is by using lengthy and cryptic naming conventions.

Public Schema

According to the PostgreSQL docs, if tables or other object types are created without specifying a schema name, they are automatically assigned to the default public schema. Every new database contains a public schema. By default, users cannot access any objects in schemas they do not own. To allow that, the schema owner must grant the USAGE privilege on the schema. by default, everyone has CREATE and USAGE privileges on the schema public. These default privileges enable all users to connect to a given database to create objects in its public schema. Some usage patterns call for revoking that privilege, which is a compelling reason not to use the public schema as part of your database design.

Pattern 2: Multiple Schemas

Separating tables and other database objects into multiple schemas is an excellent first step to refactoring a database to support microservices. As application complexity and databases naturally grow over time, schemas to separate functionality by business subdomain or teams will benefit significantly.

According to the PostgreSQL docs, there are several reasons why one might want to use schemas:

• To allow many users to use one database without interfering with each other.
• To organize database objects into logical groups to make them more manageable.
• Third-party applications can be put into separate schemas, so they do not collide with the names of other objects.

Schemas are analogous to directories at the operating system level, except schemas cannot be nested.

With Pattern 2, as an organization continues to decompose its monolithic application architecture to a microservices-based application, it could transition to a schema-per-microservice or similar level or organizational granularity.

Applying Domain-driven Design Principles

Domain-driven design (DDD) is “a software design approach focusing on modeling software to match a domain according to input from that domain’s experts” (Wikipedia). Architects often apply DDD principles to decompose a monolithic application into microservices. For example, a microservice or set of related microservices might represent a Bounded Context. In DDD, a Bounded Context is “a description of a boundary, typically a subsystem or the work of a particular team, within which a particular model is defined and applicable.” (hackernoon.com). Examples of Bounded Context might include Sales, Shipping, and Support.

One technique to apply schemas when refactoring a database is to mirror the Bounded Contexts, which reflect the microservices. For each microservice or set of closely related microservices, there is a schema. Unfortunately, there is no absolute way to define the Bounded Contexts of a Domain, and henceforth, schemas to a database. It depends on many factors, including your application architecture, features, security requirements, and often an organization’s functional team structure.

Reviewing the purpose of each table in the Pagila database and their relationships to each other, we could infer Bounded Contexts, such as Films, Stores, Customers, and Sales. We can represent these Bounded Contexts as schemas within the database as a way to organize the data. The individual tables in a schema mirror DDD concepts, such as aggregates, entities, or value objects.

	# dump v1 of pagila database
	pg_dump -Fc -d pagila_v1 -f pagila_v1.dump
	# create new v2 of pagila database
	psql -c "CREATE DATABASE pagila_v2;"
	# restore v1 of pagila database
	pg_restore -d pagila_v2 pagila_v1.dump
	# connect to new pagila database
	export PGDATABASE="pagila_v2"
	psql

view raw create_pagila_v2.sh hosted with ❤ by GitHub

	— wrap in transaction
	BEGIN;
	— optional, should be set to public by default
	SET search_path TO public;
	— create new schemas
	CREATE SCHEMA common;
	CREATE SCHEMA customers;
	CREATE SCHEMA films;
	CREATE SCHEMA sales;
	CREATE SCHEMA staff;
	CREATE SCHEMA stores;
	— common
	ALTER TABLE address SET SCHEMA common;
	ALTER TABLE city SET SCHEMA common;
	ALTER TABLE country SET SCHEMA common;
	— customers
	ALTER TABLE customer SET SCHEMA customers;
	— films
	ALTER TABLE actor SET SCHEMA films;
	ALTER TABLE category SET SCHEMA films;
	ALTER TABLE film SET SCHEMA films;
	ALTER TABLE language SET SCHEMA films;
	ALTER TABLE film_actor SET SCHEMA films;
	ALTER TABLE film_category SET SCHEMA films;
	— sales
	ALTER TABLE payment SET SCHEMA sales;
	ALTER TABLE rental SET SCHEMA sales;
	— staff
	ALTER TABLE staff SET SCHEMA staff;
	— stores
	ALTER TABLE store SET SCHEMA stores;
	ALTER TABLE inventory SET SCHEMA stores;
	COMMIT;
	— confirm all tables are removed from public schema
	\dt

view raw pagila_v2.sql hosted with ❤ by GitHub

As shown below, the tables of the Pagila database have been relocated into six new schemas: common, customers, films, sales, staff, and stores. The common schema contains tables with address data references tables in several other schemas. There are now no tables left in the public schema. We will assume other database objects (e.g., functions, views, and triggers) have also been moved and modified if necessary to reflect new table locations.

-----------+----------+-----------+---------------
 Instance  | Database | Schema    | Table
-----------+----------+-----------+---------------
 postgres1 | pagila   | common    | address
 postgres1 | pagila   | common    | city 
 postgres1 | pagila   | common    | country
-----------+----------+-----------+---------------
 postgres1 | pagila   | customers | customer
-----------+----------+-----------+---------------
 postgres1 | pagila   | films     | actor
 postgres1 | pagila   | films     | category
 postgres1 | pagila   | films     | film 
 postgres1 | pagila   | films     | film_actor
 postgres1 | pagila   | films     | film_category
 postgres1 | pagila   | films     | language
-----------+----------+-----------+---------------
 postgres1 | pagila   | sales     | payment
 postgres1 | pagila   | sales     | rental
-----------+----------+-----------+---------------
 postgres1 | pagila   | staff     | staff
-----------+----------+-----------+---------------
 postgres1 | pagila   | stores    | inventory
 postgres1 | pagila   | stores    | store

By applying schemas, we align tables and other database objects to individual microservices or functional teams that own the microservices and the associated data. Schemas allow us to apply fine-grain access control over objects and data within the database more effectively.

Refactoring other Database Objects

Typically with psql, when moving tables across schemas using an ALTER TABLE...SET SCHEMA... SQL statement, objects such as database views will be updated to the table’s new location. For example, take Pagila’s sales_by_store view. Note the schemas have been automatically updated for multiple tables from their original location in the public schema. The view was also moved to the sales schema.

	CREATE OR REPLACE VIEW sales.sales_by_store AS
	SELECT (c.city || ','::text) || cy.country AS store,
	(m.first_name || ' '::text) || m.last_name AS manager,
	sum(p.amount) AS total_sales
	FROM sales.payment p
	JOIN sales.rental r ON p.rental_id = r.rental_id
	JOIN stores.inventory i ON r.inventory_id = i.inventory_id
	JOIN stores.store s ON i.store_id = s.store_id
	JOIN common.address a ON s.address_id = a.address_id
	JOIN common.city c ON a.city_id = c.city_id
	JOIN common.country cy ON c.country_id = cy.country_id
	JOIN staff.staff m ON s.manager_staff_id = m.staff_id
	GROUP BY cy.country, c.city, s.store_id,
	m.first_name, m.last_name
	ORDER BY cy.country, c.city;

view raw sales_by_store_view.sql hosted with ❤ by GitHub

Splitting Table Data Across Multiple Schemas

When refactoring a database, you may have to split data by replicating table definitions across multiple schemas. Take, for example, Pagila’s address table, which contains the addresses of customers, staff, and stores. The customers.customer, stores.staff, and stores.store all have foreign key relationships with the common.address table. The address table has a foreign key relationship with both the city and country tables. Thus for convenience, the address, city, and country tables were all placed into the common schema in the example above.

Although, at first, storing all the addresses in a single table might appear to be sound database normalization, consider the risks of having the address table’s data exposed. The store addresses are not considered sensitive data. However, the home addresses of customers and staff are likely considered sensitive personally identifiable information (PII). Also, consider as an application evolves, you may have fields unique to one type of address that does not apply to other categories of addresses. The table definitions for a store’s address may be defined differently than the address of a customer. For example, we might choose to add a county column to the customers.address table for e-commerce tax purposes, or an on_site_parking boolean column to the stores.address table.

In the example below, a new staff schema was added. The address table definition was replicated in the customers, staff, and stores schemas. The assumption is that the mixed address data in the original table was distributed to the appropriate address tables. Note the way schemas help us avoid table name collisions.

-----------+----------+-----------+---------------
 Instance  | Database | Schema    | Table
-----------+----------+-----------+---------------
 postgres1 | pagila   | common    | city
 postgres1 | pagila   | common    | country
-----------+----------+-----------+---------------
 postgres1 | pagila   | customers | address
 postgres1 | pagila   | customers | customer
-----------+----------+-----------+---------------
 postgres1 | pagila   | films     | actor
 postgres1 | pagila   | films     | category
 postgres1 | pagila   | films     | film
 postgres1 | pagila   | films     | film_actor
 postgres1 | pagila   | films     | film_category
 postgres1 | pagila   | films     | language
-----------+----------+-----------+---------------
 postgres1 | pagila   | sales     | payment
 postgres1 | pagila   | sales     | rental
-----------+----------+-----------+---------------
 postgres1 | pagila   | staff     | address
 postgres1 | pagila   | staff     | staff
-----------+----------+-----------+---------------
 postgres1 | pagila   | stores    | address
 postgres1 | pagila   | stores    | inventory
 postgres1 | pagila   | stores    | store

To create the new customers.address table, we could use the following SQL statements. The statements to create the other two address tables are nearly identical.

	— wrap in transaction
	BEGIN;
	— create new customers.address table
	CREATE SEQUENCE IF NOT EXISTS customers.address_address_id_seq
	INCREMENT 1
	START 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	CACHE 1;
	ALTER SEQUENCE customers.address_address_id_seq
	OWNER TO pagila_admin;
	CREATE TABLE IF NOT EXISTS customers.address (
	address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL PRIMARY KEY,
	address text NOT NULL,
	address2 text,
	district text NOT NULL,
	city_id smallint NOT NULL REFERENCES common.city ON UPDATE CASCADE ON DELETE RESTRICT,
	postal_code text,
	phone text NOT NULL,
	last_update timestamp with time zone DEFAULT now() NOT NULL
	);
	ALTER TABLE customers.address
	OWNER TO pagila_admin;
	CREATE INDEX IF NOT EXISTS idx_fk_city_id ON customers.address(city_id);
	CREATE TRIGGER last_updated
	BEFORE UPDATE ON customers.address FOR EACH ROW
	EXECUTE PROCEDURE last_updated();
	COMMIT;

view raw address_objects.sql hosted with ❤ by GitHub

Although we now have two additional tables with identical table definitions, we do not duplicate any data. We could use the following SQL statements to migrate unique address data into the appropriate tables and confirm the results.

	— wrap in transaction
	BEGIN;
	— copy only customer addresses to new customers.address table
	INSERT INTO customers.address
	SELECT *
	FROM common.address
	WHERE common.address.address_id IN (
	SELECT DISTINCT address_id
	FROM customers.customer
	);
	— copy only staff addresses to new staff.address table
	INSERT INTO staff.address
	SELECT COUNT(*)
	FROM common.address
	WHERE common.address.address_id IN (
	SELECT DISTINCT address_id
	FROM staff.staff
	);
	— copy only store addresses to new stores.address table
	INSERT INTO stores.address
	SELECT *
	FROM common.address
	WHERE common.address.address_id IN (
	SELECT DISTINCT address_id
	FROM stores.store
	);
	— check for extraneous data in common.address before deleting
	SELECT *
	FROM common.address
	WHERE common.address.address_id NOT IN
	(SELECT DISTINCT address_id FROM customers.customer)
	AND common.address.address_id NOT IN
	(SELECT DISTINCT address_id FROM staff.staff)
	AND common.address.address_id NOT IN
	(SELECT DISTINCT address_id FROM stores.store);
	COMMIT;

view raw address_data.sql hosted with ❤ by GitHub

Lastly, alter the existing foreign key constraints to point to the new address tables. The SQL statements for the other two address tables are nearly identical.

	— wrap in transaction
	BEGIN;
	— customers.customer
	ALTER TABLE IF EXISTS customers.customer
	DROP CONSTRAINT IF EXISTS customer_address_id_fkey;
	ALTER TABLE IF EXISTS customers.customer
	ADD CONSTRAINT customer_address_id_fkey FOREIGN KEY (address_id)
	REFERENCES customers.address (address_id) MATCH SIMPLE
	ON UPDATE CASCADE
	ON DELETE RESTRICT;
	COMMIT;

view raw address_contraints.sql hosted with ❤ by GitHub

There is now a reduced risk of exposing sensitive customer or staff data when querying store addresses, and the three address entities can evolve independently. Individual functional teams separately responsible customers, staff, and stores, can own and manage just the data within their domain.

Before dropping the common.address tables, you would still need to modify the remaining database objects that have dependencies on this table, such as views and functions. For example, take Pagila’s sales_by_store view we saw previously. Note line 9, below, the schema of the address table has been updated from common.address to stores.address. The stores.address table only contains addresses of stores, not customers or staff.

	CREATE OR REPLACE VIEW sales.sales_by_store AS
	SELECT (c.city || ','::text) || cy.country AS store,
	(m.first_name || ' '::text) || m.last_name AS manager,
	sum(p.amount) AS total_sales
	FROM sales.payment p
	JOIN sales.rental r ON p.rental_id = r.rental_id
	JOIN stores.inventory i ON r.inventory_id = i.inventory_id
	JOIN stores.store s ON i.store_id = s.store_id
	JOIN stores.address a ON s.address_id = a.address_id
	JOIN common.city c ON a.city_id = c.city_id
	JOIN common.country cy ON c.country_id = cy.country_id
	JOIN staff.staff m ON s.manager_staff_id = m.staff_id
	GROUP BY cy.country, c.city, s.store_id,
	m.first_name, m.last_name
	ORDER BY cy.country, c.city;

view raw sales_by_store_view_v2.sql hosted with ❤ by GitHub

Below, we see the final table structure for the Pagila database after refactoring. Tables have been loosely grouped together schema in the diagram.

Pattern 3: Multiple Databases

Similar to how individual schemas allow us to organize tables and other database objects and provide better separation of concerns, we can use databases the same way. For example, we could choose to spread the Pagila data across more than one database within a single RDS database instance. Again, using DDD concepts, while schemas might represent Bounded Contexts, databases most closely align to Domains, which are “spheres of knowledge and activity where the application logic revolves” (hackernoon.com).

With Pattern 3, as an organization continues to refine its microservices-based application architecture, it might find that multiple databases within the same database instance are advantageous to further separate and organize application data.

Let’s assume that the data in the films schema is owned and managed by a completely separate team who should never have access to sensitive data stored in the customers, stores, and sales schemas. According to the PostgreSQL docs, database access permissions are managed using the concept of roles. Depending on how the role is set up, a role can be thought of as either a database user or a group of users.

To provide greater separation of concerns than just schemas, we can create a second, completely separate database within the same RDS database instance for data related to films. With two separate databases, it is easier to create and manage distinct roles and ensure access to customers, stores, or sales data is only accessible to teams that need access.

	# dump v2 of pagila database
	pg_dump -Fc -d pagila_v2 -f pagila_v2.dump
	# create 2 new v3 databases
	export PGDATABASE="postgres"
	psql << EOF
	\x
	CREATE DATABASE pagila_v3;
	CREATE DATABASE products_v3;
	EOF
	# restore v2 of pagila database
	pg_restore -d pagila_v3 pagila_v2.dump
	pg_restore -d products_v3 -n films pagila_v2.dump
	# connect to new pagila database
	export PGDATABASE="pagila_v3"
	psql

view raw create_pagila_v3.sh hosted with ❤ by GitHub

Below, we see the new layout of tables now spread across two databases within the same RDS database instance. Two new tables, highlighted in bold, are explained below.

-----------+----------+-----------+---------------
 Instance  | Database | Schema    | Table
-----------+----------+-----------+---------------
 postgres1 | pagila   | common    | city
 postgres1 | pagila   | common    | country
-----------+----------+-----------+---------------
 postgres1 | pagila   | customers | address
 postgres1 | pagila   | customers | customer
-----------+----------+-----------+---------------
 postgres1 | pagila   | films     | film
-----------+----------+-----------+---------------
 postgres1 | pagila   | sales     | payment
 postgres1 | pagila   | sales     | rental
-----------+----------+-----------+---------------
 postgres1 | pagila   | staff     | address
 postgres1 | pagila   | staff     | staff
-----------+----------+-----------+---------------
 postgres1 | pagila   | stores    | address
 postgres1 | pagila   | stores    | inventory
 postgres1 | pagila   | stores    | store
-----------+----------+-----------+---------------
 postgres1 | products | films     | actor
 postgres1 | products | films     | category
 postgres1 | products | films     | film 
 postgres1 | products | films     | film_actor
 postgres1 | products | films     | film_category
 postgres1 | products | films     | language
 postgres1 | products | films     | outbox

Change Data Capture and the Outbox Pattern

Inserts, updates, and deletes of film data can be replicated between the two databases using several methods, including Change Data Capture (CDC) with the Outbox Pattern. CDC is “a pattern that enables database changes to be monitored and propagated to downstream systems” (RedHat). The Outbox Pattern uses the PostgreSQL database’s ability to perform an commit to two tables atomically using a transaction. Transactions bundles multiple steps into a single, all-or-nothing operation.

In this example, data is written to existing tables in the products.films schema (updated aggregate’s state) as well as a new products.films.outbox table (new domain events), wrapped in a transaction. Using CDC, the domain events from the products.films.outbox table are replicated to the pagila.films.film table. The replication of data between the two databases using CDC is also referred to as eventual consistency.

In this example, films in the pagila.films.film and products.films.outbox tables are represented in a denormalized, aggregated view of a film instead of the original, normalized relational multi-table structure. The table definition of the new pagila.films.film table is very different than that of the original Pagila products.films.films table. A concept such as a film, represented as an aggregate or entity, can be common to multiple Bounded Contexts, yet have a different definition.

	CREATE TABLE IF NOT EXISTS films.outbox
	(
	film_id integer NOT NULL,
	title character varying(50) NOT NULL,
	release_year smallint NOT NULL,
	film_language character varying(20) NOT NULL,
	rating character varying(5) COLLATE NOT NULL,
	categories character varying(100) NOT NULL,
	actors character varying NOT NULL,
	rental_duration smallint NOT NULL,
	length_minutes smallint NOT NULL,
	replacement_cost numeric(5,2) NOT NULL,
	rental_rate numeric(4,2) NOT NULL,
	last_update timestamp with time zone NOT NULL DEFAULT now(),
	CONSTRAINT outbox_pkey PRIMARY KEY (film_id)
	)
	TABLESPACE pg_default;
	ALTER TABLE IF EXISTS films.outbox
	OWNER to products_admin;

view raw films_outbox_table_string_agg.sql hosted with ❤ by GitHub

Note the Confluent JDBC Source Connector  (io.confluent.connect.jdbc.JdbcSourceConnector) used here will not work with PostgreSQL arrays, which would be ideal for one-to-many categories and actors columns. Arrays can be converted to text using ::text or by building value-delimited strings using string_agg aggregate function.

	— PROCEDURE: films.insert_into_outbox(integer)
	— DROP PROCEDURE IF EXISTS films.insert_into_outbox(integer);
	— EXAMPLE: "CALL films.insert_into_outbox(100);"
	CREATE OR REPLACE PROCEDURE films.insert_into_outbox(IN filmid integer)
	LANGUAGE 'sql'
	BEGIN ATOMIC
	— delete existing record
	DELETE
	FROM films.outbox
	WHERE (outbox.film_id = insert_into_outbox.filmid);
	— insert new record
	INSERT INTO films.outbox (film_id, title, release_year,
	film_language, rating, categories,
	actors, rental_duration, length_minutes,
	replacement_cost, rental_rate)
	SELECT f.film_id,
	initcap(f.title) AS title,
	f.release_year,
	trim(BOTH FROM l.name) AS film_language,
	f.rating,
	(SELECT array
	(SELECT c.name
	FROM films.film_category AS fc
	JOIN films.category AS c ON fc.category_id = c.category_id
	WHERE film_id = f.film_id)::text AS categories),
	(SELECT array
	(SELECT initcap(concat(a.first_name, ' ', a.last_name)) AS actors
	FROM films.film_actor AS fa
	JOIN films.actor AS a ON fa.actor_id = a.actor_id
	WHERE film_id = f.film_id)::text AS actor_array),
	f.rental_duration,
	f.length AS length_minutes,
	f.replacement_cost,
	f.rental_rate
	FROM films.film f
	JOIN films.language l ON f.language_id = l.language_id
	WHERE (f.film_id = insert_into_outbox.filmid)
	GROUP BY f.film_id, (trim(BOTH FROM l.name));
	END;
	ALTER PROCEDURE films.insert_into_outbox (integer)
	OWNER TO products_admin;

view raw films_outbox_string_agg.sql hosted with ❤ by GitHub

Given this table definition, the resulting data would look as follows.

	film_id	title	release_year	film_language	rating	categories	actor_array	rental_duration	length_minutes	replacement_cost	rental_rate
	389	Gunfighter Mussolini	2006	English	PG-13	{Sports}	{"Audrey Olivier","Judy Dean","Scarlett Damon","Russell Close"}	3	127	9.99	2.99
	581	Minority Kiss	2006	English	G	{Music}	{"Vivien Basinger"}	4	59	16.99	0.99
	598	Mosquito Armageddon	2006	English	G	{Sports}	{"Goldie Brody","Kirk Jovovich","Nick Stallone","Reese West"}	6	57	22.99	0.99
	943	Villain Desperate	2006	English	PG-13	{Documentary}	{"Dustin Tautou","Cary Mcconaughey"}	4	76	27.99	4.99
	490	Jumanji Blade	2006	English	G	{New}	{"Jennifer Davis","Bob Fawcett","Nick Stallone","Gary Phoenix","Mena Temple","Jim Mostel"}	4	121	13.99	2.99
	243	Doors President	2006	English	NC-17	{Animation}	{"Karl Berry","Lucille Tracy","Natalie Hopkins","Christian Akroyd","Sylvester Dern","Gene Hopkins","Ed Mansfield","Kim Allen","Reese West"}	3	49	22.99	4.99
	40	Army Flintstones	2006	English	R	{Documentary}	{"Ed Chase","Cary Mcconaughey","Mae Hoffman","Gene Willis","Penelope Cronyn","Matthew Carrey","Russell Close"}	4	148	22.99	0.99
	317	Fireball Philadelphia	2006	English	PG	{Comedy}	{"Val Bolger","Jude Cruise","Adam Grant","James Pitt","Frances Tomei"}	4	148	25.99	0.99
	17	Alone Trip	2006	English	R	{Music}	{"Ed Chase","Karl Berry","Uma Wood","Woody Jolie","Spencer Depp","Chris Depp","Laurence Bullock","Renee Ball"}	3	82	14.99	0.99
	195	Crowds Telemark	2006	English	R	{Sci-Fi}	{"Matthew Johansson","Anne Cronyn","Jeff Silverstone","Matthew Carrey"}	3	112	16.99	4.99

view raw films_outbox.csv hosted with ❤ by GitHub

The existing pagila.stores.inventory table has a foreign key constraint on the the pagila.films.film table. However, the films schema and associated tables have been migrated to the products database’s films schema. To overcome this challenge, we can:

1. Create a new pagila.films.film table
2. Continuously replicate data from the products database to the pagila.films.film table data using CDC (see below)
3. Modify the pagila.stores.inventory table to take a dependency on the new film table
4. Drop the duplicate tables and other objects from the pagila.films schema

Debezium and Confluent for CDC

There are several technology choices for performing CDC. For this post, I have used RedHat’s Debezium connector for PostgreSQL and Debezium Outbox Event Router, and Confluent’s JDBC Sink Connector. Below, we see a typical example of a Kafka Connect Source Connector using the Debezium connector for PostgreSQL and a Sink Connector using the Confluent JDBC Sink Connector. The Source Connector streams changes from the products logs, using PostgreSQL’s Write-Ahead Logging (WAL) feature, to an Apache Kafka topic. A corresponding Sink Connector streams the changes from the Kafka topic to the pagila database.

	{
	"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
	"database.hostname": "postgres1.abcxyzdef.us-east-1.rds.amazonaws.com",
	"database.port": "5432",
	"database.user": "cdc_source_user",
	"database.password": "change_me!",
	"database.dbname": "products",
	"database.server.name": "products",
	"table.include.list": "films.outbox",
	"plugin.name": "pgoutput",
	"key.converter": "io.apicurio.registry.utils.converter.AvroConverter",
	"key.converter.apicurio.registry.url": "http://localhost:8080/apis/registry/v2",
	"key.converter.apicurio.registry.auto-register": "true",
	"key.converter.apicurio.registry.find-latest": "true",
	"value.converter": "io.apicurio.registry.utils.converter.AvroConverter",
	"value.converter.apicurio.registry.url": "http://localhost:8080/apis/registry/v2",
	"value.converter.apicurio.registry.auto-register": "true",
	"value.converter.apicurio.registry.find-latest": "true",
	"slot.name": "debezium_source_connector"
	}

view raw debezium_source_connector.json hosted with ❤ by GitHub

	{
	"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
	"tasks.max": "1",
	"topics": "products.films.outbox",
	"connection.url": "jdbc:postgresql://postgres1.abcxyzdef.us-east-1.rds.amazonaws.com:5432/pagila?stringtype=unspecified",
	"connection.user": "cdc_sink_user",
	"connection.password": "change_me!",
	"dialect.name": "PostgreSqlDatabaseDialect",
	"table.name.format": "films.film",
	"auto-evolve": "true",
	"auto.create": "true",
	"insert.mode": "upsert",
	"pk.fields": "film_id",
	"pk.mode": "record_key",
	"delete.enabled": "true",
	"key.converter": "io.apicurio.registry.utils.converter.AvroConverter",
	"key.converter.apicurio.registry.url": "http://localhost:8080/apis/registry/v2",
	"key.converter.apicurio.registry.auto-register": "true",
	"key.converter.apicurio.registry.find-latest": "true",
	"value.converter": "io.apicurio.registry.utils.converter.AvroConverter",
	"value.converter.apicurio.registry.url": "http://localhost:8080/apis/registry/v2",
	"value.converter.apicurio.registry.auto-register": "true",
	"value.converter.apicurio.registry.find-latest": "true",
	"transforms": "unwrap",
	"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
	"transforms.unwrap.drop.tombstones": "false",
	"transforms.unwrap.delete.handling.mode": "rewrite"
	}

view raw confluent_sink_connector.json hosted with ❤ by GitHub

Pattern 4: Multiple Database Instances

At some point in the evolution of a microservices-based application, it might become advantageous to separate the data into multiple database instances using the same database engine. Although managing numerous database instances may require more resources, there are also advantages. Each database instance will have independent connection configurations, roles, and administrators. Each database instance could run different versions of the database engine, and each could be upgraded and maintained independently.

With Pattern 4, as an organization continues to refine its application architecture, it might find that multiple database instances are beneficial to further separate and organize application data.

Below is one possible refactoring of the Pagila database, splitting the data between two database engines. The first database instance, postgres1, contains two databases, pagila and products. The second database instance, postgres2, contains a single database, products.

-----------+----------+-----------+---------------
 Instance  | Database | Schema    | Table
-----------+----------+-----------+---------------
 postgres1 | pagila   | common    | city
 postgres1 | pagila   | common    | country
-----------+----------+-----------+---------------
 postgres1 | pagila   | customers | address
 postgres1 | pagila   | customers | customer
-----------+----------+-----------+---------------
 postgres1 | pagila   | films     | actor
 postgres1 | pagila   | films     | category
 postgres1 | pagila   | films     | film
 postgres1 | pagila   | films     | film_actor
 postgres1 | pagila   | films     | film_category
 postgres1 | pagila   | films     | language
-----------+----------+-----------+---------------
 postgres1 | pagila   | staff     | address
 postgres1 | pagila   | staff     | staff
-----------+----------+-----------+---------------
 postgres1 | pagila   | stores    | address
 postgres1 | pagila   | stores    | inventory
 postgres1 | pagila   | stores    | store
-----------+----------+-----------+---------------
 postgres1 | pagila   | sales     | payment
 postgres1 | pagila   | sales     | rental
-----------+----------+-----------+---------------
 postgres2 | products | films     | actor
 postgres2 | products | films     | category
 postgres2 | products | films     | film
 postgres2 | products | films     | film_actor
 postgres2 | products | films     | film_category
 postgres2 | products | films     | language

Data Replication with CDC

Note the films schema is duplicated between the two databases, shown above. Again, using the CDC allows us to keep the six postgres1.pagila.films tables in sync with the six  postgres2.products.films tables using CDC. In this example, we are not using the OutBox Pattern, as used previously in Pattern 3. Instead, we are replicating any changes to any of the tables in postgres2.products.films schema to the corresponding tables in the postgres1.pagila.films schema.

To ensure the tables stay in sync, the tables and other objects in the postgres1.pagila.films schema should be limited to read-only access (SELECT) for all users. The postgres2.products.films tables represent the authoritative source of data, the System of Record (SoR). Any inserts, updates, or deletes, must be made to these tables and replicated using CDC.

	CREATE USER read_only_user WITH ENCRYPTED PASSWORD 'change_me!';
	GRANT CONNECT ON DATABASE pagila TO read_only_user;
	GRANT USAGE ON SCHEMA films TO read_only_user;
	GRANT SELECT ON ALL TABLES IN SCHEMA films TO read_only_user;
	ALTER DEFAULT PRIVILEGES IN SCHEMA films
	GRANT SELECT ON TABLES TO read_only_user;

view raw read_only_user.sql hosted with ❤ by GitHub

Pattern 5: Multiple Database Engines

AWS commonly uses the term ‘purpose-built databases.’ AWS offers over fifteen purpose-built database engines to support diverse data models, including relational, key-value, document, in-memory, graph, time series, wide column, and ledger. There may be instances where using multiple, purpose-built databases makes sense. Using different database engines allows architects to take advantage of the unique characteristics of each engine type to support diverse application requirements.

With Pattern 5, as an organization continues to refine its application architecture, it might choose to leverage multiple, different database engines.

Take for example an application that uses a combination of relational, NoSQL, and in-memory databases to persist data. In addition to PostgreSQL, the application benefits from moving a certain subset of its relational data to a non-relational, high-performance key-value store, such as Amazon DynamoDB. Furthermore, the application implements a database cache using an ultra-fast in-memory database, such as Amazon ElastiCache for Redis.

Below is one possible refactoring of the Pagila database, splitting the data between two different database engines, PostgreSQL and Amazon DynamoDB.

-----------+----------+-----------+-----------
 Instance  | Database | Schema    | Table
-----------+----------+-----------+-----------
 postgres1 | pagila   | common    | city 
 postgres1 | pagila   | common    | country
-----------+----------+-----------+-----------
 postgres1 | pagila   | customers | address
 postgres1 | pagila   | customers | customer
-----------+----------+-----------+-----------
 postgres1 | pagila   | films     | film
-----------+----------+-----------+-----------
 postgres1 | sales    | sales     | payment
 postgres1 | sales    | sales     | rental
-----------+----------+-----------+-----------
 postgres1 | pagila   | staff     | address
 postgres1 | pagila   | staff     | staff
-----------+----------+-----------+-----------
 postgres1 | pagila   | stores    | address
 postgres1 | pagila   | stores    | film
 postgres1 | pagila   | stores    | inventory
 postgres1 | pagila   | stores    | store
-----------+----------+-----------+-----------
 DynamoDB  |    -     |     -     | Films

The assumption is that based on the application’s access patterns for film data, the application could benefit from the addition of a non-relational, high-performance key-value store. Further, the film-related data entities, such as a film , category, and actor, could be modeled using DynamoDB’s single-table data model architecture. In this model, multiple entity types can be stored in the same table. If necessary, to replicate data back to the PostgreSQL instance from the DynamoBD instance, we can perform CDC with DynamoDB Streams.

CQRS

Command Query Responsibility Segregation (CQRS), a popular software architectural pattern, is another use case for multiple database engines. The CQRS pattern is, as the name implies, “a software design pattern that separates command activities from query activities. In CQRS parlance, a command writes data to a data source. A query reads data from a data source. CQRS addresses the problem of data access performance degradation when applications running at web-scale have too much burden placed on the physical database and the network on which it resides” (RedHat). CQRS commonly uses one database engine optimized for writes and a separate database optimized for reads.

Conclusion

Embracing a microservices-based application architecture may have many business advantages for an organization. However, ignoring the application’s existing databases can negate many of the benefits of microservices. This post examined several common patterns for refactoring relational databases to match a modern microservices-based application architecture.

---

This blog represents my own viewpoints and not of my employer, Amazon Web Services (AWS). All product names, logos, and brands are the property of their respective owners. All diagrams and illustrations are property of the author.