Chapter 15: Extensions

PostgreSQL’s extension architecture transforms the database from a fixed application into a programmable platform – every hook, callback API, and worker slot is a seam where external code can alter behavior without touching a single line of core source.

Why This Matters

Most databases are closed systems. You can configure them, but you cannot change how the planner costs a join, how the executor runs a scan, or how authentication validates a password. PostgreSQL is different. Through a combination of global function-pointer hooks, pluggable access method APIs, background worker slots, and the Foreign Data Wrapper protocol, external shared libraries can intercept, replace, or extend nearly every stage of query processing and server operation.

Understanding these extension points is essential for three audiences. Extension authors need to know which callbacks exist and how to register them safely. DBAs evaluating extensions (pg_stat_statements, timescaledb, citus, postgis) need to understand what those extensions are actually doing to the server process. And core hackers proposing new hooks need to see the existing patterns and their trade-offs.

This chapter covers the four major extension mechanisms: hooks (function pointers that intercept planner, executor, and authentication paths), custom access methods (the Table AM and Index AM callback structs that let you replace heap storage or btree indexing), background workers (long-running processes forked by the postmaster that can run arbitrary code), and Foreign Data Wrappers (the full planning-through-execution callback protocol for accessing external data sources as if they were local tables).

Chapter Map

Extension Loading: The Entry Point

Every PostgreSQL extension begins life as a shared library (.so / .dll) loaded into the server process. The loading mechanism and initialization protocol are the same regardless of whether the extension installs hooks, access methods, background workers, or FDW handlers.

The _PG_init Contract

When PostgreSQL loads a shared library, it looks for and calls a function named _PG_init:

/* src/include/fmgr.h */
extern PGDLLEXPORT void _PG_init(void);

This is where extensions perform all setup: installing hook functions, registering background workers, defining custom GUCs, and requesting shared memory. The function runs exactly once per library load, in the postmaster process (for shared_preload_libraries) or in a backend process (for session_preload_libraries or LOAD).

Library Loading Paths

                    +--------------------------+
                    |   postgresql.conf        |
                    |                          |
                    |  shared_preload_libraries|----> Loaded at postmaster start
                    |  session_preload_libraries|---> Loaded at session start
                    |  local_preload_libraries |----> Loaded at session start
                    +--------------------------+
                              |
                              v
                    +--------------------------+
                    |  process_shared_preload_ |
                    |  libraries()             |
                    |                          |
                    |  For each library:       |
                    |    dlopen(libname)        |
                    |    dlsym("_PG_init")     |
                    |    _PG_init()            |
                    +--------------------------+

The critical distinction: only shared_preload_libraries runs in the postmaster before any backends are forked. This is the only path that allows:

• Registering background workers (they need postmaster to fork them)
• Requesting shared memory via shmem_request_hook
• Installing hooks that must be active for all sessions

/* src/include/miscadmin.h */
extern PGDLLIMPORT bool process_shared_preload_libraries_in_progress;
extern PGDLLIMPORT bool process_shared_preload_libraries_done;

typedef void (*shmem_request_hook_type) (void);
extern PGDLLIMPORT shmem_request_hook_type shmem_request_hook;

A Minimal Extension Skeleton

/* myext.c -- minimal extension that installs a planner hook */

#include "postgres.h"
#include "fmgr.h"
#include "optimizer/planner.h"

PG_MODULE_MAGIC;

static planner_hook_type prev_planner_hook = NULL;

static PlannedStmt *
my_planner(Query *parse, const char *query_string,
           int cursorOptions, ParamListInfo boundParams,
           ExplainState *es)
{
    /* Pre-planning logic here */

    if (prev_planner_hook)
        return prev_planner_hook(parse, query_string,
                                 cursorOptions, boundParams, es);
    else
        return standard_planner(parse, query_string,
                                cursorOptions, boundParams, es);
}

void
_PG_init(void)
{
    prev_planner_hook = planner_hook;
    planner_hook = my_planner;
}

The pattern of saving the previous hook value and chaining to it (or falling back to the standard_* function) is universal across all PostgreSQL hooks. It allows multiple extensions to coexist on the same hook point.

The Extension Control File

Beyond the C shared library, PostgreSQL’s CREATE EXTENSION system uses a .control file to declare metadata and a SQL script to create the extension’s database objects:

# myext.control
comment = 'My extension description'
default_version = '1.0'
module_pathname = '$libdir/myext'
relocatable = true

This infrastructure (managed by src/backend/commands/extension.c) handles versioning, upgrade paths, and dependency tracking – but the actual power of extensions comes from the C-level APIs described in the following pages.

Connections to Other Chapters