Requêtes à partition unique dans Aurora PostgreSQL Limitless Database - Amazon Aurora
Exemples de requêtes à partition uniqueRestrictions appliquées aux requêtes à partition uniqueJointures entièrement qualifiées (explicites)Configuration d’une clé de partition active

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Requêtes à partition unique dans Aurora PostgreSQL Limitless Database

Une requête à partition unique est une requête qui peut être exécutée directement sur une partition, tout en conservant les propriétés ACID du SQL. Lorsque le planificateur de requêtes du routeur identifie une requête de ce type, il transmet la requête SQL complète à la partition concernée.

Cette optimisation réduit le nombre d’allers et retours réseau entre le routeur et la partition, améliorant ainsi les performances. Actuellement, cette optimisation est effectuée pour les requêtes INSERT, SELECT, UPDATE et DELETE.

Exemples de requêtes à partition unique

Dans les exemples suivants, nous utiliserons la table partitionnée customers, avec la clé de partition customer_id et la table de référence 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)

Restrictions appliquées aux requêtes à partition unique

Les requêtes à partition unique sont soumises aux restrictions suivantes :

Fonctions

Si une requête à partition unique contient une fonction, elle n’est éligible à l’optimisation à partition unique que si l’une des conditions suivantes est remplie :

  • La fonction est immuable. Pour plus d’informations, consultez Volatilité des fonctions.

  • La fonction est mutable, mais elle est enregistrée dans la vue rds_aurora.limitless_distributed_functions. Pour plus d’informations, consultez Répartition des fonctions.

Vues

Si une requête contient une ou plusieurs vues, l’optimisation à partition unique est désactivée si l’une des conditions suivantes est remplie :

  • N’importe quelle vue possède l’attribut security_barrier.

  • Les objets utilisés dans la requête nécessitent plusieurs privilèges utilisateur. Par exemple, une requête contient deux vues, lesquelles sont exécutées par deux utilisateurs différents.

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)
Instructions PREPARE et EXECUTE

Aurora PostgreSQL Limitless Database prend en charge l’optimisation à partition unique pour les instructions préparées SELECT, UPDATE et DELETE.

Cependant, lorsque des instructions préparées sont utilisées pour PREPARE et EXECUTE alors que plan_cache_mode est défini sur 'force_generic_plan', le planificateur de requêtes rejette l’optimisation à partition unique pour cette requête.

PL/pgSQL

Les requêtes contenant des variables PL/pgSQL sont exécutées sous forme d’instructions préparées implicites. Si une requête contient des variables PL/pgSQL, le planificateur de requêtes rejette l’optimisation à partition unique.

L’optimisation est prise en charge dans le bloc PL/pgSQL si l’instruction ne contient aucune variable PL/pgSQL.

Jointures entièrement qualifiées (explicites)

L’optimisation à partition unique est basée sur l’élimination des partitions. L’optimiseur PostgreSQL élimine les partitions en fonction de conditions constantes. Si Aurora PostgreSQL Limitless Database constate que toutes les partitions et tables restantes résident sur la même partition, elle indique que la requête est éligible à l’optimisation à partition unique. Toutes les conditions du filtre doivent être explicites pour que l’élimination des partitions fonctionne. Aurora PostgreSQL Limitless Database ne peut pas éliminer de partitions en l’absence d’un ou de plusieurs prédicats de jointure ou de filtrage portant sur les clés de partition de chaque table partitionnée de l’instruction.

Supposons que nous ayons partitionné les tables customers, orders et order_details en fonction de la colonne customer_id. Dans ce schéma, l’application essaie de conserver toutes les données d’un client sur une seule partition.

Considérons la requête suivante :

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;

Cette requête récupère toutes les données d’un client (c.customer_id = 1). Les données de ce client résident sur une seule partition, mais Aurora PostgreSQL Limitless Database ne considère pas cette requête comme une requête à partition unique. Le processus d’optimisation de la requête est le suivant :

  1. L’optimiseur peut éliminer les partitions pour customers et orders selon les conditions suivantes :

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

  2. L’optimiseur ne peut éliminer aucune partition pour order_details, car aucune condition constante n’est appliquée à la table.

  3. L’optimiseur détermine qu’il a lu toutes les partitions depuis order_details. Par conséquent, la requête ne peut pas être qualifiée pour l’optimisation à partition unique.

Pour en faire une requête à partition unique, nous ajoutons la condition de jointure explicite suivante :

o.customer_id = od.customer_id

La requête modifiée se présente comme suit :

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;

L’optimiseur peut désormais éliminer les partitions pour order_details. La nouvelle requête devient une requête à partition unique et est éligible à l’optimisation.

Configuration d’une clé de partition active

Cette fonctionnalité vous permet de définir une clé de partition unique lors de l’interrogation de la base de données, ce qui permet d’ajouter la clé de partition à toutes les requêtes SELECT et DML en tant que prédicat constant. Cette fonctionnalité est utile si vous avez migré vers Aurora PostgreSQL Limitless Database et que vous avez dénormalisé le schéma en ajoutant des clés de partition aux tables.

Vous pouvez ajouter automatiquement un prédicat de clé de partition à la logique SQL existante, sans modifier la sémantique des requêtes. L’ajout d’un prédicat de clé de partition actif n’est possible que sur les tables compatibles.

La fonctionnalité de clé de partition active utilise la variable rds_aurora.limitless_active_shard_key, dont la syntaxe est la suivante :

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

Quelques considérations concernant les clés de partition actives et les clés étrangères :

  • Une table partitionnée peut être soumise à une contrainte de clé étrangère lorsque les tables parente et enfant sont colocalisées et que la clé étrangère constitue un sur-ensemble de la clé de partition.

  • Une table partitionnée peut être soumise à une contrainte de clé étrangère vers une table de référence.

  • Une table de référence peut être soumise à une contrainte de clé étrangère vers une autre table de référence.

Prenons l’exemple d’une table partitionnée customers sur la colonne 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;

Lorsqu’une clé de partition active est définie, les requêtes subissent les transformations suivantes.

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

Lorsque vous effectuez des opérations de jointure sur des tables ayant une clé de partition active, le prédicat de clé de partition est automatiquement ajouté à toutes les tables participant à la jointure. Cet ajout automatique du prédicat de clé de partition se produit uniquement lorsque toutes les tables de la requête appartiennent au même groupe de collocation. Lorsque des tables issues de plusieurs groupes de colocalisation sont impliquées dans la requête, le système renvoie une erreur.

Supposons en outre l’existence des tables orders et order_details, qui sont colocalisées avec la table 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;

Récupérez les 10 dernières factures de commande du client portant l’ID 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;

Cette requête est transformée comme suit :

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;
Tables compatibles avec les clés de partition actives

Le prédicat de clé de partition est ajouté uniquement aux tables compatibles avec la clé de partition active. Une table est considérée comme compatible si le nombre de colonnes de sa clé de partition correspond à celui indiqué dans la variable rds_aurora.limitless_active_shard_key. Si la requête concerne des tables incompatibles avec la clé de partition active, le système génère une erreur au lieu de poursuivre la requête.

Exemples :

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