AWS、「Amazon Aurora PostgreSQL Limitless Database」を正式提供開始

$ aws rds describe-db-shard-groups
{
    "DBShardGroups": [
        {
            "DBShardGroupResourceId": "shardgroup-58dd7a78ff2c4fb88b27faf0da2dfac7",
            "DBShardGroupIdentifier": "my-first-newsql-group",
            "DBClusterIdentifier": "my-first-newsql",
            "MaxACU": 32.0,
            "MinACU": 16.0,
            "ComputeRedundancy": 1,
            "Status": "available",
            "PubliclyAccessible": false,
            "Endpoint": "my-first-newsql-group.shardgrp-xxx.ap-northeast-1.rds.amazonaws.com",
            "DBShardGroupArn": "arn:aws:rds:ap-northeast-1:12345:shard-group:shardgroup-58dd7a78ff2c4fb88b27faf0da2dfac7",
            "TagList": []
        }
    ]
}

$ aws rds describe-db-shard-groups --db-shard-group-identifier my-first-newsql-group
{
    "DBShardGroups": [
        {
            "DBShardGroupResourceId": "shardgroup-58dd7a78ff2c4fb88b27faf0da2dfac7",
            "DBShardGroupIdentifier": "my-first-newsql-group",
            "DBClusterIdentifier": "my-first-newsql",
            "MaxACU": 32.0,
            "MinACU": 16.0,
            "ComputeRedundancy": 1,
            "Status": "available",
            "PubliclyAccessible": false,
            "Endpoint": "my-first-newsql-group.shardgrp-xxx.ap-northeast-1.rds.amazonaws.com",
            "DBShardGroupArn": "arn:aws:rds:ap-northeast-1:12345:shard-group:shardgroup-58dd7a78ff2c4fb88b27faf0da2dfac7",
            "TagList": []
        }
    ]
}

$ HOST=my-first-newsql-group.cluster-xxx.ap-northeast-1.rds.amazonaws.com
$ psql -h $HOST -U postgres -d postgres_limitless
Password for user postgres:
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres_limitless=> select version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit
(1 row)

postgres_limitless=> select aurora_version();
   aurora_version
--------------------
 16.4-limitless.103
(1 row)

postgres_limitless=> SELECT router_endpoint FROM aurora_limitless_router_endpoints();
                                               router_endpoint
-------------------------------------------------------------------------------------------------------------
 cluster-oqxofvg2yqixyxjmyo7ow2gb2m--node-94826-instance-21709.csjmkalu8lxk.ap-northeast-1.rds.amazonaws.com
 cluster-oqxofvg2yqixyxjmyo7ow2gb2m--node-58698-instance-68477.csjmkalu8lxk.ap-northeast-1.rds.amazonaws.com
(2 rows)

$ psql -h $HOST -U postgres
Password for user postgres:
psql: error: connection to server at "my-first-newsql-group.cluster-xxx.ap-northeast-1.rds.amazonaws.com" (172.31.12.70), port 5432 failed: FATAL:  invalid connection request to non-limitless database "postgres" by user "postgres".
DETAIL:  Only connections to limitless databases is allowed.

-- standard table
CREATE TABLE foo(a int primary key);

-- sharded table
SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"item_id"}';
CREATE TABLE items(item_id int, item text);

-- items table と collocated な sharded table
SET rds_aurora.limitless_create_table_collocate_with='items';
CREATE TABLE item_description(item_id int, item_cat varchar, color_id int);

-- reference table
SET rds_aurora.limitless_create_table_mode='reference';
CREATE TABLE colors(color_id int primary key, color varchar);

postgres_limitless=> \d
                                     List of relations
 Schema |           Name           |       Type        |               Owner
--------+--------------------------+-------------------+-----------------------------------
 public | colors                   | foreign table     | postgres
 public | foo                      | foreign table     | postgres
 public | item_description         | partitioned table | postgres
 public | item_description_fs00001 | foreign table     | rds_aurora_limitless_tables_owner
 public | item_description_fs00002 | foreign table     | rds_aurora_limitless_tables_owner
 public | items                    | partitioned table | postgres
 public | items_fs00001            | foreign table     | rds_aurora_limitless_tables_owner
 public | items_fs00002            | foreign table     | rds_aurora_limitless_tables_owner
(8 rows)

postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables;
 table_gid | local_oid | schema_name |    table_name    | table_status | table_type |     distribution_key
-----------+-----------+-------------+------------------+--------------+------------+--------------------------
     16002 |     17750 | public      | colors           | active       | reference  |
     16004 |     17756 | public      | foo              | active       | standard   |
     16001 |     17744 | public      | item_description | active       | sharded    | HASH (item_id, item_cat)
     16000 |     17729 | public      | items            | active       | sharded    | HASH (item_id, item_cat)
(4 rows)

postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id;
 collocation_id | schema_name |    table_name
----------------+-------------+------------------
          16000 | public      | items
          16000 | public      | item_description
(2 rows)

postgres_limitless=> select c.* from item_description AS i_d JOIN   colors AS c ON i_d.color_id = c.color_id;
 color_id | color
----------+-------
        1 | blue
(1 row)

postgres_limitless=> explain select c.* from item_description AS i_d JOIN   colors AS c ON i_d.color_id = c.color_id;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=3826.67..4432.39 rows=39926 width=36)
   Merge Cond: (c.color_id = i_d.color_id)
   ->  Sort  (cost=754.38..757.79 rows=1365 width=36)
         Sort Key: c.color_id
         ->  Foreign Scan on colors c  (cost=100.00..683.30 rows=1365 width=36)
   ->  Sort  (cost=3072.29..3086.92 rows=5850 width=4)
         Sort Key: i_d.color_id
         ->  Append  (cost=100.00..2706.25 rows=5850 width=4)
               ->  Async Foreign Scan on item_description_fs00001 i_d_1  (cost=100.00..1338.50 rows=2925 width=4)
               ->  Async Foreign Scan on item_description_fs00002 i_d_2  (cost=100.00..1338.50 rows=2925 width=4)
(10 rows)

postgres_limitless=> SELECT * FROM rds_aurora.limitless_subclusters order by subcluster_id;
 subcluster_id | subcluster_type
---------------+-----------------
 2             | router
 3             | router
 4             | shard
 5             | shard
(4 rows)