Domina herramientas y mejores prácticas de DevOps - DevOps Knowledge Hub Domina herramientas y mejores prácticas de DevOps - DevOps Knowledge Hub

Optimización de los Foreign Data Wrappers de PostgreSQL para la integración de múltiples bases de datos

Usa FDWs de PostgreSQL para consultar bases de datos remotas de forma segura, optimizar el pushdown y evitar los problemas comunes de rendimiento en la federación.

Optimización de Foreign Data Wrappers de PostgreSQL para Integración Multi-Base de Datos

Los Foreign Data Wrappers de PostgreSQL ayudan cuando tus datos residen en más de un lugar y necesitas consultarlos sin copiarlos primero. Los FDWs pueden hacer que las tablas remotas parezcan locales, pero el rendimiento y el comportamiento de las transacciones dependen en gran medida del wrapper, la red y la forma de la consulta.

¿Qué son los Foreign Data Wrappers?

FDW implementa el estándar SQL/MED (Management of External Data), proporcionando:

  • Acceso transparente: Consulta datos remotos usando SQL estándar
  • Tablas virtuales: No se requiere duplicación de datos
  • Lecturas remotas en vivo: Las consultas leen de la fuente remota en lugar de una copia local
  • Capacidad de unión: Combina datos locales y remotos
  • Operaciones de escritura: Algunos wrappers soportan INSERT, UPDATE y DELETE

Casos de Uso Comunes

  1. Migración de Base de Datos: Accede a sistemas heredados durante la migración
  2. Arquitectura Multi-Inquilino: Consulta a través de bases de datos de inquilinos
  3. Agregación de Datos: Combina datos de múltiples fuentes
  4. Nube Híbrida: Conecta bases de datos locales y en la nube
  5. Reportes: Crea una base de datos de reportes centralizada
  6. Microservicios: Consulta a través de límites de servicio

Foreign Data Wrappers Disponibles

postgresql_fdw (Integrado)

  • Conecta a otras bases de datos PostgreSQL
  • Soporte completo de características
  • Mejor rendimiento

mysql_fdw

  • Conecta a MySQL/MariaDB
  • Soporte de lectura y escritura

oracle_fdw

  • Conecta a bases de datos Oracle
  • Comúnmente usado a través de la extensión de código abierto oracle_fdw, con las bibliotecas cliente de Oracle instaladas por separado

mongodb_fdw

  • Accede a colecciones MongoDB
  • Integración de datos JSON

file_fdw (Integrado)

  • Lee archivos CSV y de texto
  • Análisis de archivos de registro

Otros

  • redis_fdw, sqlite_fdw, couchdb_fdw, etc.

Configuración de postgres_fdw

Paso 1: Instalar la Extensión

-- En la base de datos local
CREATE EXTENSION postgres_fdw;

Paso 2: Crear Servidor Remoto

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

Paso 3: Crear Mapeo de Usuario

-- Mapear usuarios locales a credenciales remotas
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- Mapear todos los usuarios locales
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

Paso 4: Crear Tablas Foráneas

Definición manual de tabla:

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

Importar esquema completo:

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

Importar tablas específicas:

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

Paso 5: Consultar Datos Foráneos

-- Consulta simple
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- Unir datos locales y remotos
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

Configuración Avanzada

Opciones de Ajuste de Rendimiento

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',
        
        -- Tamaño de recuperación: filas obtenidas por viaje redondo
        fetch_size '50000',
        
        -- Habilitar optimizaciones de pushdown de consultas
        use_remote_estimate 'true',
        
        -- Reutilizar conexiones de servidor foráneo dentro de sesiones de PostgreSQL
        keep_connections 'on',
        
        -- Ejecución de consultas en paralelo
        parallel_commit 'on',
        parallel_abort 'on'
    );

Opciones a Nivel de Tabla

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',
    
    -- Sobrescribir el tamaño de recuperación para esta tabla
    fetch_size '100000',
    
    -- Usar estimaciones remotas para una mejor planificación de consultas
    use_remote_estimate 'true'
);

Opciones a Nivel de Columna

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

Operaciones de Escritura

Habilitar Operaciones de Escritura

-- Conceder permisos
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- Insertar datos
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- Actualizar datos
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- Eliminar datos
DELETE FROM remote_users WHERE username = 'newuser';

Transacciones a Través de Bases de Datos

BEGIN;
    -- Insertar en tabla local
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);
    
    -- Actualizar tabla remota
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;
    
    -- Ambas operaciones se confirman juntas
COMMIT;

PostgreSQL coordina el trabajo remoto a través del FDW, pero no debes asumir que cada wrapper te da garantías completas de transacciones distribuidas. Para postgres_fdw, revisa la opción de servidor two_phase_commit si necesitas comportamiento de confirmación en dos fases, y prueba la recuperación ante fallos antes de confiar en ella para escrituras críticas.

Federación Multi-Base de Datos

Conectando Múltiples Bases de Datos

-- Conectar a base de datos de producción
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- Conectar a base de datos de análisis
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- Conectar a base de datos de archivo
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

Vistas Unificadas a Través de Bases de Datos

-- Importar tablas
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- Crear vista unificada
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- Consultar a través de todas las bases de datos
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

Conectando a MySQL

Instalar mysql_fdw

# Los nombres de los paquetes varían según la versión de PostgreSQL y el repositorio.
# En Debian/Ubuntu, busca primero:
apt search mysql-fdw

# Luego instala el paquete que coincida con tu versión principal de PostgreSQL.
sudo apt install postgresql-16-mysql-fdw

Configurar Conexión MySQL

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- Consultar datos de MySQL desde PostgreSQL
SELECT * FROM mysql_products WHERE price > 100;

Optimización del Rendimiento

1. Usar Pushdown de Clausula WHERE

PostgreSQL empuja filtros al servidor remoto:

-- Bueno: Filtro aplicado remotamente
SELECT * FROM remote_users WHERE country = 'US';

-- Verificar plan de consulta
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- Buscar: "Remote SQL: SELECT ... WHERE country = 'US'"

2. Limitar Transferencia de Datos

-- Malo: Obtiene todas las columnas
SELECT * FROM remote_large_table;

-- Bueno: Solo columnas necesarias
SELECT id, username FROM remote_large_table;

-- Bueno: Usar LIMIT
SELECT * FROM remote_large_table LIMIT 1000;

3. Optimizar Tamaño de Recuperación

-- Para conjuntos de resultados grandes
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- Para consultas pequeñas y frecuentes
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. Usar Estimaciones Remotas

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- PostgreSQL consultará EXPLAIN remoto para estimaciones de costos precisas

5. Índices en Tablas Remotas

-- Asegurar que las tablas remotas tengan índices apropiados
-- Conectar directamente a la base de datos remota:

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. Vistas Materializadas para Datos de Acceso Frecuente

-- Almacenar en caché datos remotos localmente
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- Actualizar periódicamente
REFRESH MATERIALIZED VIEW cached_remote_data;

-- Auto-actualizar con pg_cron si esa extensión está instalada
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Monitoreo y Solución de Problemas

Ver Servidores Foráneos

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

Ver Tablas Foráneas

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

Verificar Mapeos de Usuario

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

Analizar Rendimiento de Consultas

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

Verificación de Conexión

Usa EXPLAIN (ANALYZE, VERBOSE) para confirmar el SQL remoto y el tiempo. Para enlaces PostgreSQL a PostgreSQL, también verifica pg_stat_activity en el servidor remoto mientras se ejecuta una consulta FDW larga.

Problemas Comunes y Soluciones

Problema 1: Tiempo de Espera de Conexión

Error: could not connect to server

Soluciones:

-- Agregar tiempo de espera de conexión
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- Verificar conectividad de red
-- Desde el shell:
psql -h remote-db.example.com -U user -d dbname

Problema 2: Consultas Lentas

Soluciones:

-- Habilitar estimaciones remotas
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- Aumentar tamaño de recuperación
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Verificar si la cláusula WHERE se empuja hacia abajo
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

Problema 3: Fallo de Autenticación

Error: password authentication failed

Soluciones:

-- Actualizar mapeo de usuario
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- Verificar pg_hba.conf en el servidor remoto
-- Asegurar que permite conexiones desde el servidor local

Problema 4: Desajuste de Tipo de Columna

Error: column type mismatch

Solución:

-- Convertir columnas explícitamente
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- O importar con tipos correctos
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

Consideraciones de Seguridad

1. Usar Cuentas de Solo Lectura

-- En la base de datos remota, crear usuario limitado
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- Usar en mapeo de usuario
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. Almacenamiento Seguro de Contraseñas

-- Puedes usar un archivo .pgpass para conexiones libpq en lugar de almacenar
-- la contraseña en el mapeo de usuario. Confirma la propiedad y el modo del archivo.
-- Formato ~/.pgpass:
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- Contraseña leída desde .pgpass

3. Seguridad de Red

-- Usar conexiones SSL
-- En postgresql.conf en remoto:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- Requerir SSL en mapeo de usuario
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

Mejores Prácticas

  1. Minimizar la transferencia de datos: Seleccionar solo las columnas necesarias
  2. Usar índices: Asegurar que las tablas remotas estén correctamente indexadas
  3. Almacenar en caché datos de acceso frecuente: Usar vistas materializadas
  4. Monitorear el rendimiento: EXPLAIN ANALYZE regular
  5. Limitar operaciones de escritura: Preferir solo lectura para la mayoría del uso de FDW
  6. Usar pooling de conexiones: Habilitar keep_connections
  7. Establecer fetch_size apropiado: Ajustar según patrones de consulta
  8. Probar escenarios de conmutación por error: Planificar para la indisponibilidad de la base de datos remota
  9. Documentar dependencias foráneas: Mantener documentación clara
  10. Auditorías de seguridad regulares: Revisar mapeos de usuario y permisos

Conclusión

Los Foreign Data Wrappers de PostgreSQL son útiles para la federación de bases de datos, especialmente cuando necesitas:

  • Integración sin problemas: Consultar datos remotos con SQL estándar
  • Acceso en tiempo real: Sin duplicación de datos ni retrasos de sincronización
  • Soporte multi-base de datos: PostgreSQL, MySQL, Oracle y más
  • Rendimiento: Pushdown de consultas cuando el wrapper y la consulta lo soportan
  • Flexibilidad: Acceso de lectura primero, con escrituras solo después de verificar el comportamiento del wrapper

Comienza con consultas de solo lectura a bases de datos remotas, optimiza el rendimiento con indexación adecuada y tamaños de recuperación, luego expande a integraciones más complejas según sea necesario.