Aurora DSQL - A NEW boring(?) AWS Serverless Postgres compatible database

Aurora DSQL is a serverless, distributed database with PostgreSQL compatibility. Serverless and relational databases don't usually mix, but AWS has modernized this traditional classic car(PostgresSQL), with a brand-new interior powered by a Formula 1 engine.

Let's take a look under the hood to see what makes Aurora DSQL so special and why boring is actually good!

The AWS features we love to see

Aurora DSQL is built on AWS, so you can expect all the features you love and trust. Here are a few of the highlights:

• You don't manage and patch any serves, you only get an endpoint.

• You don't have to manually scale your database, or do upfront provisioning.

• You don't have to worry about Availability Zone (AZ) or Regional fault tolerance, it's all handled for you.

• You only pay for what you use (to be confirmed after the service is released as Generally Available).

So what do we need to worry about? The business logic within your database, that's it.

AWS ensures that your database will not fall over in the event of a failure, or massive traffic spikes. You can focus on your business logic like table design, queries, indexes, etc. Using Aurora DSQL is like driving a car with an automatic transmission, you don't have to worry about shifting gears, you just focus on driving.

How does Aurora DSQL compare to similar AWS offerings?

Aurora DSQL promises an OLTP database that scales to zero but also has OLAP features for powerful querying. It's like having a car that can go from 0 to 60 in 3 seconds, but also has a trunk big enough to fit a couch. Almost like a Cybertruck, except it doesn't look like a Cybertruck, is practical and doesn't have a broken window.

Here's how Aurora DSQL compares with databases with similar features:

For the sake of comparison, we are not comparing to other purposefully built OLTP and OLAP databases, like Redshift and Athena. We are however comparing it to DynamoDB, as it sets the standard for a serverless database.

¹ to be confirmed after General Availability

² resuming takes around 15 seconds

³ IAM only authentication might make it difficult for traditional DB engineers

⁴ supported through active-passive failover within 1 minute

Aurora DSQL vs DynamoDB

Aurora DSQL promises a much better Developer experience than DynamoDB when it comes to upfront and relational design. With DynamoDB you have to identify your access patterns upfront and design your tables & indexes accordingly. With Aurora DSQL, you can design your tables as you go, and change them as needed. Making it a much more flexible and forgiving option.

Price-wise, DynamoDB will most likely be cheaper than Aurora DSQL, whose pricing model is still to be confirmed at GA, so this is speculative. So far Aurora DSQL has inherited all the serverless features of DynamoDB, and we expect it to have a similar pricing model. Paying only for the storage that you use (or even provisioned is fine) and then compute being charged per request. We will have to wait and see.

Both services are inherently serverless and scale automatically, sharing many of the good AWS features we love. Aurora DSQL wins in the Developer Experience category, but DynamoDB is a more mature service with a proven track record built for extremely high OLTP workloads.

Aurora DSQL vs RDS and Aurora

Both RDS and Aurora fall within the traditional databases category that requires upfront provisioning and maintenance. Aurora DSQL shines in this regard, where you don't have to worry about scaling, fault tolerance, or maintenance. Thus making it a much more attractive option for startups and small businesses.

Aurora Global Databases uses active-passive replication to provide cross-regional read, but single-region write capabilities. While Aurora Limitless only operates within a single region and uses sharding with smart table replication for improved read and write performance. Neither of these solutions supports active-active cross-regional writes, Global Databases have to fail over to the secondary cluster and Limitless is just sharing the write load across shards. Aurora DSQL supports active-active cross-regional writes.

Both RDS and Aurora have a much more mature ecosystem and will be better suited for constant workloads that require predictable scaling.

Aurora DSQL vs Aurora Serverless

Aurora DSQL is what Aurora Serverless should have been, serverless means scale to zero and having the luxury of not worrying about exhausting your maximum connections. Aurora Serverless version 1 scaled to zero but will be deprecated on 31 December 2024. Version 2 does not scale to zero, and resuming takes double-digit seconds.

Postgres Similarities

Aurora DSQL uses the Postgres protocol, it inherits many of the features that make a relational database great. Some of the noteworthy features include:

• ACID compliance

• Common Table Expressions (CTEs) 🤩

• Indexes (including UNIQUE)

• Joins

• Grouping

• Advance grouping functions like RANK OVER

• Ordering

• and many more..

Aurora DSQL also supports most data types but with a reduced size limit. For example, the TEXT data type has a limit of 1MB instead of 1GB you get on Postgres.

But, it's not all roses, some of the features you might expect are not yet supported as of the time of this writing.

Postgres Deviations & Unsupported Features

One of the core differences between Aurora DSQL and Postgres is the implementation of concurrency. Postgres uses pessimistic concurrency control which locks tables and rows to achieve transactional isolation.

Aurora DSQL uses optimistic concurrency control, returning an error immediately if there are conflicts. The advantage of this is that you don't have to worry about deadlocks, but you do have to handle these errors and implement explicit retries.

Another key difference lies in authentication, Aurora DSQL only supports IAM authentication. You create a 1-to-1 mapping between an IAM Role and a Database Role. By default, you get a "superuser" DB role that can create these role mappings in the database.

You can connect to the database as a "superuser" by assigning the Aurora DSQL:DbConnectSuperuser policy to any IAM role. Thereafter, you create roles with the Aurora DSQL:DbConnect policy and create the 1-to-1 mapping between the IAM role and the DB role on the database itself. For example:

-- Create the role
CREATE ROLE example WITH LOGIN;

-- Create the DB role to IAM role mapping
AWS IAM GRANT example TO 'arn:aws:iam::111122223333:role/example';

-- Assign permissions to the role
GRANT USAGE ON myschema TO example;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA myschema TO example;

Obtaining the password requires you to assume the role and then call the AWS API/SDK AxdbGenerateToken function. The password returned is a temporary password whose maximum lifetime can be up to 1 week. The generated DB password can not outlive the credentials that generated it. This means that if an IAM role is used, your DB password will expire when the role assumption expires.

A positive deviation from Postgres is that Aurora DSQL does not require connection pooling! Your short-lived connections are handled by the database itself, and you don't have to worry about managing a separate connection pool in front of the database.

There are also some notable unsupported features like:

• No support for SERIAL (auto-incrementing integers) 😢

• Other unsupported types include: JSON, bigserial, geospatial and vectors

• No support for extensions

• The only supported default value is UUID

• Temporary tables

• Views

• Triggers

Aurora DSQL in Action - a quick walkthrough

Working with Aurora DSQL is similar to working with Postgres, but constrained by the above-mentioned limitations.

Navigate to the AWS Console and create a new Aurora DSQL database. You can use the default settings for the most part.

Next, establish a connection. Specify the same parameters required to connect to a normal Postgres database:

• Host: The URL of the database after it has been created

• Port: 5432

• Username: The database role name, use admin for the supper user

• Password: The password obtained from the getDbConnectAdminAuthToken function after assuming the IAM role tied to the DB role

Next, let's create a schema and add an Invoice table, we are using the only supported default function gen_random_uuid() to auto-generate UUIDs for the id column.

CREATE SCHEMA example;

CREATE TABLE example.invoice(id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                             created TIMESTAMP, department_id INT, amount FLOAT);
CREATE INDEX invoice_created_idx on example.invoice(created);
INSERT INTO example.invoice(created, purchaser, amount)
VALUES (now(), 1, 100.0),
       (now(), 2, 200.0),
       (now(), 3, 300.0),
       (now(), 3, 100.0);

We can view the data that we just inserted by running a simple SELECT query:

SELECT * FROM example.invoice;

Which returns the following:

The purchaser column is linked to the department table ID, we leave it "loosely" linked as foreign keys are not supported. Let's first rename the purchaser column to department_id on the invoice table and then create the department table.

ALTER TABLE example.invoice
RENAME COLUMN purchaser TO department_id;

CREATE TABLE example.department(id INT PRIMARY KEY UNIQUE, name TEXT, email TEXT);
INSERT INTO example.department(id, name, email)
VALUES (1, 'Engineering', 'engineering@example.com'),
       (2, 'Sales', 'engineering@example.com'),
       (3, 'Marketing', 'marketing@example.com'),
       (4, 'HR', 'hr@@example.com');

Finally, let's query which department spent the most money:

SELECT dep.name, sum(inv.amount) AS spent
FROM example.department AS dep
    LEFT JOIN example.invoice inv ON dep.id = inv.department_id
GROUP BY dep.name
HAVING sum(inv.amount) > 0
ORDER BY spent DESC;

Which returns the following:

Conclusion

Rome is not built in a day, and neither is Aurora DSQL.

Don't expect to lift and shift applications from Postgres to Aurora DSQL. Instead, consider Aurora DSQL for its serverless multipurpose capabilities, it's an OLTP database that can also handle OLAP workloads. This makes it a great alternative to DynamoDB for those who need more powerful querying capabilities and don't know query patterns upfront.

Aurora DSQL feels like Postgres but drives like serverless. And I like that.

Further reading:

• AWS Aurora DSQL Announcement

• AWS Aurora DSQL Announcement Blog

• AWS Aurora DSQL Documentation