PostgreSQL – Foreign Data Wrapper

Què és el Foreign Data Wrapper (FDW)?

FDW és una extensió de PostgreSQL que permet accedir a diferents orígens de dades de diferent tipologia (altres bases de dades postgres, mysql, fitxers plans, etc.) com si estiguessin al propi servidor.
La llista dels diferents orígens de dades a integrar es pot trobar a:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Què cal configurar?

Per configurar el FDW primer cal crear les extensions sobre els sistemes gestors de bases de dades que volem utilitzar.
Per exemple:

CREATE EXTENSION postgres_fdw;
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION file_fdw;

Després cal crear els servidors.
Per exemple:

CREATE SERVER postgres_fdw_odoo
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host 'localhost', port '5432', dbname 'odoo');

CREATE SERVER mysql_fdw_otrs
     FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host 'localhost', port '3306');

CREATE SERVER csvfiles FOREIGN DATA WRAPPER file_fdw;

Després cal crear els usuaris mapejats. En el següent exemple es mapegen les credencials que utilitzarà l’usuari de la base de dades local odoousr per accedir als servidors remots postgres_fdw_odoo i mysql_fdw_otrs.
El FDW impersonarà l’usuari odoousr com a usrpostgres i usrmysql a la base de dades de destí.

CREATE USER MAPPING FOR odoousr SERVER postgres_fdw_odoo OPTIONS (user 'usrpostgres', password '123456');

CREATE USER MAPPING FOR odoousr SERVER mysql_fdw_matomo OPTIONS (username 'usrmysql', password '123456');

Per últim cal crear les foreign tables.

drop foreign table if exists odoo12_account_account;
create FOREIGN TABLE odoo12_account_account("id" int4 NOT NULL
  ,"name" varchar NOT NULL
  ,"currency_id" int4
  ,"code" varchar(64) NOT NULL
  ,"deprecated" bool
  ,"user_type_id" int4 NOT NULL
  ,"internal_type" varchar
  ,"last_time_entries_checked" timestamp
  ,"reconcile" bool
  ,"note" text
  ,"company_id" int4 NOT NULL
  ,"group_id" int4
  ,"create_uid" int4
  ,"create_date" timestamp
  ,"write_uid" int4
  ,"write_date" timestamp
  ,"centralized" bool
  ,"internal_group" varchar
  ,"asset_profile_id" int4
  ,"hide_in_cash_flow" bool)SERVER postgres_fdw_odoo OPTIONS (schema_name 'public', table_name 'account_account');

drop foreign table if exists matomo_db_confirm_date;
create FOREIGN TABLE matomo_db_confirm_date("page_name" text
    ,"idvisit" bigint NOT NULL
    ,"visitor" varchar(65)
    ,"name" varchar(90) NOT NULL
    ,"server_time" timestamp )SERVER mysql_fdw_matomo OPTIONS (dbname 'matomo_db', table_name 'confirm_date');

A partir d’aquest moment es poden fer consultes atacant directament a les taules odoo12_account_account i matomo_db_confirm_date des del servidor local.

Rendiment

Un dels grans handicaps que ens trobem amb FDW és el rendiment. Externament podem fer consultes creuant diferents orígens de dades com si els tinguéssim en local, però internament el que fa FDW és portar-se tota la taula remota a local (filtrant allò que ja coneix) i després fer el creuament. Si tenim taules grans (>500K registres) en remot i ho creuem amb altres taules grans el rendiment caurà en picat per que haurà de llegir les taules senceres de remot, portar-les a local i després executar la consulta.
Analitzar els plans de consulta amb explain ens ajudarà a veure quins càlculs es fan als servidors remots i quins es fan en local.

Intenteu filtrar les taules remotes dins de la consulta per reduir al màxim el tràfic de xarxa.

Si veieu que el temps d’execució se us dispara haureu de provar de persistir dades amb DBT o bé crear processos que us facin còpies regulars de les taules d’origen en local.