0
Shares
0
0
0

Cuando hablamos de grandes volúmenes de datos la idea de “particionar” suele aparecer ya que es una de las características disponibles en casi cualquier motor de base de datos moderno.

En este post vamos a comparar las posibilidades de particionado que tenemos en PostgreSQL comparándolas con las disponibles en SQL Server.

Comenzaremos haciendo una revisión a las características de particionado de SQL Server a lo largo de su historia.

En versiones anteriores a SQL Server 2005 directamente no teníamos un particionado nativo. En estas versiones el particionado que podíamos aplicar era el llamado de “vistas particionadas”. Dichas vistas unían conjuntos disjuntos de datos de otras tablas mediante UNION ALL. Cada tabla contenía los datos de una “partición” así como una o más constraints que limitaban los datos de forma que cumplieran las restricciones de la partición (rango de fecha, grupo de valores, etc.).

Esta forma de particionar, aunque a ojos de algunos se puede considerar obsoleta, a día de hoy aún sigue siendo muy útil. Por ejemplo por su mayor flexibilidad, o por poder mantener particiones con distintas versiones de esquema de la tabla o también por la posibilidad de distribuir las tablas en distintas bases de datos/instancias (las llamadas vistas particionadas distribuidas).

Un ejemplo de este tipo de particionado sería el siguiente:

CREATE VIEW dbo.vw_part
WITH SCHEMABINDING AS
SELECT c1,c2,c3
FROM dbo.t1
UNION ALL
SELECT c1,c2,c3
FROM dbo.t2
UNION ALL
SELECT c1,c2,c3
FROM dbo.t3
 

Para que el optimizador pueda hacer un trabajo eficiente de “eliminación de particiones” es necesario que cada una de las tablas t1, t2 y t3 contengan restricciones de forma que los datos de dichas tablas correspondan con el particionado lógico que se quiere implementar. Si por ejemplo la tabla t1 debe contener los registros con valor c1=1, t2 los registros con c1=2, etc. deberíamos definir dicho constraints en cada tabla:

CREATE TABLE t1 (
c1 INT NOT NULL CONSTRAINT CK_C1 CHECK (c1 = 1),
c2 DATETIME NOT NULL,
c3 INT NULL
);
 
CREATE TABLE t2 (
c1 INT NOT NULL CONSTRAINT CK_C1 CHECK (c1 = 2),
c2 DATETIME NOT NULL,
c3 INT NULL
);
 
CREATE TABLE t3 (
c1 INT NOT NULL CONSTRAINT CK_C1 CHECK (c1 = 3),
c2 DATETIME NOT NULL,
c3 INT NULL
);
 

En SQL Server desde la versión SQL Server 2005 en adelante tenemos disponible el particionado “nativo”. En las versiones hasta SQL Server 2016 SP1 esta funcionalidad de particionado era además “Enterprise only” lo cual ha limitado bastante su utilización generalizada. Incluso en versiones modernas algunas características, como el escaneo paralelo intrapartición, se reserva únicamente para la versión Enterprise.

Respecto al número de particiones soportadas el límite inicialmente era de 1000 particiones que se extendió en SQL Server 2012 hasta un máximo de 15000. Sin embargo el uso de un muy alto número de particiones tiene un impacto potencialmente negativo en el rendimiento por lo que es recomendable que el número de particiones sea el menor posible pero que nos aporte ventajas notables respecto a una tabla no particionada.

El particionado en SQL Server comienza definiendo una función de particionado, la cual en base a ciertos valores definirá los límites (boundaries) de cara una de las particiones. Esta función se aplicará a un esquema de particionado que mapeará estas particiones con los filegroups que tengamos definidos en la base de datos. Habitualmente tenemos un filegroup por partición pero también podríamos tener un mismo filegroup para, por ejemplo, los datos del año, aunque la partición se diseñe a nivel de meses.

Una vez tenemos la función de particionado (que únicamente admite un único parámetro) y el esquema de particionado podemos aplicarlo a una tabla concreta. Idealmente el particionado se planteará durante la fase de diseño inicial del modelo pero si no es así podremos reconstruir nuestra tabla sobre un esquema de particionado posteriormente. Este es un ejemplo de la creación de una tabla particionada en SQL Server donde todas las particiones se mapean al filegroup primario:

CREATE PARTITION FUNCTION pf_fecha (DATE)
AS RANGE RIGHT FOR VALUES
(‘20190101’, ‘20200101’, ‘20210101’, ‘20220101’);
 
CREATE PARTITION SCHEME ps_fecha
AS PARTITION pf_fecha
ALL TO ([Primary]);
 
CREATE TABLE test (
startDate DATE,
c1 INT,
c2 INT
) ON ps_fecha(startDate);
 

En PostgreSQL las versiones anteriores a la 10 soportaban el llamado particionado por herencia. Realmente el mecanismo de herencia en PostgreSQL se planteó como una forma de representar la herencia de objetos típica de los lenguajes de programación orientados a objetos. Por ejemplo pensemos en tener una tabla personas cuyas tablas “hijas” sean por ejemplo alumnos, profesores y administrativos. Este sistema de herencia de tablas permite tanto un acceso a las tablas base hijas como a la tabla padre que representa el conjunto total de las hijas.

En este tipo de particionado la tabla padre suele estar vacía, pero nada nos impide en realidad introducir datos. También necesitaremos “enrutar” las operaciones de escritura mediante triggers para que vayan a la tabla hija manualmente. Por ejemplo podemos crear la siguiente tabla padre:

CREATE SEQUENCE pk_seq;
CREATE TABLE padre (
pk INTEGER NOT NULL DEFAULT nextval(‘pk_seq’),
data text NOT NULL,
estado DEFAULT 0
);
 

Y añadirle tres tablas hijas, con estados 0,1 y 3:

CREATE TABLE hijo_0 (CHECK(estado = 0 )) INHERITS (padre);
CREATE TABLE hijo_1 (CHECK(estado = 1 )) INHERITS (padre);
CREATE TABLE hijo_3 (CHECK(estado = 3 )) INHERITS (padre);
ALTER TABLE ONLY hijo_0 add constraint hijo_0_pk primary key (pk);
ALTER TABLE ONLY hijo_1 add constraint hijo_1_pk primary key (pk);
ALTER TABLE ONLY hijo_3 add constraint hijo_3_pk primary key (pk);
 

El enrutado de insert podría ser un trigger similar a este:

CREATE FUNCTION ins_hijo() RETURNS TRIGGER as
$$
BEGIN
IF NEW.estado = 0 THEN INSERT INTO hijo_0 values (NEW.*);
ELSIF NEW.estado = 1 THEN INSERT INTO hijo_1 values (NEW.*);
ELSIF NEW.estado = 3 THEN INSERT INTO hijo_3 values (NEW.*);
ELSE RAISE EXCEPTION ‘¡Error en el particionado!’;
END IF;
RETURN NULL; — Evitamos que la fila “fluya” a la tabla padre
END;
$$
language ‘plpgsql’;
 

Y algo parecido a este el de UPDATE:

CREATE FUNCTION upd_hijo() RETURNS TRIGGER as
$$
BEGIN
IF (NEW.estado != OLD.estado) — si cambia de partición hacemos delete+insert
DELETE FROM padre where pk = OLD.PK;
INSERT INTO padre values (NEW.*);
END IF;
RETURN NULL; — Evitamos que la fila “fluya” a la tabla padre
END;
$$
language ‘plpgsql’;
 
CREATE TRIGGER tr_ins BEFORE INSERT ON padre
FOR EACH ROW EXECUTE PROCEDURE ins_hijo();
CREATE TRIGGER tr_upd BEFORE UPDATE ON hijo_0
FOR EACH ROW EXECUTE PROCEDURE upd_hijo();
CREATE TRIGGER tr_upd BEFORE UPDATE ON hijo_1
FOR EACH ROW EXECUTE PROCEDURE upd_hijo();
CREATE TRIGGER tr_upd BEFORE UPDATE ON hijo_3
FOR EACH ROW EXECUTE PROCEDURE upd_hijo();
 

En cierta forma todo esto se lleva bastante mal con el concepto de particionado que Codd nos dejó en su regla 11 :

Rule 11: Distribution independence:

The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only.

Obviamente no todo son desventajas, ya que este tipo de soluciones también permiten realizar subparticiones, por ejemplo particionando en un primer nivel por fecha y luego por país o viceversa. Este tipo de subparticionados no son posibles con SQL Server con particionado nativo, tendríamos que emularlo por ejemplo combinando vistas particionadas sobre tablas nativamente particionadas.

En la versión PostgreSQL 10 y posteriores aparece el particionado declarativo, más similar al de SQL Server, aunque aún con ciertas diferencias. En este caso ya declaramos en la definición de la tabla el particionado y definimos si va a estar basado en rangos (RANGE), en listas (LIST) o en un hash (HASH):

CREATE TABLE big_table (
id bigint not null,
ts date not null,
v1 int,
v2 int,
v3 int
) PARTITION BY RANGE (ts);
 

Para añadir una partición realmente lo haremos creando una tabla indicándole que va a ser una partición de la tabla “padre”:

CREATE TABLE big_table_2006 PARTITION OF big_table
FOR VALUES FROM (‘2006-01-01’) TO (‘2007-01-01’);
 

En realidad lo que tenemos es una especie de wrapper sobre herencia de tablas para hacer su uso algo más sencillo. En este caso estamos usando un rango de fechas donde el valor inicial es siempre inclusive y el cierre del rango excluye el valor indicado.

En SQL Server únicamente tenemos soporte nativo para rangos, pero en el caso de PostgreSQL podemos también usar valores concretos (LIST):

CREATE TABLE padre (
pk INTEGER NOT NULL DEFAULT nextval(‘pk_seq’),
data text NOT NULL,
estado DEFAULT 0
) PARTITION BY LIST (estado);
 
CREATE TABLE hijo_0 PARTITION OF padre FOR VALUES IN (0);
CREATE TABLE hijo_1_2 PARTITION OF padre FOR VALUES IN (1,2);
CREATE TABLE hijo_3 PARTITION OF part_tags FOR VALUES IN (3);
 

O incluso una función hash, que podemos usar para aplicar una especie de round robin:

CREATE TABLE padre (
pk INTEGER NOT NULL DEFAULT nextval(‘pk_seq’),
data text NOT NULL,
estado DEFAULT 0
) PARTITION BY HASH (pk);
 
CREATE TABLE hijo_0 PARTITION OF padre FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE hijo_1 PARTITION OF padre FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE hijo_2 PARTITION OF padre FOR VALUES WITH (MODULUS 3, REMAINDER 2);
 

Respecto al mantenimiento de estas tablas particionadas tenemos que tener en cuenta que en muchos casos se tratarán igual que tablas individuales. Por ejemplo la indexación tendrá que mantenerse de forma manual en cada tabla.

Las operaciones de truncado de particiones se podrán realizar truncando la tabla correspondiente y añadir otra partición sería equivalente a lanzar un nuevo CREATE TABLE como los anteriores. El mapeo en filegroups que tenemos en SQL Server tendría su equivalente en el uso de un tablespace concreto en la creación de la “tabla partición” correspondiente.

Para eliminar una partición podremos bien borrar la tabla o bien utilizar un comando como este:

ALTER TABLE padre DETACH PARTITION hijo_2
 

La diferencia entre estos dos comandos es que mientras el borrado elimina la tabla, el detach únicamente la separa del particionado, la deja como tabla independiente pero sin borrar su contenido. Sería más parecido al SWITCH out de una partición en SQL Server hacia una tabla vacía.

Existen algunas limitaciones importantes del particionado y que debemos considerar antes de plantearnos su uso en PostgreSQL. Una de ellas es que si definimos una restricción unique, o una primary key, debemos incluir la columna de particionado en ella forzosamente.

En SQL Server tenemos también una restricción importante, que la columna de particionado debe estar incluida en el índice clúster pero tenemos la posibilidad de tener una PK o restricción UNIQUE “no alineada” con el esquema de particionado. En ese caso sería una restricción global que afecta a todas las particiones. Esto puede traer algunos problemas, especialmente si pretendemos realizar operaciones de switch de particiones.

Otra limitación en PostgreSQL que puede ser un “killer” para poder utilizar esta funcionalidad es que no podemos tener foreign keys apuntando a nuestra tabla particionada. Es puede no ser un problema en, por ejemplo, una tabla de hechos, que normalmente como mucho tendrá foreign keys “salientes” apuntando a otras dimensiones, pero no al contrario. Sí podría ser un problema importante a la hora de utilizar particionado por ejemplo en un esquema típico OLTP, donde no podríamos tener una FK de líneas de pedidos a las cabeceras de pedidos si optamos por particionar dicha tabla de cabeceras de pedidos. Tampoco es posible en PostgreSQL crear restricciones que afecten a toda la tabla, debemos fijar las restricciones en cada una de las tablas que forman el particionado.

Conclusión

Como hemos podido ver el uso de particionado no es ni tan transparente ni sencillo como podríamos pensar en un principio. El uso de particionado siempre debería ser algo que se evalúe teniendo muy presentes sus “contras” y no pensando únicamente en sus “pros”.

Sin embargo en las bases de datos de gran tamaño (VLDB) es prácticamente imprescindible considerar el uso de particionado para poder gestionar esos grandes volúmenes de información. Es crítico que pensemos no solamente en qué opción parece más “limpia” desde un punto de vista de arquitectura sino también en el impacto en mantenimiento de la solución, cómo de sencilla será de administrar, que problemas estamos añadiendo, etc.

En resumen, tenemos que intentar minimizar que malas decisiones respecto al particionado tomadas en tiempo de diseño del modelo acaben generando un problema importante en el futuro. Os aseguro que vuestro yo del futuro os lo agradecerá.

0 Shares:
Share 0
Tweet 0
Share 0

Soy experto en soluciones de Alta Disponibilidad para empresas basadas en el diseño y puesta a punto de SQL Server. Durante los últimos 15 años, he trabajado con tecnologías de datos de Microsoft en empresas punteras de todo el mundo. Actualmente, soy arquitecto de Microsoft SQL Server y aplicaciones .NET en Verne TECH y Solution Expert en Microsoft Private Cloud y Microsoft Data Platform. Además, como Microsoft Certified Trainer, he impartido multiples cursos oficiales de Microsoft y otros tantos sobre SQL Server.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

Automatización de procesos de paquetes SQL Server en SSIS usando C#

Next article —

Episodio 1: Hacking Azure, Office 365, Ad connect etc.

You May Also Like
AArtículos de Data
Leer más
  • 1 minute read

El tiempo es oro: Cómo predecir series temporales con datos de muchas dimensiones con R – SolidQ Summit 2017

Saber cuánto vamos a vender mañana o el año que viene es el sueño dorado de muchos analistas de negocio. Sin embargo, no nos conformamos con un número, sino que necesitamos predicciones ajustadas a todos los niveles, detalles y segmentaciones posibles, y aquí es donde la predicción puede volverse realmente difícil. Descubre las implementaciones reales afrontando estas predicciones sin importar el nivel de detalle que necesites y sube un peldaño en tus sistemas inteligentes.