Administración de Vistas en PostgreSQL

Introducción

En este laboratorio, explorarás la gestión de vistas en PostgreSQL. El objetivo principal es comprender e implementar diferentes tipos de vistas, incluyendo vistas simples y vistas materializadas.

Comenzarás definiendo una vista simple basada en la tabla employees, demostrando cómo crear una vista que seleccione columnas específicas. A continuación, aprenderás cómo consultar y potencialmente modificar datos a través de las vistas. Finalmente, el laboratorio cubre la creación y población de vistas materializadas, junto con la actualización manual de estas vistas para mantenerlas actualizadas.

Definir una Vista Simple

En este paso, aprenderás a definir una vista simple en PostgreSQL. Las vistas son tablas virtuales basadas en el conjunto de resultados de una instrucción SQL. Son útiles para simplificar consultas complejas, proporcionar abstracción y controlar el acceso a los datos.

Entendiendo las Vistas

Una vista es esencialmente una consulta almacenada. Cuando consultas una vista, PostgreSQL ejecuta la consulta subyacente y devuelve el conjunto de resultados como si fuera una tabla real. Las vistas no almacenan datos por sí mismas; proporcionan una forma diferente de acceder a los datos almacenados en las tablas base.

Creando la Tabla employees

Primero, creemos una tabla llamada employees para trabajar. Abre un terminal y conéctate a la base de datos PostgreSQL como usuario postgres:

sudo -u postgres psql

Ahora, crea la tabla employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

A continuación, inserta algunos datos de muestra en la tabla employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000.00),
('Jane', 'Smith', 'Marketing', 75000.00),
('Robert', 'Jones', 'Sales', 62000.00),
('Emily', 'Brown', 'IT', 80000.00),
('Michael', 'Davis', 'Marketing', 70000.00);

Puedes verificar los datos ejecutando la siguiente consulta:

SELECT * FROM employees;

Debes ver los datos insertados en la salida.

Definiendo la Vista employee_info

Ahora que tenemos una tabla con datos, creemos una vista simple. Esta vista mostrará solo el nombre, apellido y departamento de cada empleado. Podemos definir una vista llamada employee_info usando la siguiente instrucción SQL:

CREATE VIEW employee_info AS
SELECT first_name, last_name, department
FROM employees;

Esta instrucción crea una vista llamada employee_info que selecciona las columnas first_name, last_name y department de la tabla employees.

Consultando la Vista

Para consultar la vista, puedes usar una instrucción SELECT como lo harías con una tabla regular:

SELECT * FROM employee_info;

Esta consulta devolverá el nombre, apellido y departamento de todos los empleados, tal como se define en la vista.

Describiendo la Vista

Puedes describir la vista usando el comando \d en psql:

\d employee_info

Esto te mostrará la definición de la vista y las columnas que contiene.

Consultar y Modificar Datos a través de Vistas

En este paso, aprenderás a consultar y modificar datos a través de vistas en PostgreSQL. Si bien las vistas se utilizan principalmente para consultar datos, en algunos casos también se pueden usar para modificar los datos subyacentes en las tablas base.

Consultar Datos a través de Vistas

Como se demostró en el paso anterior, consultar datos a través de una vista es sencillo. Puedes usar una instrucción SELECT para recuperar datos de la vista como si fuera una tabla regular.

Por ejemplo, para recuperar todos los datos de la vista employee_info:

SELECT * FROM employee_info;

También puedes usar cláusulas WHERE y otras estructuras SQL para filtrar y ordenar los datos:

SELECT * FROM employee_info WHERE department = 'Sales';

Esta consulta devolverá solo los empleados del departamento de Ventas.

Modificar Datos a través de Vistas

Modificar datos a través de una vista es posible bajo ciertas condiciones. La vista debe ser lo suficientemente simple para que PostgreSQL determine qué tabla base y columnas actualizar. Generalmente, una vista es modificable si cumple los siguientes criterios:

• Selecciona de una sola tabla.
• No contiene funciones agregadas (por ejemplo, SUM, AVG, COUNT).
• No contiene cláusulas GROUP BY, HAVING o DISTINCT.

Creemos otra vista que incluya el employee_id para permitir actualizaciones más fáciles:

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees;

Ahora, intentemos actualizar el salario de un empleado a través de la vista employee_details:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Esta instrucción actualiza el salario del empleado con employee_id 1 a 65000.00.

Puedes verificar la actualización consultando la tabla employees directamente:

SELECT * FROM employees WHERE employee_id = 1;

Debes ver que el salario para employee_id 1 ha sido actualizado.

Insertar Datos a través de Vistas

También puedes insertar datos a través de una vista, siempre que la vista incluya todas las columnas no nulas de la tabla base. Dado que nuestra vista employee_details incluye todas las columnas de la tabla employees, podemos insertar un nuevo empleado:

INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);

Ten en cuenta que no especificamos el employee_id porque es una columna serial y se generará automáticamente.

Verifica la inserción:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Eliminar Datos a través de Vistas

De manera similar, puedes eliminar datos a través de una vista modificable:

DELETE FROM employee_details WHERE first_name = 'David' AND last_name = 'Lee';

Verifica la eliminación:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Consideraciones Importantes

• No todas las vistas son modificables. Las vistas complejas con uniones, agregaciones u otras operaciones complejas suelen ser de solo lectura.
• Modificar datos a través de vistas puede tener implicaciones de rendimiento. PostgreSQL necesita traducir las operaciones de la vista en operaciones en las tablas base subyacentes.
• Ten cuidado al modificar datos a través de vistas, ya que los cambios afectarán directamente a las tablas base.

Crear y Poblar una Vista Materializada

En este paso, aprenderás a crear y poblar una vista materializada en PostgreSQL. A diferencia de las vistas regulares, las vistas materializadas almacenan el conjunto de resultados de la consulta como una tabla física. Esto puede mejorar significativamente el rendimiento de las consultas, especialmente para consultas complejas o consultas que acceden a datos de fuentes remotas. Sin embargo, los datos de una vista materializada no se actualizan automáticamente cuando cambian los datos subyacentes. Necesitas actualizarla manualmente o programar su actualización periódicamente.

Creando una Vista Materializada

Para crear una vista materializada, se utiliza la instrucción CREATE MATERIALIZED VIEW. Vamos a crear una vista materializada llamada employee_salaries que muestra el salario promedio por departamento.

CREATE MATERIALIZED VIEW employee_salaries AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Esta instrucción crea una vista materializada llamada employee_salaries que calcula el salario promedio por departamento basado en los datos de la tabla employees.

Consultando la Vista Materializada

Puedes consultar una vista materializada como una tabla regular:

SELECT * FROM employee_salaries;

Esto devolverá el departamento y el salario promedio para cada departamento, basado en los datos que estaban presentes en la tabla employees cuando se creó la vista materializada.

Poblando la Vista Materializada

Cuando creas una vista materializada, se pobla con los datos iniciales. Sin embargo, si los datos subyacentes en la tabla employees cambian, los datos en la vista materializada employee_salaries no se actualizarán automáticamente.

Vamos a insertar un nuevo empleado en la tabla employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Johnson', 'IT', 85000.00);

Ahora, si consultas la vista materializada employee_salaries de nuevo:

SELECT * FROM employee_salaries;

Observarás que el salario promedio del departamento de TI no ha cambiado para reflejar al nuevo empleado. Esto se debe a que la vista materializada no se ha actualizado.

Describiendo la Vista Materializada

Puedes describir la vista materializada usando el comando \d en psql:

\d employee_salaries

Esto te mostrará la definición de la vista materializada y las columnas que contiene.

Actualizar Manualmente una Vista Materializada

En este paso, aprenderá cómo actualizar manualmente una vista materializada en PostgreSQL. Como se mencionó en el paso anterior, las vistas materializadas no se actualizan automáticamente cuando cambian los datos subyacentes. Para reflejar los datos más recientes, debe actualizarlas explícitamente.

Actualización de la Vista Materializada

Para actualizar una vista materializada, utiliza la sentencia REFRESH MATERIALIZED VIEW. Hay dos opciones principales:

• REFRESH MATERIALIZED VIEW nombre_vista: Esto actualizará la vista materializada re-ejecutando la consulta que la define. Adquiere un bloqueo ACCESS EXCLUSIVE sobre la vista materializada, lo que impide el acceso concurrente.

• REFRESH MATERIALIZED VIEW CONCURRENTLY nombre_vista: Esto actualizará la vista materializada sin bloquear consultas concurrentes. Sin embargo, requiere que la vista materializada tenga al menos un índice.

Intentemos primero actualizar la vista materializada employee_salaries utilizando el comando estándar REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW employee_salaries;

Ahora, consulta la vista materializada employee_salaries de nuevo:

SELECT * FROM employee_salaries;

Debería ver que el salario promedio del departamento de TI se ha actualizado para reflejar al nuevo empleado.

Actualización Concurrente

Para actualizar la vista materializada de forma concurrente, primero debemos crear un índice UNIQUE sobre ella. Este es un requisito para las actualizaciones concurrentes, ya que PostgreSQL necesita una forma de identificar unívocamente las filas para realizar la actualización sin bloquear toda la vista. La columna department en nuestra vista employee_salaries es única porque nuestra vista agrupa por departamento, por lo que podemos crear un índice único sobre ella.

Creemos un índice único en la columna department:

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

Ahora, podemos actualizar la vista materializada de forma concurrente:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Consulte la vista materializada employee_salaries de nuevo para confirmar que los datos siguen estando actualizados:

SELECT * FROM employee_salaries;

Elegir el Método de Actualización Correcto

• Utilice REFRESH MATERIALIZED VIEW para vistas materializadas simples o cuando pueda tolerar un breve período de indisponibilidad.
• Utilice REFRESH MATERIALIZED VIEW CONCURRENTLY para vistas materializadas más grandes o cuando necesite minimizar la interrupción de consultas concurrentes. Recuerde crear primero un índice en la vista materializada.

Consideraciones Importantes

• La actualización de una vista materializada puede ser una operación que consume muchos recursos, especialmente para grandes conjuntos de datos.
• Considere programar actualizaciones regulares utilizando una herramienta como cron para mantener actualizados los datos en la vista materializada.

Recuerde salir del shell de psql escribiendo \q y presionando Enter.

Resumen

En este laboratorio, has aprendido a definir una vista simple en PostgreSQL. Empezaste creando una tabla employees con datos de muestra, incluyendo columnas para el ID del empleado, nombre, apellido, departamento y salario. Luego, definiste una vista llamada employee_info que selecciona solo el nombre, apellido y departamento de la tabla employees, demostrando cómo las vistas pueden simplificar las consultas y proporcionar una perspectiva específica sobre los datos subyacentes.

También aprendiste a consultar y modificar datos a través de vistas, y cómo crear y actualizar vistas materializadas. Las vistas materializadas almacenan el resultado de una consulta como una tabla, mejorando el rendimiento para consultas complejas. Exploraste diferentes métodos para actualizar vistas materializadas, incluyendo la actualización concurrente para minimizar las interrupciones.