Esempi di query a shard singolo Restrizioni per le query a shard singolo Join completamente qualificati (espliciti)Impostazione di una chiave di shard attiva

Query a shard singolo in Aurora PostgreSQL Limitless Database.

Una query a shard singolo è una query che può essere eseguita direttamente su uno shard mantenendo la semantica SQL ACID. Quando rileva una query di questo tipo sul router, il pianificatore di query procede a inviare l’intera query SQL allo shard corrispondente.

Questa ottimizzazione riduce il numero di andate e ritorni di rete dal router allo shard, migliorando le prestazioni. Attualmente questa ottimizzazione viene eseguita per INSERT, SELECT, UPDATE e per le query DELETE.

Argomenti

Esempi di query a shard singolo
Restrizioni per le query a shard singolo
Join completamente qualificati (espliciti)
Impostazione di una chiave di shard attiva

Esempi di query a shard singolo

Negli esempi seguenti, viene mostrata la tabella condivisa customers, con la chiave di shard customer_id, e la tabella di riferimento 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)

Restrizioni per le query a shard singolo

Le query a shard singolo prevedono le seguenti restrizioni:

Funzioni

Se una query a shard singolo contiene una funzione, la query è idonea per l’ottimizzazione a shard singolo solo se si verifica una delle seguenti condizioni:

La funzione è immutabile. Per ulteriori informazioni, consulta Volatilità della funzione.
La funzione è mutabile, ma è registrata nella visualizzazione rds_aurora.limitless_distributed_functions. Per ulteriori informazioni, consulta Distribuzione delle funzioni.

Visualizzazioni

Se una query contiene una o più visualizzazioni, l’ottimizzazione a shard singolo per la query è disabilitata se si verifica una delle seguenti condizioni:

Ogni visualizzazione possiede l’attributo security_barrier.
Gli oggetti utilizzati nella query richiedono più privilegi utente. Ad esempio, una query contiene due visualizzazioni che vengono eseguite da due utenti diversi.


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)

Istruzioni PREPARE ed EXECUTE

Aurora PostgreSQL Limitless Database supporta l’ottimizzazione a shard singolo per le istruzioni preparate SELECT, UPDATE e DELETE.

Tuttavia, se si utilizzano istruzioni preparate per PREPARE e EXECUTE con plan_cache_mode impostata su 'force_generic_plan', il pianificatore di query rifiuta l’ottimizzazione a shard singolo per quella query.

PL/pgSQL

Le query con variabili PL/pgSQL vengono eseguite come istruzioni preparate in modo implicito. Se una query contiene variabili PL/pgSQL, il pianificatore di query rifiuta l’ottimizzazione a shard singolo.

L’ottimizzazione è supportata nel blocco PL/pgSQL se l’istruzione non contiene alcuna variabile PL/pgSQL.

Join completamente qualificati (espliciti)

L’ottimizzazione a shard singolo si basa sull’eliminazione delle partizioni. Lo strumento di ottimizzazione per PostgreSQL elimina le partizioni in base a condizioni costanti. Se Aurora PostgreSQL Limitless Database rileva che tutte le partizioni e le tabelle rimanenti si trovano sullo stesso shard, contrassegna la query come idonea per l’ottimizzazione a shard singolo. Tutte le condizioni di filtro devono essere esplicite affinché l’eliminazione delle partizioni funzioni. Aurora PostgreSQL Limitless Database non è in grado di eliminare le partizioni senza uno o più predicati di join o predicati di filtro sulle chiavi di shard di ogni tabella sottoposta a sharding nell’istruzione.

Supponi di aver partizionato le tabelle customers, orders e order_details in base alla colonna customer_id. In questo schema, l’applicazione cerca di conservare tutti i dati di un cliente su un singolo shard.

Considera la query seguente:


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;

Questa query recupera tutti i dati di un cliente (c.customer_id = 1). I dati di questo cliente si trovano su uno shard singolo, ma Aurora PostgreSQL Limitless Database non considera questa query come query a shard singolo. Lo strumento di ottimizzazione per la query è il seguente:

Lo strumento di ottimizzazione può eliminare le partizioni per i customers e gli orders in base alla seguente condizione:
```
c.customer_id = 1
c.customer_id = o.customer_id
o.customer_id =  1 (transitive implicit condition)
```
Lo strumento di ottimizzazione non può eliminare alcuna partizione per i order_details, perché non esiste una condizione costante sulla tabella.
Lo strumento di ottimizzazione conclude di aver letto tutte le partizioni dai order_details. Pertanto, la query non può essere idonea per l’ottimizzazione a shard singolo.

Per rendere la query a shard singolo, aggiungiamo la seguente condizione di join esplicito:


o.customer_id = od.customer_id

La query modificata è simile alla seguente:


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;

Ora lo strumento di ottimizzazione può eliminare le partizioni per i order_details. La nuova query diventa una query a shard singolo e quindi idonea per l’ottimizzazione.

Impostazione di una chiave di shard attiva

Questa funzionalità consente di impostare una chiave di shard singola durante l’esecuzione di query sul database, facendo sì che tutte le query SELECT e DML vengano aggiunte con la chiave di shard come predicato costante. Questa funzionalità è utile se hai effettuato la migrazione ad Aurora PostgreSQL Limitless Database e hai denormalizzato lo schema aggiungendo chiavi di shard alle tabelle.

È possibile aggiungere automaticamente un predicato sulla chiave di shard alla logica SQL esistente, senza modificare la semantica delle query. L’aggiunta di un predicato sulla chiave di shard attiva viene eseguita solo per le tabelle compatibili.

La funzionalità chiave di shard attiva utilizza la variabile rds_aurora.limitless_active_shard_key, che ha la seguente sintassi:


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

Alcune considerazioni sulle chiavi di shard attive e sulle chiavi esterne:

Una tabella sottoposta a sharding può avere un vincolo di chiave esterna se le tabelle principale e secondaria sono co-localizzate e la chiave esterna è un superset della chiave di shard.
Una tabella sottoposta a sharding può avere un vincolo di chiave esterna rispetto a una tabella di riferimento.
Una tabella di riferimento può avere un vincolo di chiave esterna rispetto a un’altra tabella di riferimento.

Supponi di avere una tabella customers sottoposta a sharding sulla colonna 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;

Con un set di chiavi di shard attive, le query presentano le seguenti trasformazioni.

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;

Join

Quando si eseguono operazioni di join su tabelle con una chiave di shard attiva, il predicato della chiave di shard viene aggiunto automaticamente a tutte le tabelle coinvolte nel join. Questa aggiunta automatica del predicato sulla chiave di shard si verifica solo quando tutte le tabelle della query appartengono allo stesso gruppo di co-localizzazione. Se la query riguarda tabelle appartenenti a gruppi di co-localizzazione diversi, viene invece generato un errore.

Supponi di avere anche tabelle di orders e order_details co-localizzate con la tabella 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;

Recupera le ultime 10 fatture d’ordine per un cliente il cui 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;

Questa query viene trasformata come segue:


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;

Tabelle compatibili con la chiave di shard attiva

Il predicato della chiave di shard viene aggiunto solo alle tabelle compatibili con la chiave di shard attiva. Una tabella è considerata compatibile se ha lo stesso numero di colonne nella relativa chiave di shard, come specificato nella variabile rds_aurora.limitless_active_shard_key. Se la query riguarda tabelle incompatibili con la chiave di shard attiva, il sistema non procede con la query e genera un errore.

Per esempio:


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

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Esecuzione di query su Limitless Database

Query distribuite