AWS re:Invent 2025 - Amazon Aurora DSQL: A developer's perspective (DAT401)

🦄 Making great presentations more accessible.
This project aims to enhances multilingual accessibility and discoverability while maintaining the integrity of original content. Detailed transcriptions and keyframes preserve the nuances and technical insights that make each session compelling.

Overview

📖 AWS re:Invent 2025 - Amazon Aurora DSQL: A developer's perspective (DAT401)

In this video, Marc and Raluca demonstrate building a banking application on Aurora DSQL using Lambda and CDK. They explain Aurora DSQL as a distributed PostgreSQL implementation combining relational database features with DynamoDB-like horizontal scaling and Lambda-like infrastructure management. The session covers IAM-based authentication using temporary tokens, strong snapshot isolation with optimistic concurrency control, and handling serialization errors (40001) through retry logic. They demonstrate CREATE INDEX ASYNC for non-blocking index creation, show how the system scales from 1,000 to 10,000 TPS without provisioning, and explain DPU-based pricing where a million TPS workload costs approximately $10 for 20-30 minutes.

; This article is entirely auto-generated while preserving the original presentation content as much as possible. Please note that there may be typos or inaccuracies.

Main Part

Introduction: Building a Banking Application with Aurora DSQL

Hello and welcome everyone. Good morning. Welcome to Reinvent. My name is Marc. I'm an engineer working on Aurora DSQL, and I'm joined by my colleague, Raluca. She's a database engineer working on Aurora DSQL with me. This is a code talk from a developer's perspective. We're going to be building a very simple application in Aurora DSQL this morning using Lambda. I'm going to show you how to connect to Aurora DSQL and how to use the CDK to manage your infrastructure declaratively. We're going to be writing a simple banking application. It turns out I owe Raluca money, so I need to settle my debts with her. We're going to show you how to scale that application, how to deal with concurrency, and what happens when there are conflicts in the application.

This is really meant to be a bit of a baking show, so there will be moments where the pie is in the oven and we're waiting for it. Feel free to ask us questions. Let's get into it.

I'm going to take over Marc's computer as part of his paying his debts to me. Quick show of hands: who here has used Aurora DSQL? That's about what I expected. Aurora DSQL was announced at Reinvent this time last year in preview, almost exactly one year ago, and became generally available earlier this year. We're about six months old now. So what is Aurora DSQL? Aurora DSQL is a distributed implementation of PostgreSQL. For the longest time, customers have been asking us: we really like relational databases where we can do joins, manage our schema, and run complex queries. But we also like systems like DynamoDB where there's no single point of failure and the system scales horizontally. We also like services like Lambda where we don't have to think about infrastructure, scaling, patching, or security.

These three things—I want them in one package. That's what we set out to do, and that's what Aurora DSQL is. Aurora DSQL has four pillars. You can find these on our website: virtually unlimited scale, high availability, no infrastructure management, and ease of use.

Creating and Connecting to an Aurora DSQL Cluster

Let's hop over to the Aurora DSQL console and create a cluster. This is going to go by really fast. I challenge you all to count to ten. Five, four, three, two, one. I was late. I can't count to ten. There we go. That's what it looks like to create an Aurora DSQL cluster. It's pretty fast. We've just rolled out fast cluster creation about two weeks ago. Let's do that in slow motion. Raluca, take us back over. You'll notice there are actually two options in the dropdown: single region or multi-region. For today's talk, we're going to be focusing on single region clusters.

There is no scroll bar on the screen. This is something I'm just super excited about because I've made a ton of databases in my career, and every time I make one, I find it really awful because I have to make all these decisions ahead of time. It's really hard to decide what instance type I want, how many replicas, what subnet I should be in, what security settings to use. It's just this endless list of choices, and they really matter. Which one of those is going to be the one that causes an outage and makes me wake up in the middle of the night? In Aurora DSQL, you don't have to worry about any of that. You can tag your cluster if you want, and if you want to change the tags later, you can do that.

Clusters are always encrypted by default. If you want to change to a different key, you can do that here. If you want to change your mind later and move to a different key, you can do that later. You can toggle deletion protection. We've recently rolled out some advanced security options for some of our customers, but that's really all there is to it.

Let's connect to the Aurora DSQL cluster. Raluca, right on. Let's choose this one. Have you done anything with it? Not yet. We have two ways to connect to the Aurora DSQL cluster. This is the query editor. We actually only shipped this about two weeks ago. This is a PostgreSQL editor running in the web browser. What's really cool about this editor is that your browser is connecting to Aurora DSQL directly. Aurora DSQL supports WebSockets, so your browser is connecting and speaking the PostgreSQL protocol right there.

We also have a CloudShell integration. Every page in the AWS console has a little shell at the bottom left. I don't know if you've all seen that, but you can also go to any Aurora DSQL cluster and click connect with CloudShell.

You can also go to any DSQL cluster and click connect with CloudShell. It will fill in the prompt with everything needed to connect.

Setting Up the Lambda Function and Authentication

That covers getting started. Now we're going to swap over and start building an application. Before we do that, I want to orient you. I've built a starter kit for this code talk that's on GitHub, and we can share the link with you after the talk. This starter kit has two key folders: the CDK folder and the Lambda folder. I'm going to start with the Lambda folder because it's really simple.

Here we have a function. Quick show of hands—who here has built a Lambda function before? Almost all of you. Great. I won't go into too much detail here. We have a request handler that takes an event. The input is a request, which is one of these request interfaces, and then the response. We can call this function and say hello to whatever we want, and it will return a classic greeting to us.

Meanwhile, in the CDK folder, we have our Lambda function being bundled up. Every time we deploy the stack, we take the latest code we've been working on, send it over to Lambda, and then we can invoke it. The second thing happening in the starter kit is a DSQL cluster. This cluster follows what you saw in the UI, with deletion protection and tags. If you're using TypeScript, you get nice auto-completion to see what else you can configure here. I've already created one of these, and when you run it, this is what you get.

You get two outputs defined at the bottom of the CDK stack. One shows what our cluster endpoint is, and the other shows the IAM role for our function. DSQL clusters have their own endpoint. Behind the scenes is a big shared endpoint, which is really important because that's part of how we provide automatic capacity management. But we present a unique endpoint to your cluster—basically a UUID for your cluster—which makes it really easy to connect with PostgreSQL clients because they expect a hostname. That's why we do it this way.

What I want to show you quickly is what it looks like to invoke our Lambda function. We use the AWS Lambda invoke command with the CLI, specify our function name, pass in name equals reinvent, and write the response to a file. Then we can look at that file and see the greeting.

Now I want to show you what it looks like to connect. We need to export a couple of variables to make the PostgreSQL shell work. The first one is the PG user, which I'll set to admin. The next one is the PG database, which will be postgres. Then we have PG host. If we try to connect now, we'll get a prompt for a password. Where does my password come from? I'm going to get one from the AWS command line tool. That's my password. Feel free to copy it down because you'll notice that it's already expired.

The way this works is through a technique that looks a lot like S3 pre-signed URLs, if you're familiar with those. The AWS SDK usually makes HTTP requests to a server saying, "I would like to download this file from my S3 bucket." It builds an HTTP request with headers and a body. Then the SDK signs that request by running cryptographic hash functions over your headers and the request body, and it puts a signature on it. That signature gives us a tamperproof property, so if anybody intercepted your request, they can't modify it. It also gives us expiry, so if somebody managed to intercept this request, they would have a time-bound window in which to exploit it. Usually you can't do that because your request has been sent over a TLS connection.

Something like S3 pre-signed URLs works a little bit differently. What you're saying is, "I, who have access to my S3 bucket, am going to make this request. But before I send the request, I'm just going to take that signed URL and intentionally give it to somebody else." That's pretty much what's going on here.

You'll see that this looks like a URL. Here's the host, it's our cluster. We have an action called DB connect admin, and then we have that signature. This signature is going to take the place of our password. So rather than giving a very short-lived password, I can just export this into an environment variable. Now we should be in. Did I get the host wrong? I remember what I didn't do. I haven't had coffee this morning. Did you send the region? Okay, there we're in. I'm using a demo account and I forgot to switch the profile of the shell. But we're in now and we can just run standard PostgreSQL commands. What I'm going to do is exactly the same thing in our Lambda function.

Configuring Database Connections and IAM Role Mapping

We're going to go over to this Lambda function and you'll notice that there's a file here called DB.TS. This is essentially the Node PostgreSQL driver. I've just gone to their README and copied and pasted everything into this file. There's one change, which is this password. The password is going to come from calling a function. That function is essentially the same thing that we just did on the command line. This is using the SDK to generate one of these tokens. You'll notice that the cluster endpoint is coming in through an environment variable. Let's just wire this up quickly. We're going to say my cluster endpoint.

While you do that, we do take questions. Wave your hand if you want me to grab the mic and get a question. We have a question. You mentioned that you don't need security groups and things like that. Does that mean DSQL runs as just like a general service, kind of like S3? You don't have it in your VPC or something like that. We do have public endpoints and we do have the option to set up a VPC endpoint if you need that, but it would be through a VPC endpoint. Yes, correctly. Another question. Is there a MySQL flavor of DSQL? Not yet. But we are not very specific in terms of SQL dialect. If you have an application that's pretty much sticking to the general dialect, you should be able to use DSQL as well. Okay, one question. Just to touch on the VPC endpoint part, I noticed sometimes in some newer services in AWS, even if you do have VPC endpoints, you don't necessarily always support endpoint policies. Do you support endpoint policies for DSQL? We just added resource-based policies. Yes, okay, that's really important. What's the use case? Is it we don't want the potential of people getting access to our VPC or it's security requires it on our side? I can't remember the exact details, but there's always a sticking point for us. Got it. Thank you.

Yeah, there's actually a really detailed blog post on how to do this. If you want to chat to me afterwards, I can give you the link. It goes through like there are five different ways that you can lock this down. As Raluca just mentioned, and you saw on the cluster creation pages that advanced checkbox, you can put in just any kind of standard AWS policy language stuff there. You can say restrict to these IP addresses, restrict to these VPCs, anything like that. Okay. I need to quickly update you on what I did in this. We've changed our Lambda function to say give me one of these database pools, and then we're going to run a query. The idea is that if we're still able to see our greeting, then we've connected to DSQL. I want to quickly draw your attention to this function here. This is going to make sure that we're going to reuse our connections between Lambda invocations. So the first time our Lambda starts up, we're going to have to initialize the pool.

This pool maintains up to 20 connections in the background. Our Lambda function gets one request at a time, so this is totally overkill, but you can do whatever you want here. Every time a connection is opened, we grab one of these authentication tokens. This is a really fast operation that takes something like 20 nanoseconds to run. If you're using a service like S3 or DynamoDB, you're actually running this all the time on every request. With Aurora DSQL, you're just running this code once per connection.

The second thing we've done here is we've asked the SDK to wire in our endpoint into the Lambda function. Then down here, we've told IAM that we want to allow this Lambda function to connect to the database. If I do that and run my function, I'm expecting it to fail. The reason I'm expecting it to fail is because we've not yet told the database to allow our function to connect. We've told Lambda that the role you have is allowed to connect to this Aurora DSQL cluster. But the Aurora DSQL cluster is trying to see whether our application, which is connecting as a user called myapp, is allowed to connect. We haven't actually set that up yet.

Let's do that quickly. We can run create role myapp with login. Then I need to do some copy pasting of this ARN to say that this AWS ARN is allowed to use this role. If we try to run that again, we should hopefully see success. The way this works is we can actually just take a look at this table quickly. You don't have to remember these names. I just used tab completion there. There's a namespace in the database called sys that contains all our metadata. You'll see that we've got a mapping that says this IAM role is allowed to become this PostgreSQL role.

The reason we went this direction in the Aurora DSQL design is that it exposes the full power of PostgreSQL permissions. If you want to say here's a role that can only read these tables in the schema, or a role that has read, write, or only access to specific tables, you can do all of that with the standard PostgreSQL role system. The only thing you need to do is bind which IAM entities are allowed to become which roles.

Understanding Transactions and Optimistic Concurrency Control

I'm going to get coding on our banking application. But before we do that, I want to quickly show you what the schema looks like. We're going to have a table called accounts. Accounts have an ID, which is just an integer and a primary key. They're going to have a balance, which is also an integer. Once we have this setup, we can start to do a balance transfer. We can run a transaction where we have some money in one account, some money in another account, and then we can move it over. I'm going to do that the wrong way, and then we're going to talk about why that's wrong.

Can I start talking about transactions? As a database geek, I really love transactions. The reason I prefer to do database changes inside a transaction is because it allows me to specify that all the changes succeed or fail at once. Everything that goes between a begin and then commit or rollback will succeed together or fail together. That's the atomicity of a transaction.

I just did it the wrong way, right? Because what happens if after running this statement, my laptop died? At this point, what I'm intending to do is move some money between accounts. But any number of things can go wrong. We may lose connection, we may lose connectivity, the database may crash. But there also could be business logic errors, like maybe I don't have enough money. What happens if I only have ten dollars to my name, and unfortunately I get into a little bit of debt? I'm trying to pay off all my debt at once. So I'm going to go to Ruluka. I'm going to go to somebody else. I'm going to say, start a transaction. How much money is in my account? Ten dollars. Let's go and pay both debts off at the same time, right? Without any kind of consistency checks, two transactions could start. They could both determine that I have enough money to pay.

We could deduct money from both accounts, and then we're going to end up with a bad outcome where I've been able to spend $20 despite only having one. For these kinds of reasons, we want to make sure that we're always using transactions. DSQL supports something that's very equivalent to PostgreSQL's repeatable read isolation, except it's not stronger than that. It's a strong snapshot isolation mode. For those of you who aren't deeply familiar with isolation modes, really the easiest way to understand them is through this concept of anomalies. An anomaly is something that you can see in your transaction that doesn't make sense. A good example of this one you've probably all run into is a phantom row. You run a select statement, you don't see a row, you run a select statement again, you see the row, you run it again, you don't see the row. These kinds of things are happening because there are multiple transactions running on the database the whole time.

The job of the database is to try and isolate our transaction as much as possible to give us the illusion that we're the only user on the system. That's going to allow us to write correct code. Because DSQL is slightly stronger than repeatable read, that means there are fewer anomalies. In fact, DSQL only has one anomaly called write skew. If anybody would like to talk about write skew later in this talk, depending on how much time we have, we can do that.

What I want to do right now is start to take this lambda function that we have and turn it into a little banking application. I'm just going to add one thing to what Mark just mentioned about strong snapshot isolation. In DSQL, we have this property of being strongly consistent, so whatever you write will be immediately seen by all the other places you read from. You don't have to worry about routing your query to a specific reader or to a specific endpoint. Wherever you read from, you're going to immediately see what has been written. That is strong consistency as opposed to eventual consistency that some other database flavors would offer. So that's strong snapshot isolation.

Then there are two more things we call transactions ACID because they're atomic, consistent, isolated, and durable. I'm going to quickly touch on consistency while Mark is doing all the coding there. That means you don't get these phantom money transfers where you've sent money, you see it in one account, you don't see it in the other. Everything stays consistent. The database moves from one valid state to another in a consistent mode. Then you have durability, which means the database will recover in case of errors for you. Once you've acknowledged that a write has been made to the database, you'll always continue to see that data persisted in the database. You don't have to worry about machine restarts or failures.

All right, let's see what we're doing. I am doing ceremony. This is kind of a little boring code, but I wanted to show you what it looks like to write these transactions at a very low level. If we have time later on in this talk, we can maybe move to a bit of a higher level of abstraction through an ORM. We have our try-catch pattern. Within the try-catch, we have a begin and commit, and that's going to start our transaction boundary. If anything goes wrong, we are just going to throw the error.

We're going to have to take care to return this connection to the pool. Otherwise, when we run the function again, we're going to start to go from twenty available connections to nineteen until we eventually have an outage. Here's our payer state, here's our payer query. We're going to be taking money out of the account. Notice how I'm using bind variables just to make sure that we don't have a case of SQL injection going on here. This is the payee who's going to be receiving money. We need to do a little bit of checks here. What can go wrong? Think about that for a moment. Let me know if you need me to hand the mic to anyone.

The thing is, let's say we have two transactions running the same transactions. We're drawing the amount from the bank account. The bank account has a one hundred dollar balance. The transaction is also doing the same, withdrawing one hundred dollars because both transactions are running in parallel. How are you going to handle that situation?

$100 because both transactions are running in parallel. How are you going to handle that situation? That's an excellent question. Thank you, because it segues into what I wanted to talk to you next about, which is how DSQL handles concurrency control. We do optimistic concurrency control, which means when two transactions try to update the same row, they will both be allowed to try. Then the first transaction to issue the commit will win, and the second transaction will have to retry. That retry logic needs to be embedded in the application code. The ideal design pattern is idempotent so that transactions can just retry on failure without needing to check or embed any business logic. That's optimistic concurrency control. If you were to use the opposite, which is pessimistic concurrency control, this used to be my nightmare as an Oracle DBA. You'd get all these sessions with all the transactions lining up, waiting for a lock. I think the wait event was called "enqueue transaction row lock contention" or something like that, and in PostgreSQL it would be a lock relation. What happens there is the first transaction trying to grab and update the row grabs a lock on that row, and then the subsequent ones will just line up saying "I want to update that row, I want to update that row." Eventually, either the first session needs to be killed or it just finishes the processing it needed to do. Then the whole thing unblocks. That particular scenario also requires an abort and retry logic, except in SQL you just get notified sooner and you need to retry. The first transaction, when it acquires a lock on the row, the row will be released only when the commit happens in this SQL. In this code, the idea is that the variables are marked, but your variables are all wrong. Thank you so much. You probably just saved us an hour. So the release is not going to happen. There's no lock. It's lock-free. There is no lock in SQL. So that's why we can also support higher transaction throughput because you don't have to wait for another transaction. There's no coordination. Everybody who wants to update will just issue the update, and then the first one to commit wins. The other ones get a serialization error, which is PostgreSQL error code 40001. By the way, I have to tell you this: if you go to the AWS booth in the expo at the Venetian and they ask you the one unique thing you've learned from this session, and you tell them about serialization error 40001, they will hand you an AWS hoodie and a DSQL sticker. Do you have more questions? I have one more. Thank you. We're actually going to look right at conflicts in a moment because as soon as we start to run this a bunch of times, we're going to see exactly your question in action. So let's see if we can invoke this. Mark, I have one more question. Can I take it? Yeah, let's take that. It's actually not a question, but a little bit of a contrarian comment if I may. Don't get me wrong, I love SQL and I think this is a marvel of engineering and brings a lot of value. I just want to say that I'm not totally sold on the idea that the optimistic locking approach used here is straight away better than classical PostgreSQL because having to do idempotency puts a burden on the developer. If you run on repeatable read committed mode, which is the usual for PostgreSQL and most applications are fine with that, it's an extra burden that adds friction on the application developer side. Having to have retries can also hamper performance because you're doing retries versus just waiting a little bit for a lock to be acquired. So this comment is not that it doesn't make sense and it's an acceptable compromise, but still a compromise. I don't think it's just straight out better. We call it a trade-off. It is what you need to obtain higher throughput. So we're going to actually dig into OCC in a minute. Your two points are: is it better than PostgreSQL, is it ergonomic, and what about performance? We'll make sure we cover all three of those. Our function works. We're returning the balance using the PostgreSQL returning syntax.

Handling Conflicts and Implementing Retry Logic

So if we run this a bunch of times, we can see our money going down. I'm going to quickly run this setup script, and then we can see we have 1000 accounts. Let's see if I remember where we are now. We're on chapter 2, I think so, maybe. OK, so this is going to open up 1000 requests in parallel and it's going to call that function. Every time it calls that function, it's going to pick a random account, a random payer, a random payee, and move some money around. This is going to keep track of the errors we get back, and you'll see that we're getting change conflicts with another transaction, right?

The reason this is important from a correctness point of view is that we are running all of these checks here: does the payer exist? Does the payee exist? Do I have enough money? Because we have this concurrency going on, Aurora DSQL is detecting, and it's detecting here on line 43, that somebody else has modified the data that you've been working with, right? So you need to try again. The way we do that is we're going to handle this error code and just try again. So we'll say while true. Then I need to indent everything. In our catch block, this gets a little funky in TypeScript because of error handling. But what we're going to say is, if the error is not a PG error, something else has just gone wrong. Then we're going to run this old code. Otherwise, we're going to take a look at the error code.

This function here just lets us get some type information out of the error. We can just run and continue. Then we'll have a to do over here. Now, it's kind of important—I've made this bug a bunch of times —to make sure that we are grabbing a new connection out of the pool inside this loop over here. I'm going to do a little bit more decoration here, just so we can keep track of our retries. So we'll say retries is a number. That's a type of. And we'll say duration is a number too. And then at the start here, we'll take the current time. And then down here, we will return the duration. We're going to do that down here too for a reason that will become apparent. OK, and then we need to return retries too, right? Right, so we just have some telemetry around the OCC retries to your point. You can exactly quantify what your hotspots are, how many times you have to retry if the application tends to have these hot deadlocks and so on.

If you do want, for example, not to have all the sessions try retry it once, then you would add some exponential backoff and retries and things like that, but in this case I don't think that's going to be required in the simple application. That is, hold on. I want everybody to hear what you say. Justify me, absolutely. I like the word that it's a trade-off. That's exactly what it is. That's why I just want to make sure that it's not presented as a best solution because again if I run, for example, read committed mode using regular PostgreSQL, I will not need to do this while true loop and check for the errors and handle the retries. I'm not saying this is better or worse. It's again a trade-off, but here you're forced versus not being forced and therefore it's a compromise, yeah. Thanks. Does anybody have any other questions? OK, so this code is actually still wrong. The reason is we need to handle our errors a little bit better over here, but we'll see what happens when we run it.

Adding this retry loop is definitely work. I've gone a bit above and beyond in terms of adding telemetry because I want you to see when we run this code what the error rate is. It's going to be really bad, and the reason it's really bad is that we only have 1000 accounts and we're running at 1000 requests per second. So the chance of there being two concurrent requests that conflict is going to be pretty high. Timeout exceeded. I think I'm leaking a connection somewhere. Give me a moment to debug. I'm happy to take more questions in the meantime.

I saw that hand first, so I'll go there first. Thank you. I was just wondering what the problem was with what you mentioned about having to push the client acquisition inside the retry loop. What's the issue you run into if you don't do that? That's a great question. Give me a second, I can't do two things at once. The reason we had a leak here is that I wasn't returning the connection to the pool. The reason we want to do that is because depending on how much time we get here today, I want to show you just how you can recover from other kinds of errors in Aurora DSQL.

For example, when you're running on a single node system, what happens if your primary fails? You have to learn about the new primary and shift your connections over. In Aurora DSQL, because we're a distributed system, each of your connections is actually running on a different machine somewhere. When something goes wrong, you're going to lose just a fraction of your healthy connections, depending on what failed. It's really important that your application can take advantage of that, grab a new connection, and keep going. The simplest way to do that is to grab a connection out of the pool when you retry. If anything goes wrong, you can change that catch handler to simply say if any of these conditions happen, just grab a new connection and try again. We have one more question here, and then I'll come there.

Do we have to pay for each connection? If I open 1000 connections, do I have to pay something if I don't run any query? The question is how do you pay for connections. The answer is connections are free, with an asterisk. The asterisk is that opening a connection does a small number of reads because it has to do things like check your permissions. But the thing you're actually paying for is usage. You're paying for reads and writes. Once you've opened a connection, you can just leave it there idle, and it's fine.

Performance Optimization with Indexes and Query Planning

We're done with that, and we'll notice that our minimum execution times are pretty low, but our maximum execution times are pretty high. The reason for that is we're doing a bunch of retries. What I'm going to do now is actually load the system up a little bit more. This script is going to dump in a million accounts. While we do that, we're going to quickly add another table, which is our transaction log table. Mark, I have one more question. I know in the Aurora DSQL launch business there were a few unsupported features like foreign keys and partition sequences. Are things like that planned to be out in the future, or is it that the distributed nature kind of makes it so they'd not be able to be implemented? That's a great question. Foreign keys are on the roadmap. We do plan to be closing that gap with the PostgreSQL competitive features.

Do you have one more question? In a distributed database kind of thing, how do you manage consistency? You mentioned that write consistency is maintained in an optimistic way, and once you get a confirmation about the write commit, it will be read across the cluster in the same way. If somebody reads a transaction which is written somewhere else, does the session need to wait? How does it get the committed block from the storage because storage is being shared, not the data?

So how does that happen? This is the reason we do optimistic concurrency control in the system. There are really two ways to do it. With pessimistic concurrency control, as soon as you do anything in the system, you have to put a lock there, saying you're busy. That would require us pushing down that locking information somewhere. With optimistic concurrency control, we don't do that. We just do our work completely independent of everybody else.

When we commit, the commit is written to a single place, and that single place looks at everything you're doing. It checks if somebody else has done something that interferes with it. You need to check if somebody else made a transaction on the block that somebody else is interested in. It looks at the changes you're making. In this case, because both transactions are reading and writing the payer and the payee ID, if we have one transaction that is 1, 2, 3 (that's account 1 and account 3), and the other one is account 3 and account 5, then because both of them have 3 in the read set, when they get to the point of commit, that's when the system, called the adjudicator, is going to look at that and say only one of these can go through, and the other one has to retry.

There's nothing happening at the storage layer to do this. It's all happening at the commit layer. So do you have a memory share concept between the cluster also? Everything is just fully independent. We have a table of transactions now. All this is is a record of everything we've done in the system. There are two columns that are being automatically generated by the database. The first one is the ID, which is the UUID. The reason we want to use a UUID here is for the same reason you would want to use one in DynamoDB. That's going to give you the best possible fan-out, which is maybe a different philosophy than you would have on an instance-based PostgreSQL system, where you want to do everything sequentially to get the best I/O. In DSQL, it's just way better to go wide. The last column created is just a timestamp.

What we're going to do in our function is just populate this table. We'll pay her first. Did you assign the role? Did I do that? I don't think maybe I missed it. Thank you for checking me. What I want to show you now is this table's going to start to fill up rapidly when we start to run at scale. We're going to want to be able to query that. Imagine this is a real application. Our customers go to our page and they're clicking on their homepage, and they want to see what transactions they've run. We're going to want to run a query, select stuff from transactions. Where we can pick out for this account. Select star from transactions where payee ID equals that number and then we see our log. If we run this a few more times, we should see more rows. There we go. The RNG was on my side.

But I want to show you quickly how to think about performance here. If we do explain select from account, you'll see that we're doing a full scan on this table because there aren't any indexes. What we can do here is use explain analyze, which is going to give us a little bit more information because this actually runs a query.

So we can say, create index, and I'm intentionally getting this wrong for a moment, on transactions, payer ID, and I'm going to insert created_at in here for a reason. And we get an error: unsupported mode. You'll see that we have to use the syntax called CREATE INDEX ASYNC. The reason we do this in DSQL is because in PostgreSQL, when you create an index by default, you're going to be taking a lock on the table, so nobody else can record any transactions, because the system needs to go row by row and make sure that it's building the index correctly.

PostgreSQL has an alternative syntax called CREATE INDEX CONCURRENTLY, which does less locking. However, it still does some locking because it has to wait for a quiescent phase before it can start. It also has to scan the table twice, which can be very expensive and cause performance impact. There's another small issue with CREATE INDEX CONCURRENTLY: there's no way to track it. When we were thinking about building the system, we wanted to solve all these problems.

CREATE INDEX ASYNC gives you a job ID that you can wait for. So if we do call sys.wait_for_job and put this ID in here, it should say it succeeded. You can actually take a look at all of the jobs that the system has been running. By digging into this table, you'll notice that there's this index build that we just did here. But there's also these ANALYZE jobs, right? The system is constantly looking at stats on your tables to make sure that it's building the right query plans.

But if we go and run our query again, you'll notice that it's still doing a full scan. This is just a little gotcha for you to keep in mind. Actually, I probably picked the wrong field here. We're using our index here, but if we picked a different ID, maybe one with fewer transactions, or if we had a smaller table, you may still see a full scan because the query plan is actually free to choose. It can say, hey, should I scan this whole table? If there's only 10 rows in it, then it might just scan the whole table.

Or it's going to take a look at those ANALYZE jobs that are running, it's going to take a look at the indexes that are running, and pick an efficient query plan for you. This may look very intimidating if you're not used to reading query plans, just dump it in your favorite AI tool and it will do a really good job telling you what's going on here. But I want to quickly call out a couple of things here. This is using an index, and there is no sorting. If we do something like ORDER BY created_at DESCENDING, you'll see that there's still no sorting, just your index scan for memory, quick sort, anything like that, not in that list.

Because we actually put the timestamp in the index, right? So this is going to allow the database to traverse the data in the right order. What's really cool about CREATE INDEX ASYNC is you can do this fully online, at any scale you want, and there's never any blocking and there's never going to be any performance impact. Because it's built completely asynchronously in the background using dedicated resources for your index.

Scaling to High Throughput and Understanding Aurora DSQL Pricing

Now with that said, I want to show you how we can scale this application out. This could get a little messy because, you know, we're going to run this tool. This is a sustained load generator. We're going to tell it how many accounts there are in the system. There's a million. And then we're going to give each host a target number of transactions per second. What they're going to do is you'll notice this little in-flight counter over here. They're going to continue to open batches of connections until this number on the bottom left gets to their target.

So with this running, we have 4 machines driving 4000 TPS. If we jump over here, we can do this, and this is actually going to go row by row and count every transaction we have. If I do this again, it's going to be even slower. It's going to go row by row of even more data. But this number should start ticking up.

That didn't fail, so let's go a little bit higher. At this number, I should hit the account quota for DSQL right, on my system. Pay attention to these numbers here: error and OCC. We're not getting any errors, and we're not getting any OCC errors. The reason for that is by creating more right keys through the number of accounts, the probability of any of these transactions conflicting is low. This is obviously a little contrived example. I didn't want to spend too much of this talk getting into some kind of really complex real world schema. But the takeaway for you is to think about designing your app and schema so that you don't have what we call hot write keys.

While that runs, I want to take a quick look at pricing. If we jump over to our cluster, I want to grab everyone's attention to highlight that we went from 1,000 concurrency to what was it, 110,000? How much did you put in the system? We're running at about 10,000 TPS right now and we didn't need to provision higher instances or more parameter groups with a higher number of connections allowed or any of that. That's another nice thing to bear in mind. Nothing to patch, nothing to scale. Just send traffic at the system, it scales for you. And if you're using something like Lambda that can scale, then you also don't have to worry about the compute.

So this is the cluster page. I've clicked on metrics, I clicked on usage. You'll see that there are really two metrics for you to track. There is total DPU, a DPU is a Distributed Processing Unit. This is a pay-per-use metric. If you used to DynamoDB, you pay for writes and you pay for reads and you pay for storage. Aurora DSQL has all three of these units too. But it also has an additional one, which is called compute. This is like Lambda, you're paying for compute seconds, because we're a SQL engine. You can run these complex queries that are doing joins, that can be doing aggregates. You can do Fibonacci in it. So there needs to be some way to track that usage. DPUs can be monitored with CloudWatch. I've also built a little tool.

If we can take this cluster ID, make sure I'm in the right place, we can paste it in here. That's going to go fetch those CloudWatch metrics, just sum up all of the data points, run it through our pricing calculator. You'll see that we spent $2 on Aurora DSQL. If I stop this load and we wait a few minutes and we run this again, it will still be $2. If we wait 5 minutes and leave the load running, it will eventually take up to $3 or $4. I've run this workload for 20 to 30 minutes, and I came back and it's running at a million TPS and I spent $10. This is one of the really cool things about Aurora DSQL: not only are you not having to provision hardware, but the database just out of the box is going to give you this ability to scale up and pay only for what you're using in that moment.

So if this load stops, that's it, you're done. The thing that you're going to continue to pay for is storage. I think we have 5 minutes left, so we should probably wrap it up and take any final questions. I have 10 seconds and then the rest of questions, if there are more, I'll be hanging around outside afterwards. Thanks everyone. Please remember to fill in the survey for the session.

---

; This article is entirely auto-generated using Amazon Bedrock.