PGCon2016 - 20180510

PGCon 2016
The PostgreSQL Conference

Ashutosh Bapat
Rushabh Lathia
Day Tutorials - Day 1 - 2016-05-17
Room MRT 251
Start time 09:00
Duration 03:00
ID 950
Event type Workshop
Track Tutorial
Language used for presentation English

Foreign Data Wrappers - how to write them and use them.

Foreign data wrappers made easy

For an application which uses PostgreSQL as backend and requires accessing data from multiple data sources, Foreign Data Wrappers can help to get an integrated view of data through PostgreSQL. Foreign Data Wrapper can be tricky to use and even more so if one needs to write one. This tutorial will make it easy to use and write an FDW.

Today's applications have their data generated and stored in multiple heterogeneous database platforms including non-relational platforms like Hadoop, XML or even CSV files. A database which can access and manipulate foreign data lessens burden on the applications. PostgreSQL 9.1 was released with read-only support for SQL/MED standard in the form of Foreign Data Wrappers. From PostgreSQL 9.3, it started supporting writable FDWs. Since then PostgreSQL’s Foreign Data Wrapper features has matured as a mode to integrate with other PostgreSQL, non-PostgreSQL RDBMSes and even some non-SQL databases seamlessly using native APIs. Till now, support for foreign table inheritance, join pushdown and sort pushdown has been added in core. Work is underway to support join pushdown in postgres_fdw and DML pushdown in 9.6.

The tutorial covers following topics from the perspective of developer and user.

Developing an FDW 1. Various APIs exposed by FDW infrastructure 2. How to use those APIs to write an FDW, with examples

Using an FDW 1. Where to find an FDW and how to install an FDW. 2. Creating foreign server, user mapping and foreign tables 3. Managing and using those objects using SQL.

The features will be demonstrated using postgres_fdw.