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:sqlNota: después de instalarla, normalmente debes configurar
CREATE EXTENSION pg_stat_statements;
1shared_preload_libraries = 'pg_stat_statements'
enpostgresql.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:sql
CREATE EXTENSION pg_trgm;
1
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:sql
CREATE EXTENSION hstore;
1
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:sql
CREATE EXTENSION citext;
1
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:sqlNota: el nombre de la extensión contiene un guion y debe ir entre comillas dobles en el comando
CREATE EXTENSION "uuid-ossp";
1CREATE 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:sql
CREATE EXTENSION intarray;
1
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:sql
CREATE EXTENSION btree_gin;
1
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:sql
CREATE EXTENSION btree_gist;
1
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:sql
CREATE EXTENSION cube;
1
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:sql
CREATE EXTENSION fuzzystrmatch;
1
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 ejecutandoCREATE EXTENSION
. - Comando de instalación:sql
CREATE EXTENSION plpgsql;
1
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:sql
CREATE EXTENSION tablefunc;
1
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:sql
CREATE EXTENSION dblink;
1
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:sql
CREATE EXTENSION postgres_fdw;
1
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:sql
CREATE EXTENSION pgcrypto;
1
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:
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 espostgres
, y la base de datos también se llamapostgres
.bashpsql -U your_username -d your_database
1Por ejemplo, para conectarte a la base de datos por defecto:
bashpsql -U postgres -d postgres
1Si se solicita contraseña, ingresa la que configuraste para el usuario root/postgres en ServBay.
Instala la extensión: Una vez conectado, estarás en la consola interactiva de
psql
. Ejecuta el comandoCREATE EXTENSION
para instalar la extensión deseada, sustituyendomodule_name
por el nombre correspondiente.sqlCREATE EXTENSION module_name;
1Por ejemplo, para instalar
pg_trgm
:sqlCREATE EXTENSION pg_trgm;
1Verifica 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.sql\dx
1
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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.