Centro de Conocimiento DevOps
Centro de Conocimiento DevOps

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

Domine los Foreign Data Wrappers (FDW) de PostgreSQL para una integración fluida de múltiples bases de datos, consultas entre bases de datos y federación de datos. Guía completa con optimización de rendimiento y ejemplos del mundo real.

32 vistas

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

Introducción

Los Foreign Data Wrappers (FDW) de PostgreSQL permiten una integración perfecta con fuentes de datos externas, lo que le permite consultar bases de datos remotas, archivos e incluso API web como si fueran tablas locales. Esta potente característica permite la federación de bases de datos, uniones entre bases de datos y acceso centralizado a los datos sin complejas canalizaciones ETL.

¿Qué son los Foreign Data Wrappers?

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

  • Acceso transparente: Consulte datos remotos utilizando SQL estándar
  • Tablas virtuales: No se requiere duplicación de datos
  • Datos en tiempo real: Siempre actualizados, sin retrasos de sincronización
  • Capacidad de unión: Combine datos locales y remotos
  • Operaciones de escritura: Algunos FDW admiten INSERT/UPDATE/DELETE

Casos de uso comunes

  1. Migración de bases de datos: Acceda a sistemas heredados durante la migración
  2. Arquitectura multi-inquilino: Realice consultas en las bases de datos de los inquilinos
  3. Agregación de datos: Combine datos de múltiples fuentes
  4. Nube híbrida: Conecte bases de datos locales y en la nube
  5. Informes: Cree una base de datos de informes centralizada
  6. Microservicios: Realice consultas a través de los límites del servicio

Foreign Data Wrappers disponibles

postgresql_fdw (Integrado)

  • Conexión a otras bases de datos PostgreSQL
  • Soporte completo de funciones
  • Mejor rendimiento

mysql_fdw

  • Conexión a MySQL/MariaDB
  • Soporte de lectura y escritura

oracle_fdw

  • Conexión a bases de datos Oracle
  • Versiones comerciales y de código abierto

mongodb_fdw

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

file_fdw (Integrado)

  • Lectura de 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 el servidor externo (Foreign Server)

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 el mapeo de usuarios

-- 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 externas

Definición manual de tablas:

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 remotos

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

-- Unión de 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 optimización de rendimiento

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',

        -- Fetch size: filas recuperadas por cada viaje de ida y vuelta
        fetch_size '50000',

        -- Habilitar optimizaciones de pushdown de consultas
        use_remote_estimate 'true',

        -- Agrupación de conexiones (Connection pooling)
        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

-- Otorgar 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 entre 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 confirmadas juntas
COMMIT;

Nota: El commit de dos fases garantiza la atomicidad entre las bases de datos.

Federación de múltiples bases de datos

Conectar múltiples bases de datos

-- Conectar a la 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 la base de datos de analítica
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 la 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 entre 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 en 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;

Conexión a MySQL

Instalar mysql_fdw

# Ubuntu/Debian
sudo apt install postgresql-15-mysql-fdw

# CentOS/RHEL
sudo yum install mysql_fdw_15

Configurar la conexión a 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. Uso de pushdown de la cláusula WHERE

PostgreSQL delega los filtros al servidor remoto:

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

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

2. Limitar la transferencia de datos

-- Mal: Recupera todas las columnas
SELECT * FROM remote_large_table;

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

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

3. Optimizar el tamaño de recuperación (Fetch Size)

-- 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á el EXPLAIN remoto para obtener estimaciones de costo precisas

5. Índices en tablas remotas

-- Asegúrese de que las tablas remotas tengan los índices adecuados
-- Conéctese 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 datos remotos localmente
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- Refrescar periódicamente
REFRESH MATERIALIZED VIEW cached_remote_data;

-- Refresco automático con cron job o trigger
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Monitoreo y resolución de problemas

Ver servidores externos

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

Ver tablas externas

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 el rendimiento de las consultas

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

Verificación de la agrupación de conexiones

-- Verificar conexiones remotas activas
SELECT 
    datname,
    usename,
    application_name,
    client_addr,
    state
FROM pg_stat_activity
WHERE application_name LIKE '%fdw%';

Problemas comunes y soluciones

Problema 1: Tiempo de espera de conexión excedido

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 la terminal:
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 el tamaño de recuperación
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Verificar si la cláusula WHERE se delega (pushdown)
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

Problema 3: Fallo de autenticación

Error: password authentication failed

Soluciones:

-- Actualizar el mapeo de usuario
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'nueva_contraseña');

-- Verificar pg_hba.conf en el servidor remoto
-- Asegúrese de que permita 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 los 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 un 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 el mapeo de usuario
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'contraseña_segura');

2. Almacenamiento seguro de contraseñas

-- Usar el archivo .pgpass en lugar de contraseñas grabadas
-- Formato ~/.pgpass:
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- La contraseña se lee de .pgpass

3. Seguridad de red

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

-- Requerir SSL en el 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: Seleccione solo las columnas necesarias
  2. Usar índices: Asegúrese de que las tablas remotas estén indexadas correctamente
  3. Cachear datos de acceso frecuente: Utilice vistas materializadas
  4. Monitorear el rendimiento: Realice EXPLAIN ANALYZE periódicamente
  5. Limitar operaciones de escritura: Prefiera el modo de solo lectura para la mayoría de usos de FDW
  6. Usar agrupación de conexiones: Habilite keep_connections
  7. Establecer un fetch_size apropiado: Ajústelo basándose en los patrones de consulta
  8. Probar escenarios de conmutación por error: Planifique la falta de disponibilidad de la base de datos remota
  9. Documentar dependencias externas: Mantenga una documentación clara
  10. Auditorías de seguridad periódicas: Revise los mapeos de usuario y permisos

Conclusión

Los Foreign Data Wrappers de PostgreSQL proporcionan potentes capacidades de federación de bases de datos:

  • Integración perfecta: Consulte 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 y optimización
  • Flexibilidad: Operaciones de lectura y escritura

Comience con consultas de solo lectura a bases de datos remotas, optimice el rendimiento con una indexación adecuada y tamaños de recuperación correctos, y luego amplíe a integraciones más complejas según sea necesario.