PostgreSQL: Particionamiento de Tablas

Este es el Post #12 del Topic: Guía Práctica de PostgreSQL

SUGERENCIA:
Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla.
Descargar Script: DBAdmision.sql

En este post explicaré un poco sobre las tablas particionadas en Postgresql.

¿y eso?…

¿Para qué sirve?

Reduce la cantidad de datos a recorrer en cada consulta SQL
Aumenta el rendimiento (Menos datos que recorrer => execución más rápida).

¿En que consiste?

Segmentación de la información mediante criterios.

¿Cúales son los Criterios?

Por definiciones exactas.
Por rangos.

¿Cuándo se debe aplicar?

Cuando existen tablas con gran volúmen de datos.
Para asignar permisos a un grupo de datos específico de una tabla.

¿Cuáles son las modalidades de aplicación?

Horizontal: Segmentar datos por filas
Vertical: Segmentar datos por columnas

¿Y las Ventajas?

Trabajamos con segmentos de datos más pequeños.
Obtenemos índices más pequeños.
Realizamos los backups más rápidos.

En conclusión, la partición de tablas es una forma de organizar los datos clasificandolas según criterios de agrupación, de manera que cada transacción realizada en una tabla padre se redirija automáticamente a un menor grupo de datos que están agrupados en las tablas hijas, el interés radica a la hora de realizar las consultas con un ahorro significativo en el tiempo de respuesta.

Según el esquema mostrado, si quisiéramos realizar una consulta en la tabla Transporte y ésta no estaría particionada, se tendría que barrer todos los registros al realizar la búsqueda, pero al tener particionada la tabla la consulta se reduciría a aplicarse únicamente en alguna de las particiones, por consecuencia, sobre un menor número de datos.

Postgresql maneja los particionamientos de tablas basándose en la teoria de herencias, por lo cual debemos crear tablas el número de veces según particiones queramos realizar.

CASO PRÁCTICO:

1) Realizar el Particionamiento de la tabla Postulante por IDPerAcad:

Para mantener organizado nuestras particiones crearemos un nuevo esquema y dentro de éste las particiones que vayamos creando.

–ELIMINAR TODOS LOS DATOS DE LA TABLA Persona.Postulante

TRUNCATE TABLE Persona.Postulante;

–CREAMOS UN ESQUEMA PARA LAS PARTICIONES:

CREATE SCHEMA Particion;

–CREAMOS LAS PARTICIONES (Una tabla por cada IDPerAcad existente)

CREATE TABLE Particion.Postulante_2005_1 (CHECK ( IDPerAcad='2005-1' ))
INHERITS (Persona.Postulante);
;;
CREATE TABLE Particion.Postulante_2005_2 (CHECK ( IDPerAcad='2005-2' ))
INHERITS (Persona.Postulante);
;;
CREATE TABLE Particion.Postulante_2006_1 (CHECK ( IDPerAcad='2006-1' ))
INHERITS (Persona.Postulante);
;;
CREATE TABLE Particion.Postulante_2006_2 (CHECK ( IDPerAcad='2006-2' ))
INHERITS (Persona.Postulante);
;;
CREATE TABLE Particion.Postulante_2007_1 (CHECK ( IDPerAcad='2007-1' ))
INHERITS (Persona.Postulante);
;;
CREATE TABLE Particion.Postulante_0000_0 ()
INHERITS (Persona.Postulante);
;;

–MÉTODOS DE PARTICIONAMIENTO (FUNCIÓNES Ó REGLAS)

–MÉTODO 1:

–CREACIÓN DE LA FUNCIÓN DE PARTICIONAMIENTO:

-- CREATE OR REPLACE FUNCTION Particion.Insert_PerAcad_Function()
-- RETURNS TRIGGER AS $$
-- BEGIN--     IF ( NEW.IDPerAcad ='2005-1') THEN
--           INSERT INTO Particion.Postulante_2005_1 VALUES (NEW.*);
--     ELSIF ( NEW.IDPerAcad ='2005-2') THEN
--           INSERT INTO Particion.Postulante_2005_2 VALUES (NEW.*);
--     ELSIF ( NEW.IDPerAcad ='2006-1' ) THEN
--           INSERT INTO Particion.Postulante_2006_1 VALUES (NEW.*);
--     ELSIF ( NEW.IDPerAcad ='2006-2' ) THEN
--           INSERT INTO Particion.Postulante_2006_2 VALUES (NEW.*);
--     ELSIF ( NEW.IDPerAcad ='2007-1' ) THEN
--           INSERT INTO Particion.Postulante_2007_1 VALUES (NEW.*);
--     ELSE
--           INSERT INTO Particion.Postulante_0000_0 VALUES (NEW.*);
--     END IF;
--     RETURN NULL;
-- END;
-- $$
-- LANGUAGE plpgsql;
-- ;;

— –CREACIÓN DEL TRIGGER DE PARTICIONAMIENTO: El siguiente trigger ejecutará la función anterior antes de cada inserción de datos a la tabla Persona.Postulante.

-- CREATE TRIGGER Insert_PerAcad_Trigger
--     BEFORE INSERT ON Persona.Postulante
--     FOR EACH ROW EXECUTE PROCEDURE Particion.Insert_PerAcad_Function()
;;

–MÉTODO 2:

–CREACIÓN DE REGLAS

CREATE OR REPLACE RULE Insert_PerAcad_Ruler_2005_1 AS
ON INSERT TO Persona.Postulante
WHERE (IDPerAcad='2005-1')
DO INSTEAD INSERT INTO Particion.Postulante_2005_1 VALUES(NEW.*);
;;
CREATE OR REPLACE RULE Insert_PerAcad_Ruler_2005_2 AS
ON INSERT TO Persona.Postulante
WHERE (IDPerAcad='2005-2')
DO INSTEAD INSERT INTO Particion.Postulante_2005_2 VALUES(NEW.*);
;;
CREATE OR REPLACE RULE Insert_PerAcad_Ruler_2006_1 AS
ON INSERT TO Persona.Postulante
WHERE (IDPerAcad='2006-1')
DO INSTEAD INSERT INTO Particion.Postulante_2006_1 VALUES(NEW.*);
;;
CREATE OR REPLACE RULE Insert_PerAcad_Ruler_2006_2 AS
ON INSERT TO Persona.Postulante
WHERE (IDPerAcad='2006-2')
DO INSTEAD INSERT INTO Particion.Postulante_2006_2 VALUES(NEW.*);
;;
CREATE OR REPLACE RULE Insert_PerAcad_Ruler_2007_1 AS
ON INSERT TO Persona.Postulante
WHERE (IDPerAcad='2007-1')
DO INSTEAD INSERT INTO Particion.Postulante_2007_1 VALUES(NEW.*);
;;
CREATE OR REPLACE RULE Insert_PerAcad_Ruler_0000_0
AS ON INSERT TO Persona.Postulante
WHERE (IDPerAcad NOT IN ('2005-1','2005-2','2006-1','2006-2','2007-1'))
DO INSTEAD INSERT INTO Particion.Postulante_0000_0 VALUES(NEW.*);

–COMPROBANDO PARTICIONES:

Ejecutar Script: Insertar Postulantes.sql

–COMPROBANDO HERENCIA:

–Insertando
–Insertamos un contacto nuevo:

INSERT INTO Persona.contacto(
    Nombres
    ,Paterno
    ,Materno
    ,Genero
    ,DNI
    ,FechaNac
    ,FechaCreacion)
VALUES (
    'Anonymous'
    ,'Anonymous'
    ,'Anonymous'
    ,'0'
    ,'12345678'
    ,'Nov 14 1987 12:00AM'
    ,'Dec 18 2005  5:05PM');
;;

–Insertamos postulante a partir del contacto nuevo:

INSERT INTO Persona.Postulante(
    IDPostulante
    ,IDContacto
    ,IDCarrera
    ,IDPerAcad
    ,IDModalidad
    ,Puntaje
    ,AsistioExamen
    ,Ingreso)
VALUES ('0000000000',176,'310','2009-2',5,75,'1','1');
SELECT * FROM Persona.Postulante WHERE IDContacto=176;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=176;

–Actualizando

SELECT * FROM Persona.Postulante WHERE IDContacto=176;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=176;
UPDATE Persona.Postulante SET IDContacto=666 WHERE IDContacto=176;
SELECT * FROM Persona.Postulante WHERE IDContacto=666;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=666;

–Eliminando

SELECT * FROM Persona.Postulante WHERE IDContacto=666;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=666;
DELETE FROM Persona.Postulante WHERE IDContacto=666;
SELECT * FROM Persona.Postulante WHERE IDContacto=666;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=666;

–Deshabilitando Herencia

ALTER TABLE Particion.Postulante_2005_1 NO INHERIT Persona.Postulante;
SELECT * FROM Persona.Postulante WHERE IDContacto=1; --Deshabilitado
SELECT * FROM Particion.Postulante_2005_1 WHERE IDContacto=1; --Habilitado

–Habilitando Herencia

ALTER TABLE Particion.Postulante_2005_1 INHERIT Persona.Postulante;
SELECT * FROM Persona.Postulante WHERE IDContacto=1; --Habilitado
SELECT * FROM Particion.Postulante_2005_1 WHERE IDContacto=1; --Habilitado

–COMPROBANDO PERFORMANCE
–Modo particionado habilitado

SET constraint_exclusion=on;
EXPLAIN SELECT * FROM Persona.Postulante WHERE IDPeracad='2007-2';
--> ""Result  (cost=0.00..33.75 rows=6 width=116)"

–Modo particionado deshabilitado

SET constraint_exclusion=off;
EXPLAIN SELECT * FROM Persona.Postulante WHERE IDPeracad='2007-2';
--> "Result  (cost=0.00..118.12 rows=21 width=116)"

–Cosulta directa en la partición

EXPLAIN SELECT * FROM Particion.Postulante_0000_0 WHERE IDPeracad='2007-2';
--> "Result (cost=0.00..16.88 rows=3 width=116)"

–LIMITACIONES
–1)NO FUNCIONA EL UPDATE EN CAMPOS CON RESTRICCIONES:
–Cuando queremos actualizar los idperacad=’2005-1′ a ‘2009-1’ se produce un error de violación de restricciones:

<<ERROR: new row for relation «postulante_2005_1» violates check constraint «postulante_2005_1_idperacad_check»>>

–Esto sucede porque el idperacad ‘2005-1’ intenta actualizarse a ‘2009-1’ sobre la misma partición postulante_2005_1, y obviamente esta partición tiene restringido cualquier otro idperacad que no sea ‘2005-1’.

UPDATE Persona.Postulante SET IDPeracad='2009-2' WHERE IDPeracad='2005-1';
UPDATE Particion.Postulante_2005_1 SET IDPeracad='2009-2';

–El error se produce por las últimas restricciones asignadas al momento de crear cada partición:

<<CREATE TABLE Particion.Postulante_2005_1

(CHECK ( IDPerAcad=’2005-1′ )) INHERITS (Persona.Postulante);>>

–Por esa razón actualizamos la tabla quitándole esa restricción:

ALTER TABLE Particion.Postulante_2005_1
DROP CONSTRAINT postulante_2005_1_idperacad_check;
;;
ALTER TABLE Particion.Postulante_2005_2
DROP CONSTRAINT postulante_2005_2_idperacad_check;
;;
ALTER TABLE Particion.Postulante_2006_1
DROP CONSTRAINT postulante_2006_1_idperacad_check;
;;
ALTER TABLE Particion.Postulante_2006_2
DROP CONSTRAINT postulante_2006_2_idperacad_check;
;;
ALTER TABLE Particion.Postulante_2007_1
DROP CONSTRAINT postulante_2007_1_idperacad_check;
;;

–Quitarle las restricciones a la tablas hijas hizo que las distribuciones de idperacads no sean correctas
–Esto permitirá que en en una tabla hija existan ipderacads que no corresponden.
–Volvemos a intentar actualizar:

SELECT * FROM Particion.Postulante_2005_1;
UPDATE Persona.Postulante SET IDPeracad='2009-2' WHERE IDPeracad='2005-1';
SELECT * FROM Particion.Postulante_2005_1; --No debería pasar esto.
UPDATE Particion.Postulante_2005_1 SET IDPeracad='2005-1'; --Restablecemos idperacad
SELECT * FROM Particion.Postulante_2005_1;

–Posible solución:

SELECT * FROM Particion.Postulante_2005_1;
UPDATE Persona.Postulante SET IDPeracad='2009-2' WHERE IDPeracad='2005-1';
SELECT * FROM Particion.Postulante_2005_1;
INSERT INTO Particion.Postulante_0000_0
(SELECT * FROM Persona.Postulante WHERE IDPeracad='2009-2');
SELECT * FROM Particion.Postulante_0000_0;
DELETE FROM Particion.Postulante_2005_1 WHERE IDPeracad='2009-2';
SELECT * FROM Particion.Postulante_2005_1;

–2)DELETE/UPDATE/INSERT PASAN POR ALTO DESHABILITACIÓN DE HERENCIA:

ALTER TABLE Particion.Postulante_2006_2 NO INHERIT Persona.Postulante;
SELECT * FROM Persona.Postulante WHERE IDContacto=100; --Deshabilitado
SELECT * FROM Particion.Postulante_2006_2 WHERE IDContacto=100; --Habilitado
UPDATE Particion.Postulante_2006_2 SET IDContacto=777 WHERE IDContacto=100;
SELECT * FROM Particion.Postulante_2006_2 WHERE IDContacto=777; --Habilitado
SELECT * FROM Persona.Postulante WHERE IDContacto=777; --Deshabilitado
ALTER TABLE Particion.Postulante_2006_2 INHERIT Persona.Postulante;
SELECT * FROM Persona.Postulante WHERE IDContacto=777; --Habilitado

–A pesar de haber estado deshabilitado actualizó el registro.
–Los mismo sucede con INSERT Y DELETE.

CREATE TABLE «2005-1» (CHECK ( IDPerAcad=’2005-1′ )) INHERITS (Persona.Postulante);
CREATE TABLE «2005-2» (CHECK ( IDPerAcad=’2005-2′ )) INHERITS (Persona.Postulante);
CREATE TABLE «2006-1» (CHECK ( IDPerAcad=’2006-1′ )) INHERITS (Persona.Postulante);
CREATE TABLE «2006-2» (CHECK ( IDPerAcad=’2006-2′ )) INHERITS (Persona.Postulante);
CREATE TABLE «2007-1» (CHECK ( IDPerAcad=’2007-1′ )) INHERITS (Persona.Postulante);

Relacionado

7 thoughts on “PostgreSQL: Particionamiento de Tablas”

binance us registrace dice:

abril 12, 2024 a las 4:45 pm

Thanks for sharing. I read many of your blog posts, cool, your blog is very good.

Responder
Cindy dice:

mayo 15, 2013 a las 10:44 pm

Gracias por la info, tengo una duda hice las particiones con rule’s , ahora si analizo lo sgte:
SELECT * FROM Persona.Postulante WHERE IDContacto=176;
SELECT * FROM Particion.Postulante_0000_0 WHERE IDContacto=176;

El performance es mejor para el hijo, al analizar el primer query se ve que hace un recorrido a todas las demas tablas buscando el dato. Que me sugieres, si lo que quiero es q de igual manera q se usó rule’s o trigger’s para el INSERT se haga para SELECT (considerando que un rule para un SELECT no acepta WHERE, por lo menos a mí no me permitía ).

agradeceria tu ayuda.

Responder
zurg88 dice:

junio 19, 2012 a las 8:37 pm

exelente representacion, pero quiciera saber que diferencias o ventajas existe en realizar el particionado por triggers y no por reglas y viceversa. Cual metodo seria el recomendado y por que?

Responder
1. BeAsTiEuX dice:
  
  junio 19, 2012 a las 9:40 pm
  
  Hola, en este caso verás que ambos hacen lo mismo, tanto reglas como triggers, existen otros casos en el que se pueden hacer cosas con uno que no se pueden con lo otro, para ello te recomiendo des una leída a esto: http://mmc.igeofcu.unam.mx/LuCAS/Postgresql-es/web/navegable/programmer/x1201.html
  
  Lo que es sabido es que los triggers se dispararán y afectarán fila por fila, a diferencia de una regla que afecta a toda una estructura, que es lo que me convendría en el caso de particiones, y debería ser más rápido, para comprobarlo podríamos usar el ANALIZE de postgres y comparar cuanto es el costo por cada operación a condiciones normales.
  
  Saludos.
  
  Responder
German Lopez B. dice:

abril 15, 2012 a las 11:06 am

Genial, con esto resolví un gran problema que tenia… muchas gracias

Responder
Pingback: PostgreSQL: Base de Datos « The::Beastieux
Pingback: PostgreSQL: Particionamiento de Tablas | DbRunas

Relacionado

7 thoughts on “PostgreSQL: Particionamiento de Tablas”

Deja un comentarioCancelar respuesta