DDL limitations and other information for Aurora PostgreSQL Limitless Database

For details on what isn't supported, see Constraints.

Default values are supported. For more information, see Default values.

Not all indexes are visible on routers when you use \d table_name or similar commands. Instead, use the pg_catalog.pg_indexes view to get indexes, as shown in the following example.

SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"id"}';
CREATE TABLE items (id int PRIMARY KEY, val int);
CREATE INDEX items_my_index on items (id, val);

postgres_limitless=> SELECT * FROM pg_catalog.pg_indexes WHERE tablename='items';

 schemaname | tablename |   indexname    | tablespace |                                indexdef
------------+-----------+----------------+------------+------------------------------------------------------------------------
 public     | items     | items_my_index |            | CREATE INDEX items_my_index ON ONLY public.items USING btree (id, val)
 public     | items     | items_pkey     |            | CREATE UNIQUE INDEX items_pkey ON ONLY public.items USING btree (id)
(2 rows)

Simple constraints that involve comparison operators with literals are supported. More complex expressions and constraints that require function validations aren't supported, as shown in the following examples.

CREATE TABLE my_table (
    id  INT CHECK (id > 0)                                     -- supported
  , val INT CHECK (val > 0 AND val < 1000)                     -- supported
  , tag TEXT CHECK (length(tag) > 0)                           -- not supported: throws "Expression inside CHECK constraint is not supported"
  , op_date TIMESTAMP WITH TIME ZONE CHECK (op_date <= now())  -- not supported: throws "Expression inside CHECK constraint is not supported"
);

You can give constraints explicit names, as shown in the following example.

CREATE TABLE my_table (
    id  INT CONSTRAINT positive_id  CHECK (id > 0)
  , val INT CONSTRAINT val_in_range CHECK (val > 0 AND val < 1000)
);

You can use table-level constraint syntax with the CHECK constraint, as shown in the following example.

CREATE TABLE my_table (
    id INT CONSTRAINT positive_id  CHECK (id > 0)
  , min_val INT CONSTRAINT min_val_in_range CHECK (min_val > 0 AND min_val < 1000)
  , max_val INT
  , CONSTRAINT max_val_in_range CHECK (max_val > 0 AND max_val < 1000 AND max_val > min_val)
);

Exclusion constraints aren't supported in Aurora PostgreSQL Limitless Database.

For more information, see Foreign keys.

NOT NULL constraints are supported with no restrictions.

Primary key implies unique constraints and therefore the same restrictions on unique constraints apply on primary key. This means:

The following examples illustrate the use of primary keys.

-- Create a standard table.
CREATE TABLE public.my_table (
    item_id INT
  , location_code INT
  , val INT
  , comment text
);

-- Change the table to a sharded table using the 'item_id' and 'location_code' columns as shard keys.
CALL rds_aurora.limitless_alter_table_type_sharded('public.my_table', ARRAY['item_id', 'location_code']);

Trying to add a primary key that doesn't contain a shard key:

-- Add column 'item_id' as the primary key.
-- Invalid because the primary key doesnt include all columns from the shard key:
-- 'location_code' is part of the shard key but not part of the primary key
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR

-- add column "val" as primary key
-- Invalid because primary key does not include all columns from shard key:
--  item_id and location_code iare part of shard key but not part of the primary key
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR

Trying to add a primary key that does contain a shard key:

-- Add the 'item_id' and 'location_code' columns as the primary key.
-- Valid because the primary key contains the shard key.
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code); -- OK

-- Add the 'item_id', 'location_code', and 'val' columns as the primary key.
-- Valid because the primary key contains the shard key.
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code, val); -- OK

Change a standard table to a reference table.

-- Create a standard table.
CREATE TABLE zipcodes (zipcode INT PRIMARY KEY, details VARCHAR);

-- Convert the table to a reference table.
CALL rds_aurora.limitless_alter_table_type_reference('public.zipcode');

For more information on creating sharded and reference tables, see Creating Aurora PostgreSQL Limitless Database tables.

In sharded tables, the unique key must contain the shard key, that is, the shard key must be a subset of the unique key. This is checked when changing the table type to sharded. In reference tables there's no restriction.

CREATE TABLE customer (
    customer_id INT NOT NULL
  , zipcode INT
  , email TEXT UNIQUE
);

Table-level UNIQUE constraints are supported, as shown in the following example.

CREATE TABLE customer (
    customer_id INT NOT NULL
  , zipcode INT
  , email TEXT
  , CONSTRAINT zipcode_and_email UNIQUE (zipcode, email)
);

The following example shows the use of a primary key and a unique key together. Both keys must include the shard key.

SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"p_id"}';
CREATE TABLE t1 (
p_id BIGINT NOT NULL,
c_id BIGINT NOT NULL,
PRIMARY KEY (p_id),
UNIQUE (p_id, c_id)
);

You can create extensions, as in PostgreSQL.

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ CASCADE ]

For more information, see CREATE EXTENSION in the PostgreSQL documentation.

The following DDLs are supported:

ALTER EXTENSION name UPDATE [ TO new_version ]

ALTER EXTENSION name SET SCHEMA new_schema

For more information, see ALTER EXTENSION in the PostgreSQL documentation.

You can drop extensions, as in PostgreSQL.

DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

For more information, see DROP EXTENSION in the PostgreSQL documentation.

You can't add or drop member objects from extensions.

ALTER EXTENSION name ADD member_object

ALTER EXTENSION name DROP member_object

rds_aurora.limitless_pgstattuple(relation_name)

rds_aurora.limitless_pgstattuple(relation_name, subcluster_id)

rds_aurora.limitless_pgstatindex(relation_name)

rds_aurora.limitless_pgstatindex(relation_name, subcluster_id)

rds_aurora.limitless_pgstatginindex(relation_name)

rds_aurora.limitless_pgstatginindex(relation_name, subcluster_id)

rds_aurora.limitless_pgstathashindex(relation_name)

rds_aurora.limitless_pgstathashindex(relation_name, subcluster_id)

rds_aurora.limitless_pg_relpages(relation_name)

rds_aurora.limitless_pg_relpages(relation_name, subcluster_id)

rds_aurora.limitless_pgstattuple_approx(relation_name)

rds_aurora.limitless_pgstattuple_approx(relation_name, subcluster_id)

You can create functions, as in Aurora PostgreSQL, with the exception of changing their volatility while replacing them.

For more information, see CREATE FUNCTION in the PostgreSQL documentation.

You can alter functions, as in Aurora PostgreSQL, with the exception of changing their volatility.

For more information, see ALTER FUNCTION in the PostgreSQL documentation.

You can drop functions, as in Aurora PostgreSQL.

DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

For more information, see DROP FUNCTION in the PostgreSQL documentation.

When you distribute a function or procedure, it only deals with data that's bounded by the shard key range in that shard. In cases where the function or procedure tries to access data from a different shard, the results returned by the distributed function or procedure will be different compared to one that isn't distributed.

For example, you create a function containing queries that will touch multiple shards, but then call the rds_aurora.limitless_distribute_function procedure to distribute it. When you invoke this function by providing arguments for a shard key, it is likely that the results of running it will be bounded by the values present in that shard. These results are different from ones produced without distributing the function.

Consider the following function func where we have the sharded table customers with the shard key customer_id.

postgres_limitless=> CREATE OR REPLACE FUNCTION func(c_id integer, sc integer) RETURNS int
    language SQL
    volatile
    AS $$
    UPDATE customers SET score = sc WHERE customer_id = c_id RETURNING score;
    $$;

Now we distribute this function:

SELECT rds_aurora.limitless_distribute_function('func(integer, integer)', ARRAY['c_id'], 'customers');

The following are example query plans.

EXPLAIN(costs false, verbose true) SELECT func(27+1,10);

                    QUERY PLAN
 --------------------------------------------------
  Foreign Scan
    Output: (func((27 + 1), 10))
    Remote SQL:  SELECT func((27 + 1), 10) AS func
  Single Shard Optimized
 (4 rows)

EXPLAIN(costs false, verbose true)
 SELECT * FROM customers,func(customer_id, score) WHERE customer_id=10 AND score=27;

                          QUERY PLAN
 ---------------------------------------------------------------------
  Foreign Scan
    Output: customer_id, name, score, func
    Remote SQL:  SELECT customers.customer_id,
      customers.name,
      customers.score,
      func.func
     FROM public.customers,
      LATERAL func(customers.customer_id, customers.score) func(func)
    WHERE ((customers.customer_id = 10) AND (customers.score = 27))
  Single Shard Optimized
 (10 rows)

The following example shows a procedure with IN and OUT parameters as arguments.

CREATE OR REPLACE FUNCTION get_data(OUT id INTEGER, IN arg_id INT)
    AS $$
    BEGIN
        SELECT customer_id,
        INTO id
        FROM customer
        WHERE customer_id = arg_id;
    END;
    $$ LANGUAGE plpgsql;

The following example distributes the procedure using only IN parameters.

EXPLAIN(costs false, verbose true) SELECT * FROM get_data(1);

             QUERY PLAN
 -----------------------------------
  Foreign Scan
    Output: id
    Remote SQL:  SELECT customer_id
     FROM get_data(1) get_data(id)
  Single Shard Optimized
 (6 rows)

The setval function isn't supported. For more information, see Sequence Manipulation Functions in the PostgreSQL documentation.

The following options aren't supported.

CREATE [{ TEMPORARY | TEMP} | UNLOGGED] SEQUENCE
    [[ NO ] CYCLE]

For more information, see CREATE SEQUENCE in the PostgreSQL documentation.

The following options aren't supported.

ALTER SEQUENCE
    [[ NO ] CYCLE]

For more information, see ALTER SEQUENCE in the PostgreSQL documentation.

The ALTER TABLE command isn't supported for sequences.

postgres_limitless=> CREATE SEQUENCE s;
CREATE SEQUENCE

postgres_limitless=> SELECT nextval('s');

 nextval
---------
       1
(1 row)

postgres_limitless=> SELECT * FROM pg_sequence WHERE seqrelid='s'::regclass;

 seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle 
----------+----------+----------+--------------+--------+--------+----------+----------
    16960 |       20 |        1 |            1 |  10000 |      1 |        1 | f
(1 row)

% connect to another router
postgres_limitless=> SELECT nextval('s');

 nextval 
---------
   10001
(1 row)

postgres_limitless=> SELECT * FROM pg_sequence WHERE seqrelid='s'::regclass;

 seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle 
----------+----------+----------+--------------+--------+--------+----------+----------
    16959 |       20 |    10001 |            1 |  20000 |  10001 |        1 | f
(1 row)

postgres_limitless=> DROP SEQUENCE s;
DROP SEQUENCE

postgres_limitless=> CREATE SEQUENCE s;
CREATE SEQUENCE

postgres_limitless=> ALTER SEQUENCE s RESTART 500;
ALTER SEQUENCE

postgres_limitless=> SELECT nextval('s');

 nextval
---------
     500
(1 row)

postgres_limitless=> SELECT currval('s');

 currval
---------
     500
(1 row)

postgres=# CREATE TABLE t(a bigint primary key, b bigint);
CREATE TABLE

postgres=# CREATE SEQUENCE s minvalue 0 START 0;
CREATE SEQUENCE

postgres=# INSERT INTO t VALUES (nextval('s'), currval('s'));                                                                                             
INSERT 0 1

postgres=# INSERT INTO t VALUES (nextval('s'), currval('s'));
INSERT 0 1

postgres=# SELECT * FROM t;

 a | b
---+---
 0 | 0
 1 | 1
(2 rows)

postgres=# ALTER SEQUENCE s RESTART 10000;
ALTER SEQUENCE

postgres=# INSERT INTO t VALUES (nextval('s'), currval('s'));                                                                                             
INSERT 0 1

postgres=# SELECT * FROM t;

   a   |   b
-------+-------
     0 |     0
     1 |     1
 10000 | 10000
(3 rows)

This view shows a distributed sequence state and configuration. The minvalue, maxvalue, start, inc, and cache columns have the same meaning as in the pg_sequences view, and show the options with which the sequence was created. The lastval column shows the latest allocated or reserved value in a distributed sequence object. This doesn't mean that the value was already used, as routers keep sequence chunks locally.

postgres_limitless=> SELECT * FROM rds_aurora.limitless_distributed_sequence WHERE sequence_name='test_serial_b_seq';

 schema_name |   sequence_name   | lastval | minvalue |  maxvalue  | start | inc | cache
-------------+-------------------+---------+----------+------------+-------+-----+-------
 public      | test_serial_b_seq | 1250000 |        1 | 2147483647 |     1 |   1 |     1
(1 row)

This view shows distributed sequence metadata and aggregates sequence metadata from cluster nodes. It uses the following columns:

postgres_limitless=> SELECT * FROM rds_aurora.limitless_sequence_metadata WHERE sequence_name='test_serial_b_seq' order by subcluster_id;

 subcluster_id |   sequence_name   | schema_name | active_minvalue | active_maxvalue | reserved_minvalue | reserved_maxvalue | chunk_size | chunk_state | local_last_value 
---------------+-------------------+-------------+-----------------+-----------------+-------------------+-------------------+------------+-------------+------------------
 1             | test_serial_b_seq | public      |          500001 |          750000 |           1000001 |           1250000 |     250000 |           1 |           550010
 2             | test_serial_b_seq | public      |          250001 |          500000 |            750001 |           1000000 |     250000 |           1 |                 
(2 rows)

If the chunk size isn't set large enough and the transaction rate is high, the background workers might not have enough time to request new chunks before the active chunks are exhausted. This can lead to contention and wait events such as LIMITLESS:AuroraLimitlessSequenceReplace, LWLock:LockManager , Lockrelation, and LWlock:bufferscontent.

Increase the value of the rds_aurora.limitless_sequence_chunk_size parameter.

In PostgreSQL, sequence caching happens at the session level. Each session allocates successive sequence values during one access to the sequence object, and increases the sequence object's last_value accordingly. Then, the next uses of nextval within that session simply return the pre-allocated values without touching the sequence object.

Any numbers allocated but not used within a session are lost when that session ends, resulting in "holes" in the sequence. This can consume the sequence_chunk quickly and lead to to contention and wait events such as LIMITLESS:AuroraLimitlessSequenceReplace, LWLock:LockManager , Lockrelation, and LWlock:bufferscontent.

Reduce the sequence cache setting.