Particionado de Tablas en PostgreSQL

Estube estudiando como realizar un particionado de tabla en postgreSQL, la solución
oficial es bastante piola y fácil de realizar.

Vamos a suponer una tabla de ejemplo, personas a la que deseamos particionar.

CREATE TABLE personas(
id serial PRIMARY KEY,
nombre varchar(30) NOT NULL,
provincia varchar(30) NOT NULL);

Bien, ahora crearemos una tabla por cada partición que deseamos realizar a la tabla.
En este caso vamos a separar la tabla personas por el campo provincia, tomaremos las
personas que son de corrientes y las personas de chaco. Para esto al crear las nuevas
particiones heredamos de personas y ponemos la clausula check para definir el criterio.

CREATE TABLE personas_chaco(
CHECK ( provincia = 'chaco')
) INHERITS (personas);


CREATE TABLE personas_corrientes(
CHECK ( provincia = 'corrientes')
) INHERITS (personas);

Por ultimo debemos definir reglas en personas para que cuando entre un registro
con provincia = 'chaco' o provincia='corrientes', inserte los datos en la correspondiente partición y no directamente en el padre.

CREATE OR REPLACE RULE personas_chaco_part AS
ON INSERT TO personas WHERE (provincia='chaco') DO INSTEAD
INSERT INTO personas_chaco VALUES(NEW.id,NEW.nombre,NEW.provincia);

CREATE OR REPLACE RULE personas_corrientes_part AS
ON INSERT TO personas WHERE (provincia='corrientes') DO INSTEAD
INSERT INTO personas_corrientes VALUES(NEW.id,NEW.nombre,NEW.provincia);

Listo con esto ya tenemos la tabla particionada.

Pueden probar esto creando un script con los ejemplos anteriores y creando una base de ejemplo y cargar dicho script.

$createdb sampleDb
$psql -d sampleDb -f script_part.sql

Una vez en psql podemos ver lo que pasa cuando hacemos una consulta a
la tabla personas.

# EXPLAIN SELECT * FROM personas;
QUERY PLAN
--------------------------------------------------------------------------------------------
Result (cost=0.00..53.40 rows=2340 width=70)
-> Append (cost=0.00..53.40 rows=2340 width=70)
-> Seq Scan on personas (cost=0.00..17.80 rows=780 width=70)
-> Seq Scan on personas_chaco personas (cost=0.00..17.80 rows=780 width=70)
-> Seq Scan on personas_corrientes personas (cost=0.00..17.80 rows=780 width=70)
(5 rows)

Como pueden ver, se recorre la tabla padre y las hijas.
Bien, ahora vamos a discriminar por el campo provincia.

EXPLAIN SELECT * FROM personas WHERE provincia = 'chaco';
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..59.25 rows=12 width=70)
-> Append (cost=0.00..59.25 rows=12 width=70)
-> Seq Scan on personas (cost=0.00..19.75 rows=4 width=70)
Filter: ((provincia)::text = 'chaco'::text)
-> Seq Scan on personas_chaco personas (cost=0.00..19.75 rows=4 width=70)
Filter: ((provincia)::text = 'chaco'::text)
-> Seq Scan on personas_corrientes personas (cost=0.00..19.75 rows=4 width=70)
Filter: ((provincia)::text = 'chaco'::text)
(8 rows)

Como pueden ver volvemos a buscar en todas las particiones, no se debería buscar en personas_corrientes, dado que buscamos chaco. Esto sucede por que por defecto no se encuentra habilitada la opcion constraint_exclusion. Para habilitarla podemos cambiar postgresql.conf para hacer definitivo el cambio o para probar podemos hacer.

$SET constraint_exclusion=on;

La consulta sería ejecutada así

# EXPLAIN SELECT * FROM personas WHERE provincia = 'chaco';
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=0.00..39.50 rows=8 width=70)
-> Append (cost=0.00..39.50 rows=8 width=70)
-> Seq Scan on personas (cost=0.00..19.75 rows=4 width=70)
Filter: ((provincia)::text = 'chaco'::text)
-> Seq Scan on personas_chaco personas (cost=0.00..19.75 rows=4 width=70)
Filter: ((provincia)::text = 'chaco'::text)
(6 rows)

Como pueden ver nos evitamos realizar un seq scan en la partición de corrientes.

Las ventaja de hacer esto es que podemos logar una mejor performance en el motor dado que podemos partir nuestras tablas gigantescas por algun criterio conveniente dejando varias más
pequeñas, cosa muy deseable para el RDBMS.

La desventaja es que la administracion de los cambios sobre estas tablas no es tan sencilla como
administrar una sola tabla.