Also available in: 日本語

About a year and a half has passed since Aurora DSQL was unveiled at re:Invent 2024. It's become a pretty convenient database service: you get a RDBMS that speaks SQL with the ease of use of DynamoDB, without the design difficulty that usually comes with purpose-built databases like DynamoDB. Yet, even after all this time, real-world case studies are still scarce.

That's not surprising. While DSQL speaks SQL, its runtime behaves more like a DynamoDB-style database than vanilla PostgreSQL, and that's the trade-off you pay for that ease of use. Put differently, compatibility with PostgreSQL stops at the protocol level; the behavior isn't compatible. That seems to be the sticking point that makes migrating an existing PostgreSQL application difficult to do as-is. On that front, other PostgreSQL-compatible serverless databases like Neon arguably have the edge, even though their pricing model differs.

In this post, I'll walk through where we've hit friction and how we've worked around it, based on our experience actually adopting Aurora DSQL as the database for a Rails application we've been building from scratch since late last year.

tl;dr

What makes Aurora DSQL different

The biggest behavioral characteristic is that there's no locking: queries run under Optimistic Concurrency Control. The optimistic locking you often end up implementing yourself with DynamoDB or Redis (Valkey) happens automatically within a transaction here, and if a conflicting write occurs, the entire transaction rolls back with an error.

I won't go deep into this here, but you need to build your application assuming transactions fail more often than they would under vanilla PostgreSQL.

Connecting to Aurora DSQL

IAM auth only

Aurora DSQL only offers IAM authentication. IAM auth itself isn't meaningfully different from what's offered across the rest of the Aurora and RDS family.

Stop the ORM from issuing syntax DSQL doesn't support

When using DSQL through an ORM, the first thing you need to do is stop it from issuing SQL statements DSQL doesn't support.

For Rails, for example, you'll need to apply the monkey patch from aws-samples/aurora-dsql-samples. The same repository has patches for other frameworks too, worth checking out.

• Disable client_min_messages
• Disable set_standard_conforming_strings
• Set supports_ddl_transactions? to false

On top of that, I'd also recommend applying these patches to Rails itself:

• Skip ROLLBACK statement following TransactionRollbackError by sorah · Pull Request #56717 · rails/rails
  • Stops Rails from issuing an unnecessary ROLLBACK on a transaction that's already been rolled back, which otherwise makes libpq print a warning to stderr
• Fix PostgreSQL primary key introspection for covering indexes by amaksimo · Pull Request #58555 · rails/rails
  • Fixes a bug where a covering column INCLUDEd in the primary key index breaks automatic primary_key detection, forcing you to declare self.primary_key = "id" on every model to work around it

Connection options for DSQL

Beyond the usual TLS and IAM auth settings, it's worth setting the following:

• advisory_locks: false: not supported
• prepared_statements: false: not supported (as far as I know)
• max_age: 2700: connections expire after 60 minutes, so reconnect earlier than that (the default pool_jitter of 0.2 means it actually varies between 36 and 45 minutes at 2700 seconds)

production:
  advisory_locks: false
  prepared_statements: false
  max_age: 2700 # 45m

OCC in practice (with a side of Rails)

Build a transaction block that retries automatically

Because of OCC, a conflicting write doesn't wait on a lock; it fails with a SQL error instead. As a rule, the right move is to retry (or, if your API client can retry for you, that works too).

So we added a method to ApplicationRecord that retries automatically, and as a rule build transactions with ApplicationRecord.retryable_transaction do … end. In the code below, an OCC conflict triggers a retry with jittered exponential backoff.

module DsqlTransactionable
  extend ActiveSupport::Concern

  module ClassMethods
    def retryable_transaction(retries: 4, base_sleep_seconds: 0.2, &block)
      attempts = 0
      begin
        transaction(&block)
      rescue ActiveRecord::SerializationFailure => e
        raise unless e.dsql_retryable? && attempts < retries

        attempts += 1
        sleep(base_sleep_seconds * (2**(attempts - 1)) * (0.5 + (0.5 * rand)))
        retry
      end
    end

    # A marker to indicate that the transaction is non-idempotent
    def non_idempotent_transaction(&block) = transaction(&block)
  end

  def retryable_transaction(...) = self.class.retryable_transaction(...)
  def non_idempotent_transaction(&block) = self.class.non_idempotent_transaction(&block)
end

# Additional monkey patch
class ActiveRecord::SerializationFailure
  def dsql_retryable? = message.include?("(OC001)") || message.include?("(OC000)")
end

class PG::TRSerializationFailure
  def dsql_retryable? = message.include?("(OC001)") || message.include?("(OC000)")
end

On top of this, we ban plain ApplicationRecord.transaction via a RuboCop lint, and force any call site that genuinely can't tolerate a retry to use non_idempotent_transaction from the same concern instead.

class TransactionOccAwareness < RuboCop::Cop::Base
  MSG = "Use #retryable_transaction or #non_idempotent_transaction for DSQL OCC."

  RESTRICT_ON_SEND = %i[transaction with_lock].freeze

  def on_send(node)
    add_offense(node.loc.selector)
  end
end

Watch out for what happens on retry

Because retryable_transaction retries the entire block, you need to re-issue any SELECT inside the transaction, via reload or lock!, to actually re-acquire locks on the records involved.

This would be good practice even without DSQL's retry requirement, but the OCC constraint also pushes you toward keeping transactions short in wall-clock time. In practice that means querying outside the transaction first, for example running precondition checks, before entering the actual write transaction.

If you drop a SELECT that should have been there, you can end up overwriting data without ever taking a lock, or having a record revert to a stale state on retry. So if conflicts are rare, one option is simply to avoid this pattern.

We don't currently have a mechanism that enforces this (something like an exception thrown when you touch a stale ActiveRecord object's attributes after a retry would be nice).

# BAD
challenge.verify(totp_response)
challenge.mark_as_used
ApplicationRecord.retryable_transaction do
  challenge.save!
end

# GOOD
challenge.verify(totp_response)
ApplicationRecord.retryable_transaction do
  challenge.reload
  challenge.verify(totp_response)
  challenge.mark_as_used
  challenge.save!
end

There's no gap lock

Since OCC has no notion of a gap lock, if two processes concurrently check "does this not exist yet?" before inserting, both can succeed. (You should have a unique index in place regardless, but still.)

# BAD
ApplicationRecord.retryable_transaction do
  raise Duplicate if user.records.exists?(key: k)
  user.records.create!(key: k)
end

# GOOD
ApplicationRecord.retryable_transaction do
  user.lock!("FOR KEY SHARE")
  raise Duplicate if user.records.exists?(key: k)
  user.records.create!(key: k)
end

Calling lock! with no arguments issues SELECT FOR UPDATE. FOR KEY SHARE support was added recently, and it means writes to non-key columns aren't treated as conflicts, so if all you need is a conflict check like this, FOR KEY SHARE is usually the better fit.

(Key columns: columns included in an index that's unique and neither partial nor an expression index.)

Avoid conflicts in the first place

We had a model with a column like last_activity_at that gets updated to the current time whenever a particular action happens. When that action fires concurrently at scale, even a SELECT-then-immediate-UPDATE inside a transaction runs into conflicts, and even with jittered backoff we'd hit the retry limit, and beyond that, retries alone make response times worse.

We initially delayed the write via an async job over SQS, but OCC conflicts still showed up prominently in metrics and logs, so as a compromise we now reduce the resolution of the value to about a minute (skip the update if the current value and the value to be set are within a minute of each other).

As I'll mention later, splitting tables up to some degree also helps avoid getting needlessly pulled into conflicts by unrelated update queries.

Keep transactions short in wall-clock time too

OCC's conflict detection is based on wall-clock time (it scans for conflicting writes between when the transaction starts and when it attempts to commit). So the shorter the transaction, the lower the chance of a conflict. DSQL also has a hard limit on the number of rows a single transaction can query or modify.

This is arguably true of vanilla PostgreSQL as well, but it's worth being careful not to run slow operations inside a transaction (checking a password hash match, for example).

There's also a technique of moving a SELECT inside a transaction out into its own, separately committed transaction, if strong consistency isn't required. If a SELECT used for something like a rate-limit check and the DML for the actual business logic live in the same transaction, the conflict surface naturally grows (think: an unrelated process updating the same record for something totally different).

Nested transactions and SAVEPOINT aren't implemented

In modern Rails, it's common to see transaction do … end wrapped wherever a model or controller needs one, regardless of whether the caller already has a transaction open, which sometimes means transactions end up nested.

Aurora DSQL doesn't implement SAVEPOINT, so this pattern isn't available. As a result, we open a transaction in either the controller or a service class (leaning toward the controller as the default), and write model code assuming the caller is never already inside a transaction when it opens one of its own. It's painful.

Schema design

No turning back on some things

DSQL has shipped a lot of feature additions over the past six months, and the set of hard-to-reverse constraints has shrunk quite a bit. At this point, about the only things you still can't do are changing a column's type and adding NOT NULL to an existing column.

Early on you also couldn't DROP COLUMN or change a DEFAULT value, but now, as long as you're disciplined about which data needs to be NOT NULL from the start, it's not too bad (easier said than done, though).

Keep tables small

Given the constraints above, the OCC trade-offs, and the fact that ActiveRecord tends to SELECT every column by default, we generally keep individual tables small (the max is 1,000 tables per cluster).

There's json and jsonb support, and expression indexes are now supported too, so if you want to keep some schema flexibility or store structured data, reaching for json might be a reasonable move.

No partial indexes

DSQL doesn't support partial indexes, which PostgreSQL users reach for often. This is a bit inconvenient when you want a unique index across multiple columns that can include NULLs. Expression indexes might work around it?

-- Does this work?
create unique index async idx_active on things
  ((case when discarded_at is null then user_id end));

On top of that, NULLS NOT DISTINCT has been supported since 2026-08-13, so now you can express the opposite case (treating NULLs as a single unique value) directly. That covers a fair number of cases on its own.

Schema migrations

You'll run into states that, while technically possible under vanilla PostgreSQL, aren't ones you'd normally see, and both migration-style and declarative-style tooling can end up not playing well with them:

• The access method shows up as using index_btree
• The primary key is a clustered index
• Only one DDL statement is allowed per transaction

Also, for indexes you're stuck using the CREATE INDEX ASYNC syntax, and there's usually no way around dealing with that.

I use psqldef, and I submitted the following patch (since released) adding support for CREATE INDEX ASYNC syntax and skipping transactions for DDL. In hindsight, ASYNC might have been better handled as something like a use_create_index_async configuration option, which was actually proposed as an alternative during review, but the PR got merged as-is anyway. CREATE INDEX CONCURRENTLY has a similar flag, after all.

• Aurora DSQL support by sorah · Pull Request #903 · sqldef/sqldef

Also worth reading: できれば知らずに済ませたかったAurora DSQL非互換集 - ArkEdge Space Blog

OCC errors from DDL

DDL itself can trigger OCC errors. An OC001 conflict error can occur against a table you're altering, or a table you're creating an index on, so any tool that runs DDL, psqldef included, needs to detect OCC errors and retry.

Waiting for async indexes

As the name CREATE INDEX ASYNC implies, index creation always happens asynchronously, so it's a good idea to pair schema changes with a script that waits for them to finish. An OCC conflict can even occur at the moment an asynchronously created index finishes and becomes active.

When Aurora DSQL finishes an asynchronous index task, it updates the system catalog to show that the index is active. If other transactions reference the objects in the same namespace at this time, you might see a concurrency error.

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-create-index-async.html

There's also a sys.wait_for_job() function, but since we use psqldef and can end up with multiple jobs running, we poll the following two queries until everything's done instead.

select job_id, status, details from sys.jobs where status not in ('completed', 'failed');

select c.relname as index_name
from pg_index i
join pg_class c on c.oid = i.indexrelid
join pg_class t on t.oid = i.indrelid
join pg_namespace n on n.oid = t.relnamespace
where
  n.nspname = $1
  and i.indisunique = true
  and i.indisvalid = false
;

It's especially important to wait for this before rolling out something like a server update that depends on a new unique index. Also, at the time I implemented this, I observed that a fresh connection was required to see up-to-date information (particularly for the catalog), so we reconnect before every poll (not sure if that's still the case).

Local development

There's no DSQL Local

AWS doesn't distribute a DynamoDB Local equivalent. Using plain PostgreSQL for local development would probably be fine on the compatibility front, aside from taking locks instead of throwing OCC errors, but our team currently develops against a real DSQL cluster.

We reuse a pool of development DSQL clusters, provisioned in fixed numbers ahead of time via Terraform, that developers can freely use. Access to AWS resources used for development, DSQL included, is set up so that a least-privilege IAM role is picked up automatically within the project via sorah/mairu and AWS SSO, which means we can hand it straight to coding agents like Claude Code too.

Provisioning a per-user schema

After cloning the repo and running the setup script, it discovers a DSQL cluster with a free schema slot and creates a schema and runs psqldef under the name ${USER}_${ENV} (e.g. sorah_development, sorah_test). It also creates a role based on $USER and points search_path at the schema it created.

When it detects an agent running in an isolated environment, like Devin, it picks a random name instead (drawn from a Schema Pool described below). Whichever cluster and schema get chosen are recorded in a file under tmp/, and that same schema keeps getting reused until you explicitly delete it.

A DSQL cluster caps out at 10 schemas, and once you account for public and the like, you effectively get about 7 you can freely create. Because of that, we provision a number of clusters upfront, roughly matching the number of developers, and the setup process picks one from the pool (a limit increase for clusters per region is available on request).

Wrangling automated tests

Running tests against a real DSQL cluster makes latency a fairly significant problem once you're outside of AWS. On top of that, you need to work around DSQL's constraints too.

One option would be to pair local development, and maybe CI as well, with vanilla PostgreSQL instead (e.g. run both in CI). At the time our team committed to Aurora DSQL, we didn't have enough know-how yet, and were worried about running into DSQL-specific limitations further down the road, so that's not the path we took.

What follows are mostly hacks aimed at Rails (RSpec).

OCC conflicts happen in tests too

Even when running tests against a real DSQL cluster with no other process touching the same schema concurrently, OCC conflicts can occur because of other work running on the same cluster. As mentioned earlier, OCC conflicts can be triggered by schema changes too.

This shows up especially in CI, where an ALTER TABLE from another PR or from main can trigger it, making CI runs flaky. To avoid that, tests need to retry on OCC conflicts as well.

module DsqlRetryable
  BASE_SLEEP_SECONDS = 0.1
  MAX_RETRIES = 10

  def self.sleep_duration(attempt)
    BASE_SLEEP_SECONDS * (2**(attempt - 1))
  end

  def self.with_retry(label)
    retries = 0
    begin
      yield
    rescue ActiveRecord::SerializationFailure, PG::TRSerializationFailure => e
      raise unless e.dsql_retryable? && retries < MAX_RETRIES

      retries += 1
      warn "retryable SerializationFailure in #{label}, retrying (#{retries}/#{MAX_RETRIES})"
      sleep sleep_duration(retries)
      retry
    end
  end

  def self.retryable_exception?(exception)
    case exception
    when ActiveRecord::SerializationFailure, PG::TRSerializationFailure
      exception.dsql_retryable?
    when RSpec::Core::MultipleExceptionError
      exception.all_exceptions.all? { |e| retryable_exception?(e) }
    when RSpec::Expectations::ExpectationNotMetError
      exception.message.include?("(OC001)") || exception.message.include?("(OC000)")
    else
      false
    end
  end
end

RSpec.configure do |config|
  config.around do |example|
    example.run
    10.times do |i|
      break if example.exception.nil?
      break unless DsqlRetryable.retryable_exception?(example.exception)

      warn "retryable SerializationFailure detected in #{example.location} ; retrying the test (#{i + 1}/#{DsqlRetryable::MAX_RETRIES})"
      sleep DsqlRetryable.sleep_duration(i + 1)

      # Clear memoized let/let! values so they are re-created with fresh DB records on retry.
      # The after hook's DatabaseRewinder.clean may have deleted the records that previous let
      # values reference, causing "User must exist" or similar validation errors on retry.
      example.example.instance_variable_get(:@example_group_instance)&.send(:__init_memoized)

      example.example.display_exception = nil
      example.run
    end
  end
end

No SAVEPOINT

As mentioned, there's no SAVEPOINT, so Rails' transactional fixtures are off the table (the test suite's own use of transactions would require nesting).

config.use_transactional_fixtures = false

There's no TRUNCATE either, so cleanup has to happen via DELETE. We use the old-school amatsuda/database_rewinder gem, which issues DELETE against any table that received an INSERT, to reset the database between unit tests.

That said, use transactions wherever you still can

On the other hand, if the code under test doesn't itself use a transaction, there's no reason not to wrap the test in one.

Wrapping each RSpec example in a transaction

For examples tagged :batch_transactions, we've set up before/after hooks that run the whole example inside a transaction. For a model test, that means the entire unit test runs inside one transaction, essentially applying use_transactional_fixtures selectively.

Rails automatically starts (and immediately commits) a transaction of its own, and setting aside the wait for that COMMIT, the round-trip cost of issuing BEGIN/COMMIT explicitly adds up in ways that are surprisingly slow. So we allow this example group to run inside an explicit transaction. For a model test, everything you want to test usually fits inside one transaction anyway, and this seems to cover most cases just fine.

# spec/support/transaction.rb
module FactoryTransactionBatcher
  def self.finalize_transaction(conn)
    conn.commit_transaction
  rescue StandardError
    conn.rollback_transaction rescue nil
  end
end

RSpec.configure do |config|
  config.prepend_before do |example|
    if example.metadata[:batch_transactions]
      # joinable avoids a nested transaction; _lazy defers issuing BEGIN until it's actually needed
      ActiveRecord::Base.lease_connection.begin_transaction(joinable: true, _lazy: true)
    end
  end

  config.after do |example|
    next unless example.metadata[:batch_transactions]
    conn = ActiveRecord::Base.lease_connection
    FactoryTransactionBatcher.finalize_transaction(conn) if conn.transaction_open?
  end
end

# spec/…_spec.rb
RSpec.describe Thing, :batch_transactions do
  describe "…" do … end
end

Wrap before_all (let_it_be) in its own transaction

Even so, we found it usually doesn't hurt to bundle everything a before_all sets up into a single transaction, so we've set that up as the default. This also has OCC-aware retry built in.

module DsqlBeforeAllRetry
  def before_all(setup_fixtures: TestProf::BeforeAll.config.setup_fixtures, &block)
    return super(setup_fixtures: setup_fixtures) unless block

    retryable_block = proc {
      retries = 0
      begin
        ActiveRecord::Base.transaction { instance_exec(&block) }
      rescue ActiveRecord::SerializationFailure, PG::TRSerializationFailure => e
        raise unless e.dsql_retryable? && retries < DsqlRetryable::MAX_RETRIES

        retries += 1
        warn "retryable SerializationFailure in before_all/let_it_be, retrying (#{retries}/#{DsqlRetryable::MAX_RETRIES})"
        sleep DsqlRetryable.sleep_duration(retries)
        retry
      end
    }
    super(setup_fixtures: setup_fixtures, &retryable_block)
  end
end

RSpec::Core::ExampleGroup.singleton_class.prepend(DsqlBeforeAllRetry)

Rails' stock TimeHelpers breaks IAM auth

This probably applies to Aurora and RDS in general whenever IAM auth is in play, not just Aurora DSQL, but it's the kind of thing you only run into because DSQL testing means talking to a real cluster.

Rails ships a time-manipulation helper, ActiveSupport::Testing::TimeHelpers, which we were using, but it affects the timestamp embedded in the IAM auth signature used for the DSQL connection, and that caused DB connection failures. Unlike Timecop, which has long been the de facto standard in the community, there was no way to selectively suspend the time manipulation it introduces. So we switched from TimeHelpers back to Timecop, and built token generation to disable Timecop, if present, while generating the auth token.

class DsqlAuthTokenGenerator
  def call(host:, port:, user:)
    # When Timecop is used for time travel in tests, restore real time for token generation
    # to avoid "Signature not yet current" errors from AWS
    if defined?(Timecop)
      Timecop.return { generate_token(host: host, user: user) }
    else
      generate_token(host: host, user: user)
    end
  end

  private def generate_token(host:, user:)
    # e.g. host == "<clusterID>.dsql.us-east-1.on.aws"
    region = host.split(".")[2]
    raise "Unable to extract AWS region from host '#{host}'" unless region =~ /[\w\d-]+/

    token_generator = Aws::DSQL::AuthTokenGenerator.new(
      credentials: Aws::CredentialProviderChain.new.resolve,
    )

    auth_token_params = {
      endpoint: host,
      region: region,
      expires_in: 15 * 60,
    }

    case user
    when "admin"
      token_generator.generate_db_connect_admin_auth_token(auth_token_params)
    else
      token_generator.generate_db_connect_auth_token(auth_token_params)
    end
  end
end

PG::AWS_RDS_IAM.auth_token_generators.add :dsql do
  DsqlAuthTokenGenerator.new
end

CI

Running against a real cluster locally means the same is true in CI on GitHub Actions: unsurprisingly, we run tests against a real Aurora DSQL cluster there too. And of course, we want CI to finish fast. Several tricks help address the speed issues that come with using real DSQL.

First, as of this writing, GitHub-hosted runners for GitHub Actions tend to land in Azure's US regions. Being outside of AWS already costs you a latency penalty, and Azure has regions in places AWS doesn't, which makes it worse. On top of that, DSQL's DDL isn't particularly fast, and waiting on CREATE INDEX ASYNC takes a noticeable amount of time even against an empty dataset.

Here are the hacks we use to muscle through all that.

Picking a DSQL region

Just like our development clusters, we provision multiple CI clusters too (since many run concurrently across pull requests and the parallelization described below). We currently have 5 clusters in each of several US AWS regions. To discover the cluster closest to whichever GitHub Actions runner got assigned, we use Route 53 latency-based routing.

The Terraform state that provisions the DSQL clusters creates an SRV record set per region, which the schema setup step queries to pick a cluster.

Incidentally, Devin also seems to run somewhere in the US, so we point Devin at the same CI clusters too.

Parallelizing test runs

Given how much latency SQL execution already carries, we parallelized the test suite starting from when it was still fairly small.

Nothing fancy here: we use the parallel_tests gem, split across 4 jobs via a GitHub Actions matrix.

Smoothing out test timing variance

Because parallel_tests splits work by file, each split job needs to take roughly the same amount of time for the split to actually help. The standard approach is to log per-file timings and split based on that. Caching those timing logs in GitHub Actions is also standard, but the catch here is that Aurora DSQL lives outside the Actions runner. Looking at our own logs, we saw numbers like this:

Azure region	n	avg	p50	spread	AWS regions drawn
eastus	103	5.78	5.67	8.55	us-east-1: 103
eastus2	33	9.65	9.60	3.98	us-east-1: 33
westus2	55	16.49	16.34	4.50	us-west-2: 55
northcentralus	45	22.62	21.98	22.47	us-east-2: 33, us-east-1: 12
westus	71	23.91	23.85	4.87	us-west-2: 71
canadacentral	2	26.87	24.00	5.74	us-east-2: 2
centralus	59	38.16	39.26	27.94	us-east-2: 58, us-east-1: 1
westus3	61	39.13	39.22	2.64	us-west-2: 61
westcentralus	36	42.36	42.08	5.86	us-west-2: 36
southcentralus	3	54.05	59.27	21.25	us-east-2: 2, us-east-1: 1

(We identify the Azure region quickly from the runner by resolving the CNAME for azure.archive.ubuntu.com., and we record SELECT NOW(); round-trip time and print it in the job summary.)

Looking at rows with n > 10, eastus → us-east-1 comes in around 6ms, versus roughly 42ms for westcentralus → us-west-2, about a 7x difference purely based on which runner you got assigned. Unsurprisingly, the fastest pairings are between Azure and AWS regions that are both clustered around Virginia, while the inland Azure regions (westcentralus in Wyoming, westus3 in Arizona) are the farthest out. If there's a way to pay to exclude these Azure regions from the GitHub runner pool, I would gladly pay for it.

Since this variance would throw off job splitting if left as-is, we feed parallel_tests a normalized version of the timing log, scaled by this ratio.

Schema Pool

Next, schema provisioning. Tables and indexes need to exist in the schema before tests can run, and DSQL's DDL isn't especially fast. Even a psqldef run covering about 32 tables and 56 indices takes roughly a minute and a half (even from eastus, which is close to us-east-1), about 50 seconds of which is waiting on the async CREATE INDEX ASYNC jobs.

So the ideal is to provision schemas ahead of time and reuse them. We call this pool of pre-provisioned schemas the Schema Pool. In practice, our CI maintains about 5 pooled schemas, with main's psqldef already applied, across each of 15 DSQL clusters (5 clusters x 3 regions), and CI runs, PRs included, just pick one up.

Each DSQL cluster has its own table for managing the state of its Schema Pool. Each CI job randomly picks a free schema and marks it in use; once the job finishes, regardless of outcome, it triggers a workflow_dispatch called checkin, which discards and recreates the schema. Since walking a schema back to main's state isn't reliably possible given the constraints described earlier, when it's used from a PR we always delete and recreate it rather than trying to reset it.

If a schema ends up abandoned for some reason, a cron-triggered workflow detects and recreates it the same way. And when a schema change lands on main, a workflow fires that applies psqldef incrementally to each schema in turn.

Lately, with stacked PRs and worktree-based parallel work across PRs, even a small team can end up with a lot of open/draft PRs running at once. That can run the Schema Pool dry, since we're already using nearly all of the schema slots per cluster to begin with; we can't provision new ones on demand, so right now we just let those jobs fail.

Each of these maintenance workflows uses a dynamic matrix, so jobs run in parallel per DSQL cluster and per schema that needs to be created, updated, or deleted. Being able to spin these up cheaply in parallel thanks to ubuntu-slim has been great.

Things I hope get fixed soon

That's the current state of the pain points in working with Aurora DSQL day to day. Not a small list. That said, running it in production has been genuinely pleasant: it costs us essentially nothing at our current traffic, performance is good, and I like it.

The big items I'd like to see addressed:

• DSQL Local
• SAVEPOINT, nested transactions
• ALTER COLUMN … TYPE, SET NOT NULL
• No Zero-ETL or Parquet/Iceberg export
• DDL is slow, requires SQL, and has its quirks
  • This one feels like a real missed opportunity: being able to define a schema without touching DDL at all would make casual adoption much easier
• No instant clone or snapshot like Aurora has
  • Neon-style branching would be great to have

And some things that have gotten better

We've been running this in production seriously since late last year, and improvements have kept landing recently too:

• ALTER TABLE … DROP COLUMN (2026-08-03)
• SET/DROP DEFAULT, DROP NOT NULL, DROP CONSTRAINT, ADD GENERATED AS IDENTITY (2026-07-06)
• jsonb (2026-06-08), json (2026-05-04)
• Sequences and identity columns (2026-02-13), numeric as an index key (2026-02-03)
• Higher numeric precision, text/varchar compression (2026-08-26)
• Expression indexes, INCLUDE, NULLS [NOT] DISTINCT (2026-08-13)
• SELECT … FOR KEY SHARE and FOR UPDATE without full-PK equality predicates (2026-08-24/25)
• Database Insights support

With sequence support now in place, some existing applications might migrate over fairly easily. That said, some column types still aren't supported, and OCC is still a factor, so it depends on the case. When I need something OSS to self-host and need real PostgreSQL to keep costs down, I still might reach for something like Neon instead.

Outro

It's more workable than you'd expect, so wherever it fits, go ahead and use it.

Plug 1: I'm speaking at DSQL Day

By the way, I'll be giving a talk based on this post at Amazon Aurora DSQL Day Tokyo, happening 2026-09-14 at Amazon HND25 (AWS's Azabudai office). I'll also be covering data migration, which I didn't get into here. Come say hi if you're around.

Plug 2: We're hiring

I'm keeping this section in translation, but unfortunately my current employer requires a certain level of Japanese proficiency. If you're at JLPT N2 or above and interested in these positions, don't hesitate to apply.

This post is about a system built on the following position at my current employer. We're looking for people to work across the board, from developer-experience-flavored platform work to the platform underpinning the product itself (think IAM, billing, and, in a very "this company" kind of way, SIP for phone systems).

• E12. プラットフォームエンジニア（リードクラス） - 株式会社IVRy
• E14. プロダクトセキュリティエンジニア - 株式会社IVRy

---

All code snippets in this post are licensed under 0BSD or MIT license.