Gestión de Vistas en PostgreSQL

Introducción

En este laboratorio, explorará la Gestión de Vistas de PostgreSQL. El objetivo principal es comprender e implementar diferentes tipos de vistas, incluyendo vistas simples y vistas materializadas (materialized views).

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

Skills Graph

Definir una Vista Simple

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

Comprendiendo las Vistas

Una vista es esencialmente una consulta almacenada. Cuando consulta una vista, PostgreSQL ejecuta la consulta subyacente y devuelve el conjunto de resultados como si fuera una tabla real. Las vistas no almacenan datos en 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. Abra una terminal y conéctese a la base de datos PostgreSQL como el usuario postgres:

sudo -u postgres psql

Ahora, cree 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, inserte algunos datos de ejemplo 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);

Puede verificar los datos ejecutando la siguiente consulta:

SELECT * FROM employees;

Debería 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, el apellido y el departamento de cada empleado. Podemos definir una vista llamada employee_info utilizando la siguiente sentencia SQL:

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

Esta sentencia 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, puede usar una sentencia SELECT como lo haría con una tabla normal:

SELECT * FROM employee_info;

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

Describiendo la Vista

Puede describir la vista utilizando el comando \d en psql:

\d employee_info

Esto le 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á cómo 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 utilizar 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. Puede utilizar una sentencia SELECT para recuperar datos de la vista como si fuera una tabla normal.

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

SELECT * FROM employee_info;

También puede utilizar cláusulas WHERE y otras construcciones SQL para filtrar y ordenar los datos:

SELECT * FROM employee_info WHERE department = 'Sales';

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

Modificar Datos a Través de Vistas

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

• Selecciona desde una sola tabla.
• No contiene funciones de agregación (aggregate functions) (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 sentencia actualiza el salario del empleado con employee_id 1 a 65000.00.

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

SELECT * FROM employees WHERE employee_id = 1;

Debería ver que el salario para employee_id 1 se ha actualizado.

Insertar Datos a Través de Vistas

También puede insertar datos a través de una vista, siempre que la vista incluya todas las columnas no anulables (non-nullable columns) 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);

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

Verifique la inserción:

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

Eliminar Datos a Través de Vistas

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

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

Verifique 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 joins, agregaciones u otras operaciones complejas suelen ser de solo lectura (read-only).
• Modificar datos a través de vistas puede tener implicaciones en el rendimiento. PostgreSQL necesita traducir las operaciones de la vista en operaciones en las tablas base subyacentes.
• Tenga 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á cómo crear y poblar una vista materializada (materialized view) en PostgreSQL. A diferencia de las vistas regulares, las vistas materializadas almacenan el conjunto de resultados (result set) 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 en una vista materializada no se actualizan automáticamente cuando cambian los datos subyacentes. Debe actualizarla manualmente o programarla para que se actualice periódicamente.

Crear una Vista Materializada

Para crear una vista materializada, utilice la sentencia CREATE MATERIALIZED VIEW. Creemos una vista materializada llamada employee_salaries que muestre el salario promedio para cada departamento.

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

Esta sentencia crea una vista materializada llamada employee_salaries que calcula el salario promedio para cada departamento basándose en los datos de la tabla employees.

Consultar la Vista Materializada

Puede consultar una vista materializada como si fuera una tabla normal:

SELECT * FROM employee_salaries;

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

Poblar la Vista Materializada

Cuando crea una vista materializada, se completa automáticamente 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.

Insertemos un nuevo empleado en la tabla employees:

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

Ahora, si consulta la vista materializada employee_salaries nuevamente:

SELECT * FROM employee_salaries;

Notará que el salario promedio para el departamento de IT no ha cambiado para reflejar el nuevo empleado. Esto se debe a que la vista materializada no se ha actualizado.

Describir la Vista Materializada

Puede describir la vista materializada utilizando el comando \d en psql:

\d employee_salaries

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

Actualizar una Vista Materializada Manualmente

En este paso, aprenderá cómo actualizar manualmente una vista materializada (materialized view) 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.

Actualizar la Vista Materializada

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

• REFRESH MATERIALIZED VIEW view_name: Esto actualizará la vista materializada volviendo a ejecutar la consulta que la define. Adquiere un bloqueo ACCESS EXCLUSIVE en la vista materializada, lo que impide el acceso concurrente.

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

Primero, intentemos actualizar la vista materializada employee_salaries utilizando el comando estándar REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW employee_salaries;

Ahora, consulte la vista materializada employee_salaries nuevamente:

SELECT * FROM employee_salaries;

Debería ver que el salario promedio para el departamento de IT ahora se ha actualizado para reflejar el nuevo empleado.

Actualizar Concurrentemente

Para actualizar la vista materializada concurrentemente, primero debemos crear un índice en ella. Creemos un índice en la columna department:

CREATE INDEX idx_employee_salaries_department ON employee_salaries (department);

Ahora, podemos actualizar la vista materializada concurrentemente:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Consulte la vista materializada employee_salaries nuevamente para confirmar que los datos aún están 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 las consultas concurrentes. Recuerde crear un índice en la vista materializada primero.

Consideraciones Importantes

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

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

Resumen

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

También aprendió cómo consultar y modificar datos a través de las vistas, y cómo crear y actualizar vistas materializadas (materialized views). Las vistas materializadas almacenan el resultado de una consulta como una tabla, lo que mejora el rendimiento para consultas complejas. Exploró diferentes métodos para actualizar las vistas materializadas, incluida la actualización concurrente para minimizar la interrupción.