Particionado de Tablas PostgreSQL

Consorcio SIU

PARTICIONADO DE TABLAS PARA MEJORA DE PERFORMANCE

Consideraciones Preliminares Si bien Postgresql no soporta ‘directamente’ particionado horizontal de tablas (refiriéndome a la sentencia de Particionado a través de DDL), existen métodos de hacerlo aunque sean algo engorrosos o no muy soportados.

escogiéramos para el mismo caso, particionar por mes, podríamos consultar con buenos resultados por este rango, pero si consultamos por periodo la performance seria menor ya que el ejecutor deberá ‘navegar’ por varias tablas para traer el resultado. Es ahí donde se produce el pequeño ‘overhead’. Si por alguna razón debería consultar de todos los periodos, la performance podría ser menor que si no hubiese partición.

Tanto las técnicas de Particionado horizontal como vertical de una tabla, intentan disminuir los accesos a disco, ordenando De esto se desprende que el Particionado físicamente los datos por un factor en común. debe ser pensado de acuerdo a las consultas que realizaremos. Si bien el particionado puede ser establecido previo a una carga de datos, por lo Esta planteado para versiones futuras, que general los desarrolladores no tienen en cuenta el particionado será incluido en el DDL. La esto. técnica que utilizaremos es con herencia de tablas (mejor dicho herencia de clases). El particionado genera un pequeño ‘overhead’ (sobrecarga) de procesamiento proporcional, en comparación con la tabla sin Particionado Vertical esta técnica. Por lo que particionar una tabla con pocos registros puede que no sea la mejor El particionado vertical busca disminuir los de las ideas. accesos a disco a través de la proyección (selección de columnas). Ambos tipos de Particionado son distintos y se logran de distinta manera. En tanto el El particionado vertical bien puede ser Particionado horizontal es por rango, el vertical considerado desde el diseño ( dos o más tablas, divide una tabla. unidas por una llave con cardinalidad 1:1 ). Particionado horizontal En el caso de Particionado horizontal, el acceso a disco disminuye si la consulta que realizamos busca solamente unos pocos elementos del conjunto de rangos (por selección). Si las consultas por lo general acceden a elementos de todos los grupos, el Particionado horizontal no ayudará mucho, e inclusive puede que empeore la performance.

En este caso sucede lo mismo que con las de particionado horizontal, pero la sobrecarga no es el mismo ya que no utiliza herencia. El modo de simular esto es creando ambas tablas y crear una vista global de ambas, que incluyan todas las columnas. El particionado vertical, por lo general puede ser tenido en cuenta por el desarrollador, pero el DBA puede implementarlo posteriormente.

Por ejemplo, supongamos este ejemplo: tenemos una tabla que contiene movimientos de los últimos 3 años. Nuestras consultas, casi siempre consultan por periodo (1er semestre 2do semestre / año), por lo que si particionamos por periodo obtendremos 6 particiones reduciendo los accesos a disco prácticamente Caso 1: ejemplo particionando unas 6 veces en consultas sobre 1 periodo. Si preexistentes SIU

tablas

PÁGINA 1 DE 9

Consorcio SIU Consorcio SIU

INSERT INTO anio_2009 (select * from Supongamos que tenemos la siguiente padre2 where EXTRACT(year from fecha) = tabla: '2009'); CREATE TABLE padre AS DELETE FROM padre SELECT WHERE (now() - (round(random()*1) || tableoid = ('padre'::regclass)::oid; ' year')::interval)::date as fecha, Con esto, tendremos los registros i, separados en las particiones, pudiendo acceder random()*100 as flotante a los mismos realizando una simple consulta a FROM la tabla padre: generate_series(1,50000) j(i); SELECT DISTINCT Esta sentencia crea una tabla con una tableoid::regclass FROM padre; columna de tipo ‘date’ que contendrá fechas aleatorias con 2 años distintos (actual y actualCon esto sabremos en que partición está 1). La columna ‘i’ contiene los valores de la almacenada cada registro: fuente del FROM y la otra columna es simplemente un valor aleatorio. SELECT tableoid::regclass, * FROM padre; Podremos observar los años de la tabla con la cantidad de tuplas para cada uno, con la siguiente consulta: Los datos están particionados, pero: ¿Qué pasa a partir de ahora? SELECT DISTINCT EXTRACT(year from fecha), La forma más sencilla de bifurcar los datos count(*) a su partición correspondiente es utilizando FROM padre RULES. Otra forma más eficiente pero compleja GROUP BY fecha; es a través de disparadores. Suponiendo que queremos particionar por Básicamente las RULES son disparadores año, deberemos crear las tablas pero más limitados pero más sencillos de correspondientes para cada uno de los mismos: declarar. CREATE TABLE anio_2008 CREATE RULE ins_2008 AS ON INSERT (CHECK TO padre WHERE EXTRACT(year from (EXTRACT(year from fecha) = '2008')) fecha) = '2008' INHERITS (padre); DO INSTEAD INSERT INTO anio_2008 VALUES (NEW.*); CREATE TABLE anio_2009 (CHECK CREATE RULE ins_2009 AS ON INSERT (EXTRACT(year from fecha) = '2009')) TO padre WHERE EXTRACT(year from INHERITS (padre); fecha) = '2009' DO INSTEAD INSERT INTO anio_2009 Luego, insertaremos todos los registros de VALUES (NEW.*); la tabla padre en cada una de las particiones que tengamos y borraremos los registros que están insertados en la tabla padre (no en las Ahora, cada vez que intentemos insertar en hijas): la tabla padre, los registros serán bifurcados a las particiones. INSERT INTO anio_2008 (select * from padre2 where EXTRACT(year from fecha) = Adicionalmente, tendremos que crear los '2008'); índices para las particiones y la tabla padre (ya SIU

PÁGINA 2 DE 9

Consorcio SIU


que si por alguna razón la consulta viaja por más de 1 partición, necesitaremos las ubicaciones generales): CREATE INDEX x_fecha_anio_2008 ON anio_2008 (fecha);

super (tipo tipo_persona CHECK (upper(tipo) IN (‘cliente’,’persona’)) ) INHERITS (cliente, persona);

CREATE INDEX x_fecha_anio_2009 ON anio_2009 (fecha); CREATE INDEX x_fecha_padre ON padre2 (fecha); Para actualizar las particiones, deberemos utilizar en este caso: INSERT INTO anio_2009 (select * from anio_2008 WHERE EXTRACT (year from fecha) = '2009'); DELETE FROM anio_2008 WHERE EXTRACT (year from fecha) = '2009';

Caso 2: Ejemplo de particionado complejo con disparadores.

Padre

cliente

Super

persona

INSERT S

En este ejemplo implementaremos un particionado por tipo. Esto quiere decir que tendremos particiones que contendrán elementos de un determinado tipo. Como vemos, tenemos dos tipos (cliente y persona). La tabla padre contendrá los ID’s y por ende, los tendremos ‘duplicados’ ya que los hijas también tendrán el ID del padre para distinguir los registros.

En este ejemplo haremos algo un poco más Para la bifurcación, en este caso, osado. Suponiendo el siguiente esquema de utilizaremos un disparador en PL/pgsql, ya que tablas: las RULES no permitirían hacer operaciones complejas como las que necesitamos. CREATE TABLE padre (id int PRIMARY KEY); CREATE OR REPLACE FUNCTION simple_part() RETURNS TRIGGER AS $simple_part$ DECLARE CREATE TABLE flag boolean = true; persona BEGIN (sexo char(1) CHECK (upper(sexo) IN ('M','F'))) IF (NEW.tipo = 'cliente') THEN INHERITS (padre); INSERT INTO cliente(id,dato) VALUES (NEW.id, NEW.dato); CREATE DOMAIN tipo_persona AS text ELSIF (NEW.tipo = 'persona') THEN CHECK (VALUE IN ('cliente', 'persona')); BEGIN INSERT INTO persona(id,sexo) CREATE TABLE CREATE TABLE cliente (dato text) INHERITS (padre);

SIU

PÁGINA 3 DE 9


VALUES (NEW.id, NEW.sexo); EXCEPTION Ejecutaremos la opción EXPLAIN WHEN check_violation THEN ANALYZE para verificar el la ruta de la consulta: flag = false; END; END IF; SELECT * if flag = true THEN FROM padre2 INSERT INTO padre WHERE fecha VALUES (NEW.id); BETWEEN '2009-09-09' END IF; AND '2009-10-10'; RETURN NULL; EXCEPTION WHEN others THEN RAISE NOTICE 'ERROR (%)', SQLERRM; END; $simple_part$ VOLATILE LANGUAGE plpgsql; Luego de creada la función que retorna el disparador, debemos declarar el mismo: CREATE TRIGGER simple_part BEFORE INSERT ON super FOR EACH ROW EXECUTE PROCEDURE simple_part(); Podemos hacer un pequeño set de prueba: INSERT INTO super(id,tipo,dato) VALUES (1,'cliente', 'dato cualquiera'); INSERT INTO super(id,tipo,sexo) VALUES (2,'persona','d'); --debe fallar INSERT INTO super (id,tipo,sexo) VALUES (3,'persona','m');

Podemos observar que para las 3 tablas implicadas (padre2, anio_2008 y anio_2009), se accedió de manera distinta. En la tabla padre2 no hay registros por lo que utilizo Bitmap Index Scan. Si bien no hay datos directamente en la tabla padre, el nodo APPEND reúne todos los datos dependientes de las tablas heredadas. Para la tabla anio_2008, simplemente se utilizo un Index Scan, evitando accesos más costosos.

En cuanto a la tabla anio_2009, se accedió por acceso secuencial. Esto sucedió porque esta consulta trajo todos los registros de la tabla Las consultas las realizaremos sobre la (ya que cuando insertamos los valores de clase padre, de la misma manera que prueba no cambiamos los meses). En el caso de que hubiese menos ocurrencias, ejecutamos en el ejemplo 1. seguramente hubiese utilizado el índice. Entendiendo a fondo el trabajo planeador con el particionado horizontal Para explicar el funcionamiento planeador tomaremos el caso del ejemplo 1. SIU

El overhead, es generado por el nodo APPEND. Si la consulta hubiese obtenido ocurrencias de la otra tabla hija, no tendríamos mejoras en la performance comparado a una del sola tabla.

del

PÁGINA 4 DE 9

Consorcio SIU


Si quisiéramos buscar solamente por año y no por rango de fechas, podríamos crear índices por expresión: CREATE INDEX x_fecha_anio ON padre2 (EXTRACT(year from fecha));

(SELECT '{a1,b2,c3,e4}'::text[]) a(n)) as texto, round(random()*100) as entero, i + round(random()*1000) as entero2 FROM generate_series(1, 50000) j(i);

Por lo tanto la consulta quedaría: Añadimos una llave: SELECT * FROM padre2 WHERE EXTRACT(year from fecha) = '2009' ;

ALTER TABLE general ADD PRIMARY KEY(i);

Con esto, estamos simulando una tabla, Si realizamos un EXPLAIN ANALYZE, que ya está cargada con datos. Ahora, lo que veremos que el planeador a decidido utilizar vamos a hacer a continuación es particionarla nuestro índice: verticalmente en dos tablas. Luego la tabla general puede ser borrada o dejada de backup. Para ello creamos las tablas: CREATE TABLE general1 AS SELECT i, floaty, epoch, arreglo FROM general;

De esto se desprende que los índices sobre la tabla ‘padre’ afectarán a todas las consultas registros de las hijas.

Caso 3: Ejemplo de particionado vertical Supongamos el ejemplo de la siguiente tabla: CREATE TABLE general AS SELECT i, random()::double precision as floaty, (now() - (round(random()*100) || ' days')::interval)::timestamp as epoch, ('{' || i + round(random()*10) || ',' ||i + round(random()*100)||'}')::int[] as arreglo, (select n[round(random()*4)]::text FROM SIU

CREATE TABLE general2 AS SELECT i, texto, entero, entero2 FROM general; ALTER TABLE general RENAME TO general_TO_DROP; Paso siguiente, creamos la vista que contendrá la combinación de ambas tablas: CREATE VIEW general AS SELECT * FROM general1 JOIN general2 USING (i); Por lo tanto, cuando hagamos consultas que traigan solamente columnas de una de las dos particiones, el planeador decidirá no acceder a la partición no ocurrente. Vemos como realizar una consulta a la vista: part=# SELECT * FROM general LIMIT 1; -[ RECORD 1 ]----------------------PÁGINA 5 DE 9


i |1 floaty | 0.791756465099752 epoch | 2009-09-28 15:31:34.818921 arreglo | {9,39} texto | c3 entero | 75 entero2 | 483 Y el EXPLAIN nos muestra: EXPLAIN SELECT floaty, epoch FROM general; QUERY PLAN ---------------------------------------------------------Hash Join (cost=1615.00..4127.50 rows=50000 width=16) Hash Cond: (general1.i = general2.i) -> Seq Scan on general1 (cost=0.00..991.00 rows=50000 width=20) -> Hash (cost=819.00..819.00 rows=50000 width=4) -> Seq Scan on general2 (cost=0.00..819.00 rows=50000 width=4) Consideraciones finales Por lo que apreciamos, no hay mucha mejora sustancial (comparado al particionado horizontal), sin embargo se usan para casos muy distintos ambos modelos. En los dos métodos, esta técnica es transparente a la aplicación por lo que no es necesario efectuar modificaciones a las consultas.

Extendiendo las vistas del particionado con reglas Creada la vista ‘tres’ sobre las tablas ‘una’ y ‘dos’, queremos que al insertar sobre la vista, automáticamente bifurque los valores sobre las tablas correspondientes. Para ello podemos utilizar disparadores, o en su defecto, unos RULE: CREATE OR REPLACE RULE R_tres_in AS ON INSERT TO tres DO INSTEAD (INSERT INTO una VALUES(NEW.i, NEW.floaty) ; INSERT INTO dos VALUES(NEW.g,NEW.valor)); CREATE OR REPLACE RULE R_tres_up AS ON UPDATE TO tres DO INSTEAD ( UPDATE una SET i =NEW.i, floaty = NEW.floaty WHERE i = OLD.i AND floaty =OLD.floaty; UPDATE dos SET g =NEW.g , valor = NEW.valor WHERE g = OLD.g AND valor = OLD.valor; ); CREATE OR REPLACE RULE R_tres_del AS ON DELETE TO tres DO INSTEAD ( DELETE FROM una WHERE i = OLD.i OR floaty = OLD.floaty; DELETE FROM dos WHERE g = OLD.g OR valor = OLD.valor; );

En el caso preciso del particionado vertical, el hecho de que la tupla sea más chica, permitirá que existan mayor cantidad de Con estas reglas, la aplicación intentará registros por bloque. insertar en la vista ‘tres’ pensando que es la tabla correspondiente. Cabe destacar que esta En el particionado horizontal, el registro no técnica se utilizaría para los particionados varía de tamaño, lo que hace que haya la misma verticales. cantidad de registros por bloque. Lo que hace la reducción de accesos es el orden. Por eso es La intención de agregar esto, es que sea crucial la buena selección del campo que totalmente transparente a la aplicación, de dividirá las particiones. modo tal de que pueda ser implementado en un entorno en producción sin cambios en el Por eso, si nuestras consultas no respetan aplicativo. rangos, es posible que el particionado vertical Véase que las reglas tienen una sentencia mejore la performance. por tabla (que insertará, modificará o borrará de acuerdo a los valores nuevos).

SIU

PÁGINA 6 DE 9

Consorcio SIU


Constraint Exclusion nombre | text | Hasta ahora hemos visto el particionado en sí de las tablas. Como hemos visto también, contenido | bytea | cada partición requiere un CHECK o constraint. Quizás hasta ahora parecía redundante, pero Indexes: veremos como agregando esa restricción se peude hacer que el planeador mejore y haga "ix_maestra_cdu_date" btree (cdu_date) más inteligente la búsqueda. Triggers: La variable constraint_exclusion, permite optimizar las búsquedas en las tablas t_part_maestra_date_char BEFORE INSERT heredadas. Puede tener 3 valores en la versión ON maestra FOR EACH ROW EXECUTE 8.4 ('on', 'off','partition'). PROCEDURE part_maestra() particionado=# select name, setting, context, extra_desc from pg_settings where name = 'constraint_exclusion'; [RECORD 1 ] name

| constraint_exclusion

La partición esta basada sobre el campo date_char el cual contiene 8 caracteres para la representacion de la fecha en formato YYYYMMDD.

setting

| partition

En una búsqueda cotidiana obtendriamos algo parecido a esto con la variable activada en 'partition':

context

| user

particionado=# show constraint_exclusion;

extra_desc | Table scans will be skipped if their constraints guarantee that no rows match the query.

constraint_exclusion | partition (1 row)

En el siguiente ejemplo tenemos una tabla llamada maestra con 33 particiones en períodos de 1 mes cada una. explain select * from maestra substring(cdu_date,0,7) = '200804'; particionado=# \d maestra Table "public.maestra" Column |

Type

where

Result (cost=0.00..696.89 rows=27 width=82) | Modifiers

-> Append width=82)

(cost=0.00..696.89 rows=27

-----------+-----------------------------+----------date_char | character varying(8) entero floaty fecha SIU

| integer | double precision

|

|

-> Seq Scan on maestra (cost=0.00..18.25 rows=3 width=118) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text)

|

| timestamp without time zone |

-> Seq Scan on maestra_200804 maestra (cost=0.00..678.64 rows=24 width=78) PÁGINA 7 DE 9


Filter: ("substring"((cdu_date)::text, 0, Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) 7) = '200804'::text) (6 rows)

-> Seq Scan on maestra_200708 maestra (cost=0.00..358.40 rows=13 width=78)

En cambio, desactivada tendremos: particionado=# set constraint_exclusion = 'off';

Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200807 maestra (cost=0.00..733.54 rows=26 width=79)

SET (…. y continua) particionado=# explain select * from maestra where substring(cdu_date,0,7) = '200804'; Result width=79)

(cost=0.00..13764.65

Verifiquen los costes uds. mismos los costes de ambas consultas.

rows=493

-> Append (cost=0.00..13764.65 rows=493 width=79) -> Seq Scan on maestra (cost=0.00..18.25 rows=3 width=118) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200710 maestra (cost=0.00..354.94 rows=13 width=79) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200806 maestra (cost=0.00..699.97 rows=25 width=79) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200808 maestra (cost=0.00..725.67 rows=26 width=78) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200706 maestra (cost=0.00..359.44 rows=13 width=78) Filter: ("substring"((cdu_date)::text, 0, 7) = '200804'::text) -> Seq Scan on maestra_200811 maestra (cost=0.00..699.91 rows=25 width=78) SIU

PÁGINA 8 DE 9

SIU

PÁGINA 9 DE 9

Particionado de Tablas PostgreSQL

Recommend Documents