Fundamentos de la gestión de esquemas en PostgreSQL

¿Te preguntas qué son los esquemas en Postgresql y por qué son importantes y cómo puedes usar los esquemas para hacer tus implementaciones de bases de datos más robustas y mantenibles? Este artículo introducirá los fundamentos de los esquemas en Postgresql y le mostrará cómo crearlos con algunos ejemplos básicos. Futuros artículos profundizarán en ejemplos de cómo asegurar y utilizar los esquemas para aplicaciones reales.

En primer lugar, para aclarar posibles confusiones terminológicas, entendamos que en el mundo de Postgresql, el término «esquema» está quizás algo sobrecargado por desgracia. En el contexto más amplio de los sistemas de gestión de bases de datos relacionales (RDBMS), el término «esquema» podría entenderse como el diseño lógico o físico global de la base de datos, es decir, la definición de todas las tablas, columnas, vistas y otros objetos que constituyen la definición de la base de datos. En ese contexto más amplio, un esquema podría expresarse en un diagrama entidad-relación (ER) o en un script de sentencias del lenguaje de definición de datos (DDL) utilizado para instanciar la base de datos de la aplicación.

En el mundo de Postgresql, el término «esquema» podría entenderse mejor como un «espacio de nombres». De hecho, en las tablas del sistema Postgresql, los esquemas se registran en columnas de la tabla denominadas «espacio de nombres», que, en mi opinión, es una terminología más precisa. Como cuestión práctica, siempre que veo «esquema» en el contexto de Postgresql lo reinterpreto silenciosamente como si dijera «espacio de nombres».

Pero te preguntarás: «¿Qué es un espacio de nombres?» En general, un espacio de nombres es un medio bastante flexible para organizar e identificar la información por su nombre. Por ejemplo, imaginemos dos hogares vecinos, los Smith, Alicia y Bob, y los Jones, Bob y Cathy (véase la figura 1). Si sólo utilizáramos los nombres de pila, podría resultar confuso saber a qué persona nos referimos cuando hablamos de Bob. Pero añadiendo el apellido, Smith o Jones, identificamos de forma única a qué persona nos referimos.

A menudo, los espacios de nombres se organizan en una jerarquía anidada. Esto permite una clasificación eficiente de grandes cantidades de información en una estructura muy fina, como, por ejemplo, el sistema de nombres de dominio de Internet. En el nivel superior, «.com», «.net», «.org», «.edu», etc., definen amplios espacios de nombres dentro de los cuales se registran los nombres de entidades específicas, así, por ejemplo, «severalnines.com» y «postgresql.org» están definidos de forma única. Pero bajo cada uno de ellos hay una serie de subdominios comunes como «www», «mail» y «ftp», por ejemplo, que por sí solos son duplicados, pero dentro de los respectivos espacios de nombres son únicos.

Los esquemas de Postgresql sirven para este mismo propósito de organizar e identificar, sin embargo, a diferencia del segundo ejemplo anterior, los esquemas de Postgresql no pueden ser anidados en una jerarquía. Aunque una base de datos puede contener muchos esquemas, sólo hay un nivel y, por tanto, dentro de una base de datos, los nombres de los esquemas deben ser únicos. Además, cada base de datos debe incluir al menos un esquema. Cada vez que se instala una nueva base de datos, se crea un esquema por defecto llamado «public». El contenido de un esquema incluye todos los demás objetos de la base de datos, como tablas, vistas, procedimientos almacenados, triggers, etc. Para visualizarlo, consulte la Figura 2, que representa un anidamiento tipo muñeca matrioska que muestra dónde encajan los esquemas en la estructura de una base de datos Postgresql.

Además de organizar simplemente los objetos de la base de datos en grupos lógicos para hacerlos más manejables, los esquemas tienen el propósito práctico de evitar la colisión de nombres. Un paradigma operativo consiste en definir un esquema para cada usuario de la base de datos a fin de proporcionar cierto grado de aislamiento, un espacio en el que los usuarios puedan definir sus propias tablas y vistas sin interferir entre sí. Otro enfoque consiste en instalar herramientas de terceros o extensiones de bases de datos en esquemas individuales para mantener todos los componentes relacionados lógicamente juntos. En un artículo posterior de esta serie se detallará un enfoque novedoso para el diseño de aplicaciones robustas, empleando los esquemas como medio de indirección para limitar la exposición del diseño físico de la base de datos y, en su lugar, presentar una interfaz de usuario que resuelva las claves sintéticas y facilite el mantenimiento a largo plazo y la gestión de la configuración a medida que evolucionan los requisitos del sistema.

¡Hagamos algo de código!

El comando más simple para crear un esquema dentro de una base de datos es

CREATE SCHEMA hollywood;

Este comando requiere privilegios de creación en la base de datos, y el esquema recién creado «hollywood» será propiedad del usuario que invoca el comando. Una invocación más compleja puede incluir elementos opcionales que especifiquen un propietario diferente, e incluso puede incluir sentencias DDL que instancien objetos de base de datos dentro del esquema, ¡todo en un solo comando!

El formato general es

CREATE SCHEMA schemaname ]

donde «username» es quien será el propietario del esquema y «schema_element» puede ser uno de ciertos comandos DDL (consulte la documentación de Postgresql para más detalles). Se requieren privilegios de superusuario para utilizar la opción AUTHORIZATION.

Así que, por ejemplo, para crear un esquema llamado «hollywood» que contenga una tabla llamada «films» y una vista llamada «winners» en un solo comando, se podría hacer

CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

Los objetos adicionales de la base de datos se pueden crear posteriormente de forma directa, por ejemplo, se añadiría una tabla adicional al esquema con

CREATE TABLE hollywood.actors (name text, dob date, gender text);

Nótese en el ejemplo anterior el prefijo del nombre de la tabla con el nombre del esquema. Esto es necesario porque por defecto, es decir, sin especificación explícita del esquema, los nuevos objetos de la base de datos se crean dentro de lo que es el esquema actual, que cubriremos a continuación.

Recuerde cómo en el primer ejemplo de espacio de nombres anterior, teníamos dos personas llamadas Bob, y describimos cómo desconfigurar o distinguirlas incluyendo el apellido. Pero dentro de cada uno de los hogares Smith y Jones por separado, cada familia entiende que «Bob» se refiere al que va con ese hogar en particular. Así, por ejemplo, en el contexto de cada hogar respectivo, Alice no necesita dirigirse a su marido como Bob Jones, y Cathy no necesita referirse a su marido como Bob Smith: cada uno puede decir simplemente «Bob».

El esquema actual de Postgresql es algo así como el hogar del ejemplo anterior. Los objetos en el esquema actual pueden ser referenciados sin calificar, pero referirse a objetos con nombres similares en otros esquemas requiere calificar el nombre anteponiendo el nombre del esquema como arriba.

El esquema actual se deriva del parámetro de configuración «search_path». Este parámetro almacena una lista separada por comas de nombres de esquemas y puede examinarse con el comando

SHOW search_path;

o establecer un nuevo valor con

SET search_path TO schema ;

El primer nombre de esquema de la lista es el «esquema actual» y es donde se crean los nuevos objetos si se especifican sin calificar el nombre del esquema.

La lista de nombres de esquemas separada por comas también sirve para determinar el orden de búsqueda por el que el sistema localiza los objetos existentes sin calificar. Por ejemplo, volviendo al vecindario de Smith y Jones, una entrega de paquetes dirigida sólo a «Bob» requeriría visitar cada hogar hasta encontrar al primer residente llamado «Bob». Tenga en cuenta que éste podría no ser el destinatario previsto. La misma lógica se aplica a Postgresql. El sistema busca tablas, vistas y otros objetos dentro de los esquemas en el orden de search_path, y luego se utiliza el primer objeto con nombre encontrado. Los objetos con nombre calificados por el esquema se utilizan directamente sin referencia a search_path.

En la configuración por defecto, la consulta de la variable de configuración search_path revela este valor

SHOW search_path; Search_path-------------- "$user", public

El sistema interpreta el primer valor mostrado arriba como el nombre del usuario actual conectado y acomoda el caso de uso mencionado anteriormente donde cada usuario tiene asignado un esquema con nombre de usuario para un espacio de trabajo separado de otros usuarios. Si no se ha creado tal esquema con nombre de usuario, esa entrada se ignora y el esquema «público» se convierte en el esquema actual en el que se crean los nuevos objetos.

Así, volviendo a nuestro ejemplo anterior de crear la tabla «hollywood.actors», si no hubiéramos calificado el nombre de la tabla con el nombre del esquema, entonces la tabla se habría creado en el esquema público. Si anticipamos la creación de todos los objetos dentro de un esquema específico, entonces podría ser conveniente establecer la variable search_path como

SET search_path TO hollywood,public;

facilitando la abreviatura de escribir nombres no calificados para crear o acceder a los objetos de la base de datos.

También hay una función de información del sistema que devuelve el esquema actual con una consulta

select current_schema();

En caso de engordar la ortografía, el propietario de un esquema puede cambiar el nombre, siempre que el usuario también tenga privilegios de creación para la base de datos, con el

ALTER SCHEMA old_name RENAME TO new_name;

Y por último, para eliminar un esquema de una base de datos, existe un comando drop

DROP SCHEMA schema_name;

El comando DROP fallará si el esquema contiene algún objeto, por lo que éste debe ser eliminado primero, u opcionalmente se puede eliminar recursivamente un esquema todo su contenido con la opción CASCADE

DROP SCHEMA schema_name CASCADE;

¡Estos fundamentos te harán empezar a entender los esquemas!