Santiago Matiz
Abril 7 del 2022 – 1 Min de Lectura

Particionamiento de tablas postgresql

Muchas veces nuestras tablas en postgresql alcanzan un número de registros suficientemente grande creando un problema del desempeño en las consultas.

Por más que intentemos aumentar el desempeños con estrategias de indices ,no lo lograremos ya que el número de registros en inmenso.

Existe una técnica que consiste en dividir o distribuir los registros en varias tablas logrando un desempeño mayor, pero … si no se sabe hacer lo que se obtiene es frustración en el proceso.

Veamos en este articulo cómo se debe hacer :

Desde la versión 10 de Postgresql, se crea la opción de particionar las tablas de una manera más fácil, antes de esta versión se debía generar cada una de las tablas y a partir de trigger comenzar a gestionar la inversión de cada uno de los registros.

Comencemos con el siguiente ejemplo sencillo:

Tenemos varios usuario y estos estás distribuidos en diferentes países, sencillamente son tres países para nuestro ejemplo.

Antes de mostrar las instrucciones para la creación de tabla se hacer los análisis de las sentencias SQL para tomar la decisión de como vamos a particionar las tablas.

En el caso de tabla de usuario los querys hacen un filtro por país por lo que es ideal particionar por este campo.

veamos por que:

Creamos una tabla de usuario y le decimos que la particionamos por el país

Creación de las tablas

CREATE TABLE usuarios (
user_id int not null,
user_email varchar(50) not null,
logdate date not null,
country varchar(30) not null
) PARTITION BY LIST (country);

CREATE TABLE usuario_col PARTITION OF usuarios FOR VALUES IN (‘COL’);
CREATE TABLE usuario_per PARTITION OF usuarios FOR VALUES IN (‘PER’);
CREATE TABLE usuario_ecu PARTITION OF usuarios FOR VALUES IN (‘ECU’);

Insertemos los siguientes registros :

INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (1, ‘usario1@mail.com’, ‘2001-01-01’, ‘COL’);
INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (2, ‘usario2@mail.com’, ‘2002-01-01’, ‘COL’);
INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (3, ‘usario3@mail.com’, ‘2003-01-01’, ‘ECU’);
INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (4, ‘usario4@mail.com’, ‘2004-01-01’, ‘PER’);
INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (5, ‘usario5@mail.com’, ‘2005-01-01’, ‘ECU’);
INSERT INTO public.usuarios(user_id, user_email, logdate, country) VALUES (6, ‘usario6@mail.com’, ‘2006-01-01’, ‘COL’);

Análisis

Ejercicio 1

EXPLAIN SELECT * FROM usuarios

Obtenemos un costos de 45.75

Lo que nos muestra el análisis, es que debe recorrer cada una de las tablas particionadas…

Ejercicio 2

EXPLAIN SELECT * FROM usuarios WHERE country=’COL’

Obtenemos un costos de 14.38, lo cual es una mejora considerable

Ejercicio 3

Ahora veamos el siguiente query filtrando por un criterio no particionado ,

Explain select * from usuarios WHERE logdate=’2004-01-01′

Obtenemos un costo de 43.16, muy similar a tener un query sin filtros

Conclusión

Podemos concluir: particionamos de acuerdo a un análisis de las SENTENCIAS de SQL y por esta razón logramos incrementar un desempeño considerable en los querys, buscando por el criterio del particionamiento.

Si por lo contrario se hubiera particionado por fecha , y el uso de los querys necesita recorrer todas las tablas para encontrar los registros, el resultado es nefasto.