Skip to main content

From DynamoDB to Aurora DSQL: migrating Group Expense with Ease

· 21 min read
Ivan Barlog
Ivan Barlog
AWS Solutions Architect @ BeeSolve

I built Group Expense with Ease because I wanted Splitwise without ads. It also turned out to be my favourite playground for trying new technology, frameworks and approaches. To keep that tradition alive I moved it from DynamoDB to Aurora DSQL and measured what changed.

Disclaimer

This article was put together by an LLM agent, not written by me word for word. The idea, the opinions and all the technical content are mine - I just did not have the time to write it up, so I asked the agent to assemble it from my notes, my journal of the migration and my earlier posts. The agent did not come up with any of this. As with every generated article on this blog, I have proofread and edited it myself before publishing.

Motivation​

I already wrote about why this site exists and how I rewrote it from an SPA to SSR. At the end of that post I mentioned I was playing with the idea of replacing DynamoDB with Aurora DSQL. Here we are.

DynamoDB was a great fit for this project. But let's be honest - it was a great fit because I made it a great fit. I used a lot of tricks and patterns to get there (I will go over them below) and I wrapped everything in Valibot schemas so I could trust the data coming out of the table. Some things are just easier in plain SQL though.

What held me back from DSQL for a long time was the missing foreign keys. That changed when AWS added support for foreign key constraints. From my perspective that was the moment DSQL approached maturity for a project like this one.

Group Expense with Ease is a small project and I have it fully under control, so the choice was obvious. If something goes wrong, nobody but me and a few friends will notice.

DynamoDB vs DSQL​

Both are serverless, meaning you pay for what you use and there is nothing to provision. Both seem to have similar pricing for a small project:

  • DynamoDB on-demand charges per read and write request plus storage
  • DSQL charges for DPUs and storage ($8 per million DPUs, $0.33 per GB-month when I checked, with a free tier of 100,000 DPUs and 1 GB per month) and scales to zero when idle

The production table is about 108 KB and the DynamoDB bill for it was $0 in the months I looked at. I did not measure DPU usage of the DSQL version, so I have no honest price comparison for you. Spoiler: for a project of this size I do not expect one to matter.

The real difference is the whole approach to the data. With DynamoDB you design the table around your access patterns and do the rest in code. With DSQL you model the data and let the database do the work. I will show you what that means in the next two sections.

Both are also distributed databases. That matters because it means I did not switch from a distributed system to a classic server-based one like RDS, which would be a much bigger difference. If you want to study this further:

You do not need to understand either of these deeply to follow the rest of the article. Just know that DSQL is not a single Postgres server in disguise.

DynamoDB patterns​

The application keeps all of its domain data (users, groups, members, expenses, debts, friendships, archives, pending notifications) in one DynamoDB table - single table design. Partition key id, sort key entity and a reverse global secondary index on entity and id so I can list everything of one kind.

Even though it is a single table, the data is full of relations. And this is where the patterns come from.

DynamoDB is not a relational database and you cannot query it the way you query MySQL or Postgres. You can read items by their key, or query a range of items under one partition key (or a secondary index). That is fast and it scales, but it is all you get:

  • there are no joins, so anything that spans two kinds of items needs more than one request
  • there are no foreign keys, so nobody checks that a relation points to something that exists
  • there are no cheap aggregates, so "how many expenses does this group have?" means reading all of them (and paying for it) unless you stored the number somewhere
  • you can only look up data by the keys you designed up front, so each additional way of looking at the data needs its own item or index

You are not supposed to fight this. You are supposed to design the table around your access patterns, which means doing at write time what SQL does at read time. DynamoDB does not know about relations, so I kept them by hand:

  • Mirrored records. I need to list members of a group and also groups of a user. A key can only be looked up one way, so a membership is stored twice - once as member|user|<id> under the group and once as member|group|<id> under the user. A friendship is stored the same way, two items per friendship. Both mirrors are written in one transaction so they stay in sync.
  • Sets and maps on the item. Without joins, loading a group with its members, notification settings and debts should be one read. So the group item holds sets of member ids, a set of users who want notifications, a set of users who confirmed a review and a map of debts.
  • Counters. expensesCount, groupsCount and friendRequestCount exist because counting is expensive. They are incremented and decremented inside many transactions so a lookup is one cheap read.
  • Transactions with condition checks. There are no foreign keys, so to prove that every borrower of an expense is a member of the group, the transaction contained one ConditionCheck item per borrower (which also capped an expense at about 98 borrowers because of the 100 item limit).
  • TTL. Pending notifications live for 72 hours and group archives for 3 months. This one is not a workaround - it is a feature I liked. I just wrote the expiresAt attribute and DynamoDB deleted the items for me for free.
  • Key encoding. Since the key is the only thing you can look up by, debts, archives and pending notifications encode multiple values in the id (groupId#debtor#creditor) which I split and validated with Valibot on every read.

I did not use DynamoDB Streams for change data capture. That turned out to be useful because there was nothing downstream I would have to move to DSQL together with the data.

Most of the heavy lifting happened in code. Since DynamoDB cannot join, I fetched ids first and then the items by ids, with BatchGet in chunks of 100 and a loop for unprocessed keys. For deletes I used BatchWrite in chunks of 25. Here is what adding a member looked like (trimmed):

await this.props.dynamo.send(
  new TransactWriteCommand({
    TransactItems: [
      { Put: { TableName: this.props.tableName, Item: member } },
      { Put: { TableName: this.props.tableName, Item: membership } },
      {
        Update: {
          TableName: this.props.tableName,
          Key: toGroupKey(props.groupId),
          UpdateExpression: `ADD #members :member, #notifyUserIds :member`,
          // ...
        },
      },
      {
        Update: {
          TableName: this.props.tableName,
          Key: toUserKey(props.userId),
          UpdateExpression: `ADD #groupsCount :increment`,
          // ...
        },
      },
    ],
  }),
);

Two mirrored puts, a set update and a counter update - to say "this user is in this group". And listing the groups of a user looked like this (also trimmed):

const groupIds = await groupMembers.listManyGroupId(userId);
if (groupIds.length === 0) return [];

const [groupList, friendList] = await Promise.all([
  groups.getMany(groupIds),
  friends.listFriendships(userId),
]);

const confirmedFriends = friendList
  .filter(({ confirmed }) => confirmed)
  .map(({ friendId }) => friendId);

return groupList
  .filter(notArchived)
  .filter(ownOrConfirmed)
  .sort(newestFirst)
  .map(/* ... */);

Three round trips (list ids, batch get groups, list friendships) and then filtering, sorting and mapping in memory.

And you know what? This is still perfectly fine for most workloads. Throughout the life of this service I have not experienced a single problem with it. We did not always have the luxury of hitting DynamoDB once, but it was fast enough and it was cheap. I am not migrating because DynamoDB failed me.

DSQL alternatives​

The main theme of this migration: I did not move the data 1:1.

I could have. I could have created a table with pk and sk columns, stored mirrored rows and kept the counters. It would have worked and it would not have been much of an improvement. It would also be using the technology wrongly - like hitting a screw with a hammer. The relational model exists for a reason, so I used it.

If you are not familiar with the terms, normalization means storing every fact once and referring to it by key (denormalization is the opposite - copying data around so reads are quick). DynamoDB pushes you towards denormalization. SQL lets you normalize and join.

One row per relation​

A membership is now one row in group_members with a foreign key to the group and to the user:

export const groupMembers = pgTable(
  "group_members",
  {
    groupId: text()
      .notNull()
      .references(() => groups.id),
    userId: text()
      .notNull()
      .references(() => users.id),
    notify: boolean().notNull().default(true),
    reviewConfirmedAt: timestamptz(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (table) => [
    primaryKey({ columns: [table.groupId, table.userId] }),
    // "groups of a user".
    index("group_members_user_id_idx").on(table.userId),
  ],
);

No mirror. No members set on the group. No notifyUserIds set (it is a notify column) and no reviewConfirmed set (it is a nullable timestamp). A friendship is one row too, and a unique index on least(requester_id, acceptor_id), greatest(requester_id, acceptor_id) makes sure the same pair cannot be stored twice in two directions.

This matters because mirrors drift. When I did a dry run of the migration against production I found that the table had 35 member|user| items and only 30 member|group| items. Five groups had been removed and left one mirror item behind. That is 5 out of 35 (14%) drifting silently with no visible symptom. In the relational schema this kind of drift cannot be stored.

Counters are gone​

I no longer keep expensesCount, groupsCount or friendRequestCount. They are count(*) or EXISTS at read time. Fewer rows written per transaction and nothing to get out of sync.

Foreign keys instead of condition checks​

Remember the one ConditionCheck per borrower? It is now a composite foreign key from expense_borrowers (group_id, user_id) to group_members (group_id, user_id). The database refuses a borrower who is not in the group. As a bonus the same trick works for the payer, which the old code never checked at all. The 98 borrower cap is gone as well because it was a DynamoDB limit, not a domain one.

A query instead of a loop​

The groups list from above is now one statement:

const rows = await this.props.db
  .select({
    id: groups.id,
    name: groups.name,
    status: groups.status,
    createdAt: groups.createdAt,
    membersCount: sql<number>`(select count(*)::int from group_members m where m.group_id = ${groups.id})`,
    settledUp: sql<boolean>`(${groups.status} = 'locked' and not exists (
      select 1 from debts d where d.group_id = ${groups.id} and d.settled_at is null))`,
    notifyEnabled: groupMembers.notify,
  })
  .from(groups)
  .innerJoin(groupMembers, eq(groupMembers.groupId, groups.id))
  .where(/* member of the group, not archiving, owner is me or my confirmed friend */)
  .orderBy(desc(groups.createdAt));

Filtering, sorting and counting is done by the database. In the same fashion BatchGet and BatchWrite chunking loops are gone - there are JOINs, WHERE conditions, INSERT ... SELECT and DELETE ... RETURNING. Removing a group used to be one transaction per member plus the group. Now it is one transaction.

Here is what that looks like in numbers. I counted these in the repository at the commit before the migration and on the migration branch, so take them as indicative:

MeasureDynamoDBDSQL + Drizzle
Lines in the 8 repository modules2,5341,774 (-30%)
Data layer including schema and client2,5922,280 (-12%)
Hand-written chunking and unprocessed-items loops160
TransactWriteCommand calls vs transact( calls136
Lines mentioning a stored counter250
Valibot "parse on every read" references400

Lines of code are a weak proxy, and the table is not the whole picture. The new schema is 340 declarative lines which replaced validation logic but also holds constraints the old code never had. And I also wrote things which did not exist before: the migration scripts, a runbook, the cleanup job and a test harness. A fair total should include them. Still, the code that is left is the kind I like - less of it, and less of it is mine to get wrong.

Round trips improved in the same direction:

PathBeforeAfter
list groups31
group membersids fetch + batch get1 join
remove groupone transaction per member + the group1 transaction
friend listlist + batch get1 UNION ALL join
transient friendships of a group1 read + n(n-1)/2 transactions1 INSERT ... SELECT ... RETURNING + 1 read

The "before" numbers come from reading the old code, not from measuring it.

TTL is now a cron job​

DSQL has no TTL. So I did what DynamoDB did for me, only now I do the deletion myself.

Reading is the same pattern as before: every read filters by expires_at > now(), so an expired row is invisible even if it was not deleted yet. DynamoDB TTL has the same property - items can live for a while after they expire, which is why you filter on expiresAt in DynamoDB too.

The deletion is a daily job. I put it into the digest Lambda which already runs every day, after the digest itself. Since SQL can filter, it is a single statement per chunk:

readonly deleteExpired = async (limit: number = deleteExpiredChunkSize) => {
  const deleted = await this.props.db
    .delete(pendingNotifications)
    .where(
      sql`${pendingNotifications.id} in (
        select ${pendingNotifications.id} from ${pendingNotifications}
        where ${pendingNotifications.expiresAt} <= now() limit ${limit})`,
    )
    .returning({ id: pendingNotifications.id });

  return deleted.length;
};

A DSQL transaction can touch at most 3,000 rows, so the job deletes 1,000 rows per call and repeats until a call returns fewer (capped at 50 calls per table, so a misbehaving query cannot loop forever). A cleanup failure is logged and swallowed so it cannot fail the digest and cause a duplicate one. Correctness never depends on the cleanup because of the read filter.

The difference to be aware of: DynamoDB TTL deletes are free and need no schedule. Here the deletion is my job, it costs DPUs and it needs a schedule.

What did not move​

Not everything got easier. The debt simplification algorithm is still TypeScript. Sorting names stays in TypeScript too. DSQL can only sort text by raw byte values, so "Zuzana" would come before "adam" and "Šimon" would land after "Zuzana". localeCompare sorts the way people expect, so ORDER BY name is not an option for me. And DSQL brought new work: retrying optimistic concurrency conflicts, the cleanup job instead of TTL, IAM tokens and connection handling.

The migration skill​

I did all of this with Kiro agents. During the work it became obvious that the process was repeatable - discover DynamoDB patterns in a code base, map every pattern to a relational replacement, write an idempotent migration script, plan the cutover - so I turned it into a skill. It grew from a single SKILL.md into a whole folder (pattern catalog, DSQL and Drizzle notes, data migration guide, scripts and templates). I put it into a public repository so you can use it too: ivanbarlog/skills. Copy the dynamodb-to-dsql folder into your project's .kiro/skills/ and ask your agent to migrate your DynamoDB data layer. The skill asks before it touches anything in your AWS account.

Technology used​

For the data access I used Drizzle ORM with @aws/aurora-dsql-drizzle, the adapter AWS publishes. It handles the IAM token for every new connection (no password, DSQL uses IAM) and ships a CLI which transforms generated migrations so DSQL accepts them - for instance it rewrites CREATE INDEX to CREATE INDEX ASYNC.

Why not Prisma? I have used it before so it was a real option. But AWS has a Prisma blog post which, when I read it, still claimed DSQL lacks foreign keys (it no longer does), and AWS maintains the Drizzle adapter, so I went with that.

It felt a bit weird from the start though, but not because ORMs were new to me. I have used both Drizzle and Prisma before, with MySQL on RDS. An ORM simply makes no sense with DynamoDB, so there I used the AWS SDK, repository classes with dependency injection and Valibot for validation. Going "back to an ORM" after that felt strange. In the end the schema in TypeScript replaced most of the "validate every item I read" code (Valibot stays at the input boundary where it belongs) and the queries are typed.

Reading and writing data through Drizzle is quick. The migrations are the pain. Here is what I ran into:

  • No output. db:migrate prints nothing until it finishes. It runs each statement one after another and waits for the background job to complete.
  • Super slow. Schema changes in DSQL are asynchronous background jobs. On an empty staging database the 10 index builds took 8 seconds in the best and 36 seconds in the worst case, 2 minutes 44 seconds end to end. On production the whole db:migrate (48 statements, indexes and validation of the 14 foreign keys) took about 11 minutes. Reading a log with no progress and wondering whether it is stuck is not a great feeling.
  • Not resumable. I interrupted the first staging run while it was waiting for a job. The job finished in DSQL but the adapter (version 0.2.0) records a statement as applied only after the wait returns, so the re-run failed with relation already exists. I had to write a --repair flag for my migration script.
  • Smaller papercuts. aurora-dsql-drizzle generate exits with code 3 whenever the transform had to fix something, so it looks like a failure. Comments in hand-written migrations get stripped by the transform. And I have to stay on Drizzle 0.45 because the adapter peers on it and Drizzle 1.0 beta changes how casing works.

The adapter is pre-1.0 and it shows. I would still pick it again, but I would plan time for migrations (surface area, not hours) and I would not run them from a laptop on a flaky connection.

Everything else was straightforward. Unit tests run on PGlite with the committed migrations, so bun test needs no AWS access: 146 tests in about 3 seconds. A separate static test runs the AWS DSQL linter over the migrations because PGlite is more permissive than DSQL (it accepts SERIAL and TRUNCATE for instance).

Performance comparison​

I did not want to compare numbers from my head so I set up one controlled run on production, around the cutover.

Methodology. Same stage, same data, same Lambda (1,024 MB), the only difference is the data layer. One timed scenario, repeated step by step on both sides:

  • sign in, create two virtual friends, create a group, add the friends as members
  • add five expenses (equal split, two pair splits, custom shares 1:2:3 and 3:2:1)
  • delete the expenses one by one, delete the group
  • timed page reads: dashboard, friends page, settings

Before each run I forced a cold start by updating the function configuration so the first request pays for the init.

Frontend timings come from the browser Performance API - TTFB for page loads and the duration of the /_app/remote/ call for actions, so they include CloudFront and the network. Backend timings come from the REPORT records of the site Lambda in CloudWatch Logs Insights. Every record in the window matched a request of the run (44 of 44 on both sides, no other visitors).

Results. All numbers are milliseconds from one run each, so treat them as indicative. The fifth DynamoDB "add expense" is left out of the range because my computer went to sleep while I was entering it, which skewed that measurement:

StepDynamoDB frontend / backendDSQL frontend / backend
Dashboard, cold start719 / 214 (+ 271 init)1,143 / 395 (+ 461 init)
Friends page (SSR)100 / 17122 / 34
Group page (SSR)181 / 26110 / 10
Dashboard again (SSR)141 / 1688 / 10
Create group266 / 157162 / 71
Add members253 / 164297 / 222
Add expense (range)156 to 252 / 54 to 97157 to 215 / 65 to 123
Remove expense (range)136 to 160 / 35 to 41133 to 187 / 47 to 66

The stats for the whole window:

DynamoDBDSQL
Warm requests4243
Warm p5035.2 ms33.9 ms
Warm p90110.4 ms123.2 ms
Warm max163.7 ms335.6 ms
Cold starts21
Cold first request213.9 ms395.3 ms
Average init323.6 ms460.9 ms

What I take from it:

  • Warm median is the same. Most reads are equal or faster on DSQL, which makes sense because one join beats three round trips (the friends page was the exception, slower by a few milliseconds).
  • Writes are slower on DSQL, by tens of milliseconds. That is the price of foreign key checks, optimistic concurrency and a database that validates what I used to validate in code.
  • Cold starts are heavier. The init needs to sign an IAM token and open the first connection, and the first request pays for the TLS handshake and the first queries. 461 ms of init vs 271 to 376 ms. For this app I can live with it. You know where I stand on cold starts - but it is a real difference.
  • The first groupOne after creating a group took 336 ms in the DSQL run while the later ones took 26 to 30 ms. I do not have an explanation for this, my guess is the first use of those queries on the connection, but I did not check it.

Please be careful with these numbers. It is one run per side on a tiny database (261 items, 593 rows after migration) and nothing in here says anything about scale.

Conclusion​

In our case there will not be much of a price difference. Group Expense with Ease is a small service used by a few people. But we have proved something more interesting: DSQL is ready for production and it is a feasible alternative to DynamoDB for small projects if you want to use SQL instead of NoSQL. The migration went to production with about 20 minutes of announced downtime, my verification script ended with VERIFY: OK (row counts, amount sums and balances of every group match) and the application now runs on DSQL.

Beware though, DSQL is not a drop-in PostgreSQL replacement - it is a PostgreSQL compatible distributed system. Along the way I ran into:

  • no foreign key cascades on unbounded children and a 3,000 row limit per transaction
  • no TRUNCATE, triggers, sequences or array columns, and no language aware sorting of text
  • asynchronous schema changes which are slow and bring all the migration pain from above
  • optimistic concurrency, so you need to retry conflicting transactions
  • ORM tooling which is not yet at 1.0

None of these was a blocker for me. But if you plan to move a Postgres application to DSQL, read the list of supported features first, and if you are moving from DynamoDB do not translate items to rows 1:1. Design for the relational model and let the database do the lifting.

And if you want to see the result, try Group Expense with Ease. It is free, it has no ads and it might solve your "who owes whom" problems on your next holiday. It also helps me - the more people use it, the more relevant data I will have for the next experiment.