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:

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.

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

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;

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:

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:

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:

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