# Creating Aurora PostgreSQL Limitless Database tables Creating Limitless Database tables There are three types of tables that contain your data in Aurora PostgreSQL Limitless Database: + Standard – This is the default table type in Aurora PostgreSQL Limitless Database. You create standard tables using the [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html) command, and can run Data Description Language (DDL) and Data Manipulation Language (DML) operations on them. Standard tables aren't distributed tables. They're stored on one of the shards chosen internally by the system. + Sharded – These tables are distributed across multiple shards. Data is split among the shards based on the values of designated columns in the table. This set of columns is called a shard key. + Reference – These tables are replicated on all shards. They're used for infrequently modified reference data, such as product catalogs and zip codes. Join queries between reference and sharded tables can be run on shards, eliminating unnecessary data movement between shards and routers. There are two ways to create limitless tables: + [Creating limitless tables by using variables](limitless-creating-config.md) – Use this method when you want to create new sharded and reference tables. + [Converting standard tables to limitless tables](limitless-converting-standard.md) – Use this method when you want to convert existing standard tables into sharded and reference tables. We also provide [sample schemas](limitless-sample-schemas.md) for Aurora PostgreSQL Limitless Database. # Creating limitless tables by using variables You can use variables to create sharded and reference tables by setting the table creation mode. Then the tables that you create will use this mode until you set a different mode. Use the following variables to create sharded and reference tables: + `rds_aurora.limitless_create_table_mode` – Set this session variable to `sharded` or `reference`. The default value of this variable is `standard`. + `rds_aurora.limitless_create_table_shard_key` – Set this session variable to an array of column names to use as shard keys. This variable is ignored when `rds_aurora.limitless_create_table_mode` isn't `sharded`. Format the value as `untyped array literal`, similar to when you insert literals into an array column. For more information, see [Arrays](https://www.postgresql.org/docs/current/arrays.html) in the PostgreSQL documentation. + `rds_aurora.limitless_create_table_collocate_with` – Set this session variable to a specific table name to collocate newly created tables with that table. If two or more tables are sharded using the same shard key, you can explicitly align (collocate) those tables. When two or more tables are collocated, rows from those tables with the same shard key values are placed on the same shard. Collocation helps to restrict some operations to a single shard, which results in better performance. **Note** All primary and unique keys must include the shard key. This means that the shard key is a subset of the primary or unique key. Limitless tables have some limitations. For more information, see [DDL limitations and other information for Aurora PostgreSQL Limitless Database](limitless-reference.DDL-limitations.md). **Topics** + [ ## Examples using variables to create limitless tables ](#limitless-tables-examples) + [ ## Aurora PostgreSQL Limitless Database table views ](#limitless-table-views) ## Examples using variables to create limitless tables Examples using variables The following examples show how to use these variables to create sharded and reference tables. Create a sharded table named `items`, with the shard key `id`. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"id"}'; CREATE TABLE items(id int, val int, item text); COMMIT; ``` Create a sharded table named `items`, with a shard key composed of the `item_id` and `item_cat` columns. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"item_id", "item_cat"}'; CREATE TABLE items(item_id int, item_cat varchar, val int, item text); COMMIT; ``` Create a sharded table named `item_description`, with a shard key composed of the `item_id` and `item_cat` columns, and collocate it with the `items` table from the previous example. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='sharded'; SET LOCAL rds_aurora.limitless_create_table_shard_key='{"item_id", "item_cat"}'; SET LOCAL rds_aurora.limitless_create_table_collocate_with='items'; CREATE TABLE item_description(item_id int, item_cat varchar, color_id int); COMMIT; ``` Create a reference table named `colors`. ``` BEGIN; SET LOCAL rds_aurora.limitless_create_table_mode='reference'; CREATE TABLE colors(color_id int primary key, color varchar); COMMIT; ``` To reset the `rds_aurora.limitless_create_table_mode` session variable to `standard`, use the following statement: ``` RESET rds_aurora.limitless_create_table_mode; ``` After you reset this variable, tables are created as standard tables, which is the default. For more information on standard tables, see [Converting standard tables to limitless tables](limitless-converting-standard.md). ## Aurora PostgreSQL Limitless Database table views You can find information about Limitless Database tables by using the following views. **rds\$1aurora.limitless\$1tables** The `rds_aurora.limitless_tables` view contains information about limitless tables and their types. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables; table_gid | local_oid | schema_name | table_name | table_status | table_type | distribution_key -----------+-----------+-------------+-------------+--------------+-------------+------------------ 5 | 18635 | public | standard | active | standard | 6 | 18641 | public | ref | active | reference | 7 | 18797 | public | orders | active | sharded | HASH (order_id) 2 | 18579 | public | customer | active | sharded | HASH (cust_id) (4 rows) ``` **rds\$1aurora.limitless\$1table\$1collocations** The `rds_aurora.limitless_table_collocations` view contains information about collocated sharded tables. For example, the `orders` and `customers` tables are collocated, and have the same `collocation_id`. The `users` and `followers` tables are collocated, and have the same `collocation_id`. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id; collocation_id | schema_name | table_name ----------------+-------------+------------ 16002 | public | orders 16002 | public | customers 16005 | public | users 16005 | public | followers (4 rows) ``` **rds\$1aurora.limitless\$1table\$1collocation\$1distributions** The `rds_aurora.limitless_table_collocation_distributions` shows the key distribution for each collocation. ``` postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocation_distributions ORDER BY collocation_id, lower_bound; collocation_id | subcluster_id | lower_bound | upper_bound ----------------+---------------+----------------------+---------------------- 16002 | 6 | -9223372036854775808 | -4611686018427387904 16002 | 5 | -4611686018427387904 | 0 16002 | 4 | 0 | 4611686018427387904 16002 | 3 | 4611686018427387904 | 9223372036854775807 16005 | 6 | -9223372036854775808 | -4611686018427387904 16005 | 5 | -4611686018427387904 | 0 16005 | 4 | 0 | 4611686018427387904 16005 | 3 | 4611686018427387904 | 9223372036854775807 (8 rows) ``` # Converting standard tables to limitless tables You can convert standard tables into sharded or reference tables. During the conversion, data is moved from the standard table to the distributed table, then the source standard table is deleted. Data is moved using the `INSERT INTO SELECT FROM` command. **Contents** + [ ## Creating sharded tables ](#limitless-creating-sharded) + [ ## Creating collocated tables ](#limitless-creating-sharded.colocated) + [ ## Creating reference tables ](#limitless-creating-reference) ## Creating sharded tables You create sharded tables by running the `rds_aurora.limitless_alter_table_type_sharded` procedure on standard tables. This procedure takes a standard table and a list of columns, then distributes the given table using the list of columns as the shard key. The procedure runs synchronously, and acquires an `ACCESS EXCLUSIVE` lock on the table. After the procedure finishes successfully, the source standard table is deleted, and a sharded table with the same name becomes available. The `rds_aurora.limitless_alter_table_type_sharded` procedure uses the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('schema.table', ARRAY['shard_key1', 'shard_key2', ... 'shard_keyn']); ``` The procedure requires the following parameters: + `schema` – The database schema that contains the table to be sharded. If the schema isn't specified, the procedure uses the `search_path`. + `table` – The table to be sharded. + `shard_keyn` – An array of table columns to use as the shard key. Shard key values are string literals, and are therefore case sensitive. If a shard key contains a single quote ('), use another single quote to escape it. For example, if a table column is named `customer's id`, use `customer''s id` as the shard key. Backslashes (\$1) and double quotes (") don't need to be escaped. **Note** All primary and unique keys must include the shard key. This means that the shard key is a subset of the primary or unique key. In sharded tables, the `CHECK` constraint doesn't support expressions. For more information, see [Constraints](limitless-reference.DDL-limitations.md#limitless-reference.DDL-limitations.Constraints). **To create a sharded table** The following example shows how to create the `customer` sharded table with the shard key `customer_id`. 1. Create the standard table. ``` CREATE TABLE customer (customer_id INT PRIMARY KEY NOT NULL, zipcode INT, email VARCHAR); ``` 1. Convert the standard table to a sharded table. ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('public.customer', ARRAY['customer_id']); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless (6 rows) ``` ## Creating collocated tables If two or more tables are sharded using the same shard key, you can explicitly align (collocate) those tables. When two or more tables are collocated, rows from those tables with the same shard key values are placed on the same shard. Collocation helps to restrict some operations to a single shard, which results in better performance. You use the `rds_aurora.limitless_alter_table_type_sharded` procedure with the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('schema.collocated_table', ARRAY['shard_key1', 'shard_key2', ... 'shard_keyn'], 'schema.sharded_table'); ``` The procedure requires the following parameters: + `schema` – The database schema that contains the tables to be collocated. If the schema isn't specified, the procedure uses the `search_path`. + `collocated_table` – The table to be collocated. + `shard_keyn` – An array of table columns to use as the shard key. You must use the same shard key as for the original sharded table, including the same column names and column types. + `sharded_table` – The sharded table with which you're collocating the `collocated_table`. **To create a collocated table** 1. Create the first sharded table by following the procedure in [Creating sharded tables](#limitless-creating-sharded). 1. Create the standard table for the collocated table. ``` CREATE TABLE mytable2 (customer_id INT PRIMARY KEY NOT NULL, column1 INT, column2 VARCHAR); ``` 1. Convert the standard table to a collocated table. ``` postgres=> CALL rds_aurora.limitless_alter_table_type_sharded('public.mytable2', ARRAY['customer_id'], 'public.customer'); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless public | mytable2 | partitioned table | postgres_limitless public | mytable2_fs1 | foreign table | postgres_limitless public | mytable2_fs2 | foreign table | postgres_limitless public | mytable2_fs3 | foreign table | postgres_limitless public | mytable2_fs4 | foreign table | postgres_limitless public | mytable2_fs5 | foreign table | postgres_limitless (12 rows) ``` ## Creating reference tables You create reference tables by running the `rds_aurora.limitless_alter_table_type_reference` procedure on standard tables. This procedure replicates a given table to all shards in the DB shard group, and changes the table type to reference. The procedure runs synchronously, and acquires an `ACCESS EXCLUSIVE` lock on the table. After the procedure finishes successfully, the source standard table is deleted, and a reference table with the same name becomes available. The `rds_aurora.limitless_alter_table_type_reference` procedure uses the following syntax: ``` postgres=> CALL rds_aurora.limitless_alter_table_type_reference('schema.table'); ``` The stored procedure requires the following parameters: + `schema` – The database schema that contains the table to be replicated. If the schema isn't specified, the procedure uses the `search_path`. + `table` – The table to be replicated. **Note** The standard table from which you create the reference table must have a primary key. In reference tables, the `CHECK` constraint doesn't support expressions. The previous function, `limitless_table_alter_type_reference`, is deprecated. **To create a reference table** The following example shows how to create the `zipcodes` reference table. 1. Create the standard table. ``` CREATE TABLE zipcodes (zipcode INT PRIMARY KEY, details VARCHAR); ``` 1. Convert the standard table to a reference table. ``` CALL rds_aurora.limitless_alter_table_type_reference('public.zipcodes'); postgres=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------------------+-------------------- public | customer | partitioned table | postgres_limitless public | customer_fs1 | foreign table | postgres_limitless public | customer_fs2 | foreign table | postgres_limitless public | customer_fs3 | foreign table | postgres_limitless public | customer_fs4 | foreign table | postgres_limitless public | customer_fs5 | foreign table | postgres_limitless public | zipcodes | foreign table | postgres_limitless (7 rows) ``` The output shows the `customer` sharded table and the `zipcodes` reference table. # Aurora PostgreSQL Limitless Database sample schemas Sample schemas We provide the following sample schemas for Aurora PostgreSQL Limitless Database: + [Limitless E-Commerce sample schema](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database/tree/main/limitless_ec_sample_schema) + [Limitless pgbench](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database/tree/main/limitless_pgbench) You can use these schemas to quickly create a sample database and load data into Aurora PostgreSQL Limitless Database tables. For more information, see the [GitHub repository](https://github.com/aws-samples/sample-schemas-for-amazon-aurora-postgresql-limitless-database).