Using AWS DSQL as metadata server?

[javafanboy]

I really love the idea of DuckLake and using DuckDB for local analytics. As the organisation I work with use 100% AWS for all backend storage and compute I would like use an AWS hosted database server for the meta storage. To this end I am curious about how much "postgre compatibility" that is needed? I assume the fully Postgre compatible Aurora would work (or is a duckdb postgre extension needed also on the meta server?) and the "serverless" version would be ok (as it nowadays scale down to zero hardware when there are no load) but I would have preferred the TRULY serverless DSQL that today have somewhat "thin" compatibility lacking quite a bit of POSTGRE features.

Is there some description of what SQL engine features that are required for storing the Ducklake meta data?

Anybody that have already tried runing meta server on AWS Aurora or even better on DSQL?

[gahtan-syarif]

The Ducklake format itself is database agnostic, it can work with any SQL database as long as it supports primary keys and transactions. The DuckDB engine that communicates with the DuckLake, however, currently only supports PostgreSQL, MySQL and SQLite. I'm not sure about Aurora and DSQL as I have never used them, but assuming they support the PostgreSQL protocol, then yes they can be used for the ducklake meta server. I do, however, have tried and can confirm that Neon Serverless can be used as ducklake meta server since its postgres compatible even though its technically not postgres itself.

[javafanboy]

Thanks for the very detailed answer. Will try some different options now when I better understand the requirements to make it work!

[joeblew999]

You could use SQLite and replicate the WAL using Litestream perhaps .

Or replicate with NATS using https://github.com/wongfei2009/harmonylite

I have used this way to replicate the ducklake SQLite . It will sync with every other ducklake SQLite using CRDT sync so you have a multi master replication of ducklake.

It’s 100% a side car listening to the WAL of SQLite , and so need absolutely no integration with ducklake to work .

[javafanboy]

I looked deeper into using AWS DSQL as meta store but right now it is not possible as DSQL only suports a single DDL statement per transaction - I get the following error message:

java.sql.SQLException: Invalid Error: Failed to initialize DuckLake:Failed to execute query "CREATE TABLE "public"."ducklake_snapshot"("snapshot_id" BIGINT PRIMARY KEY, "snapshot_time" TIMESTAMP WITH TIME ZONE, "schema_version" BIGINT, "next_catalog_id" BIGINT, "next_file_id" BIGINT);": ERROR: multiple ddl statements not supported in a transaction
at org.duckdb.DuckDBNative.duckdb_jdbc_execute(Native Method)
at org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:193)
at org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:159)
at org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:257)
at org.example.Example.testDuckLake(Example.java:105)
at org.example.Example.main(Example.java:129)

The "postgres" extension for DuckDB seem to work with DSQL (I only tried some simple selects so far though) so once AWS adds better Postgre compatibility there MAY be future hope to use it also as a Ducklake meta store...

[gahtan-syarif]

Im curious to what your preference of AWS DSQL is over aurora/RDS? Is it purely scalability or does DSQL have some other advantage? Because scalability wise aurora is more than enough as metadata server for petabyte scale lakehouse

[javafanboy]

DSQL is fully serverless i.e. you pay "per transaction" (somewhat simplified) while Aurora "Serverles" only scales down to zero if there have been no traffic for a configurable time and in this case there is a quite significant "cold start" to get it running again (while hardware is attached I assume). Using non-serverless Aurora is of course and option if your meta load is static or you are ok with paying for the max load case 24x7 even if this would not be needed...

For use-cases where ingestion and analytics is reasonably low volume but never goes down to zero (or where the cold start time of Aurora serverless is not acceptable) DSQL would be a better alternative.

It gives me a good feeling that DSQL (like for instance DynamoDB) is built and hardened to be accessed from Internet (needed to do local analytics on your "laptop" unless you are using a VPN connection to AWS) - for instance you are only allowed to use short term credentials and not normal passwords - while I would say this, even though possible, is a worst practise for a database connected through your VPC like normal RDS & Aurora and probably not a configuration used by many users today as REST services (hosted in AWS using say Lambda) are typically used in between database and client...

DSQL also support multi-master distribution over multiple AWS regions possibly supporting lower latency meta access in global scenarios. If Duck Lake added support in the future (would require some more parameters to support multiple S3 buckets in different regions) this could together with with S3 region replication provide fully global and very resilient (but expensive) data lakes for orgs with extreme requirements...

DSQL can also support extreme peeks (more or less no upper limit on transactions / sec).

We will see how the service plays out for AWS in general but for meta data, where a slightly higher latency that I expect DSQL to have compared to using dedicated server hardware may not be the end of the world, I think it would be a good fit...

[gahtan-syarif]

I see, i understand now that there are clear benefits from using DSQL over aurora/rds, thanks for the explanation!

@javafanboy on another note, since the error you encounter is due to dsql not supporting multiple ddl statements per transaction, im curious but maybe you can create the ducklake metadata tables manually yourself in dsql as separate transactions? you can find the ddl sql statements for creating a ducklake here:

ducklake/src/storage/ducklake_metadata_manager.cpp

Line 52 in 7de4610

CREATE TABLE {METADATA_CATALOG}.ducklake_metadata(key VARCHAR NOT NULL, value VARCHAR NOT NULL, scope VARCHAR, scope_id BIGINT);

[javafanboy]

Tried the above but ran into some other problems so putting this aside for now and using a regular RDS that worked with no problems (as it is plain Postgre). As DSQL matures it will hopefully work with DuckLake eventually...