Particiones en PostgreSQL

Evaluación de PostgreSQL 8.3

Miguel Angel Cruz Condormiguelcrux@gmail.com

¿Particiones en PostgreSQL?

Particiones en PostgreSQL

● Qué tablas se deben particionar y cómo implementar la partición

● Constraints, Inheritance y como trabajan en conjunto

● Usar Trigger para las Particiones.

Particiones en PostgreSQL

● ¿Por qué hacer particiones?● Rendimiento● Gestión● Escalabilidad

Particiones en PostgreSQL

● ¿Por qué hacer particiones?● Rendimiento

– Crece el tamaño de la tabla, la consulta se vuelve lenta, igual la indexación.

– Las consultas necesitan pequeñas porciones del contenido de la tabla

● Gestión● Escalabilidad

Particiones en PostgreSQL

● ¿Por qué hacer particiones?● Rendimiento● Gestión

– Mejora añadir y remover tablas de manera sencilla :D– Comandos de mantenimiento (vacuum, reindex, cluster)

se dedican a los datos activos.● Escalabilidad

Particiones en PostgreSQL

● ¿Por qué hacer particiones?● Rendimiento● Gestión● Escalabilidad

– Controla la limitación de hardware (espacio en disco y velocidad)

– Mejorar el rendimiento y gestión en datos de gran magnitud.

Términos en Particiones.

● Rango – definido por un rango de datos, por lo general, timestamp● Entre 01/01/09 - 31/12/09

● Lista – lista de valores: estado, código, usuario, etc.● Pedidos pagados● Usuario● Códigos

Consideraciones

● ¿La tabla debería ser particionada?● Añade complejidad y costo extra en la gestión.● Crece número de filas, los tipos de consultas y la

data.

Creando particiones en tabla

● PostgreSQL 8.3 puede optimizar la consulta SELECT usando partición por Rango o Lista.

● Partir la tabla es práctico solo cuando el tamaño de la tabla excede la memoria física.

● Procedimientos almacenados con PL/Proxy hacen sencillo el llamado a otros procedimientos almacenados en otros servidores de bases de datos usando sharding y campos hashing.

Creando la partición de tabla

● Creando una tabla maestra para heredar (inherits)● Tabla “envio”

Creando la partición de tabla

● Crear tablas hija en servicio de la tabla maestra.

● Definir Table Constrains para filtrar los valores.● Definir un Index para las columnas de cada

partición.● Asegurar la configuración de postgreSQL para

atender Constrains.

Creando la partición de tabla

● Crear un Trigger o regla de redirección para INSERT en la tabla maestra. No se implementa Trigger para UPDATE (Opcional)

● Activar lenguaje plpgsql

Creando la partición de tabla

● Trigger selecciona INSERT entre● 2008-01-01 hasta 2009-01-01● Menor a 2008-01-01

Trigger

CREATE TRIGGER envio_insertar_trigger

BEFORE INSERT ON envio

FOR EACH ROW EXECUTE PROCEDURE envio_insertar();

INSERT en tabla “envio”

Verificar optimización de consulta

Plan de ejecución

Herencia de tabla

● A considerar:● Childs heredan:

– NOT NULL Constrains (primary key constrains)– Table constrains– Valores predeterminados de Columna

● Childs NO heredan– Indexes– Foreign key constrains– Permissions– Ownership

Contener tablas en archivos

Tablespaces

● Sirve para especificar la localización de tablas e índice en el sistema de archivos.

● Por ejemplo:● Cargar índices en un disco rápido● Guardar histórico en un disco lento

Preparar sistema de archivos

mkdir /mnt/dbspace2/

mount -t tmpfs -o size=100M,noatime tmpfs/mnt/dbspace2/

mkdir /mnt/dbspace2/postgresql

mkdir /mnt/dbspace2/postgresql/data

chown postgres:postgres /mnt/dbspace2/postgresql/data

chmod 0700 /mnt/dbspace2/postgresql/data

Modo alternativo: RULE

RULE

● Usar RULE en lugar de herencia y triggers● Indicar dónde se debe insertar● Mayor carga de trabajo● SELECT necesita UNION

CREATE RULE measurement_insert_y2006m02 ASON INSERT TO measurement WHERE ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )DO INSTEAD INSERT INTO measurement_y2006m02 VALUES (NEW.*);...CREATE RULE measurement_insert_y2008m01 ASON INSERT TO measurement WHERE ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )DO INSTEAD INSERT INTO measurement_y2008m01 VALUES (NEW.*)

RULE

● SELECT necesita UNION

CREATE VIEW measurement AS SELECT * FROM measurement_y2006m02UNION ALL SELECT * FROM measurement_y2006m03...UNION ALL SELECT * FROM measurement_y2007m11UNION ALL SELECT * FROM measurement_y2007m12UNION ALL SELECT * FROM measurement_y2008m01

RULE

● A mayor cantidad de particiones, más reglas.● Reglas requieren recarga de trabajo.● Las reglas pueden ocasionar efectos

secundarios● Adding/Removing/Modifying Rules requieren

bloqueo exclusico● Mayor cantidad de reglas, el rendimiento se

reduce.

Usar Triggers

● Mejor control de la base de datos.● Puede alcanzar un rendimiento constante,

independientemente de número de particiones.● Trigger no exige bloqueo.

Mantenimiento de particiones

● Lista● Centrado en hash y

estado● Define un conjunto de

particiones● Crecen los datos en

cada child● Patron de consulta

estático● Bajo matenimiento

● Rango● Centrado en fechas,

series● Creciente conjunto de

particiones● Datos de child son

estáticos● Patrones de consulta

volátiles.● Mucho mantenimiento

Rendimiento

PLProxy y PostgreSQL Cluster

● Otra técnica de particionamiento es colocar las bases de datos en múltiples servidores

● Ejemplo:● Dividir tabla de usuario.

● Plproxy conecta varias servidores de bases de datos para resolver llamados remotos

localdb=#CREATE FUNCTION get_user_email(username text) RETURNS text AS $$ CONNECT 'dbname=remotedb host=123.456.123.21 user=myuser';$$ LANGUAGE plproxy;

Cluster

Gracias

:-D