post icon

Particionamiento Horizontal de Tablas en PostgreSQL

Más de uno me mirará con una cara ¿De que hablas, Willis? preguntándose que es un particionamiento horizontal de tablas. Se le llama así cuando tenemos tablas que almacenan un conjunto demasiado grande de registros (en millones) y éste comienza a tener un bajo rendimiento, es entonces cuando se decide partir la tabla en tablas más pequeñas en cuanto a su almacenamiento de datos se refiere.

Para comprender completamente el concepto de Particionamiento Horizontal necesitaremos el conocimiento previo de herencia de tablas en éste motor, lo pueden leer aquí si lo desean.

Hace unos meses hablaba con un amigo y colega sobre sus tareas en una empresa de gran porte en donde tiene millones de filas creadas al día, el rubro es la telefonía celular y almacenan los mensajes de texto para luego trabajar con ellos, me mencionaban que iban partiendo sus tablas de manera manual separado en meses y el DBA debía crear todos los meses una tabla para almacenar los datos de ese mes especifico, por ejemplo los mensajes de enero se almacenaban en la tabla SMS_ENERO_2010. En sí no representaba un problema de rendimiento por indices muy pesados ya que estaban por así decirlos en meses, ni tampoco se sobrecargaban las tablas, el problema venia cuando tenían que crear los reportes para poder hacer SELECT’s de las tablas debían armar dinámicamente los Query’s y como sabemos los SQL dinámicos son asesinos de bases de datos, entonces se debería optar otra solución, un modo de partir la tabla pero que siga llamandose SMS siempre. La solución lo tienen postgre y veremos como lograrlo.

Algunas ventajas que obtenemos haciendo utilizando esta configuración son las siguientes:

  • Mayor velocidad de Lectura de los datos de la tabla
  • Mayor velocidad de Escritura en UPDATE
  • Mayor velocidad de Escritura en DELETE ya que se puede emplear DROP TABLE
  • Indices más pequeños contra uno grande, recarga menos la memoria del servidor
  • Al separar en tablas el tamaño en disco es menor y es más fácil transportar en dispositivos económicos como DVD’s, USB Drive, etc.

Para configurar lo primero que debemos hacer es optimizar los query’s para el particionado modificando el fichero postgresql.conf habilitando la siguiente línea

constraint_exclusion = partition

Lo primero que debemos hacer es crear la tabla maestra la cual será meramente referencia y no debe contener datos, servirá solamente para que las tablas hijas hereden sus características.

CREATE TABLE sms
(
   id serial NOT NULL,
   desde integer NOT NULL,
   para integer NOT NULL,
   mensaje text,
   fecha_envio timestamp without time zone NOT NULL
)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE sms OWNER TO postgres;

Procederemos a crear 3 tablas, una para cada mes: enero, febrero y marzo (eso sí es un poco tedioso ésta creación es algo larga pero para eso pagamos un DBA, no?). Las tablas hijas no contendrán campos nuevos, sólo unos CHECK CONSTRAINT para determinar cual será su rango de fechas para almacenar (en éste ejemplo no valido los años biciestos, se los dejo a sus criterios).

CREATE TABLE sms_a2010m01
(
CONSTRAINT chk_fecha_a2010m01 CHECK (fecha_envio >= '2010-01-01'::date AND fecha_envio <= '2010-01-31'::date)
)
INHERITS (sms)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE sms_a2010m01 OWNER TO postgres;

--2010/02
CREATE TABLE sms_a2010m02
(
CONSTRAINT chk_fecha_a2010m02 CHECK (fecha_envio >= '2010-02-01'::date AND fecha_envio <= '2010-02-28'::date)
)
INHERITS (sms)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE sms_a2010m02 OWNER TO postgres;

--2010/03
CREATE TABLE sms_a2010m03
(
CONSTRAINT chk_fecha_a2010m01 CHECK (fecha_envio >= '2010-03-01'::date AND fecha_envio <= '2010-03-31'::date)
)
INHERITS (sms)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE sms_a2010m03 OWNER TO postgres;

Aquí podemos ir creando los índices según necesitemos, yo lo veo necesario sobre el campo fecha_envio, es conveniente hacerlo en cada tabla independientemente, porque en la tabla padre no tiene sentido crearla ya que no almacenarán datos en ningún momento.

-- indices
CREATE INDEX ix_fecha_envio_sms_a2010m01
   ON sms_a2010m01 (fecha_envio ASC NULLS LAST);

CREATE INDEX ix_fecha_envio_sms_a2010m02
   ON sms_a2010m02 (fecha_envio ASC NULLS LAST);

CREATE INDEX ix_fecha_envio_sms_a2010m03
   ON sms_a2010m03 (fecha_envio ASC NULLS LAST);

Al tener listas nuestras estructuras de almacenamiento nos queda programar la redirección de los datos a sus respectivas tablas evaluando los rangos de fechas. Creamos una función para un trigger.

-- funcion trigger
CREATE OR REPLACE FUNCTION sms_insert_trigger()
  RETURNS trigger AS
$BODY$begin
if (new.fecha_envio >= date '2010-01-01' and new.fecha_envio <= date '2010-01-31') then
	insert into sms_a2010m01 values (new.*);
elsif (new.fecha_envio >= date '2010-02-01' and new.fecha_envio <= date '2010-02-28') then
	insert into sms_a2010m02 values (new.*);
elsif (new.fecha_envio >= date '2010-03-01' and new.fecha_envio <= date '2010-03-31') then
	insert into sms_a2010m03 values (new.*);
else
	raise exception 'Rango de fecha desbordado, amplie la funcion en el trigger sms_insert_trigger()';
end if;
return null;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION sms_insert_trigger() OWNER TO postgres;

Desde nuestro trigger por insert invocamos a la función que acabamos de crear.

-- trigger
CREATE TRIGGER insert_sms_trigger
  BEFORE INSERT
  ON sms
  FOR EACH ROW
  EXECUTE PROCEDURE sms_insert_trigger();

Es hora de poblar y leer las tablas, veremos que usaremos el nombre de SMS nada más las operaciones, así no requeriremos de SQL Dinámico, y obtendremos un mejor provecho de nuestro hardware a la vez.

-- poblamos la tabla
insert into sms values (1, 981568121, 968514841, '*******************', '2010-03-02 12:51:05');
insert into sms values (2, 982545981, 968454512, '*******************', '2010-02-13 11:24:03');
insert into sms values (3, 994224542, 938484524, '*******************', '2010-01-09 02:45:22');
insert into sms values (4, 985200544, 956458452, '*******************', '2010-01-04 00:12:26');
insert into sms values (5, 986311881, 954854854, '*******************', '2010-03-30 03:34:08');
insert into sms values (6, 965821801, 971245540, '*******************', '2010-02-18 15:20:10');
insert into sms values (7, 986154511, 964245746, '*******************', '2010-03-12 22:00:46');

-- ejemplos de selects
select * from sms
select * from sms where id = 4
select * from sms where fecha_envio between date '2010-01-09' and date '2010-02-18'

Comentarios desde Facebook:

  1. avatar
    LuisC PARAGUAY Google Chrome Linux
    15 noviembre 2010 at 14:17 #

    ndeeeraaa… estos tipos almacenan todos mis SMSs? ya era entonces mi carrera por la presidencia de la república 😕

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      15 noviembre 2010 at 14:30 #

      La primer y segunda compañia mas grande de nuestro pais al menos si lo hacen. Hasta donde me consta la mas grande las elimina tras un periodo de 3 meses.

Trackbacks/Pingbacks

  1. Stream Warehousing (Almacenamiento de datos en flujo continuo) | Sapere aude MEXICO WordPress - 27 noviembre 2013

    […] registros y un constante movimiento, se ha ideado hacer la carga de actualizaciones (updates) sobre particiones de la misma tabla, es decir, para cada paquete de actualizaciones se crea una nueva tabla idéntica […]

  2. Tweets that mention Como hacer Particionamiento Horizontal de Tablas en PostgreSQL | DevTroce.com -- Topsy.com UNITED STATES - 15 noviembre 2010

    […] This post was mentioned on Twitter by Diogenes Caraballo, DevTroce. DevTroce said: Particionamiento Horizontal de Tablas en #PostgreSQL: Más de uno me mirará con una cara ¿De… http://goo.gl/fb/q9g6p […]

Responder