Exemplos de consulta de fragmento único Restrições para consultas de fragmento único Junções totalmente qualificadas (explícitas)Configurar uma chave de fragmento ativa

Consultas de fragmento único no Aurora PostgreSQL Limitless Database

Uma consulta de fragmento único é uma consulta que pode ser executada diretamente em um fragmento, mantendo a semântica ACID do SQL. Quando essa consulta é encontrada pelo planejador de consultas no roteador, o planejador a detecta e procede enviando toda a consulta SQL para o fragmento correspondente.

Essa otimização reduz o número de viagens de ida e volta na rede do roteador para o fragmento, melhorando o desempenho. Atualmente, essa otimização é executada para consultas INSERT, SELECT, UPDATE e DELETE.

Tópicos

Exemplos de consulta de fragmento único
Restrições para consultas de fragmento único
Junções totalmente qualificadas (explícitas)
Configurar uma chave de fragmento ativa

Exemplos de consulta de fragmento único

Nos exemplos a seguir, temos a tabela fragmentada customers, com a chave de fragmento customer_id, e a tabela de referência 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)

Restrições para consultas de fragmento único

As consultas de fragmento único têm as seguintes restrições:

Funções

Se uma consulta de fragmento único contiver uma função, a consulta se qualificará para otimização de fragmento único somente se uma das seguintes condições se aplicar:

A função é imutável. Para obter mais informações, consulte Volatilidade da função.
A função é mutável, mas está registrada na visualização rds_aurora.limitless_distributed_functions. Para obter mais informações, consulte Distribuição de funções.

Visões

Se uma consulta contiver uma ou mais visualizações, a otimização de fragmento único será desabilitada para a consulta se ela tiver uma das seguintes condições:

Toda visualização tem o atributo security_barrier.
Os objetos usados na consulta exigem vários privilégios de usuário. Por exemplo, uma consulta contém duas visualizações, e as visualizações são executadas sob dois usuários diferentes.


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)

Instruções PREPARE e EXECUTE

O Aurora PostgreSQL Limitless Database comporta a otimização de fragmento único para declarações SELECT, UPDATE e DELETE preparadas.

No entanto, se você usar declarações preparadas para PREPARE e EXECUTE com plan_cache_mode definido como 'force_generic_plan', o planejador de consultas rejeitará a otimização de fragmento único para essa consulta.

PL/pgSQL

As consultas com variáveis PL/pgSQL são executadas como instruções preparadas implicitamente. Se uma consulta contiver qualquer variável PL/pgSQL, o planejador de consultas rejeitará a otimização de fragmento único.

A otimização é compatível no bloco PL/pgSQL se a instrução não contiver nenhuma variável PL/pgSQL.

Junções totalmente qualificadas (explícitas)

A otimização de fragmento único é baseada em eliminações de partições. O otimizador do PostgreSQL elimina partições com base em condições constantes. Se o Aurora PostgreSQL Limitless Database descobrir que todas as partições e tabelas restantes estão no mesmo fragmento, ele marca a consulta como elegível para otimização de fragmento único. Todas as condições de filtro devem ser explícitas para que a eliminação de partições funcione. O Aurora PostgreSQL Limitless Database não pode eliminar partições sem um ou mais predicados de junção ou predicados de filtro nas chaves de fragmento de cada tabela fragmentada na instrução.

Suponha que tenhamos particionado as tabelas customers, orders e order_details com base na coluna customer_id. Nesse esquema, a aplicação tenta manter todos os dados de um cliente em um único fragmento.

Considere a seguinte consulta:


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;

Essa consulta recupera todos os dados de um cliente (c.customer_id = 1). Os dados desse cliente estão em um único fragmento, mas o Aurora PostgreSQL Limitless Database não qualifica essa consulta como uma consulta de fragmento único. O processo de otimização da consulta é o seguinte:

O otimizador pode eliminar partições para customers e orders com base na seguinte condição:


c.customer_id = 1
c.customer_id = o.customer_id
o.customer_id =  1 (transitive implicit condition)

O otimizador não consegue eliminar nenhuma partição para order_details, pois não há nenhuma condição constante na tabela.
O otimizador conclui que leu todas as partições de order_details. Portanto, a consulta não pode ser qualificada para otimização de fragmento único.

Para fazer disso uma consulta de fragmento único, adicionamos a seguinte condição de junção explícita:


o.customer_id = od.customer_id

A consulta alterada fica assim:


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;

Agora, o otimizador pode eliminar partições para order_details. A nova consulta se torna uma consulta de fragmento único e se qualifica para otimização.

Configurar uma chave de fragmento ativa

Esse recurso permite que você defina uma única chave de fragmento ao consultar o banco de dados, fazendo com que todas as consultas SELECT e DML sejam anexadas à chave de fragmento como um predicado constante. Esse recurso é útil se você migrou para o Aurora PostgreSQL Limitless Database e desnormalizou o esquema adicionando chaves de fragmento às tabelas.

É possível anexar um predicado de chave de fragmento automaticamente à lógica SQL existente, sem alterar a semântica das consultas. A anexação de um predicado de chave de fragmento ativa é feita somente para tabelas compatíveis.

O recurso de chave de fragmento ativa usa a variável rds_aurora.limitless_active_shard_key, que tem a seguinte sintaxe:


SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';

Algumas considerações sobre chaves de fragmento ativas e chaves estrangeiras:

Uma tabela fragmentada pode ter uma restrição de chave externa se as tabelas pai e filha tiverem localização compartilhada e a chave externa for um superconjunto da chave de fragmento.
Uma tabela fragmentada pode ter uma restrição de chave externa para uma tabela de referência.
Uma tabela de referência pode ter uma restrição de chave externa para outra tabela de referência.

Suponha que temos uma tabela customers que é fragmentada na coluna customer_id.


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;

Com um conjunto de chaves de fragmento ativo, as consultas têm as transformações a seguir.

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;

Junções

Ao realizar operações de junção em tabelas com uma chave de fragmento ativa, o predicado da chave de fragmento é automaticamente adicionado a todas as tabelas envolvidas na junção. Essa adição automática do predicado da chave de fragmento ocorre somente quando todas as tabelas na consulta pertencem ao mesmo grupo de colocalização. Se a consulta envolver tabelas de diferentes grupos de colocalização, um erro será gerado em vez disso.

Suponha que também temos tabelas orders e order_details que estão colocalizadas com a tabela customers.


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;

Recupere as últimas 10 faturas de pedidos de um cliente cujo ID de cliente é 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;

Essa consulta é transformada na seguinte:


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;

Tabelas compatíveis com chave de fragmento ativa

O predicado da chave de fragmento é adicionado somente às tabelas compatíveis com a chave de fragmento ativa. Uma tabela é considerada compatível se tiver o mesmo número de colunas em sua chave de fragmento, conforme especificado na variável rds_aurora.limitless_active_shard_key. Se a consulta envolver tabelas incompatíveis com a chave de fragmento ativa, o sistema gerará um erro em vez de prosseguir com a consulta.

Por exemplo:


-- 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;

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Consulta do Limitless Database

Consultas distribuídas