단일 샤드 쿼리 예시 단일 샤드 쿼리에 대한 제한 사항 완전한 자격이 있는(명시적인) 조인 활성 샤드 키 설정

Aurora PostgreSQL Limitless Database의 단일 샤드 쿼리

단일 샤드 쿼리는 SQL ACID 의미 체계를 유지하면서 샤드에서 직접 실행할 수 있는 쿼리입니다. 라우터의 쿼리 플래너가 이러한 쿼리를 발견하면 플래너는 이를 감지하고 전체 SQL 쿼리를 해당 샤드로 푸시다운합니다.

이 최적화를 통해 라우터와 샤드 간의 네트워크 왕복 횟수를 줄여 성능을 높일 수 있습니다. 현재 이 최적화는 INSERT, SELECT, UPDATE 및 DELETE 쿼리에 대해 수행됩니다.

단일 샤드 쿼리 예시

다음 예시에서는 샤드 키 customer_id와 참조 테이블 zipcodes가 있는 샤딩된 테이블 customers가 있습니다.

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)

단일 샤드 쿼리에 대한 제한 사항

단일 샤드 쿼리에는 다음과 같은 제한 사항이 있습니다.

함수

단일 샤드 쿼리에 함수가 포함된 경우 쿼리는 다음 조건 중 하나가 적용되는 경우에만 단일 샤드 최적화 자격을 얻습니다.

함수를 변경할 수 없는 경우 자세한 내용은 함수 휘발성 섹션을 참조하세요.
함수를 변경할 수 있지만 rds_aurora.limitless_distributed_functions 뷰에 등록되어 있는 경우. 자세한 내용은 함수 배포 섹션을 참조하세요.

보기

쿼리에 하나 이상의 뷰가 포함된 경우 쿼리에 대해 다음 조건 중 하나가 있는 경우 단일 샤드 최적화가 비활성화됩니다.

뷰에 security_barrier 속성이 있는 경우
쿼리에 사용되는 객체에 여러 사용자 권한이 필요한 경우. 예를 들어 쿼리에 두 개의 뷰가 포함되어 있으며, 뷰가 두 명의 다른 사용자로 실행되는 경우입니다.


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 문에 대한 단일 샤드 최적화를 지원합니다.

그러나 plan_cache_mode가 'force_generic_plan'으로 설정된 상태로 PREPARE 및 EXECUTE에 준비된 문을 사용하는 경우 쿼리 플래너는 해당 쿼리에 대한 단일 샤드 최적화를 거부합니다.

PL/pgSQL

PL/pgSQL 변수가 있는 쿼리는 암시적으로 준비된 문으로 실행됩니다. 쿼리에 PL/pgSQL 변수가 포함된 경우 쿼리 플래너는 단일 샤드 최적화를 거부합니다.

문에 PL/pgSQL 변수가 포함되지 않은 경우 PL/pgSQL 블록에서 최적화가 지원됩니다.

완전한 자격이 있는(명시적인) 조인

단일 샤드 최적화는 파티션 제거를 기반으로 합니다. PostgreSQL 옵티마이저는 일정한 조건에 따라 파티션을 제거합니다. Aurora PostgreSQL Limitless Database에서 나머지 파티션과 테이블이 모두 동일한 샤드에 있는 것으로 확인되면 쿼리가 단일 샤드 최적화에 적합한 것으로 표시됩니다. 파티션 제거가 작동하려면 모든 필터 조건이 명시적이어야 합니다. Aurora PostgreSQL Limitless Database는 문에 있는 모든 샤딩된 테이블의 샤드 키에 조인 조건자 또는 필터 조건자가 하나 이상 있지 않은 경우 파티션을 제거할 수 없습니다.

customer_id 열을 기반으로 customers, orders 및 order_details 테이블을 파티셔닝했다고 가정합니다. 이 스키마에서 애플리케이션은 고객의 모든 데이터를 단일 샤드에 유지하려고 시도합니다.

다음과 같은 쿼리를 가정합니다.


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;

조인

활성 샤드 키를 사용하여 테이블에 대한 조인 작업을 수행할 때 샤드 키 조건자는 조인에 관련된 모든 테이블에 자동으로 추가됩니다. 샤드 키 조건자의 이 자동 추가는 쿼리의 모든 테이블이 동일한 공동 배치 그룹에 속하는 경우에만 발생합니다. 쿼리에 서로 다른 공동 배치 그룹의 테이블이 포함된 경우 오류가 발생합니다.

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;

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

Limitless Database 쿼리

분산된 쿼리