¿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