Amazon Aurora DSQL has probably been the most impactful announcement in recent years at AWS. It's a significant advancement that adds a new layer to a service like Amazon Aurora, which was already impressive.

However, it's not a database for everyone, and we will discuss it in this series of articles. In this first article, we'll discuss how we arrived at this point and what alternatives we have to Amazon Aurora DSQL, as Amazon Aurora DSQL may not be the most suitable option for our use case.

To summarize, Amazon Aurora DSQL is a distributed database that provides write consistency, enabling ACID transactions in multi-region scenarios.

In this article, we'll cover:

• What ACID is and its importance
• How databases have evolved to be more efficient
• How Aurora revolutionized databases
• What Aurora Global Database brings us and its limitations
• Why do we need Aurora DSQL

What is ACID?

First, let's clarify what ACID is (Atomicity, Consistency, Isolation, and Durability).

Researchers coined the term ACID in 1983, and it forms the foundation of all current database development. Although researchers coined the term in 1983, these types of databases have existed since 1973.

For a database to be ACID compliant, its transactions must meet these requirements:

Atomicity

A transaction typically involves multiple steps, but the system must treat it as a single entity; either the transaction executes correctly or fails. It cannot leave data partially modified.

Consistency

Transaction consistency ensures that data remains unchanged, preventing corruption when executing a transaction.

Isolation

Isolation ensures that operations execute in isolation without affecting each other, so when multiple operations are modifying the same record, one doesn't execute until the previous one finishes.

Durability

The system guarantees that once it completes a transaction, it cannot lose the transaction, even in the event of a disaster, and the data remains available once the system recovers from the disaster.

Do all databases support ACID transactions?

Not all databases support ACID. For example, Amazon MemoryDB is not ACID, and even other AWS databases, such as Athena or DynamoDB, initially didn't support ACID transactions (both currently support them).

Now, let's examine how developers have historically solved this problem and how we arrived at Amazon Aurora DSQL.

Is it easy to make our database ACID?

No, it's not. It's one of the main limitations of a database. If you've ever wondered why databases have so many problems with Unix file systems in general, this is why.

A database transaction typically involves multiple read/write operations, and it requires a certain level of locking (i.e., while the system performs a transaction, another transaction that modifies the same data cannot execute).
This creates many latency problems, not just network latency, but even latency between server components. For databases, historically, disk write latency has been a significant problem.

Manufacturers have significantly improved disk speeds, but it still wasn't enough. Therefore, years ago, developers decided to write to memory instead of disk. RAM provides significantly faster writing and reading, offering several advantages, but it also presents some challenges. On the one hand, it's volatile, we can lose data; on the other hand, we can't load the entire content because memory usually has less capacity than our disks.

Therefore, developers decided to utilize a common approach: paged memory, which creates memory "pages" that are physical memory blocks. This way we can have part of our data in a memory page (frequent or new data) and asynchronously dump data to disk to persist it. This improves both performance and disk utilization by optimizing write and read operations.

However, we would encounter a problem in the event of failure before dumping data to disk or if we need to recover it. A transactional logging system known as Write-Ahead Logging (WAL) addresses this issue, which follows the DO-UNDO-REDO principle.

In addition to writing the transaction result to memory, the system writes a log with the transaction itself:

This way, if we have a failure, we can load an old persisted page and apply all transactions that haven't been persisted (REDO). Or conversely, if we want to rectify a transaction, we can know what it modified and return to the previous state (UNDO):

This system continuously improves database performance and has been utilized for many years in various database engines.

How can a database be ACID across multiple Availability Zones?

So far, we've seen how to achieve this on a single server, but if we want high availability, we need to set up two servers (a primary and a standby), and here appears the main problem of all: network latency.

A transaction is not just a read or write in a database, but a sequence of operations, so the execution time of a transaction depends on latency.

This is a problem with synchronous replication because we have to wait for each operation to replicate. This dramatically increases transaction duration because we have to send the operation over the network, have it execute on the replica, and wait for it to return the ACK. Since network latency is much higher, transaction execution time multiplies exponentially.

We can opt for a slightly more efficient solution: Read Replicas, which, instead of using synchronous replication, use asynchronous replication, similar to how we did with disk. However, they will experience replication lag, which is the time when the replica does not have the most up-to-date data.

You might wonder how we can guarantee that a transaction is ACID with replication if we have replication lag. The answer is simple: by limiting writes to a single instance. This way, we guarantee consistency and isolation.

Can it be improved?

This method is improvable since replication lag can significantly affect us, and this is where Amazon Aurora's magic comes in.

Amazon Aurora did something exciting: it separated the Storage and Compute layers, leaving the database engine in one instance and managing the storage layer independently.

The database engine is precisely the same, but when performing a write, instead of calling the disk directly, it calls Aurora's Storage Nodes.

Each new write launches this scheme:

1. Records are received in the Incoming Queue and stored in the Hot Log in memory
2. ACK is returned to the database engine without waiting for persistence
3. Data is registered in the Update Queue and grouped to optimize writes
4. A new Data Page is generated, distributing it in 6 synchronized copies (2 per AZ)
5. Periodic backups of Hot Logs and Data Pages are made to S3

This approach enables the achievement of very low replication latency, less than 100 ms (which is exceptionally low for a multi-AZ environment), across multiple availability zones, with six copies, along with continuous backups and the ability to perform point-in-time restores.

The best part is that this doesn't impact database performance, as storage operations are executed on a separate instance from where our database engine resides, allowing for better overall performance.

Additionally, the system replicates all transactions via the Log to all nodes.

Aurora supports up to 15 read replicas and 64 TB of automatic storage and can handle up to 500,000 reads and 100,000 writes per second.

Note: There are additional steps that I've simplified for proper understanding. Since it's a distributed storage system, there isn't just one Storage node, etc.

Note 2: At the database engine level, records are also stored in memory, so writing is faster.

What about multiple regions?

If we need to replicate our database to another region, we'll find that replication latency is significantly higher, primarily because network latency between regions is very high.

However, this is where Amazon Aurora Global Database comes to our aid.

Amazon Aurora Global Database extends its replication model to other regions. In this case, in addition to separating the Storage layer, it separates the replication layer.

This way, we'll have an independent layer to manage replication between regions and an independent storage layer to manage replication between zones. This enables Amazon Aurora Global Database to achieve better database-level performance and highly optimized inter-region replication.

Each new write launches this scheme:

1. New records are written to Storage nodes and zonal database replicas, and additionally written to a replication server.
2. The replication server replicates write records to the server with the replication agent belonging to the replication server group in other regions.
3. This replication server acts as if it were a primary database and executes write records both in the global database replica and in this region's Storage nodes.
4. Finally, if the replication server detects it hasn't received any write records, it will collect them from the primary region's storage nodes.

This approach also achieves very low replication latency, with values of less than 1 second in up to 5 secondary regions. Each secondary region will also have availability zone replication and six copies distributed among them, in addition to multi-regional and continuous backups, as the system stores backups in each secondary region.

What about multi-region writes?

This is the biggest problem with this model, as writes can only be executed in the primary region, resulting in extremely high multi-region write latency.

There's an intermediate solution: using write forwarding in Amazon Aurora. This allows us to configure a global replica to write to it, although what we're doing is sending these requests with a forward to the central database.

It's a slightly more efficient way to write in multi-region, allowing us to use endpoints per region, but it's not very efficient for writing.

The Challenge of Consistent Multi-Region Writes

As we've seen, both Amazon Aurora and Aurora Global Database have addressed performance and replication issues. However, they still have a fundamental limitation: writes must be centralized in a primary region to ensure ACID consistency.

This architecture works perfectly for applications that can function with centralized writes, but what happens when we need users in different regions to be able to write with low latency while maintaining write consistency? This is where Amazon Aurora DSQL comes into play.

One of the write problems is controlling the time sequence, because if we launch a write from one region and launch another write on the same record 100ms later in another region, the correct data for each of these regions is not the same. After all, we can't control time, and we'll have a reasonably significant consistency problem.

(Well, we actually can control time, but we'll see that in the following article.)

Conclusions

Throughout this article, we've covered the evolution in AWS that led to the development of Amazon Aurora DSQL, from ACID fundamentals to the limitations of current multi-region systems.

The replication system used by Amazon Aurora and Amazon Aurora Global Database is truly remarkable, as it significantly improves performance and drastically reduces replication times.

If we don't need multi-region writes, Amazon Aurora or Amazon Global Database is the best option in most cases. However, if we require a global, distributed database with consistent multi-region writes, we need to opt for Amazon Aurora DSQL.

How much does Aurora DSQL cost?

Although AWS hasn't published official prices yet, Aurora DSQL will be considerably more expensive than Aurora due to its distributed complexity.

Taking Aurora MySQL/PostgreSQL as reference:
Aurora Global Database: is 65% more expensive
Aurora DSQL: Estimated between 3 and 5 times more than Aurora Global

Which to choose?

DON'T use Aurora DSQL if:

• Your writes can be centralized in one region (>80% of cases)
• You need to use MySQL as a database engine
• Your transactions don't require low-latency multi-region writes

You can use Aurora Global Database when:

• You can tolerate write forwarding or writes in a single region
• You require intensive multi-region reads with occasional writes
• You need multi-region DR but not active-active writes

You can use Aurora when:

• You have regional applications
• Limited budget
• You require operational simplicity

Now that we have the problem clear, we need to ask ourselves a question: How has AWS achieved a database with multi-regional writes without breaking ACID?

In the following article, we'll unveil the secrets of Aurora DSQL and how AWS has managed to tame time in a database.

👉 Next: "Aurora DSQL: How to Control Time"