Módulos oficiales de extensión de PostgreSQL

ServBay, como un potente entorno local de desarrollo web, integra diversas herramientas para desarrolladores, incluyendo la versátil base de datos PostgreSQL. La fortaleza de PostgreSQL no solo reside en su núcleo, sino también en su extenso ecosistema de extensiones oficiales. ServBay viene con la mayoría de los módulos oficiales de PostgreSQL preinstalados, permitiendo a los desarrolladores activarlos y utilizarlos fácilmente en el entorno local. Esto amplía enormemente las capacidades de la base de datos y responde a demandas complejas de diversas aplicaciones.

Este artículo presenta algunas de las extensiones oficiales más usadas de PostgreSQL y te guía paso a paso para instalarlas y utilizarlas en ServBay.

Resumen de extensiones comunes

PostgreSQL ofrece una amplia gama de extensiones, desde monitorización de rendimiento hasta nuevos tipos de datos, búsqueda de texto completo y más. A continuación se enlistan algunos módulos de extensión que suelen ser útiles en el trabajo diario de desarrollo:

1. pg_stat_statements

• Función: Recopila y registra estadísticas sobre todas las sentencias SQL ejecutadas por el servidor, incluyendo número de llamadas, tiempo total de ejecución, número de filas procesadas, entre otros.
• Uso: Ayuda a administradores y desarrolladores de bases de datos a analizar el rendimiento de las consultas, identificando consultas lentas y cuellos de botella para su optimización.
• Comando de instalación:

  CREATE EXTENSION pg_stat_statements;

  Nota: después de instalarla, normalmente debes configurar shared_preload_libraries = 'pg_stat_statements' en postgresql.conf y reiniciar el servicio de PostgreSQL para que recopile datos correctamente. ServBay facilita la modificación de estas configuraciones.

2. pg_trgm

• Función: Proporciona funciones para medir la similitud de texto y soporte de índices usando trigramas (secuencias de tres caracteres).
• Uso: Muy útil para implementar búsquedas de coincidencia difusa, búsqueda por similitud y texto completo, especialmente para manejar errores ortográficos o variantes.
• Comando de instalación:

  CREATE EXTENSION pg_trgm;

3. hstore

• Función: Añade un tipo de dato para almacenar colecciones de pares clave-valor en un solo valor de PostgreSQL.
• Uso: Ideal para situaciones donde es necesario almacenar propiedades dinámicas, datos semi-estructurados o preferencias de usuario, sin definir un esquema estricto de tablas.
• Comando de instalación:

  CREATE EXTENSION hstore;

4. citext

• Función: Proporciona un tipo de datos de cadena de texto que no distingue entre mayúsculas y minúsculas.
• Uso: Perfecto para almacenar y comparar nombres de usuario, correos electrónicos, etiquetas y otros datos donde la sensibilidad a mayúsculas/minúsculas no es deseada. Simplifica la lógica evitando el uso repetido de LOWER() en consultas.
• Comando de instalación:

  CREATE EXTENSION citext;

5. uuid-ossp

• Función: Ofrece funciones para generar identificadores únicos universales (UUID), soportando varios estándares como UUID versión 1, 3, 4 y 5.
• Uso: Útil para generar claves primarias globalmente únicas, identificadores de sesión o marcas de unicidad en sistemas distribuidos.
• Comando de instalación:

  CREATE EXTENSION "uuid-ossp";

  Nota: el nombre de la extensión contiene un guion y debe ir entre comillas dobles en el comando CREATE EXTENSION.

6. intarray

• Función: Añade funciones y operadores extra para manipular arrays de enteros en PostgreSQL.
• Uso: Ideal cuando necesitas realizar operaciones de conjunto (intersección, unión), búsquedas de inclusión, ordenamientos y otras operaciones complejas directamente sobre arrays de enteros en la base de datos.
• Comando de instalación:

  CREATE EXTENSION intarray;

7. btree_gin

• Función: Permite utilizar índices GIN (Generalized Inverted Index) con tipos de datos B-tree comunes como enteros, texto y fechas.
• Uso: GIN es útil para índices que incluyen múltiples valores (como arrays, hstore, tsvector). btree_gin posibilita usar GIN para elementos B-tree dentro de estos tipos, optimizando las consultas.
• Comando de instalación:

  CREATE EXTENSION btree_gin;

8. btree_gist

• Función: Añade soporte para índices GiST (Generalized Search Tree) en tipos de datos B-tree.
• Uso: GiST es una estructura de índice versátil. btree_gist permite usar GiST en datos indexables por B-tree, útil para consultas por rango, búsquedas de contención o creación de índices multicolumna.
• Comando de instalación:

  CREATE EXTENSION btree_gist;

9. cube

• Función: Define un tipo de dato para representar cubos multidimensionales o puntos, incluyendo sus operadores y funciones.
• Uso: Esencial para trabajar con datos multidimensionales, como información geoespacial (puntos en 2D o 3D), análisis multidimensional en data warehouses o ciertos cálculos científicos.
• Comando de instalación:

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Función: Ofrece varias funciones para determinar similitud y distancia entre cadenas, incluyendo distancia de Levenshtein, Soundex, Metaphone y otros algoritmos.
• Uso: Para implementación de coincidencia difusa de cadenas, detección de errores de escritura, limpieza y normalización de datos.
• Comando de instalación:

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• Función: Procedural Language para PostgreSQL, permite escribir procedimientos almacenados, funciones trigger y bloques de código anónimos.
• Uso: Permite implementar lógica avanzada, tareas automatizadas y flujos de procesamiento de datos directamente en el servidor de la base de datos. plpgsql suele estar instalado por defecto, pero puedes asegurarte ejecutando CREATE EXTENSION.
• Comando de instalación:

  CREATE EXTENSION plpgsql;

12. tablefunc

• Función: Incorpora funciones para manipular y transformar tablas completas, destacando la función crosstab.
• Uso: Realiza consultas tipo tabla dinámica (pivot), convirtiendo valores únicos de una columna en columnas individuales. Muy empleado para reportes y análisis de datos.
• Comando de instalación:

  CREATE EXTENSION tablefunc;

13. dblink

• Función: Permite conectar y ejecutar consultas desde una base de datos PostgreSQL a otra, todo desde dentro del mismo servidor.
• Uso: Facilita consultas entre diferentes bases de datos o migraciones de datos, sin tener que gestionar la conexión cruzada desde la capa de aplicación.
• Comando de instalación:

  CREATE EXTENSION dblink;

14. postgres_fdw

• Función: Implementa un envoltorio de datos externos (Foreign Data Wrapper) para acceder y consultar tablas de bases de datos PostgreSQL remotas como si fuesen locales.
• Uso: Permite la integración e interconsulta entre bases de datos de forma más potente y flexible que dblink, soportando optimizaciones en consultas complejas.
• Comando de instalación:

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• Función: Proporciona funciones para encriptación y desencriptación, incluyendo hash, cifrado simétrico o de clave pública.
• Uso: Crucial para almacenar contraseñas cifradas, encriptar datos sensibles o implementar firmas digitales directamente desde la base de datos.
• Comando de instalación:

  CREATE EXTENSION pgcrypto;

Estos son solo algunos de los módulos oficiales de extensión de PostgreSQL incluidos en ServBay. La plataforma se esfuerza por incorporar la mayoría de los complementos oficiales de la comunidad PostgreSQL para cubrir todas las necesidades de los desarrolladores.

Instalación y uso de extensiones PostgreSQL en ServBay

Instalar y utilizar módulos de extensión en el entorno ServBay es sumamente sencillo. Recuerda que las extensiones se instalan en una base de datos específica, lo que significa que debes habilitar cada extensión en cada base de datos donde la vayas a utilizar.

Requisitos previos:

• Asegúrate de que ServBay esté en ejecución y que el servicio de PostgreSQL esté encendido.

Pasos a seguir:

1. Conéctate a la base de datos destino de PostgreSQL: Abre tu terminal (por ejemplo, Terminal o iTerm2 en macOS) y utiliza la herramienta de línea de comandos psql para conectarte a la base donde planeas instalar la extensión. Por defecto, el usuario de PostgreSQL en ServBay es postgres, y la base de datos también se llama postgres.

   psql -U your_username -d your_database

   Por ejemplo, para conectarte a la base de datos por defecto:

   psql -U postgres -d postgres

   Si se solicita contraseña, ingresa la que configuraste para el usuario root/postgres en ServBay.

2. Instala la extensión: Una vez conectado, estarás en la consola interactiva de psql. Ejecuta el comando CREATE EXTENSION para instalar la extensión deseada, sustituyendo module_name por el nombre correspondiente.

   CREATE EXTENSION module_name;

   Por ejemplo, para instalar pg_trgm:

   CREATE EXTENSION pg_trgm;

3. Verifica la instalación: Tras finalizar, utiliza el comando \dx para listar todas las extensiones instaladas en la base de datos y confirmar que tu extensión se agregó correctamente.

   \dx

Ejemplo: Activar la extensión pg_trgm

A continuación, un ejemplo completo de cómo conectar a la base postgres y habilitar la extensión pg_trgm usando la línea de comandos de psql:

# Ejecuta el siguiente comando en la terminal para conectarte a la base
psql -U postgres -d postgres

# Una vez conectado, verás el prompt de psql
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- Muestra las extensiones actualmente instaladas (por defecto pueden estar solo plpgsql y pgcrypto)
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- Instalar la extensión pg_trgm
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- Verifica que pg_trgm está en la lista de extensiones instaladas
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- ¡Instalación exitosa! Ahora puedes usar las funciones y operadores de pg_trgm en esta base de datos

Consultar las extensiones disponibles de PostgreSQL en ServBay

Antes de instalar, puede ser útil conocer todas las extensiones disponibles que vienen incluidas con la versión de PostgreSQL integrada en ServBay. Esto te ayudará a descubrir más utilidades potencialmente valiosas.

Una vez conectada cualquier base de datos de PostgreSQL, ejecuta esta consulta SQL para ver la lista completa de extensiones:

SELECT * FROM pg_available_extensions ORDER BY name;

La consulta mostrará una tabla con todas las extensiones soportadas por la versión de PostgreSQL empaquetada con ServBay, detallando nombre, versión por defecto, si están instaladas y su descripción.

Un ejemplo típico del resultado obtenido (la lista puede ser extensa y los nombres/versiones pueden variar dependiendo de la versión de PostgreSQL/SewrvBay):

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2             |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

Ten en cuenta que la lista mostrada es solo un ejemplo; la lista y las versiones disponibles en tu instalación real de ServBay pueden variar según la versión de ServBay o PostgreSQL.

Conclusión

ServBay simplifica enormemente la utilización de potentes funcionalidades avanzadas en PostgreSQL gracias a su amplio conjunto de extensiones oficiales preinstaladas. Así, los desarrolladores pueden activarlas fácilmente mediante simples comandos SQL, sin tener que compilar ni instalar nada adicional, permitiéndoles concentrarse en el desarrollo de sus aplicaciones y aumentar la productividad. Utiliza estas extensiones para potenciar el rendimiento, implementar búsquedas avanzadas, reforzar la seguridad de tus datos y agregar capacidades a tu proyecto con solo unos pocos pasos.