Postgresql y el particionamiento de tablas (Partitioning Tables)

A veces cuando estamos manejando tablas con un numero grande de registros, varios millones o mas es necesario particionar las tablas para obtener un mejor desempeño a la hora de consultar dichas tablas. A este proceso se le suele denominar "Partitioning" y es le metodo usado para descomponer una enorme tabla (father) en un cojunto de tablas hijas (child).

Algunas de las enormes ventajas son:

• Desempeño mejorado para la obtencion de registros (Querys).


• Desempeño mejorado para la actualización (update).


• Indices mas pequeños para cada tabla hija contra indices grandes y dificiles de colocar en memoria en una tabla grande.


• Eliminación rapida de registros. Empleando DROP TABLE en vez de DELETE.


• Los datos pueden ser migrados o respaldados en medios economicos y pequeños como DVDs, discos duros extraibles, etc.

En postgresql el tipo de partitioning soportado se denomina particionado mediante herencia de tablas. Cada partición puede ser creada como una tabla hija de una unica tabla padre. La tabla padre normalmente debe de ser una tabla vacia, que representara a todo el conjunto de datos.

Los dos tipos de particionado mas comunes en postgresql son:

1. Particionar por rangos. La tabla es particionada mediante rangos definidos en base a la columna de llave primary o cualquier columna que no se solape entre los rangos de valores asignados a diferentes tablas hijas.



2. Particionar por lista. La tabla es particionada listando los valores de cada una de las llaves en cada particion.

En postgresql para poder utilizar dicha técnica es necesario seguir algunos pasos:

1. Crear la tabla "maestra", de la cual todas las tablas hijas heredaran. Esta tabla no contendra datos, no debe de tener ningun tipo de restricción (check), no debe de tener indice ni nada por el estilo.


2. Crear todas las tablas hijas heredando de la tabla maestra. Por lo regular estas tablas heredaran todos los campos de la tabla padre y no es necesario crearlos nosotros mismos.


3. Agregar a todas las tablas hijas las restricciones correspondientes sobre los datos que albergaran. Algunos ejemplos de esto serian: CHECK ( pais IN ('México', 'Argentina')), CHECK ( id_cliente BETWEEN 100 AND 200 ) ... CHECK ( id_cliente BETWEEN 5000 AND 5200), en estos dos ultimos ejemplos para evitar traslapes entre los ids de clientes.


4. Para cada particion, crear un indice para la columna(s) llave, tambien se pueden poner los indices que a cada quien le convengan. El indice para la llave primaria no es estrictamente necesario, pero en la mayoria de los casos ayuda mucho. Si tu necesitas una llave unica o llave primaria, necesitaras crearla para cada tabla hija.


5. Definir una regla (RULE) o trigger para redirigir las modificaciones de la tabla padre (master) a la apropiada particion.


6. Verificar que este habilitado a on el parametro constraint_exlusion (SET constraint_exclusion = on;) en el archivo de configuracion postgresql.conf para que los querys sean optimizados para el partitioning.

Pues bien, procedamos a realizar un ejemplo con esto.

Empecemos creando la tabla maestra para nuestro ejemplo.

El esquema de la tabla quedaria de la siguiente forma:

sicodelico=> \d produccion;
     Tabla «public.produccion»
     Columna      |  Tipo   | Modificadores
-------------------+---------+---------------
id                | integer | not null
no_serie          | integer |
id_modelo         | integer |
id_personal       | integer |
fecha_fabricacion | date    |
fecha_salida      | date    |

Una vez creada la tabla maestra procedemos a crear las tabla hijas que en nuestro ejemplo particionaremos en base al modelo del producto fabricado, que seran 4 tablas hijas en base a los 4 modelos almacenados en nuestra base de datos.

Un leve vistazo de la tabla de modelos nos arroja lo siguiente:

sicodelico=> SELECT * from modelos order by id;
id |     modelo      |    caracteristicas     |     descripcion      | imagen
----+-----------------+------------------------+----------------------+--------
 1 | ODIN            | 5 litros por minuto    | calentador de paso   |                           
 2 | DELTA 01        | 7 litros por minuto    | calentador de paso   |
 3 | DELTA 01-PLUS   | 9 litros por minuto    | calentador de paso   |
 4 | DELTA 02        | 13 LITROS POR MINUTO   | CALENTADOR DE PASO   |

(4 filas)

Ahora crearemos las tablas hijas heredando de la tabla "produccion", aqui es donde se crean las restricciones (CHECK) para los datos que albergaran cada tabla hija.


CREATE TABLE produccion_odin ( CHECK (id_modelo = 1) ) INHERITS (produccion);
CREATE TABLE produccion_delta01 ( CHECK (id_modelo = 2) ) INHERITS (produccion);
CREATE TABLE produccion_delta01plus ( CHECK (id_modelo = 3) ) INHERITS (produccion);
CREATE TABLE produccion_delta02 ( CHECK (id_modelo = 4) ) INHERITS (produccion);


Las restricciones CHECK tambien se pueden agregar despues mediante la sintaxis del ALTER TABLE.

Mediante las restricciones impuestas en las tablas aseguramos que no almacenen información que no les corresponde, por ejemplo que en la tabla produccion_odin que contiene registro de todos los calentadores del modelo ODIN no tengan calentadores de otros modelos, como DELTA02.

Ahora necesitamos crear los indices para cada tabla hija creada segun nos convenga para agilizar la busqueda de registros o garantizar la unicidad de los mismos, si necesitamos alguna llave primaria, aqui es donde podemos crearla.

Para el caso de las llaves primarias nos valemos de la sintaxis del ALTER TABLE como por ejemplo: ALTER TABLE produccion_odin ADD PRIMARY KEY (id);.

Para nuestro ejemplo nos conformamos con unos simples indices Btree. Quedarian de la siguiente manera:


CREATE INDEX produccion_odin_id ON produccion_odin (id);
CREATE INDEX produccion_delta01_id ON produccion_delta01 (id);
CREATE INDEX produccion_delta01plus_id ON produccion_delta01plus (id);
CREATE INDEX produccion_delta02_id ON produccion_delta02 (id);


Bien, ahora procederemos a crear el juego de reglas para garantizar el llenado en cascada de los datos y que cuando estos caigan en la tabla maestra sean redirigidos a sus respectivas tablas.


-- Regla para produccion_odin
CREATE RULE produccion_odin_insert_rule AS
ON INSERT TO produccion WHERE
( id_modelo = 1 )
DO INSTEAD
INSERT INTO produccion_odin VALUES ( NEW.id,
NEW.no_serie,
NEW.id_modelo,
NEW.id_personal,
NEW.fecha_fabricacion,
NEW.fecha_salida);

-- Regla para produccion_delta01
CREATE RULE produccion_delta01_insert_rule AS
ON INSERT TO produccion WHERE
( id_modelo = 2 )
DO INSTEAD
INSERT INTO produccion_delta01 VALUES ( NEW.id,
NEW.no_serie,
NEW.id_modelo,
NEW.id_personal,
NEW.fecha_fabricacion,
NEW.fecha_salida);

-- Regla para produccion_delta01plus
CREATE RULE produccion_delta01plus_insert_rule AS
ON INSERT TO produccion WHERE
( id_modelo = 3 )
DO INSTEAD
INSERT INTO produccion_delta01plus VALUES ( NEW.id,
NEW.no_serie,
NEW.id_modelo,
NEW.id_personal,
NEW.fecha_fabricacion,
NEW.fecha_salida);

-- Regla para produccion_delta02
CREATE RULE produccion_delta02_insert_rule AS
ON INSERT TO produccion WHERE
( id_modelo = 4 )
DO INSTEAD
INSERT INTO produccion_delta02 VALUES ( NEW.id,
NEW.no_serie,
NEW.id_modelo,
NEW.id_personal,
NEW.fecha_fabricacion,
NEW.fecha_salida);



Ya con esto tenemos gestionada la metodologia para la inserción en multiples tablas, tambien es importante notar que se pueden usar triggers en vez de reglas para este fin.

Ahora procedemos a crear una vista que contendra todos los valores como si se tratase de una tabla enorme:


CREATE VIEW produccion_total AS
SELECT * FROM produccion_odin
UNION ALL SELECT * FROM produccion_delta01
UNION ALL SELECT * FROM produccion_delta01plus
UNION ALL SELECT * FROM produccion_delta02;


Con esto ya tenemos nuestro esquema de tablas particionado, solo nos falta habilitar el obtimizador de consultas para evitar la busqueda de algun dato en todas las particiones y eso se hace asi desde dentro de psql:

SET constraint_exclusion = on;

o modificando el postgresql.conf de la siguiente manera, anexando la siguiente linea:

constraint_exclusion = on

Pues ya con esto me despido y espero les sirva.