Administración de las particiones de PostgreSQL con la extensión pg_partman

Las particiones de tablas de PostgreSQL proporcionan un marco para el manejo de alto rendimiento de la entrada de datos y la generación de informes. Utilice particiones para bases de datos que requieren una entrada muy rápida de grandes cantidades de datos. Las particiones también proporcionan consultas más rápidas de tablas grandes. Las particiones ayudan a mantener los datos sin afectar la instancia de base de datos porque requiere menos recursos de E/S.

Mediante el uso de particiones, puede dividir los datos en fragmentos de tamaño personalizado para su procesamiento. Por ejemplo, puede dividir datos de series temporales para rangos como por hora, por día, por semana, por mes, por trimestre, por año, personalizados o cualquier combinación de estos. Para un ejemplo de datos de series temporales, si divide la tabla por hora, cada partición contiene una hora de datos. Si divide la tabla de series temporales por día, las particiones contienen datos de un día, y así sucesivamente. La clave de partición controla el tamaño de una partición.

Cuando se utiliza un comando INSERT o UPDATE de SQL en una tabla particionada, el motor de base de datos enruta los datos a la partición adecuada. Las particiones de tablas de PostgreSQL que almacenan los datos son tablas secundarias de la tabla principal.

Durante las lecturas de consultas de la base de datos, el optimizador de PostgreSQL analiza la cláusula WHERE de la consulta y, si es posible, dirige el análisis de la base de datos solo a las particiones relevantes.

A partir de la versión 10, PostgreSQL utiliza particiones declarativas para implementar particiones de tablas. Esto también se conoce como particionado PostgreSQL nativo. Antes de PostgreSQL versión 10, usaba desencadenadores para implementar particiones.

Las particiones de tablas de PostgreSQL proporcionan las siguientes características:

• Creación de nuevas particiones en cualquier momento.

• Rangos de particiones variables.

• Particiones desmontables y reconectables mediante instrucciones de lenguaje de definición de datos (DDL).

  Por ejemplo, las particiones desmontables son útiles para eliminar datos históricos de la partición principal, pero mantienen los datos históricos para su análisis.

• Las nuevas particiones heredan las propiedades de la tabla de base de datos principal, incluidas las siguientes:

  • Índices

  • Claves principales, que deben incluir la columna de la clave de partición

  • Claves externas

  • Restricciones de comprobación

  • Referencias

• creación de índices para la tabla completa o cada partición específica

No se puede modificar el esquema de una partición individual. Sin embargo, se puede modificar la tabla principal (como agregar una nueva columna), que se propaga a las particiones.

Información general de la extensión pg_partman de PostgreSQL

Puede utilizar la extensión pg_partman de PostgreSQL para automatizar la creación y el mantenimiento de las particiones de tablas. Para obtener más información general, consulte PG Partition Manager en la documentación de pg_partman.

nota

La extensión pg_partman es compatible con las versiones 12.6 y posteriores de Aurora PostgreSQL.

En lugar de tener que crear manualmente cada partición, configure pg_partman con las siguientes opciones:

• Tabla que se dividirá

• Tipo de partición

• Clave de partición

• Grado de detalle de la partición

• Opciones de precreación y administración de particiones

Después de crear una tabla con particiones de PostgreSQL, la registra con pg_partman al llamar a la función create_parent. Al hacerlo, se crean las particiones necesarias en función de los parámetros que pase a la función.

La extensión pg_partman también proporciona la función run_maintenance_proc, que puede ejecutarse de forma programada para administrar automáticamente las particiones. Para asegurarse de que se creen las particiones apropiadas según sea necesario, programe esta función para que se ejecute periódicamente (por ejemplo, por hora). También puede asegurarse de que las particiones se eliminen automáticamente.

Habilitación de la extensión pg_partman

Si tiene varias bases de datos dentro de la misma instancia de base de dato de PostgreSQL para la que desea administrar particiones, debe habilitar la extensión pg_partman por separado para cada base de datos. Para habilitar la extensión pg_partman para una base de datos específica, cree el esquema de mantenimiento de particiones y, después, cree la extensión pg_partman de la siguiente manera:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

nota

Para crear la extensión pg_partman, asegúrese de tener privilegios rds_superuser.

Si recibe un error como el siguiente, conceda los privilegios rds_superuser a la cuenta o utilice su cuenta de superusuario.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

Para conceder privilegios rds_superuser, conéctese con su cuenta de superusuario y ejecute el siguiente comando:

GRANT rds_superuser TO user-or-role;

Para los ejemplos que muestran el uso de la extensión pg_partman, utilizamos la siguiente tabla de base de datos y partición de muestra. Esta base de datos utiliza una tabla particionada basada en una marca temporal. Un esquema data_mart contiene una tabla denominada events con una columna denominada created_at. En la events tabla se incluyen los siguientes ajustes:

• Claves primarias event_id y created_at, que deben tener la columna utilizada para guiar la partición.

• Una restricción de comprobación ck_valid_operation para aplicar los valores para una columna de la tabla operation.

• Dos claves externas, donde una (fk_orga_membership) apunta a la tabla externa organization y la otra (fk_parent_event_id) es una clave externa con referencia propia.

• Dos índices, donde uno (idx_org_id) es para la clave externa y el otro (idx_event_type) es para el tipo de evento.

Las siguientes instrucciones DDL crean estos objetos, que se incluyen automáticamente en cada partición.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

Configuración de particiones mediante la función create_parent

Después de habilitar la extensión pg_partman, utilice la función create_parent para configurar las particiones dentro del esquema de mantenimiento de particiones. En este ejemplo se utiliza el ejemplo de la tabla events creado en Habilitación de la extensión pg_partman. Ejecute la función create_parent de la siguiente manera:

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

Los parámetros son los siguientes:

• p_parent_table – La tabla principal particionada. Esta tabla ya debe existir y estar totalmente cualificada, incluido el esquema.

• p_control – La columna en la que se basará la partición. El tipo de datos debe ser entero o basado en el tiempo.

• p_type: el tipo es 'native' o 'partman'. Normalmente, utiliza el tipo native para sus mejoras de rendimiento y flexibilidad. El tipo partman se basa en la herencia.

• p_interval – El intervalo de tiempo o intervalo de enteros para cada partición. Los valores de ejemplo incluyen daily, por hora, etc.

• p_premake – La cantidad de particiones que se debe crear de antemano para admitir nuevas inserciones.

Para obtener una descripción completa de la función create_parent, consulte Funciones de creación en la documentación de pg_partman.

Configuración del mantenimiento de particiones mediante la función run_maintenance_proc

Puede ejecutar operaciones de mantenimiento de particiones para crear automáticamente nuevas particiones, desasociar particiones o eliminar particiones antiguas. El mantenimiento de particiones se basa en la función run_maintenance_proc de la extensión pg_partman y la extensión pg_cron, que inicia un programador interno. El programador pg_cron ejecuta automáticamente instrucciones SQL, funciones y procedimientos definidos en las bases de datos.

En el ejemplo siguiente se utiliza el ejemplo de la tabla events creado en Habilitación de la extensión pg_partman para establecer que las operaciones de mantenimiento de particiones se ejecuten automáticamente. Como requisito previo, agregue pg_cron al parámetro shared_preload_libraries en el grupo de parámetros de la instancia de base de datos.

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

A continuación, puede encontrar una explicación paso a paso del ejemplo anterior:

1. Modifique el grupo de parámetros asociado a la instancia de base de datos y agregue pg_cron al valor del parámetro shared_preload_libraries. Este cambio requiere un reinicio de la instancia de base de datos para que surta efecto. Para obtener más información, consulte Modificación de parámetros de un grupo de parámetros de base de datos.

2. Ejecute el comando CREATE EXTENSION pg_cron; con una cuenta que tenga los permisos rds_superuser. Esto habilita la extensión pg_cron. Para obtener más información, consulte Programación de mantenimiento con la extensión pg_cron de PostgreSQL.

3. Ejecute el comando UPDATE partman.part_config para ajustar la configuración de pg_partman para la tabla data_mart.events.

4. Ejecute el comando SET . . . para configurar la tabla data_mart.events, con estas cláusulas:

   1. infinite_time_partitions = true, – Configura la tabla para que pueda crear automáticamente nuevas particiones sin ningún límite.

   2. retention = '3 months', – Configura la tabla para que tenga una retención máxima de tres meses.

   3. retention_keep_table=true – Configura la tabla para que cuando venza el periodo de retención, la tabla no se elimine automáticamente. En su lugar, las particiones que son anteriores al periodo de retención solo se separan de la tabla principal.

5. Ejecute el comando SELECT cron.schedule . . . para hacer una llamada a la función pg_cron. Esta llamada define la frecuencia con la que el programador ejecuta el procedimiento de mantenimiento de pg_partman, partman.run_maintenance_proc. Para este ejemplo, el procedimiento se ejecuta cada hora.

Para obtener una descripción completa de la función run_maintenance_proc, consulte Funciones de mantenimiento en la documentación de pg_partman.