AnalyticDB:Definición de tablas particionadas

Cuándo utilizar el particionamiento

El particionamiento funciona mejor cuando se cumplen las tres condiciones siguientes:

• La tabla es grande. Las tablas con un gran número de filas son buenas candidatas para el particionamiento.

• El rendimiento de las consultas no es satisfactorio. Particione solo las tablas donde las consultas produzcan tiempos de respuesta más lentos de lo aceptable.

• Los predicados de consulta tienen patrones de acceso claros. Revise las cláusulas WHERE en sus consultas habituales. Si las mismas columnas aparecen repetidamente, esas columnas son candidatas naturales para ser clave de partición.

Tipos de particionamiento compatibles

Elección de una clave de partición

La clave de partición determina si la eliminación de particiones funciona eficazmente:

• Coincidencia con los predicados de consulta. Elija la columna o columnas que aparecen con mayor frecuencia en las cláusulas WHERE. La eliminación de particiones es efectiva solo cuando las condiciones de la consulta incluyen la clave de partición.

• Alineación con su estrategia de retención de datos. Si elimina regularmente datos antiguos en bloque, diseñe las particiones para que los datos a eliminar se encuentren en una sola partición. Eliminar una partición es mucho más rápido que ejecutar una sentencia DELETE.

• Restricción de clave primaria. Si la tabla tiene una clave primaria, la clave de partición debe ser una columna de dicha clave. Sin una clave primaria, cualquier columna es elegible.

Creación de una tabla particionada por rango

Especifique START, END y EVERY para generar automáticamente particiones dentro de un rango. De forma predeterminada, START es inclusivo y END es exclusivo.

Particionamiento por fecha:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

Particionamiento por entero con una partición predeterminada:

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
  DEFAULT PARTITION extra );

La DEFAULT PARTITION captura las filas que quedan fuera de todos los rangos definidos.

Creación de una tabla particionada por lista

La clave de partición puede ser cualquier columna con un tipo de dato que admita comparación de valores. Declare una descripción de valor para cada partición.

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

Creación de una tabla particionada multinivel

El siguiente ejemplo crea una tabla particionada en tres niveles:

• Nivel 1: particionada por rango según year

• Nivel 2 (subpartición): particionada por rango según month

• Nivel 3 (subpartición): particionada por lista según region

CREATE TABLE sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
    SUBPARTITION BY RANGE (month)
       SUBPARTITION TEMPLATE (
        START (1) END (13) EVERY (1),
        DEFAULT SUBPARTITION other_months )
           SUBPARTITION BY LIST (region)
             SUBPARTITION TEMPLATE (
               SUBPARTITION usa VALUES ('usa'),
               SUBPARTITION europe VALUES ('europe'),
               SUBPARTITION asia VALUES ('asia'),
               DEFAULT SUBPARTITION other_regions )
( START (2002) END (2012) EVERY (1),
  DEFAULT PARTITION outlying_years );

Adición de una partición

Utilice ALTER TABLE ... ADD PARTITION para agregar una partición a una tabla particionada existente.

Si la tabla se creó con una plantilla de subparticiones, la nueva partición se subparticiona automáticamente:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE;

Si no se utilizó ninguna plantilla de subparticiones, defina las subparticiones en línea al agregar la partición:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'),
        SUBPARTITION asia VALUES ('asia'),
        SUBPARTITION europe VALUES ('europe') );

Para subparticionar una partición existente:

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

No se pueden agregar particiones a una tabla que ya tiene una partición predeterminada. Para agregar particiones a este tipo de tabla, divida la partición predeterminada. Consulte Split a partition .

Especificación de un nombre de partición

A partir de AnalyticDB for PostgreSQL V6.3.10.9, utilice la cláusula WITH(tablename=<tablename>) para asignar nombres explícitos a las particiones al crear una tabla:

CREATE TABLE partition_with_name_list (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY LIST (a)
(
    PARTITION p1 VALUES (1)  WITH (tablename='partition_with_name_list_p1'),
    PARTITION p2 VALUES (2)  WITH (tablename='partition_with_name_list_p2'),
    PARTITION p3 VALUES (3)  WITH (tablename='partition_with_name_list_p3'),
    PARTITION p4 VALUES (4)  WITH (tablename='partition_with_name_list_p4')
);

Cambio de nombre de una partición

A partir de AnalyticDB for PostgreSQL V6.3.10.9, cambie el nombre de una partición con:

ALTER TABLE partition_with_name_list_p1 RENAME TO partition_with_name_list_p1r;

División de una partición

Utilice ALTER TABLE ... SPLIT PARTITION para dividir una partición en dos.

Límites:

• Si existen subparticiones, solo se puede dividir la subpartición del nivel más bajo.

• El valor de división en la cláusula AT se asigna a la segunda partición.

División de una partición existente:

El siguiente ejemplo divide una partición de enero de 2017 en la fecha del 16 de enero. Los datos del 1 al 15 de enero van a la primera partición; los datos del 16 al 31 de enero van a la segunda.

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

División de la partición predeterminada para agregar una nueva partición:

Cuando una tabla tiene una partición predeterminada, divídala para separar una partición con nombre. La partición predeterminada debe ser la segunda partición en la cláusula INTO.

ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2017-01-01') INCLUSIVE
END ('2017-02-01') EXCLUSIVE
INTO (PARTITION jan17, default partition);

Determinación de la granularidad de las particiones

La granularidad de las particiones implica un equilibrio entre la cantidad de datos en cada partición y el número total de particiones. Una granularidad más fina resulta en menos datos por partición, pero en un mayor número de particiones.

Mantenga el número de particiones en 200 o menos por tabla como regla general. Un gran número de particiones puede reducir el rendimiento de la base de datos; por ejemplo, el optimizador de consultas tarda más en generar planes de ejecución y las operaciones VACUUM tardan más en completarse.

Optimización de consultas en tablas particionadas

AnalyticDB for PostgreSQL admite la eliminación de particiones: cuando una consulta incluye condiciones sobre la clave de partición, el optimizador examina solo las particiones coincidentes en lugar de toda la tabla.

Compruebe que la eliminación esté activa:

EXPLAIN
  SELECT * FROM sales
  WHERE year = 2008
    AND month = 1
    AND day = 3
    AND region = 'usa';

Salida esperada (eliminación funcionando correctamente):

Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

En este plan, Partitions selected: 1 (out of 468) confirma que la eliminación está activa. Si observa un número alto de particiones seleccionadas a pesar de tener condiciones de filtro específicas, compruebe si la columna del filtro coincide con la clave de partición.

Consulta de definiciones de particiones

Consulte todas las particiones de una tabla desde la vista del sistema pg_partitions:

SELECT
  partitionboundary,
  partitiontablename,
  partitionname,
  partitionlevel,
  partitionrank
FROM pg_partitions
WHERE tablename='sales';

Operaciones relacionadas

Para obtener una lista completa de las operaciones de gestión de particiones —incluida la eliminación, el truncamiento y el intercambio de particiones— consulte Partitioning Large Tables.

Preguntas frecuentes

¿Cómo elijo la clave de partición?

Si la tabla tiene una clave primaria, la clave de partición debe ser una de las columnas de dicha clave. Sin una clave primaria, puede especificar cualquier columna como clave de partición. Para obtener los mejores resultados de eliminación, elija la columna que aparece con mayor frecuencia en las cláusulas WHERE.