Archivo de la etiqueta: PostgreSQL

Fragmentación Horizontal en PostgreSQL

La “Fragmentación Horizontal en PostgreSQL” es una técnica ampliamente utilizada en el caso de que existan millares de registros en una tabla, a continuación se muestra cómo se implementa, además de un ejercicio práctico para probar:

[1].- Se creará una tabla Normal y otra para la implementación de la Fragmentación;

[2].- Se implementará un “store procedure” para el registro de 1’000.000 de datos;

[3].- Luego, se podrán realizar las respectivas pruebas con respecto a los tiempos del INSERT, UPDATE, DELETE y SELECT.

1.- Creación de Tablas.-

CREATE TABLE NORMAL ( LLAVE BIGINT PRIMARY KEY, DATO BIGINT );
CREATE TABLE PARTICIONADA ( LLAVE BIGINT, DATO BIGINT );

CREATE INDEX particionada_llave ON PARTICIONADA USING btree( LLAVE );

CREATE TABLE PARTICIONADA_100000 ( CHECK (LLAVE <= 100000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_200000 ( CHECK (LLAVE > 100000 AND LLAVE <= 200000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_300000 ( CHECK (LLAVE > 200000 AND LLAVE <= 300000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_400000 ( CHECK (LLAVE > 300000 AND LLAVE <= 400000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_500000 ( CHECK (LLAVE > 400000 AND LLAVE <= 500000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_600000 ( CHECK (LLAVE > 500000 AND LLAVE <= 600000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_700000 ( CHECK (LLAVE > 600000 AND LLAVE <= 700000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_800000 ( CHECK (LLAVE > 700000 AND LLAVE <= 800000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_900000 ( CHECK (LLAVE > 800000 AND LLAVE <= 900000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_1000000 ( CHECK (LLAVE > 900000 AND LLAVE <= 1000000) ) INHERITS (PARTICIONADA);
CREATE TABLE PARTICIONADA_1100000 ( CHECK (LLAVE > 1000000 AND LLAVE <= 1100000) ) INHERITS (PARTICIONADA);

CREATE INDEX particionada_llave_100000 ON PARTICIONADA_100000 USING btree( LLAVE );
CREATE INDEX particionada_llave_200000 ON PARTICIONADA_200000 USING btree( LLAVE );
CREATE INDEX particionada_llave_300000 ON PARTICIONADA_300000 USING btree( LLAVE );
CREATE INDEX particionada_llave_400000 ON PARTICIONADA_400000 USING btree( LLAVE );
CREATE INDEX particionada_llave_500000 ON PARTICIONADA_500000 USING btree( LLAVE );
CREATE INDEX particionada_llave_600000 ON PARTICIONADA_600000 USING btree( LLAVE );
CREATE INDEX particionada_llave_700000 ON PARTICIONADA_700000 USING btree( LLAVE );
CREATE INDEX particionada_llave_800000 ON PARTICIONADA_800000 USING btree( LLAVE );
CREATE INDEX particionada_llave_900000 ON PARTICIONADA_900000 USING btree( LLAVE );
CREATE INDEX particionada_llave_1000000 ON PARTICIONADA_1000000 USING btree( LLAVE );
CREATE INDEX particionada_llave_1100000 ON PARTICIONADA_1100000 USING btree( LLAVE );

--- Trigger function
CREATE OR REPLACE FUNCTION on_particionada_insert() RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.LLAVE <= 100000 ) THEN
        INSERT INTO PARTICIONADA_100000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 100000 AND NEW.LLAVE <= 200000 ) THEN
        INSERT INTO PARTICIONADA_200000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 200000 AND NEW.LLAVE <= 300000 ) THEN
        INSERT INTO PARTICIONADA_300000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 300000 AND NEW.LLAVE <= 400000 ) THEN
        INSERT INTO PARTICIONADA_400000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 400000 AND NEW.LLAVE <= 500000 ) THEN
        INSERT INTO PARTICIONADA_500000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 500000 AND NEW.LLAVE <= 600000 ) THEN
        INSERT INTO PARTICIONADA_600000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 600000 AND NEW.LLAVE <= 700000 ) THEN
        INSERT INTO PARTICIONADA_700000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 700000 AND NEW.LLAVE <= 800000 ) THEN
        INSERT INTO PARTICIONADA_800000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 800000 AND NEW.LLAVE <= 900000 ) THEN
        INSERT INTO PARTICIONADA_900000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 900000 AND NEW.LLAVE <=  1000000 ) THEN
        INSERT INTO PARTICIONADA_1000000 VALUES (NEW.*);
    ELSIF ( NEW.LLAVE > 1000000 AND NEW.LLAVE <= 1100000 ) THEN
        INSERT INTO PARTICIONADA_1100000 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Desconocido';
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER particionada_insert
    BEFORE INSERT ON PARTICIONADA
    FOR EACH ROW EXECUTE PROCEDURE on_particionada_insert();

2.- Creación de los procedimientos almacenados 

CREATE OR REPLACE FUNCTION ins_normal(v1 BIGINT) RETURNS BIGINT AS $$
BEGIN
	WHILE v1 > 0 LOOP
		INSERT INTO NORMAL VALUES(v1, v1); 
		v1 = v1 - 1;
	END LOOP;
	RETURN v1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ins_particionada(v1 BIGINT) RETURNS BIGINT AS $$
BEGIN
	WHILE v1 > 0 LOOP
		INSERT INTO PARTICIONADA VALUES(v1, v1); 
		v1 = v1 - 1;
	END LOOP;
	RETURN v1;
END;
$$ LANGUAGE plpgsql;

3.- Scripts respectivos para las pruebas del INSERT, UPDATE, DELETE, SELECT

SELECT "ins_normal"(1000000);
SELECT "ins_particionada"(1000000);

SELECT * FROM NORMAL;
SELECT * FROM PARTICIONADA;

UPDATE NORMAL SET DATO = DATO + 10;
UPDATE PARTICIONADA SET DATO = DATO + 10;

DELETE FROM NORMAL;
DELETE FROM PARTICIONADA;

Bueno, espero les sirva.