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.
Temas
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 Managerpg_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
ycreated_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 tablaoperation
. -
Dos claves externas, donde una (
fk_orga_membership)
apunta a la tabla externaorganization
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 tiponative
para sus mejoras de rendimiento y flexibilidad. El tipopartman
se basa en la herencia. -
p_interval
– El intervalo de tiempo o intervalo de enteros para cada partición. Los valores de ejemplo incluyendaily
, 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ónpg_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:
-
Modifique el grupo de parámetros asociado a la instancia de base de datos y agregue
pg_cron
al valor del parámetroshared_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. -
Ejecute el comando
CREATE EXTENSION pg_cron;
con una cuenta que tenga los permisosrds_superuser
. Esto habilita la extensiónpg_cron
. Para obtener más información, consulte Programación de mantenimiento con la extensión pg_cron de PostgreSQL. -
Ejecute el comando
UPDATE partman.part_config
para ajustar la configuración depg_partman
para la tabladata_mart.events
. -
Ejecute el comando
SET
. . . para configurar la tabladata_mart.events
, con estas cláusulas:-
infinite_time_partitions = true,
– Configura la tabla para que pueda crear automáticamente nuevas particiones sin ningún límite. -
retention = '3 months',
– Configura la tabla para que tenga una retención máxima de tres meses. -
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.
-
-
Ejecute el comando
SELECT cron.schedule
. . . para hacer una llamada a la funciónpg_cron
. Esta llamada define la frecuencia con la que el programador ejecuta el procedimiento de mantenimiento depg_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 mantenimientopg_partman
.