PostgreSQL: Creación de Tablespaces

Este es el Post #13 del Topic: Guía Práctica de PostgreSQL

SUGERENCIA:
Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla.

Descargar Script: DBAdmision.sql

¿Qué es?

Los tablespaces son referencias a ubicaciones físicas  del almacenamiento de bases de datos y/o de los objetos que éste contiene.

¿Cuándo debo utilizarlo?

Es recomendable utilizar tablespaces cuando se quiere especificar ubicaciones alternativas para determinadas bases de datos o tablas, como cuando queremos que ciertas tablas estén en otros discos distintos a los que se encuentran por ejemplo, para ubicar los tablespaces con data al que se accesa muy raras veces, en dispositivos de almacenamientos lentos y a los tablespaces con data a la que constantemente se accede, en medios de almacenamiento mucho mas rápidos.

Para estos ejemplo se requiere la re-creación de la base de datos dbadmisión y  una ligera revisión al post Particionamiento de Tablas.

1- ELIMINACIÓN DE CONTENIDO DE LA TABLA Persona.Postulante;

TRUNCATE TABLE Persona.Postulante;

2- CREACIÓN DE DIRECTORIOS:

Esta sentencia unix crea una estructura de directorios que servirán para almacenar los tablespaces:

# mkdir -p /mnt/DATA/Postulante/tbe_{2005_{1,2},2006_{1,2},2007_1,0000_0}

/mnt/DATA
    |-- Postulante
        |-- tbe_0000_0
        |-- tbe_2005_1
        |-- tbe_2005_2
        |-- tbe_2006_1
        |-- tbe_2006_2
        |-- tbe_2007_1

3- ESTABLECER PROPIETARIO:

Para poder crear los tablespaces en una determinada localización es necesario que las ubicaciones asignadas pertencezcan al usuario que se creó en la instalación de postgres.

# chown -R  postgres:postgres /mnt/DATA/

4- CREAR TABLESPACES:

CREATE TABLESPACE tbe_2005_1 LOCATION '/mnt/DATA/Postulante/tbe_2005_1';
CREATE TABLESPACE tbe_2005_2 LOCATION '/mnt/DATA/Postulante/tbe_2005_2';
CREATE TABLESPACE tbe_2006_1 LOCATION '/mnt/DATA/Postulante/tbe_2006_1';
CREATE TABLESPACE tbe_2006_2 LOCATION '/mnt/DATA/Postulante/tbe_2006_2';
CREATE TABLESPACE tbe_2007_1 LOCATION '/mnt/DATA/Postulante/tbe_2007_1';
CREATE TABLESPACE tbe_0000_0 LOCATION '/mnt/DATA/Postulante/tbe_0000_0';

5- VERIFICACIÓN DE data/pg_tblspc/

En el directorio data/pg_tblspc/ se crean automáticamente los enlaces de las ubicaciones de los tablespaces. Por cada tablespace se generará un directorio y dentro de cada directorio un fichero PG_VERSION con un tamaño de 4 K.

# ls -l /opt/PostgresPlus/8.4SS/data/pg_tblspc/

6- CONSULTAR  pg_tablespace:

pg_tablespace es una table existente en el catálogo de postgres y almacena información sobre los tablespaces.

La consulta siguiente retorna el nombre, ubicacion y tamaño de los tablespaces creados en  /mnt/DATA/Postulante. Para obtener el tamaño utilizamos de la función pg_tablespace_size(nombre_de_tablespace).

SELECT spcname,spclocation,pg_tablespace_size(spcname) "tamaño"
FROM pg_tablespace
WHERE spclocation LIKE '/mnt/DATA/Postulante%';

--"tbe_2005_1";"/mnt/DATA/Postulante/tbe_2005_1";4
--"tbe_2005_2";"/mnt/DATA/Postulante/tbe_2005_2";4
--"tbe_2006_1";"/mnt/DATA/Postulante/tbe_2006_1";4
--"tbe_2006_2";"/mnt/DATA/Postulante/tbe_2006_2";4
--"tbe_2007_1";"/mnt/DATA/Postulante/tbe_2007_1";4
--"tbe_0000_0";"/mnt/DATA/Postulante/tbe_0000_0";4

7- TAMAÑO DE ALMACENAMIENTO FÍSICO (UNIX🙂

# find /mnt/DATA/Postulante/ -type f -exec ls -l {} ;|grep -v PG_VERSION|awk ‘{print $5+4100,»t»,$9}’

La sentencia anterior es un bloque de instrucciones UNIX, y es otra manera de poder consultar informacion de los archivos fisicos de los tablespaces (análogo al de pg_tablespace).

A partir de este punto se realizará la creación de las particiones y funciones necesarias que intervienen en el proceso tal como se hizo en el post anterior relativo a  Particionamiento de Tablas

8- CREACIÓN DE ESQUEMA PARA PARTICIONES:

CREATE SCHEMA Particion;

9- CREACIÓN DE PARTICIONES (Cada partición en su respectivo tablespace)

Con diferencia a lo realizado en Particionamiento de Tablas, aquí asignamos un tablespace a cada partición que se creará.

CREATE TABLE Particion.Postulante_2005_1 (CHECK ( IDPerAcad='2005-1' ))
INHERITS (Persona.Postulante)
TABLESPACE tbe_2005_1;
;;
CREATE TABLE Particion.Postulante_2005_2 (CHECK ( IDPerAcad='2005-2' ))
INHERITS (Persona.Postulante)
TABLESPACE tbe_2005_2;
;;
CREATE TABLE Particion.Postulante_2006_1 (CHECK ( IDPerAcad='2006-1' ))
INHERITS (Persona.Postulante)
TABLESPACE tbe_2006_1;
;;
CREATE TABLE Particion.Postulante_2006_2 (CHECK ( IDPerAcad='2006-2' ))
INHERITS (Persona.Postulante)
TABLESPACE tbe_2006_2;
;;
CREATE TABLE Particion.Postulante_2007_1 (CHECK ( IDPerAcad='2007-1' ))
INHERITS (Persona.Postulante)
TABLESPACE tbe_2007_1;
;;
CREATE TABLE Particion.Postulante_0000_0 ()
INHERITS (Persona.Postulante)
TABLESPACE tbe_0000_0;
;;

10- CREACIÓN DE LA FUNCIÓN DE PARTICIONAMIENTO:

 CREATE OR REPLACE FUNCTION Particion.Insert_PerAcad_Function()
 RETURNS TRIGGER AS $$
 BEGIN
 IF ( NEW.IDPerAcad ='2005-1') THEN
 INSERT INTO Particion.Postulante_2005_1 VALUES (NEW.*);
 ELSIF ( NEW.IDPerAcad ='2005-2') THEN
 INSERT INTO Particion.Postulante_2005_2 VALUES (NEW.*);
 ELSIF ( NEW.IDPerAcad ='2006-1' ) THEN
 INSERT INTO Particion.Postulante_2006_1 VALUES (NEW.*);
 ELSIF ( NEW.IDPerAcad ='2006-2' ) THEN
 INSERT INTO Particion.Postulante_2006_2 VALUES (NEW.*);
 ELSIF ( NEW.IDPerAcad ='2007-1' ) THEN
 INSERT INTO Particion.Postulante_2007_1 VALUES (NEW.*);
 ELSE
 INSERT INTO Particion.Postulante_0000_0 VALUES (NEW.*);
 END IF;
 RETURN NULL;
 END;
 $$
 LANGUAGE plpgsql;

11- CREACIÓN DEL TRIGGER DE PARTICIONAMIENTO:

CREATE TRIGGER Insert_PerAcad_Trigger
BEFORE INSERT ON Persona.Postulante
FOR EACH ROW EXECUTE PROCEDURE Particion.Insert_PerAcad_Function();

12- PRIMERA VERIFICACIÓN DE TABLESPACES;

SELECT spcname,spclocation,pg_tablespace_size(spcname) "tamaño"
FROM pg_tablespace
WHERE spclocation LIKE '/mnt/DATA/Postulante%';

--"tbe_2005_1";"/mnt/DATA/Postulante/tbe_2005_1";4100
--"tbe_2005_2";"/mnt/DATA/Postulante/tbe_2005_2";4100
--"tbe_2006_1";"/mnt/DATA/Postulante/tbe_2006_1";4100
--"tbe_2006_2";"/mnt/DATA/Postulante/tbe_2006_2";4100
--"tbe_2007_1";"/mnt/DATA/Postulante/tbe_2007_1";4100
--"tbe_0000_0";"/mnt/DATA/Postulante/tbe_0000_0";4100

13- INSERTAR DATOS EN Persona.Postulante

Una vez creado el entorno volveremos a insertar datos en la tabla Persona.Postulante:

Ejecutar Script: Insertar Postulantes.sql 

14- SEGUNDA VERIFICACIÓN DE TABLESPACES;

SELECT spcname,spclocation,pg_tablespace_size(spcname) "tamaño"
FROM pg_tablespace
WHERE spclocation LIKE '/mnt/DATA/Postulante%';

--"tbe_2005_1";"/mnt/DATA/Postulante/tbe_2005_1";12292
--"tbe_2005_2";"/mnt/DATA/Postulante/tbe_2005_2";12292
--"tbe_2006_1";"/mnt/DATA/Postulante/tbe_2006_1";12292
--"tbe_2006_2";"/mnt/DATA/Postulante/tbe_2006_2";12292
--"tbe_2007_1";"/mnt/DATA/Postulante/tbe_2007_1";12292
--"tbe_0000_0";"/mnt/DATA/Postulante/tbe_0000_0";4100

Con excepción del tablespace tbe_0000_0, se ha insertado datos a todos las demás particiones, teniendo como resultado el crecimiento de sus tablespaces.

15- ELIMINACIÓN DE DATOS DEL TABLESPACE tbe_2005_1 y tbe_2005_2

Eliminamos un grupo de datos para comprobar como cambia la magnitud de sus tablespaces.

DELETE FROM Persona.Postulante WHERE IDPerAcad IN ('2005-1','2005-2');

16- REALIZAR VACUUM:

Cuando se hace uso de la sentencia DELETE, postgres elimina los registros solo de manera lógica, ello para hacer más rápida la ejecución de la sentencia pero  en este ejemplo para comprobar el espacio en tablespace que se reduce al realizar la eliminación de datos , forzaremos la eliminación física de estos.

VACUUM FULL Particion.Postulante_2005_1;
VACUUM FULL Particion.Postulante_2005_2;

17- TERCERA VERIFICACIÓN DE TABLESPACES;

SELECT spcname,spclocation,pg_tablespace_size(spcname) "tamaño"
FROM pg_tablespace
WHERE spclocation LIKE '/mnt/DATA/Postulante%';

--"tbe_2005_1";"/mnt/DATA/Postulante/tbe_2005_1";4100
--"tbe_2005_2";"/mnt/DATA/Postulante/tbe_2005_2";4100
--"tbe_2006_1";"/mnt/DATA/Postulante/tbe_2006_1";12292
--"tbe_2006_2";"/mnt/DATA/Postulante/tbe_2006_2";12292
--"tbe_2007_1";"/mnt/DATA/Postulante/tbe_2007_1";12292
--"tbe_0000_0";"/mnt/DATA/Postulante/tbe_0000_0";4100

Como se eliminaron registros con IDPerAcad ‘2005-1’ y ‘2005-2’, el espacio ocupado anteriormente por sus tablespaces a disminuido.

18- DEFAULT_TABLESPACE:

Cuadn se establece un tablespace por defecto todos los objetos creados se ubicarán en dicho tablespace.

SHOW default_tablespace; --Verificar Tablespace actual

SET default_tablespace='tbe_0000_0'; --establecer tablespace por defecto

SHOW default_tablespace; --Verificar cambio de Tablespace

*-Los valores definidos con SET solo son temporales.

|