How to Build a PostgreSQL FDW with Go: From Theory to Practice

Background of FDW and SQL/MED

FDW (Foreign Data Wrapper) enables PostgreSQL to act as a universal SQL engine, allowing SQL queries against data stored outside the database. It implements the SQL/MED standard (ISO/IEC 9075‑9) which defines a uniform way to access heterogeneous data sources.

Evolution of FDW in PostgreSQL

Support began with syntax only in PostgreSQL 8.4 and became functional in 9.1. Subsequent releases added push‑down of joins, aggregates, updates, and thousands of wrappers now exist for files, NoSQL stores, and web services.

Reference: https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Core FDW objects and workflow

To create an FDW you install the wrapper extension, then create a FOREIGN DATA WRAPPER , a SERVER , an optional USER MAPPING , and finally a FOREIGN TABLE . After that normal SELECT (and optionally INSERT, UPDATE, DELETE) statements can be used.

Callback functions required by a FDW

At least seven callbacks are needed: GetForeignRelSize, GetForeignPaths, GetForeignPlan, BeginForeignScan, IterateForeignScan, ReScanForeignScan, EndForeignScan. They are invoked during the Optimizer and Executor phases.

typedef void (*GetForeignRelSize_function)(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid);
typedef void (*GetForeignPaths_function)(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid);
typedef ForeignScan *(*GetForeignPlan_function)(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid, ForeignPath *best_path, List *tlist, List *scan_clauses, Plan *outer_plan);
typedef void (*BeginForeignScan_function)(ForeignScanState *node, int eflags);
typedef TupleTableSlot *(*IterateForeignScan_function)(ForeignScanState *node);
typedef void (*EndForeignScan_function)(ForeignScanState *node);

Implementing FDW callbacks with Go (cgo)

Because PostgreSQL’s API is C‑based, Go code must use cgo. The steps are: import "C", declare Go functions matching the C signatures, export them with //export, and implement the logic in Go. Example of GetForeignRelSize implementation:

//export doubanGetForeignRelSize
func doubanGetForeignRelSize(root *C.PlannerInfo,
    baserel *C.RelOptInfo, foreigntableid C.Oid) {
    // Collect needed attributes for joins and output.
    targetlist := (*C.Node)(unsafe.Pointer(baserel.reltargetlist))
    // ... other logic ...
    baserel.fdw_private = Save(attributesRetrieved)
    baserel.rows = C.double(MovieRankingTop250Num)
}

Common pitfalls include unavailable C macros (e.g., foreach), pointer casts that require unsafe.Pointer, and cgo pointer‑passing restrictions. Work‑arounds involve using C.list_length / C.list_nth instead of macros, performing explicit unsafe casts, and employing the go-pointer library to map Go pointers to C addresses.

Build process

FDW is built as a PostgreSQL extension using PGXS. A typical Makefile defines MODULES, EXTENSION, DATA, and includes the PGXS makefile. CGO_CFLAGS and CGO_LDFLAGS must point to PostgreSQL’s include and lib directories.

MODULES = myfdw
EXTENSION = myfdw
DATA = myfdw--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Practical considerations

While Go speeds development, the cgo stack switches introduce noticeable overhead, so performance‑critical FDWs (e.g., postgres_fdw used for sharding) are still best written in pure C.

Outlook

FDW functionality has grown steadily from PostgreSQL 9.2 to 10, and with the maturing built‑in sharding solution, interest in FDW is expected to increase.