# Tracking Database Changes: When It's Most Useful

[Jozef Cipa](https://www.strv.com/blog/authors/jozef)  
Backend Engineer

---

My task was simple: We had a Postgres database and a data science team that needed to consume changes from the database to update their internal datasets. But how on earth would I do that? Luckily, my colleagues brought some light into this mysterious database world — but I still had (wanted) to read up on it to better understand and make sure I did it right.

Today, almost every application needs some place to store data. Most of the time, this place is a database and it often plays a crucial role in the whole system. There are many different databases available, each designed for a specific purpose. They can be hosted locally on a computer, somewhere in a data center or in a cloud — so-called **managed database** instances. These are especially useful when we don’t want to take care of maintenance, data durability and availability.

The other big benefit of managed databases is **scalability**. This is very important when an application starts getting users. As engineers, we have to make sure that it will withstand the incoming traffic. There are multiple techniques to achieve this very complex and difficult task. When the database begins to run out of resources due to a lack of free memory (RAM), overutilized CPU or reaching a storage limit, some action is necessary. The easiest solution is raising the resource limits, like providing more RAM, better CPU or more disk space. This is known as **vertical scaling**; you just add more resources to a single (database) machine.

However, this is not sustainable in the long term because, sooner or later, the database might hit its limits again, and further scaling could become very expensive or even physically impossible. That’s where **horizontal scaling** comes into play. Instead of increasing the physical parameters of a single machine, we add multiple smaller machines that can split the load.

In some scenarios where too much data is being stored, [**sharding**](https://www.digitalocean.com/community/tutorials/understanding-database-sharding?ref=strv.ghost.io) — data partitioned among several databases — might come in handy, but this is out of the scope of this article.

Another important concept is **replication**, which is key in ensuring **data persistence**. It also helps with offloading the main database server by introducing a **read replica** that is used for reading queries.

This is what we will focus on in this article: a very high-level description of **how replication works**, **the various types of replication** and how we can leverage them.

## What Is Replication?

“…sharing information so as to ensure consistency between redundant resources, such as software or hardware components, to improve **reliability**, **fault-tolerance**, or **accessibility**…” — Wikipedia

As we can see, replication is an essential and commonly used method. In short, it’s supposed to help keep your application available to users. It does so by copying data into multiple machines so an application can continue working seamlessly if the main database becomes unavailable.

There are [**many ways**](https://www.postgresql.org/docs/13/different-replication-solutions.html?ref=strv.ghost.io) to achieve this, from low-level solutions like **sharing a disk** or a network file system across machines, to **shipping WAL logs** (<em>we'll talk about WAL later</em>), having an **SQL middleware** that intercepts all queries and sends them to other servers, **logical replication** and more.

**Note:** It’s important to keep in mind that there are plenty of different databases, some designed for very specific use cases; therefore, not everything we mention can be applied to all of them. In this article, we are using [Postgres](https://www.postgresql.org/?ref=strv.ghost.io) 13, a powerful, widely-used relational database that’s most common.

## Logical Replication

While disk-based (AKA **physical**) methods work on a binary level — the exact block addresses are sent over directly (byte-by-byte replication) — logical replication works with tables rather than raw database data. It replicates data objects and their changes based on their replication identity (usually a primary key).

It has many use cases, such as:
- replicating data between different major versions of PostgreSQL or different platforms (e.g., Linux to Windows)
- grouping multiple databases into one (e.g., for analytics)
- sharing a subset of the database with other users or systems
- distributing database changes to subscribers in real-time

However, it’s important to note that it **only supports** [**DML operations**](https://stackoverflow.com/a/44796508/4480179?ref=strv.ghost.io) (`INSERT`, `UPDATE`, `DELETE`); schema changes will not be replicated and the schema itself must be identified and defined beforehand.

It uses a publisher/subscriber model which can be used as follows:
```sql
CREATE PUBLICATION pub FOR TABLE users
```
The **publication** is defined on a primary database and represents a set of changes generated from a table or multiple tables.

Then, on the secondary (replica) database, you would create a **subscription** that specifies the connection to the main database and the set of publications to subscribe to:
```sql
CREATE SUBSCRIPTION sub CONNECTION 'host=192.168.1.1 port=5432 user=foo dbname=bar password=xyz123' PUBLICATION pub
```
This way, we can configure logical replication between two databases.

## Logical Decoding

Logical decoding is very similar to logical replication, but it provides an option to consume the changes to a database’s tables to **external consumers**. This could be a different kind of database, a business application, auditing software, etc.

The data is stored using **replication slots**. A slot represents a stream of changes that can be replayed to a client in the order they were made on the origin server. You can think of it as an ordered list where a database pushes the changes and a consuming application on the other side reads those changes. Usually, there should be a separate replication slot for each consumer, as the changes are wiped after reading(!).

The output format is defined by a plugin that you can set. Several [**available plugins**](https://wiki.postgresql.org/wiki/Logical_Decoding_Plugins?ref=strv.ghost.io) can process the WAL log and print out the results in a format that is desired and processable by a consumer application.

There are two options for consuming the changes:
- using special SQL functions for retrieving data, such as `pg_logical_slot_get_changes()`
- using a streaming protocol to get real-time events of database changes; this can be achieved by using [**pg_recvlogical**](https://www.strv.com/<https://www.postgresql.org/docs/current/app-pgrecvlogical.html>) utility

## Write-ahead Log (WAL)

So, what the heck is this WAL, anyway?

In the Postgres database, all changes to data files must be recorded in the log file before they are written to the database. Only after this is a transaction deemed as committed ([**asynchronous commit**](https://www.postgresql.org/docs/current/wal-async-commit.html?ref=strv.ghost.io)). This is a standard method for ensuring data integrity and durability in the event of a database crash, for example; all changes can be reapplied to the database using the log.

Following this procedure, the database doesn’t need to flush data pages to disk on every transaction commit — which also improves the speed of transactions, as explained in the [Postgres docs](https://www.postgresql.org/docs/current/wal-intro.html?ref=strv.ghost.io):
> “Using WAL results in a significantly reduced number of disk writes because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, so the cost of syncing the log is much less than the cost of flushing the data pages.”

Now that we talked about what the WAL is, you can get a better idea of how the replication process works. As the database keeps these log files, they can just be sent to another machine and applied there.

## Configuring the Database

As explained in the beginning, we needed to configure a third-party service to consume updates from the main database. Therefore, we had to use logical decoding that allows us to create a replication slot with a given output formatting plugin and consume the changes. We used the popular plugin **wal2json**, which takes the WAL records and converts them into a JSON format, making them easily processable by third-party applications.

In order to do this, we first needed to grant replication permissions to the database user. As we were using an RDS instance in AWS, the command looked like:
```sql
GRANT rds_replication TO myuser
```

After that, we needed to configure some Postgres parameters so the replication could start. Normally, we’d look for a config file like `postgresql.conf` — but since we were on a managed database, we used the so-called Parameter Group where we could easily configure all the necessary values in the UI.

- `rds.logical_replication` ⇒ `1`  
  This is the first parameter that has to be set to `1` in order to enable logical replication

- `wal_level` ⇒ set to `logical` (handled automatically by AWS once you enable `rds.logical_replication` ([read more](https://postgresqlco.nf/doc/en/param/wal_level/?ref=strv.ghost.io)))

- `max_slot_wal_keep_size` ⇒ `20000` MB  
  This value specifies how much data can be stored in a replication slot before it starts recycling the memory. It is specified in MB. Consider your database use case and the amount of data written, then set a proper value. If the number is **too low** and your database **writes a lot** of changes, the slot might **get recycled too soon** — and if you don’t read all the changes before the databases (or third-party system) get **out of sync**, you will need to do a full sync again!  

Important: This value is set to **-1** by default, which means unlimited storage size. If you’re not careful, your slot might end up **taking up all the available free storage** on the database, resulting in **database unavailability** as it runs out of memory. [Find out more](https://postgresqlco.nf/doc/en/param/max_slot_wal_keep_size/?ref=strv.ghost.io).

**Beware:** These changes will require a **database restart**!

And that’s all that needs to be configured to have logical decoding work in Postgres. Naturally, there are many more parameters that can be set, but we were okay with their default values for now.

Other interesting parameters: **max_replication_slots** and **max_wal_senders**. The default values sufficed for our purposes.

One crucial point: **slots keep data until it’s read** by a consumer. Once the data is read, it is automatically deleted from the slot. If you don’t use a replication slot anymore, it should be dropped; otherwise, unnecessary data will pile up and fill storage!  

There is even a warning in the [official docs](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html?ref=strv.ghost.io#LOGICALDECODING-REPLICATION-SLOTS):
> “Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot. In extreme cases this could cause the database to shut down to prevent transaction ID wraparound (see [Section 25.1.5](https://www.postgresql.org/docs/current/routine-vacuuming.html?ref=strv.ghost.io#VACUUM-FOR-WRAPAROUND)). So if a slot is no longer required it should be dropped.”

**Note:** If you want to learn more about the specific Postgres configuration parameters, I really recommend [postgresqlco.nf](http://postgresqlco.nf/?ref=strv.ghost.io) or the [official Postgres docs](https://www.postgresql.org/docs/14/index.html?ref=strv.ghost.io), which are very well written and their explanation of various concepts is easy to read and understand.

## Consuming Changes

Now that we’ve got our database configured properly, we can finally start consuming database changes. To do so, we need to **create a replication slot** first using the function:
```sql
SELECT * FROM pg_create_logical_replication_slot('my_replication_slot', 'wal2json')
```
This creates a slot named `my_replication_slot` with the `wal2json` output plugin.

To verify the slot creation, run:
```sql
SELECT * FROM pg_catalog.pg_replication_slots
```

**Note:** To use the `wal2json` plugin, you might need to **install** it first. On AWS RDS (and possibly other cloud providers), the plugin is already installed, even if it’s not visible in the `pg_extension` table.  
  
Now, let’s see how it looks with a simple `INSERT`:
```sql
INSERT INTO users (id, email, role, name)
VALUES (
	'847be54f-3a82-4591-a293-023ea15b2962',
	'john.wick@example.com',
	'user',
	'John Wick'
)
```

Then, peek at what has been logged:
```sql
SELECT * FROM pg_logical_slot_peek_changes('my_replication_slot', NULL, NULL, 'include-xids', '0')
```
It will return the inserted data:
```
{
	"change":[
		{
			"kind":"insert",
			"schema":"public",
			"table":"users",
			"columnnames":["id","email","role","name"],
			"columntypes":["uuid","character varying(255)","character varying(255)"],
			"columnvalues":["847be54f-3a82-4591-a293-023ea15b2962","john.wick@example.com","user","John Wick"]
		}
	]
}
```
Peeking is good for testing. In production, you’d use:
```sql
SELECT * FROM pg_logical_slot_get_changes('my_replication_slot', NULL, NULL, 'include-xids', '0')
```
This retrieves and removes the changes from the slot after reading.

If everything works, no more changes will be returned on subsequent reads. You can then connect your consumer app to process all changes, and if needed, **drop the slot**:
```sql
SELECT * FROM pg_drop_replication_slot('my_replication_slot')
```

## Conclusion

We’ve learned what replication is, the many techniques to achieve it, and how it can be used not only for keeping data in sync between databases but also with third-party systems. We delved into how databases store changes in the WAL log and how logical decoding, with various plugins, becomes a powerful tool for data visibility.

Now we understand how this decoding works, why it’s essential to read docs carefully, and how to configure parameters to prevent surprises like unresponsive databases due to memory issues. Monitoring and visibility over what’s happening are always more than desired.

---

**I would like to thank my colleagues** [**Honza**](https://www.linkedin.com/in/janhybl?ref=strv.ghost.io) **and** [**Jozef**](https://www.linkedin.com/in/jozefreginac?ref=strv.ghost.io), **who helped me understand the details and provided some useful tips and insights.**

---

*Don't miss anything*