[derefr]

The most common reason to write a PG extension is to add native support for a structure/data type that PG doesn't already have native support for.

For example, despite Postgres having a (base-10000) numeric/decimal type, there are certain things that can be done much more cheaply in base-256 (e.g. ser/des from bit-packed and/or hex strings), and thus pgmp (https://github.com/dvarrazzo/pgmp) exists as a Postgres libgmp binding.

There are also "data service" type extensions—exposing some other data "into" Postgres so it can participate in a query. PG's own pg_stat_statements extension is an example. Postgres's support for Foreign Data Wrappers has mostly obviated writing these as extensions, though; it's much easier (and safer for your RDBMS-side data!) to just write these as standalone daemons that Postgres mounts as FDW servers.

[hans_castorp]

A foreign data wrapper is an extension.

To install it, you run "create extension my_fdw"

[derefr]

Yes, a given Foreign Data Wrapper—that is, the module of functionality that describes the connection strategy for some remote resource, like an ORM "adapter" module—is a Postgres extension. To get PG to speak a new protocol, you need to write a new FDW extension.

But a Foreign Data Wrapper server is just an instance of that class. You don't need to write a Postgres extension, just to use an existing Foreign Data Wrapper.

And, crucially, many of these FDWs are written to be backed by standard (or at least well-documented) network protocols.

Which means that, a large percentage of the time, people thinking "I'll take this third-party thing and integrate its data into Postgres" these days, don't write a Foreign Data Wrapper, but rather choose one of the existing network protocols with an existing Foreign Data Wrapper that supports it, and make their third-party thing speak that protocol, so that they (and anyone else who likes) can mount it as a server using the existing FDW.

Specifically, many systems that want to be "Postgres-compatible" these days (CockroachDB, Materialize, etc.), speak the Postgres wire protocol as if they were Postgres itself. This is not (mainly) so that real clients can talk to them through a libpq binding (as they tend to have their own, more idiomatic client ABI); but rather so that actual Postgres instances can mount them using `postgres_fdw`, the FDW that assumes the other end is another Postgres server and uses libpq to talk to it.