Herencia de tablas en PostgreSQL

En ocasiones tenemos tablas muy grandes, con millones de tuplas y queremos optimizar el rendimiento de dicha tabla. Una forma de optimizar tablas muy grandes es realizar un particionado de éstas, es decir, subdividir una tabla padre en varias tablas pequeñas hijas y vaciar la tabla padre utilizando la cláusula ONLY. El procedimiento es muy sencillo y sistemático.

Lo vemos en un vídeo y lo explicamos en texto 😉

 

 

Herencia de tablas

1. Crear las tablas hijas.
2. Insertar en las tablas hijas los datos de la tabla padre, realizando una subconsulta según la condición que necesitemos. Por ejemplo, por fechas anuales.
3. Eliminar los datos de la tabla padre, puesto que debe de quedar completamente vacía.

 

Consideraciones previas

Hay que tener un especial cuidado cuando se eliminen los datos de la tabla padre, puesto que si realizamos un “delete” se eliminan todas las filas de la tabla padre y también los datos de las tablas hijas. Para prevenir que se eliminen los datos de las tablas hija, tenemos la cláusula ONLY. Con esta cláusula nos referimos sólo a la tabla padre.

 

Ejemplo

Para que se vea más claro como sería realizar un particionado de tablas muy grandes, a continuación te dejamos un ejemplo sin utilizar la cláusula ONLY y otro con dicha cláusula.

Partimos de una base de datos llamada “test” con una tabla “prueba” con 6000000 tuplas y que ocupa 207 MB.

 

Crear las tablas hijas

Lo primero es crear las 6 tablas hijas heredadas de la tabla padre “prueba” con una restricción para que chequee el intervalo de filas cuando se inserten los valores de la tabla padre en la tabla hija correspondiente. La sentencia SQL es algo similar a esta.

test=# CREATE TABLE prueba_1 (CHECK (col >0 AND col <1000001)) INHERITS (prueba);

 

Crear reglas adicionales

Para prevenir que no se realicen inserciones en la tabla padre y se redirijan a las tablas hijas correspondientes, se crean 6 reglas de forma similar a esta.

test=# CREATE RULE prueba_3_rule AS ON INSERT TO prueba WHERE (col >2000000 AND col <3000001) DO INSTEAD INSERT INTO prueba_3 VALUES (NEW.*);

 

Insertar valores

Procedemos a realizar una inserción en cada tabla hija, realizando una subconsulta de la tabla padre con el rango de datos correspondiente. La sentencia SQL quedaría así.

test=# INSERT INTO prueba_6 SELECT * FROM prueba WHERE (col >5000000 AND col <6000001);

 

Eliminar datos de la tabla padre sin usar la cláusula ONLY.

Sí no tenemos cuidado y eliminamos los datos de la tabla padre sin utilizar la cláusula ONLY, se eliminan tanto los datos de la tabla padre como de las hijas.

test=# DELETE FROM prueba;
DELETE 12000000

test=# SELECT count(*) FROM prueba;
count
——-
0
(1 row)

Eliminar datos de la tabla padre usando la cláusula ONLY.

Al utilizar la cláusula ONLY, nos estamos refiriendo a la tabla padre. Con esta cláusula sólo se eliminan los 6 millones de tuplas que tiene la tabla padre.

test=# DELETE FROM ONLY prueba;
DELETE 6000000

test=# SELECT count(*) FROM ONLY prueba;
count
——-
0
(1 row)

 

Podemos observar que al realizar una consulta sin usar dicha cláusula, el resultado es la suma de las filas de las tablas hijas.

test=# SELECT count(*) FROM prueba;
count
———
6000000
(1 row)

 

Conclusión

Se ha subdividido una tabla con 6 millones de tuplas en 6 tablas, cada una con un millón de filas, que a su vez es posible particionar cada una de estas tablas hijas en otras tablas de forma que contenga menos filas. Así con este método se gana mucho en rendimiento, puesto que a la hora de realizar una lectura secuencial, son muchas menos tuplas que leer.