Consultas de una sola partición en Base de datos ilimitada de Aurora PostgreSQL - Amazon Aurora
Ejemplos de consultas de una sola particiónRestricciones para las consultas de una sola particiónUniones totalmente cualificadas (explícitas)Definición de una clave de partición activa

Consultas de una sola partición en Base de datos ilimitada de Aurora PostgreSQL

Una consulta de una sola partición es una consulta que se puede ejecutar directamente en una partición y, al mismo tiempo, mantener la semántica ACID de SQL. Cuando el planificador de consultas del enrutador encuentra una consulta de este tipo, la detecta y envía toda la consulta SQL a la partición correspondiente.

Esta optimización reduce el número de recorridos de ida y vuelta a través de la red entre el enrutador y la partición, lo que mejora el rendimiento. Actualmente, esta optimización se realiza para las consultas INSERT, SELECT, UPDATE y DELETE.

Ejemplos de consultas de una sola partición

En los siguientes ejemplos, tenemos la tabla particionada customers, con la clave de partición customer_id y la tabla de referencia 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)

Restricciones para las consultas de una sola partición

Las consultas de una sola partición tienen las siguientes restricciones:

Funciones

Si una consulta de una sola partición contiene una función, la consulta solo es apta para la optimización de una sola partición si se cumple una de las siguientes condiciones:

Vistas

Si una consulta contiene una o más vistas, la optimización de una sola partición estará deshabilitada para la consulta si cumple una de las siguientes condiciones:

  • Cualquier vista tiene el atributo security_barrier.

  • Los objetos utilizados en la consulta requieren varios privilegios de usuario. Por ejemplo, una consulta contiene dos vistas y las vistas se ejecutan con dos usuarios 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)
Instrucciones PREPARE y EXECUTE

La base de datos ilimitada de Aurora PostgreSQL admite la optimización de una sola partición para las instrucciones SELECT, UPDATE y DELETE preparadas.

Sin embargo, si usa instrucciones preparadas para PREPARE y EXECUTE con plan_cache_mode establecido en 'force_generic_plan', el planificador de consultas rechaza la optimización de una sola partición para esa consulta.

PL/pgSQL

Las consultas con variables PL/pgSQL se ejecutan como instrucciones preparadas de forma implícita. Si una consulta contiene variables PL/pgSQL, el planificador de consultas rechaza la optimización de una sola partición.

El bloque PL/pgSQL admite la optimización si la instrucción no contiene ninguna variable PL/pgSQL.

Uniones totalmente cualificadas (explícitas)

La optimización de una sola partición se basa en la eliminación de particiones. El optimizador de PostgreSQL elimina las particiones en función de condiciones constantes. Si Base de datos ilimitada de Aurora PostgreSQL descubre que todas las particiones y tablas restantes se encuentran en la misma partición, marca la consulta apta para la optimización de una sola partición. Todas las condiciones del filtro deben ser explícitas para que la eliminación de particiones funcione. Base de datos ilimitada de Aurora PostgreSQL no puede eliminar las particiones sin uno o más predicados de unión o predicados de filtrado en las claves de partición de cada tabla particionada de la instrucción.

Supongamos que hemos dividido las tablas customers, orders y order_details en función de la columna customer_id. En este esquema, la aplicación intenta mantener todos los datos de un cliente en una única partición.

Analice la siguiente 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;

Esta consulta recupera todos los datos de un cliente (c.customer_id = 1). Los datos de este cliente se encuentran en una sola partición, pero Base de datos ilimitada de Aurora PostgreSQL no cualifica esta consulta como consulta de una sola partición. El proceso del optimizador de la consulta es el siguiente:

  1. El optimizador puede eliminar las particiones de customers y orders de la siguiente condición:

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

  2. El optimizador no puede eliminar ninguna partición de order_details porque no hay ninguna condición constante en la tabla.

  3. El optimizador concluye que ha leído todas las particiones de order_details. Por lo tanto, la consulta no puede cualificarse para la optimización de una sola partición.

Para convertirla en una consulta de una sola partición, agregamos la siguiente condición de unión explícita:

o.customer_id = od.customer_id

El cambio de consulta tiene un aspecto similar al siguiente:

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;

Ahora el optimizador puede eliminar las particiones de order_details. La nueva consulta se convierte en una consulta de una sola partición y cumple los requisitos para la optimización.

Definición de una clave de partición activa

Esta característica le permite establecer una única clave de partición al consultar la base de datos, lo que hace que todas las consultas SELECT y DML se asocien a la clave de partición como predicado constante. Esta característica resulta útil si ha migrado a Base de datos ilimitada de Aurora PostgreSQL y ha desnormalizado el esquema al añadir claves de partición a las tablas.

Puede añadir un predicado de clave de partición de forma automática a la lógica SQL existente, sin cambiar la semántica de las consultas. La adición de un predicado de clave de partición activa solo se realiza en el caso de las tablas compatibles.

La característica de clave de partición activa utiliza la variable rds_aurora.limitless_active_shard_key, que tiene la siguiente sintaxis:

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

Algunas consideraciones sobre las claves de partición activas y las claves externas:

  • Una tabla particionada puede tener una restricción de clave externa si las tablas principal y secundaria están colocadas y la clave externa es un superconjunto de la clave de partición.

  • Una tabla particionada puede tener una restricción de clave externa a una tabla de referencia.

  • Una tabla particionada puede tener una restricción de clave externa a otra tabla de referencia.

Supongamos que tenemos una tabla customers particionada en la columna 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 conjunto de claves de partición activo, las consultas sufren las siguientes transformaciones.

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

Al realizar operaciones de unión en tablas con una clave de partición activa, el predicado de la clave de partición se añade automáticamente a todas las tablas implicadas en la unión. Esta adición automática del predicado de la clave de partición solo se produce cuando todas las tablas de la consulta pertenecen al mismo grupo de colocación. Si la consulta incluye tablas de diferentes grupos de colocación, se genera un error.

Supongamos que también tenemos las tablas orders y order_details y que están colocadas junto a la tabla 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 las últimas diez facturas de pedidos de un cliente cuyo ID de cliente sea 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;

Esta consulta se convertirá en lo siguiente:

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;
Tablas que admiten claves de partición activas

El predicado de la clave de partición se agrega solo a las tablas que son compatibles con la clave de partición activa. Una tabla se considera compatible si tiene el mismo número de columnas en su clave de partición que el especificado en la variable rds_aurora.limitless_active_shard_key. Si la consulta incluye tablas que son incompatibles con la clave de partición activa, el sistema genera un error en lugar de continuar con la consulta.

Por ejemplo:

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