Requêtes à partition unique dans la base de données Aurora PostgreSQL Limitless - Amazon Aurora
Exemples de requêtes à partition uniqueRestrictions applicables aux requêtes portant sur une seule partitionJointures 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 la base de données Aurora PostgreSQL Limitless

Une requête à partition unique est une requête qui peut être exécutée directement sur une partition tout en conservant la sémantique SQL ACID. Lorsqu'une telle requête est détectée par le planificateur de requêtes sur le routeur, le planificateur la détecte et envoie la requête SQL complète vers le shard correspondant.

Cette optimisation réduit le nombre d'allers-retours entre le routeur et le shard, améliorant ainsi les performances. Actuellement, cette optimisation est effectuée pour les DELETE requêtes INSERT SELECTUPDATE,, et.

Exemples de requêtes à partition unique

Dans les exemples suivants, nous avons le tableau fragmentécustomers, avec la clé de partitioncustomer_id, et le tableau 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 applicables aux requêtes portant sur une seule partition

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

Fonctions

Si une requête à partition unique contient une fonction, elle est éligible à l'optimisation à partition unique uniquement si l'une des conditions suivantes s'applique :

  • La fonction est immuable. Pour de plus amples informations, veuillez consulter Volatilité des fonctions.

  • La fonction est modifiable, mais elle est enregistrée dans la rds_aurora.limitless_distributed_functions vue. Pour de plus amples informations, veuillez consulter Répartition des fonctions.

Vues

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

  • N'importe quelle vue possède security_barrier cet attribut.

  • Les objets utilisés dans la requête nécessitent plusieurs privilèges d'utilisateur. Par exemple, une requête contient deux vues, et les vues 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

La base de données Aurora PostgreSQL Limitless prend en charge l'optimisation d'une partition unique pour les instructions préparées et. SELECT UPDATE DELETE

Toutefois, si vous utilisez des instructions préparées pour PREPARE et EXECUTE avec plan_cache_mode set to'force_generic_plan', le planificateur de requêtes rejette l'optimisation à partition unique pour cette requête.

PL/pgSQL

Pour les requêtes contenant PL/pgSQL variables are run as implicitly prepared statements. If a query contains any PL/pgSQL des variables, le planificateur de requêtes rejette l'optimisation à partition unique.

L'optimisation est prise en charge dans les PL/pgSQL block if the statement doesn't contain any PL/pgSQL variables.

Jointures entièrement qualifiées (explicites)

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

Supposons que nous ayons partitionné les order_details tablescustomers,orders, et en fonction de la customer_id colonne. 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 se trouvent sur une seule partition, mais la base de données Aurora PostgreSQL Limitless ne qualifie pas cette requête de requête mono-partition. 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 pourorder_details, car il n'y a aucune condition constante sur la table.

  3. L'optimiseur conclut qu'il a lu toutes les partitions depuisorder_details. Par conséquent, la requête ne peut pas être qualifiée pour l'optimisation d'une 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 ressemble à ceci :

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 pourorder_details. La nouvelle requête devient une requête à partition unique et remplit les conditions requises pour 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 SELECT requêtes DML en tant que prédicat constant. Cette fonctionnalité est utile si vous avez migré vers la base de données Aurora PostgreSQL Limitless 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 est effectué uniquement pour les tables compatibles.

La fonctionnalité clé de partition active utilise la rds_aurora.limitless_active_shard_key variable, 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 fragmentée peut être soumise à une contrainte de clé étrangère si les tables parent et enfant sont colocalisées et si la clé étrangère est un sur-ensemble de la clé de partition.

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

  • Une table de référence peut avoir une contrainte de clé étrangère par rapport à une autre table de référence.

Supposons que nous ayons un customers tableau fragmenté sur la customer_id colonne.

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;

Avec un ensemble de clés de partition actif, les requêtes présentent 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 avec une clé de partition active, le prédicat de clé de partition est automatiquement ajouté à toutes les tables impliquées dans 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. Si la requête implique des tables provenant de différents groupes de collocation, une erreur est générée à la place.

Supposons que nous ayons également orders des order_details tables colocalisées avec la customers table.

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 d'un client dont le numéro de client est 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 Active Shard Key

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 sa clé de partition contient le même nombre de colonnes que celui spécifié dans la rds_aurora.limitless_active_shard_key variable. 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.

Par exemple :

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