Aurora PostgreSQL Limitless Database の単一シャードクエリ
単一シャードクエリは、SQL ACID
この最適化により、ルーターとシャード間のネットワークラウンドトリップの回数が減り、パフォーマンスが向上します。現在、この最適化は INSERT、SELECT、UPDATE、および DELETE クエリに対して実行されます。
単一シャードクエリの例
次の例では、シャードキー customer_id を持つシャードテーブル customers と、リファレンステーブル zipcodes を示します。
- SELECT
-
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM customers WHERE customer_id = 100; QUERY PLAN --------------------------------------------------------- Foreign Scan Output: customer_id, other_id, customer_name, balance Remote SQL: SELECT customer_id, other_id, customer_name, balance FROM public.customers WHERE (customer_id = 100) Single Shard Optimized (9 rows)postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders LEFT JOIN zipcodes ON orders.zipcode_id = zipcodes.zipcode_id WHERE customer_id = 11; QUERY PLAN --------------------------------------------------------------------------------------------------------- Foreign Scan Output: customer_id, order_id, zipcode_id, customer_name, balance, zipcodes.zipcode_id, zipcodes.city Remote SQL: SELECT orders.customer_id, orders.order_id, orders.zipcode_id, orders.customer_name, orders.balance, zipcodes.zipcode_id, zipcodes.city FROM (public.orders LEFT JOIN public.zipcodes ON ((orders.zipcode_id = zipcodes.zipcode_id))) WHERE (orders.customer_id = 11) Single Shard Optimized (13 rows) - INSERT
-
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO customers (customer_id, other_id, customer_name, balance) VALUES (1, 10, 'saikiran', 1000); QUERY PLAN ------------------------------------------------------- Insert on public.customers -> Result Output: 1, 10, 'saikiran'::text, '1000'::real Single Shard Optimized (4 rows) - UPDATE
-
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) UPDATE orders SET balance = balance + 100 WHERE customer_id = 100; QUERY PLAN --------------------------------------------------------------------------------------------- Update on public.orders Foreign Update on public.orders_fs00002 orders_1 -> Foreign Update Remote SQL: UPDATE public.orders SET balance = (balance + (100)::double precision) WHERE (customer_id = 100) Single Shard Optimized (6 rows) - DELETE
-
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM orders WHERE customer_id = 100 and balance = 0; QUERY PLAN --------------------------------------------------------------------- Delete on public.orders Foreign Delete on public.orders_fs00002 orders_1 -> Foreign Delete Remote SQL: DELETE FROM public.orders WHERE ((customer_id = 100) AND (balance = (0)::double precision)) Single Shard Optimized (6 rows)
単一シャードクエリの制限
単一シャードクエリには以下の制限があります。
- 関数
-
単一シャードクエリに関数が含まれている場合、クエリは次のいずれかの条件に該当する場合にのみ単一シャード最適化の対象となります。
- ビュー
-
クエリに 1 つ以上のビューが含まれている場合で、次のいずれかの条件に該当する場合、クエリの単一シャード最適化は無効になります。
-
どのビューにも
security_barrier属性がある。 -
クエリで使用されるオブジェクトには、複数のユーザー権限が必要である。例えば、クエリに 2 つのビューが含まれ、ビューが 2 つの異なるユーザーで実行される場合です。
CREATE VIEW v1 AS SELECT customer_name FROM customers c WHERE c.customer_id = 1; CREATE VIEW v2 WITH (security_barrier) AS SELECT customer_name FROM customers c WHERE c.customer_id = 1; postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v1; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan (cost=100.00..101.00 rows=100 width=0) Output: customer_name Remote Plans from Shard postgres_s3: Seq Scan on public.customers_ts00001 c (cost=0.00..24.12 rows=6 width=32) Output: c.customer_name Filter: (c.customer_id = 1) Query Identifier: -6005737533846718506 Remote SQL: SELECT customer_name FROM ( SELECT c.customer_name FROM public.customers c WHERE (c.customer_id = 1)) v1 Query Identifier: -5754424854414896228 (12 rows) postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v2; QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.customers_fs00001 c (cost=100.00..128.41 rows=7 width=32) Output: c.customer_name Remote Plans from Shard postgres_s3: Seq Scan on public.customers_ts00001 customers (cost=0.00..24.12 rows=6 width=32) Output: customers.customer_name Filter: (customers.customer_id = 1) Query Identifier: 4136563775490008117 Remote SQL: SELECT customer_name FROM public.customers WHERE ((customer_id = 1)) Query Identifier: 5056054318010163757 (9 rows) -
- PREPARE ステートメントと EXECUTE ステートメント
-
Aurora PostgreSQL Limitless Database は、準備済み
SELECTステートメントに対する単一シャード最適化をサポートしています。ただし、準備済みステートメントが
UPDATEまたはDELETEで、準備済み変数が含まれている場合、クエリプランナーはそのクエリに対する単一シャード最適化を拒否します。次にこの例を示します。Single Shard Optimizedインジケータはありません。postgres_limitless=> PREPARE testStmt AS SELECT customer_name FROM customers c WHERE c.customer_id = $1; PREPARE postgres_limitless=> EXECUTE testStmt(1); customer_name --------------- (0 rows)postgres_limitless=> EXPLAIN verbose EXECUTE testStmt(1); QUERY PLAN -------------------------------------------------------------------------- Foreign Scan (cost=100.00..101.00 rows=100 width=0) Output: customer_name Remote Plans from Shard postgres_s3: Seq Scan on public.customers_ts00001 c (cost=0.00..24.12 rows=6 width=32) Output: c.customer_name Filter: (c.customer_id = 1) Query Identifier: 1520926022284463170 Remote SQL: SELECT customer_name FROM public.customers c WHERE (customer_id = $1) Query Identifier: 1520926022284463170 (11 rows) - PL/pgSQL
-
PL/pgSQL 変数を含むクエリは、暗黙的に準備済みステートメントとして実行されます。クエリに PL/pgSQL 変数が含まれている場合、クエリプランナーは単一シャード最適化を拒否します。
ステートメントに PL/pgSQL 変数が含まれていない場合、PL/pgSQL ブロックで最適化がサポートされます。
完全修飾 (明示的) 結合
単一シャード最適化は、パーティション排除に基づいています。PostgreSQL オプティマイザーは、一定の条件に基づいてパーティションを排除します。Aurora PostgreSQL Limitless Database が、残りのすべてのパーティションとテーブルが同じシャードにあると判断した場合、単一シャード最適化の対象としてクエリをマークします。パーティション排除が機能するには、すべてのフィルター条件が明示的である必要があります。Aurora PostgreSQL Limitless Database は、ステートメント内のすべてのシャードテーブルのシャードキーに対して 1 つ以上の結合述語またはフィルター述語がないパーティションを排除することはできません。
customer_id 列に基づいて、customers、orders、order_details テーブルをパーティション化したとします。このスキーマでは、アプリケーションは顧客のすべてのデータを 1 つのシャードに保持しようとします。
次のクエリについて考えます。
SELECT * FROM customers c, orders o, order_details od WHERE c.customer_id = o.customer_id AND od.order_id = o.order_id AND c.customer_id = 1;
このクエリは、顧客 (c.customer_id = 1) のすべてのデータを取得します。この顧客のデータは単一のシャードにありますが、Aurora PostgreSQL Limitless Database はこのクエリを単一シャードクエリとして認識しません。クエリのオプティマイザープロセスは次のとおりです。
-
オプティマイザーは、次の条件に基づいて
customersおよびordersのパーティションを排除できます。c.customer_id = 1 c.customer_id = o.customer_id o.customer_id = 1 (transitive implicit condition) -
テーブルに一定の条件がないため、オプティマイザーは
order_detailsのパーティションを排除できません。 -
オプティマイザーは、
order_detailsからすべてのパーティションを読み取ったと結論付けます。そのため、このクエリは単一シャード最適化の対象とはなりません。
これを単一シャードクエリにするには、次の明示的な結合条件を追加します。
o.customer_id = od.customer_id
変更後のクエリは次のようになります。
SELECT * FROM customers c, orders o, order_details od WHERE c.customer_id = o.customer_id AND o.customer_id = od.customer_id AND od. order_id = o. order_id AND c.customer_id = 1;
これで、オプティマイザーは order_details のパーティションを排除できます。新しいクエリは単一シャードクエリになり、最適化の対象となります。
アクティブなシャードキーの設定
この機能を使用すると、データベースのクエリ中に単一のシャードキーを設定でき、これにより、すべての SELECT および DML クエリにシャードキーが定数述語として追加されます。この機能は、Aurora PostgreSQL Limitless Database に移行し、テーブルにシャードキーを追加してスキーマを非正規化した場合に便利です。
クエリのセマンティクスを変更せずに、シャードキー述語を既存の SQL ロジックに自動的に追加できます。アクティブなシャードキー述語の追加は、互換性のあるテーブルに対してのみ行われます。
アクティブなシャードキー機能は、次の構文を持つ rds_aurora.limitless_active_shard_key 変数を使用します。
SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';
アクティブなシャードキーと外部キーに関する考慮事項:
-
親テーブルと子テーブルがコロケーションされ、外部キーがシャードキーのスーパーセットである場合、シャードテーブルには外部キー制約を含めることができます。
-
シャードテーブルには、リファレンステーブルへの外部キー制約を含めることができます。
-
リファレンステーブルには、別のリファレンステーブルへの外部キー制約を含めることができます。
customer_id 列にシャードされた customers テーブルがあるとします。
BEGIN; SET local rds_aurora.limitless_create_table_mode='sharded'; SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}'; CREATE TABLE customers(customer_id int PRIMARY KEY, name text , email text); COMMIT;
アクティブなシャードキーが設定されている場合、クエリには次の変換が適用されます。
- SELECT
-
SET rds_aurora.limitless_active_shard_key = '{"123"}'; SELECT * FROM customers; -- This statement is changed to: SELECT * FROM customers WHERE customer_id = '123'::int; - INSERT
-
SET rds_aurora.limitless_active_shard_key = '{"123"}'; INSERT INTO customers(name, email) VALUES('Alex', 'alex@example.com'); -- This statement is changed to: INSERT INTO customers(customer_id, name, email) VALUES('123'::int, 'Alex', 'alex@example.com'); - UPDATE
-
SET rds_aurora.limitless_active_shard_key = '{"123"}'; UPDATE customers SET email = 'alex_new_email@example.com'; -- This statement is changed to: UPDATE customers SET email = 'alex_new_email@example.com' WHERE customer_id = '123'::int; - DELETE
-
SET rds_aurora.limitless_active_shard_key = '{"123"}'; DELETE FROM customers; -- This statement is changed to: DELETE FROM customers WHERE customer_id = '123'::int; - Joins
-
アクティブなシャードキーを持つテーブルで結合オペレーションを実行すると、結合に関係するすべてのテーブルにシャードキー述語が自動的に追加されます。このシャードキー述語の自動追加は、クエリ内のすべてのテーブルが同じコロケーショングループに属している場合にのみ発生します。クエリに異なるコロケーショングループのテーブルが含まれる場合、代わりにエラーが発生します。
customersテーブルとコロケーションされているordersテーブルとorder_detailsテーブルがあるとします。SET local rds_aurora.limitless_create_table_mode='sharded'; SET local rds_aurora.limitless_create_table_collocate_with='customers'; SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}'; CREATE TABLE orders (id int , customer_id int, total_amount int, date date); CREATE TABLE order_details (id int , order_id int, customer_id int, product_name VARCHAR(100), price int); COMMIT;顧客 ID が 10 である顧客の過去 10 件の注文請求書を取得します。
SET rds_aurora.limitless_active_shard_key = '{"10"}'; SELECT * FROM customers, orders, order_details WHERE orders.customer_id = customers.customer_id AND order_details.order_id = orders.order_id AND customers.customer_id = 10 order by order_date limit 10;このクエリは、次に変換されます。
SELECT * FROM customers, orders, order_details WHERE orders.customer_id = customers.customer_id AND orders.order_id = order_details.order_id AND customers.customer_id = 10 AND order_details.customer_id = 10 AND orders.customer_id = 10 AND ORDER BY "order_date" LIMIT 10; - アクティブなシャードキーと互換性のあるテーブル
-
シャードキー述語は、アクティブなシャードキーと互換性のあるテーブルにのみ追加されます。テーブルのシャードキー内の列数が
rds_aurora.limitless_active_shard_key変数で指定されている列数と同じである場合、テーブルは互換性があると見なされます。クエリにアクティブなシャードキーと互換性のないテーブルが含まれる場合、システムはクエリを続行する代わりにエラーを発生させます。以下に例を示します。
-- Compatible table SET rds_aurora.limitless_active_shard_key = '{"10"}'; -- The following query works because the customers table is sharded on one column. SELECT * FROM customers; -- Incompatible table SET rds_aurora.limitless_active_shard_key = '{"10","20"}'; -- The following query raises a error because the customers table isn't sharded on two columns. SELECT * FROM customers;
