Introducción a la base de datos relacional PostgreSQL

Publicado por pico.dev el , actualizado el .
blog-stack gnu-linux planeta-codigo planeta-linux programacion
Comentarios

De todas las funcionalidades que tiene SQL muchos desarrolladores solo usamos un pequeño conjunto de las posibilidades del lenguaje. Algunas bases de datos no implementan muchas posibilidades del lenguaje SQL y no son usables en esos sistemas, PostgreSQL es una de las bases de datos relacionales que mejor soporta el estándar ANSI-SQL. Conociendo sus posibilidades podremos implementar funcionalidades de forma más sencilla o con mejor rendimiento.

PostgreSQL

Aún con el reciente auge de las bases de datos NoSQL las bases de datos relacionales siguen siendo la opción usada mayoritariamente para persistir los datos de una aplicación. El potente lenguaje SQL permite obtener, modificar, insertar y eliminar datos de forma declarativa. Una característica deseada de las bases de datos relacionales es la de mantener con transacciones la integridad referencial y consistencia de los datos en todo momento que las bases de datos NoSQL no ofrecen aunque estas últimas a cambio ofrecen mejores opciones para escalar. Por otro lado los datos ya sean en una base de datos relacional o NoSQL seguirán un esquema aunque en este último caso no se exija, las bases de datos relacionales al exigir que los datos sigan un esquema evitará inconsistencias y los tipos de los datos serán los definidos en la tabla de datos en las que se guarden.

De las bases de datos relacionales más utilizadas que tienen una licencia de software libre están MySQL, MariaDB y PostgreSQL, con licencia privativa y comerciales están Microsoft SQL Server, Oracle y DB2 siendo su coste significativo en algunos casos solo alcanzable por grandes organizaciones. PostgreSQL con su licencia de software libre es una de las bases de datos más avanzadas soportando muchas de las opciones definidas en el estándar del lenguaje SQL.

Muchos desarrolladores conocemos las opciones básicas del lenguaje SQL, las sentencias insert, update, delete y select, sin embargo las últimas versiones del lenguaje SQL añade muchas posibilidades que quizá desconozcamos. PostgreSQL por ejemplo soporta inserciones de múltiples filas en una misma sentencia, actualización o inserción con la sentencia upsert, window functions, common table expressions o consultas recursivas. PostgreSQL además tiene un sistema de tipos avanzado pudiendo definir tipos de datos personalizados y funciones sobre esos tipos asi como herencia que son motivos por los cuales se autodenomina una base de datos object-relational.

Veamos algunos ejemplos de estas características del lenguaje SQL y que PostgreSQL soporta siendo una de las bases de datos relacionales más ANSI-SQL compliant. Para los casos demostrativos de las sentencias SQL usaré una base de datos de ejemplo con unas pocas tablas y datos sobre ciudades, países, población y lenguajes obtenida de PgFoundry Sample Databases, hay varias en concreto usaré la base de datos world. En la página de bases de datos ejemplo para PostgreSQL hay otras.

Para una fácil instalación de una instancia de la base de datos PostgreSQL usaré Docker con la que una vez terminados los ejemplos se puede eliminar sin dejar ningún rastro. Si aún no has usado Docker puedes leer la serie de artículos sobre Docker que he escrito.

Instalación PostgreSQL con Docker

Una vez instalado Docker e iniciado su servicio y con el comando docker-compose y el archivo docker-compose.yml que contiene la definición del contenedor lo iniciamos con el comando docker-compose up. El comando docker ps lista los contenedores en ejecución y con el comando docker exec iniciamos un proceso bash en el contenedor indicado con su identificativo.

Comandos básicos del shell psql

El shell de psql usa varios comandos precedidos por una contrabarra para interpretar algunos comandos muy útiles como listar las bases de datos, cambiar de base de datos de trabajo, listar las tablas de una base de datos, mostrar la definición de una tabla para saber sus campos y tipos o salir del shell. Los siguientes son solo unos pocos de los disponibles.

  • \l: lista las bases de datos de la instancia de PostgreSQL.
  • \connect [database]: cambia de base de datos actual de trabajo.
  • \dt: lista las tablas de la base de datos actual de trabajo.
  • \d+ [table]: muestra la definición de una tabla de la base de datos actual de trabajo.
  • \q: sale del intérprete de comandos del shell de PostgreSQL.

  • psql shell

Importación base de datos de ejemplo

Antes de lanzar sentencias SQL hay que crear una base de datos con varias tablas y datos, en este caso usando una base de datos de ejemplo que se descarga con el comando wget, se descomprime, se crea un nuevo esquema y se importan las tablas y datos, finalmente se listas las definiciones de las tablas.

Para probar que la base de datos se ha importado correctamente la siguiente sentencia SQL lista el número de ciudades por país ordenados alfabéticamente o por número de ciudades descendentemente.

Para algunas sentencias usaré una base de datos un poco más sencilla que con una tabla para almacenar empresas.

Inserción múltiple

Si insertamos muchos datos en una misma tabla podemos insertarlos en una única sentencia en vez de múltiples para un mejor rendimiento, evitando enviar al servidor mútiples sentencias individuales.

UPSERT

En algún caso quizá tengamos la necesidad de hacer un insert y si el registro ya existe hacer un update. Usando la expresión ON CONFLICT UPDATE conocida como UPSERT podemos hacer esta operación que nos evitará hacerlo de forma programática en la aplicación.

En el ejemplo, se hace una insert de la empresa Canonical, en el segundo caso como esta empresa ya está creada y hay una restricción en el nombre para que sea único se realiza un update y se actualiza su número de empleados pero no se inserta un nuevo registro duplicado.

Common table expressions

Las cláusula WITH que define las common table expressions o CTE proporcionan una forma de escribir sentencias auxiliares para su uso en una sentencia más grande. Cada sentencia auxiliar de una cláusula WITH puede ser un SELECT, INSERT, UPDATE o DELETE y la sentencia primaria asociada a la cláusula WITH también puede ser un SELECT, INSERT, UPDATE o DELETE.

Window functions

Las window functions realizan cálculos sobre un conjunto de datos que están relacionados de alguna forma con la fila actual. Al contrario que las funciones de agregación el cálculo de las window functions no causan que las filas se agrupen en una única fila manteniéndose como filas separadas.

Usando la base de datos world que contienen ciudades y países con sus poblaciones con la siguiente consulta SQL se obtienen las tres ciudades más pobladas de Alemania, España, Francia e Italia con su porcentaje respecto al total del país. En este caso Berlín es la ciudad más poblada de Alemania con aproximadamente el 12% de la población de ese país. En este caso además de usar windows functions se usa una Common Table Expressions con la cláusula WITH.

Consultas recursivas

El modificador RECURSIVE cambia la sentencia WITH de una conveniencia sintáctica en una funcionalidad que proporciona algo que no sería posible con el SQL que soporta algunas otras bases de datos. Usando RECURSIVE, una cláusula WITH puede referenciar su propia salida. Con esta cláusula las relaciones jerárquicas pueden implementarse sin usar otras soluciones más complejas.

Tipo array, enumerado

Con la ayuda de los arrays podemos definir una columna con un conjunto de valores que en casos simples nos evitarán crear una tabla con una relación 1 a N. Además, con las funciones asociadas a los arrays podemos definir una columna con un conjunto de valores cuyos valores no se repitan o si la lista es un conjunto limitados de valores con un enumerado.

Tipo personalizado

En PostgresSQL se pueden definir nuevos tipos de datos así como nuevas funciones sobre estos tipos de datos. Una vez definidos las columnas de las tablas pueden hacer uso de ellos. Pueden ser:

  • Compuestos: están formados por una lista de nombres de atributos y tipos.
  • Enumerados: son una lista de una o más etiquetas.
  • Rangos
  • Base
  • Arrays: las columnas de una tabla se pueden definir como un array multidimensional de longitud variable. Se pueden crear arrays de cualquier de los tipos incorporados por defecto y de los tipos base, enumerados y compuestos.

Creando tipos de datos personalizados se evita crear en las tablas varios campos de tipos básicos individuales pero relacionados y estos tipos se pueden reutilizar en la definición de varias tablas.

Índices

Los índices cuando son utilizados son una forma que mejora enormemente el rendimiento de una consulta. Permiten buscar y obtener filas específicas mucho más rápido que sin un usar un índice.

Índice parcial

Un índice parcial es un índice construido sobre un subconjunto de una tabla, el subconjunto es definido por una expresión condicional. El índice contiene entradas solo para las filas de la tabla que satisfacen el predicado.

La motivación de los índices parciales es evitar indexar valores comunes. Dado que una búsqueda para un valor común no usará el índice de todas maneras no hay necesidad de mantener esas filas en el índice. Esto reduce el tamaño del índice que hará más rápidas aquellas consultas que lo usen así como las actualizaciones de la tabla ya que no será necesario actualizarlo en todos los casos.

Índices multicolumna

Un índice puede ser definido sobre más de una columna de una tabla. Son apropiados cuando hay consultas con predicados por las dos columnas del índice.

Restricciones, Constraints

Los tipos de datos son una forma de limitar los tipos de datos que pueden ser almacenados en una tabla. Para muchas aplicaciones las restricciones que proporcionan son demasiado simples. Por ejemplo, una columna que contenga el precio de un producto debería aceptar solo valores positivos. Pero no hay un tipo de datos que acepte solo números positivos. Otro problema es que quizá deseemos restringir el dato de una columna respecto a otras columnas o filas. Por ejemplo, en una tabla que contenga información de un producto el número del producto debería ser único.

SQL permite definir restricciones en columnas y tablas proporcionando el control sobre los datos que deseamos. Si se intentan almacenar datos en una columna que viola una restricción se lanza un error.

Tipos de tablas

Si se especifica en la creación de la tabla TEMPORARY o TEMP esta es creada con una tabla temporal que es eliminada al final de la sesión u opcionalmente al finalizar la transacción actual. Si se especifica UNLOGGED es creada como no trazable haciendo que los datos escritos en la tabla no sean escritos en el write-ahead log que lo hace considerablemente más rápido que las tablas ordinarias. Sin embargo, no son seguras ante fallos.

PL/pgSQL

PostgreSQL al igual que otras bases de datos ofrece un lenguaje procedural que puede ser usado para crear procedimientos de funciones o triggers, añadir estructuras de control al lenguaje SQL, realizar cálculos complejos, hereda todos los tipos de usuario, funciones y operadores, puede ser definido como de confianza por el servidor y es fácil de usar. El lenguaje sql es fácil de aprender y es común a las bases de datos relacionales pero cada sentencia SQL debe ser ejecutada individualmente por el servidor. Esto significa que la aplicación cliente debe enviar cada sentencia al servidor, esperar a que sea procesada, recibir y procesar los resultados, realizar algún cálculo y entonces enviar más sentencias al servidor. Todo esto incurre en comunicación entre procesos y de red si el cliente está en una máquina diferente del servidor de base de datos.

Con PL/pgSQL se puede crear un bloque de computación y una serie de sentencias SQL dentro del servidor de base de datos, tiendo el poder de un lenguaje procedural y la facilidad de SQL pero con un considerable ahorro de comunicación entre cliente y servidor. Las ventajas son evitar viajes entre el servidor y el cliente, resultados inmediatos que no son necesarios convertir y transferir entre el cliente y servidor y múltiples pasos de procesado de las sentencias son evitados. Todo esto resulta en algunos casos un incremento de rendimiento considerable comparado con una aplicación que no usa procedimientos almacenados.

Otras

Otros elementos que soporta la base de datos PostgreSQL en el lenguaje SQL son Grouping Sets, ROLLUP, CUBE, Set Returning Functions, tablefunc, búsquedas a texto completo que para casos sencillos no hace falta recurrir a soluciones más especializadas como Elasticsearch, selección y bloqueo de filas con la clásula FOR UPDATE, vistas y vistas materializadas entre seguro otras muchas cosas de las que me olvido o desconozco.

Por todas estas características se considera a PostgreSQL una de las bases de datos relacionales más avanzadas existentes. Hay mucha literatura sobre las bases de datos relacionales desde el lenguage SQL en general, libros específicos sobre PostgreSQL o como evitar errores diseño al estructurar la información en tablas y columnas.

En caso de optar por una base de datos NoSQL para persistir la información la base de datos NoSQL MongoDB permite guardar los datos en forma de documentos y conseguir la escalabilidad que las bases de datos relacionales con sus propiedades ACID es difícil.