Learning Postgres extensions as a Zig developer

September 29, 2026

Opening the black box

I was recently telling a friend how Zig has opened up a whole set of systems and problems that were out of reach for me in higher-level languages like Java, Node.js, and Python.

I’ve wanted to go deep into Postgres for quite some time. I’ve read a good chunk of the docs and blogs like PlanetScale’s and TigerData’s, but I’m the learn-by-doing kind. I also had a couple of skill issues to overcome: I don’t know C very well, and I couldn’t understand what was going on just by reading through the codebase.

So I decided to learn Postgres by extending it: write small extensions, see where they plug in, and let each one force me to understand one more piece of the server.

How I got here

The starting point was pgrx, the Rust framework for writing Postgres extensions. It’s impressive, but I wanted to do this in Zig, and that led me to pgzx, a Zig take on the same idea from Xata.

pgzx had fallen behind Zig releases. Someone had already forked it and made it work on Zig 0.15; I forked that fork and brought it to Zig 0.16. So most of the foundation isn’t mine, I stand on two layers of other people’s work, and I’ve been adding pieces as I needed them to learn. Most of the APIs were already there.

Zig turns out to be a good fit here. Postgres is a C codebase full of macros, and Zig imports C headers directly, so the whole server API is right there. comptime also pulls its weight: it reads Zig function signatures and works out the SQL types for each function, so the extension’s CREATE FUNCTION script can be generated at build time. It’s still more manual than pgrx, which can infer a lot from Rust code with proc macros, but it’s something.

The APIs are already there

Whatever you want to do, there’s probably a hook or a primitive for it. An extension can hook into the planner, the executor, authentication, logging, shared memory, background processes, and even replace how data is indexed or stored.

Interestingly, the big guys don’t use them all. It’s not because something’s there that you should use it. The extensions I know best, TimescaleDB and pgvector, neither rewrote Postgres’s storage, which says something about how good those primitives are, and how much you’d lose by rolling your own.

TimescaleDB splits a hypertable into chunks, and each chunk is an ordinary Postgres table with ordinary B-tree indexes. Even its columnar compression is built on top of that: about 1,000 rows are grouped into a batch, each column is compressed with an algorithm suited to its type (delta-of-delta for timestamps, Gorilla for floats), and the batch is stored as a single regular row. The compressed values are large, so Postgres moves them out of line with TOAST, which handles fetching the full Datum value when it exceeds a single page. Queries go through custom scan nodes plugged in with hooks. That’s the power of the SQL abstraction: you query the table as you would in any other case, but the underlying data structures are completely different.

Gorilla was a nice reunion. It comes from a Facebook paper on compressing time-series data, and RedisTimeSeries uses it too. I implemented it in zedis, my Redis clone in Zig, and it’s still one of the most astonishing algorithms I’ve learned. Whoever came up with it is a genius.

pgvector takes a different route to the same idea: it adds a new index type on top of regular tables. There are some better alternatives; however, it’s good enough for most cases.

The goal: by the end of this journey, fully understand how Postgres extension primitives work.

pgzx core

Postgres’s extension surface is a set of C APIs and hooks, plus SQL packaging. This is the map I drew when I started. Each area gets a line on what it is, what it taught me, and a small pgzx snippet. Later posts in the series go deep on each one.

Packaging and lifecycle

How an extension is found, loaded, and wired into the server.

  • A .control file plus a versioned SQL script, installed with CREATE EXTENSION
  • PG_MODULE_MAGIC, so the server can refuse a library built for another version
  • _PG_init(), called when the shared library is loaded (_PG_fini() exists but hasn’t been called since Postgres 15; libraries are never unloaded)
  • shared_preload_libraries, for extensions that must load at server start

What it taught me: an extension is just SQL objects plus a shared library that Postgres loads on demand.

comptime {
    pgzx.PG_MODULE_MAGIC();
    pgzx.PG_FUNCTION_V1("char_count_zig", functions.char_count_zig);
}

pub export fn _PG_init() void {
    // Runs once when the library is loaded into a backend,
    // or at server start with shared_preload_libraries.
}

The function manager

The glue between SQL and native code: how functions are registered and how arguments and results travel as Datum values.

  • PG_FUNCTION_INFO_V1, Datum, PG_GETARG_* / PG_RETURN_*
  • fmgr calling conventions and Pg_finfo_record

Every value in Postgres is a Datum, a pointer-sized word. Small types fit inside it; everything else is a pointer.

// A fixed-length, pass-by-reference type stores a pointer in the Datum,
// so the conversion is just a pointer round trip.
inline fn fromDatum(d: pg.Datum) Rational {
    const p: *const Rational = @ptrFromInt(d);
    return p.*;
}

fn makeDatum(r: Rational) !pg.Datum {
    const p = try pgzx.mem.PGCurrentContextAllocator.create(Rational);
    p.* = r;
    return pg.PointerGetDatum(p);
}

pgzx has to translate between Zig types and Postgres types in both directions. It’s a fair amount of code, but pretty straightforward.

Memory

Every allocation goes through Postgres memory contexts, so the server can track and free everything an extension touches.

  • palloc / palloc0 / repalloc / pfree
  • The MemoryContext API (AllocSetContextCreate, MemoryContextSwitchTo)

Postgres rarely frees things one by one. It frees whole contexts at once when a query or transaction ends, like an arena allocator. That’s basically what it is.

In my opinion, this is where pgzx feels better than pgrx, and it makes a lot of sense as a Zig developer: you create a context from a parent, allocate from it, and destroy the whole thing when you’re done. It was much easier than I initially thought it would be.

// Allocate in the current context (palloc/pfree under the hood).
const allocator = pgzx.mem.PGCurrentContextAllocator;

// Or create a context you control and free it in one shot.
var memctx = try pgzx.mem.createAllocSetContext("my_context", .{
    .parent = pg.CurrentMemoryContext,
});
defer memctx.deinit();
const a = memctx.allocator();

Catalogs, tables, and SPI

Direct access to tables, rows, and the system catalogs, or run plain SQL from inside the server through SPI.

  • table_open / index_open, table_beginscan, systable_beginscan
  • heap_insert / heap_update / heap_delete, HeapTuple, TupleDesc, TupleTableSlot
  • SPI (SPI_connect, SPI_execute, SPI_prepare, SPI_cursor_*)

Postgres describes itself with its own tables. Your functions, types, and tables are all just rows in the system catalogs.

SPI is the part I got most excited about. It’s basically a way to run SQL from inside your extension.

try pgzx.spi.connect();
defer pgzx.spi.finish();

var rows = try pgzx.spi.queryTyped(
    i64,
    "SELECT id FROM spi.spi_example ORDER BY random() LIMIT 1",
    .{ .read_only = true },
);
defer rows.deinit();
const id = try rows.next();

Hooks

Global function pointers that let an extension step in front of the planner, the executor, utility commands, and a lot more.

  • Planner: planner_hook, create_plan_hook, get_relation_info_hook
  • Executor: ExecutorStart / ExecutorRun / ExecutorFinish / ExecutorEnd
  • ProcessUtility_hook, post_parse_analyze_hook
  • object_access_hook, emit_log_hook, ClientAuthentication_hook
  • shmem_request_hook / shmem_startup_hook, needs_fmgr_hook

Hooks are a chain. You save the previous hook, install yours, and call the previous one (or the standard implementation) when you’re done. Forget that last step and you break every other extension that hooked in before you.

It’s such a simple mechanism, just a global pointer, and yet it’s how things like pg_stat_statements and pgaudit work.

var prev_ExecutorStart_hook: pg.ExecutorStart_hook_type = null;

pub export fn _PG_init() void {
    prev_ExecutorStart_hook = pg.ExecutorStart_hook;
    pg.ExecutorStart_hook = pgaudit_zig_ExecutorStart_hook;
}

fn pgaudit_zig_ExecutorStart_hook(queryDesc: [*c]pg.QueryDesc, eflags: c_int) callconv(.c) void {
    // ...inspect queryDesc...
    if (prev_ExecutorStart_hook) |hook| {
        hook(queryDesc, eflags);
    } else {
        pg.standard_ExecutorStart(queryDesc, eflags);
    }
}

Pluggable subsystems

Swap out whole subsystems, like storage, indexing, foreign data, scans, and decoding, with your own implementation.

  • Table access methods (TableAmRoutine), index access methods (IndexAmRoutine)
  • Foreign data wrappers (FdwRoutine)
  • Custom scans (CustomScan / CustomExecMethods), logical decoding, tablesample (TsmRoutine)

These are the deepest extension points and the hardest to get right. This is where pgvector lives (it’s an index access method), and it’s also why so few extensions write their own table storage.

This is the level I want to reach by the end of the series.

// Parse and validate an FDW option list (DefElem).
var iter = pgzx.fdw.DefElemList.iteratorFrom(options);
while (iter.next()) |def| {
    const name = std.mem.span(def.?.defname);
    const value = pgzx.fdw.getString(def.?);
    // ...
}

Background workers

Extra server processes that run next to the normal connections, for maintenance or parallel work.

  • RegisterBackgroundWorker, RegisterDynamicBackgroundWorker, BackgroundWorker

Postgres uses processes, not threads. Each connection is its own process, and a background worker is just one more process the postmaster starts for you. You register a function, and Postgres takes care of starting it, restarting it, and shutting it down.

export fn bgworker_main(main_arg: pg.Datum) callconv(.c) void {
    _ = main_arg;
    pg.BackgroundWorkerUnblockSignals();
    pgzx.elog.Log(@src(), "pgzx bgworker example: worker started", .{});
    pg.proc_exit(0);
}

pub export fn _PG_init() void {
    pgzx.bgworker.register("pgzx_bgworker", "bgworker", "bgworker_main", .{
        .flags = pg.BGWORKER_SHMEM_ACCESS,
        .worker_type = "bgworker",
        .restart_time = pg.BGW_NEVER_RESTART,
    });
}

Concurrency and shared memory

Locks and shared memory, so processes can coordinate with each other.

  • LWLocks (LWLockAcquire, RequestNamedLWLockTranche), heavyweight locks, spinlocks
  • ConditionVariable, shared memory (ShmemInitStruct, ShmemInitHash)

Since everything is a separate process, shared state has to live in shared memory, and that memory has to be requested when the server starts. You can’t just spin up a thread and share a struct, that doesn’t fit the Postgres model.

// LWLocks
pg.LWLockAcquire(pgzx.lwlock.ProcArray(), pg.LW_EXCLUSIVE);
defer pg.LWLockRelease(pgzx.lwlock.ProcArray());

// Shared memory, initialized once at server start.
const MyState = struct {
    pub const SHMEM_NAME = "my_extension_state";
    value: u32 = 0,

    pub fn init() MyState {
        return .{ .value = 0xdeadbeef };
    }
};

var state: *MyState = undefined;
pgzx.shmem.registerSharedState(MyState, &state);

Configuration (GUCs)

Custom settings you can change through postgresql.conf or SET.

  • DefineCustomIntVariable / DefineCustomBoolVariable / DefineCustomStringVariable
  • DefineCustomEnumVariable / DefineCustomRealVariable, MarkGUCPrefixReserved

Every setting I’ve ever tuned, like work_mem or shared_buffers, is a GUC, and an extension can add its own with the exact same machinery.

var sample_int: pgzx.guc.CustomIntVariable = undefined;

pub export fn _PG_init() void {
    sample_int.register(.{
        .name = "guc.sample_int",
        .short_desc = "Sample integer GUC",
        .initial_value = 42,
        .min_value = -100,
        .check_hook = pgzx.guc.checkIntHook(clampInt),
    });
    pgzx.guc.markPrefixReserved("guc");
}

Errors and transactions

Report errors through Postgres’s error system and hook into the transaction lifecycle.

  • ereport, elog, PG_TRY / PG_CATCH / PG_FINALLY
  • RegisterXactCallback, RegisterSubXactCallback, syscache and relcache callbacks
  • on_proc_exit / before_shmem_exit, RegisterSnapshot

A Postgres error is a longjmp. It jumps straight past your stack frames, which means Zig’s defer never runs. pgzx has to catch the jump, turn it into a Zig error, and rethrow it before handing control back to Postgres.

As a Zig developer this one hurt a bit. You get used to defer and errdefer always running, and here they just don’t, unless you’re careful at every boundary.

// Raise a Postgres error (a longjmp under the hood).
return pgzx.elog.Error(@src(), "batch must be positive, got {d}", .{batch});

// Catch a Postgres error and turn it into a Zig error value.
var errctx = pgzx.err.Context.init();
defer errctx.deinit();
if (errctx.pg_try()) {
    pg.someFunctionThatMayThrow();
} else {
    return error.PGErrorStack;
}

File I/O

Backend-safe file access through Postgres’s virtual file descriptor (VFD) layer.

  • The VFD API in fd.c (PathNameOpenFile, FileRead, FileWrite, FileClose)
  • Postgres 18’s asynchronous I/O (io_method, FileStartReadV)

A backend shouldn’t just call open(). Postgres manages its own file descriptors, so it can stay under max_files_per_process and clean up temporary files when a transaction ends. pgzx wraps all of that as pgzx.fd:

// A transaction-scoped temporary file, managed by Postgres.
var file = try pgzx.fd.openTemp(false);
defer file.close();

_ = try file.writeAll(data, 0);
try file.sync();

const out = try pgzx.mem.PGCurrentContextAllocator.alloc(u8, data.len);
_ = try file.readAll(out, 0);

My first extension

Let’s do a function that counts how many times a character shows up in a string. It’s trivial on purpose, the point is to see every piece an extension needs.

The build

pgzx’s build support uses pg_config to find your Postgres install, then adds steps to build the library, generate the SQL, and run the tests.

const std = @import("std");

// pgzx build support. It uses pg_config to find all dependencies
// and provides functions to create and test extensions.
const PGBuild = @import("pgzx").Build;

pub fn build(b: *std.Build) void {
    const proj = PGBuild.Project.init(b, .{
        .name = "char_count_zig",
        .version = .{ .major = 0, .minor = 1 },
        .root_dir = "src/",
        .root_source_file = "src/main.zig",
    });

    _ = proj.addSteps(.{
        .schema = .{},
        .pg_regress = .{
            .db_user = "postgres",
            .db_port = 5432,
            .scripts = &[_][]const u8{"char_count_test"},
        },
        .unit = .{
            .db_user = "postgres",
            .db_port = 5432,
        },
    });
}

The control file

char_count_zig.control tells Postgres what the extension is called, its default version, and where to find the shared library.

comment = 'Zig function to count the number of specified characters'
default_version = '0.1'
module_pathname = '$libdir/char_count_zig'
relocatable = true

Registering the function

main.zig marks the library as a Postgres module and registers the function with fmgr. The second comptime block registers the unit tests, and those run inside a real Postgres backend, which I love.

const pgzx = @import("pgzx");
const functions = @import("functions.zig");

comptime {
    pgzx.PG_MODULE_MAGIC();

    pgzx.PG_FUNCTION_V1("char_count_zig", functions.char_count_zig);
}

comptime {
    pgzx.testing.registerTests(
        @import("build_options").testfn,
        .{ functions.Testsuite1, functions.Testsuite2 },
    );
}

The SQL, generated from Zig signatures

Every extension ships a SQL script that runs CREATE FUNCTION. With pgzx, you declare your functions once in schema.zig, and the build generates char_count_zig--0.1.sql for you.

Zig can’t write files at comptime, so this happens in two stages. At comptime, pgzx.ddl looks at each function’s signature with @typeInfo and maps the Zig types to SQL types ([]const u8 → text, u32 → bigint, since Postgres has no unsigned integers), baking the SQL text into the binary. Then zig build compiles a tiny generator program that just prints that SQL, runs it, and saves the output as the extension script.

const functions = @import("functions.zig");

pub const pgzx_sql = .{
    .functions = .{
        .{ .name = "char_count_zig", .func = functions.char_count_zig },
    },
};

The function itself

Just plain Zig. pgzx turns the text arguments into slices and the u32 result back into a Datum. Errors go through pgzx.elog, so they show up as regular Postgres errors.

const std = @import("std");
const pgzx = @import("pgzx");

pub fn char_count_zig(input_text: []const u8, target_char: []const u8) !u32 {
    if (target_char.len > 1) {
        return pgzx.elog.Error(@src(), "Target character must be a single byte", .{});
    }

    var count: u32 = 0;
    for (input_text) |char| {
        if (char == target_char[0]) {
            count += 1;
        }
    }
    return count;
}

pub const Testsuite1 = struct {
    pub fn testHappyPath() !void {
        const count = try char_count_zig("Hello World", "l");
        try std.testing.expectEqual(@as(u32, 3), count);
    }
};

pub const Testsuite2 = struct {
    pub fn testMoreThanOneByteError() !void {
        _ = char_count_zig("Hello World", "lo") catch |err| {
            try std.testing.expectEqual(error.PGErrorStack, err);
            return;
        };
        try std.testing.expect(false);
    }
};

Running it

CREATE EXTENSION char_count_zig;

SELECT char_count_zig('Hello World', 'l');
-- 3

Seeing that 3 come back from Zig code running inside the database was pretty cool.

What I learned so far

  • The primitives are there to build almost anything. Background processes, SQL from inside the server through SPI, functions over any type, hooks into planning and execution. The limit is mostly how much of Postgres you understand, and in my case that’s the part I’m working on.
  • Everything is an Oid. Tables, types, functions, operators, and schemas are all identified by an Oid, a 32-bit unsigned integer that works as the primary key in the system catalogs. When I created my function, Postgres inserted a row into pg_proc and recorded in pg_depend that it belongs to the extension, which is how DROP EXTENSION knows to remove it.
  • Errors don’t unwind, they jump. Coming from Zig, where cleanup is explicit, the longjmp error model was the biggest mental shift. Postgres cleans up by throwing away whole memory contexts and aborting transactions, not by running destructors.
  • Zig fits really well, but it’s still manual. Importing C headers and comptime code generation get you far. You still register functions and write the control file by hand, where pgrx would infer them for you. There’s much more to do.

If you’re a Zig developer curious about databases, give pgzx a try, it’s a great way in.

Next up: what actually happens when you run CREATE EXTENSION.