The Extension System

PostgreSQL extensions are the sanctioned mechanism for adding types, operators, functions, index access methods, and background workers to a running database without patching or forking the server itself.

Every extension a team enables becomes a permanent, versioned part of that database's catalog, so understanding the machinery underneath CREATE EXTENSION matters more than memorizing the command syntax.

This page builds the mental model of the extension system as a whole: what an extension actually is on disk, how the server decides when code needs to load, how versions relate to each other, and where the trust boundary sits.

Summary

• Core Idea: An extension is a packaged, versioned bundle of SQL objects (and optionally compiled code) that PostgreSQL tracks as a single dependency unit in its catalog.
• Why It Matters: Extensions let a database grow new capabilities (vector search, geospatial types, cryptography, scheduling) without a server fork, while still being upgradable, auditable, and reversible.
• Key Concepts: control file, extension script, shared_preload_libraries, pg_extension, dependency graph, trust boundary.
• When to Use: Reach for this mental model whenever you are deciding whether an extension needs a restart, whether it is safe to grant to an application role, or why an upgrade path is blocked.
• Limitations / Trade-offs: Extensions are per-database, not global, and any extension containing C code runs inside the server process with the same memory space as core PostgreSQL.
• Related Topics: CREATE EXTENSION mechanics, trusted vs. untrusted extensions, extension upgrade paths, extension allowlist governance.

Foundations

An extension is not a downloaded package in the way an npm or pip package is; it is a set of files that must already exist on the machine's filesystem before SQL ever runs.

At minimum, an extension ships a control file (extname.control) that declares its name, default version, comment, and whether it is relocatable.

Alongside the control file sits one or more SQL scripts that define the actual objects: types, functions, operators, casts, or index support routines.

Some extensions add a third piece, a compiled shared library (a .so file on Linux), which the SQL script's function definitions point to via AS 'MODULE_PATHNAME'.

CREATE EXTENSION reads the control file, runs the matching install script inside a transaction, and records the result as a single row in the pg_extension catalog.

That catalog row is what makes an extension a first-class, trackable object rather than a loose pile of functions someone happened to run once.

pg_available_extensions shows what control files the server can see on disk, while pg_extension shows what has actually been installed into the current database.

A useful analogy is a plugin architecture: the control file is the manifest, the SQL script is the installer, and the shared library (when present) is the compiled plugin binary.

Because the manifest and installer are just files, an extension being "available" says nothing about whether the underlying OS package was ever installed on that host.

Mechanics & Interactions

The server treats extension code in two very different ways depending on when it needs to run.

Pure-SQL extensions (types, functions written in SQL or PL/pgSQL, views) need nothing beyond the normal catalog lookup that already happens for any database object.

Extensions backed by a C shared library are dynamically loaded on first use inside a backend process, the same way any other loadable module is resolved.

A small subset of extensions need something more: they register background workers, custom shared-memory segments, or hooks that must exist before the postmaster accepts its first connection.

Those extensions must be named in shared_preload_libraries in postgresql.conf, because the postmaster reads that setting once at startup and allocates shared memory accordingly.

This is why adding an entry to shared_preload_libraries requires a full server restart, while most CREATE EXTENSION calls do not.

The dependency relationship between extensions is not just informal documentation, since PostgreSQL records it in pg_depend, and CREATE EXTENSION can declare a REQUIRES list in its control file so dependent extensions install automatically or fail clearly.

Versioning follows the same graph logic: each extension has a default version plus a set of update scripts named like extname--1.0--1.1.sql, and ALTER EXTENSION ... UPDATE walks that chain step by step rather than jumping straight to the target version.

If a link in that chain is missing, for example a package ships 1.0 and 1.2 but not the 1.0--1.1 or 1.1--1.2 script, the upgrade simply cannot proceed until the missing script is installed.

-- The dependency graph is queryable, not just conceptual
SELECT dep.refobjid::regclass AS depends_on,
       ext.extname          AS extension
FROM pg_depend dep
JOIN pg_extension ext ON ext.oid = dep.objid
WHERE dep.deptype = 'e';

Relocatable extensions can be moved to a different schema after install, while non-relocatable ones (PostGIS is a common example) hard-code schema-qualified references in their install script and resist a clean move.

Advanced Considerations & Applications

At scale, the extension system becomes a governance problem as much as a technical one, because every C-backed extension executes inside the same process and memory space as PostgreSQL core.

That shared memory space is exactly why untrusted extensions historically required superuser, since a malicious or buggy shared library can read arbitrary server memory, crash the postmaster, or bypass row-level security entirely.

PostgreSQL's trusted-extension mechanism narrows that risk by allowing specific, audited extensions to be installed by a non-superuser role that merely holds CREATE privilege on the target database, without granting that role the ability to load arbitrary C code.

Managed cloud providers lean on this same boundary from the other direction, curating pg_available_extensions down to a vetted allowlist so customers never see control files for extensions the platform has not reviewed.

Extension conflicts are another advanced concern: two extensions can define an operator or function with the same name in the same schema, and the one installed second simply fails or silently shadows the first depending on search path order.

Upgrade orchestration compounds across a cluster, since a Patroni-managed replica set needs matching shared library binaries on every node before ALTER EXTENSION UPDATE runs anywhere, or the replica risks loading a library version its WAL stream does not expect.

The dependency graph also matters for logical replication, where a subscriber database must have the same extension-provided types and functions installed before it can apply changes that reference them.

Common Misconceptions

• "CREATE EXTENSION downloads and compiles the code" - it does not; the control file, SQL scripts, and any shared library must already be present on the host via an OS package, and CREATE EXTENSION only registers and runs what is already there.
• "Every extension needs a restart" - only extensions listed in shared_preload_libraries do, because that setting is read once at postmaster startup, while the majority of extensions activate immediately inside the current session.
• "Extensions are global to the server" - they are installed per-database, so a cluster can have vector in one database and nothing extra in another even though both share the same PostgreSQL binary.
• "Trusted means safe from any risk" - trusted only means the extension does not require superuser to install, not that it is free of bugs, license concerns, or CVEs.
• "Dropping an extension only removes its own objects" - DROP EXTENSION can cascade to anything that depends on those objects, including views, indexes, and other extensions, unless you review pg_depend first.

FAQs

Related

• Extensions Basics - the step-by-step CREATE EXTENSION and shared_preload_libraries recipe this page builds a mental model for
• Trusted vs Untrusted Extensions - a deeper look at the trust boundary introduced above
• Extension Upgrade Path - the operational procedure for walking the version upgrade graph
• Extension Allowlist Policy - governance practices for deciding which extensions are approved
• Extensions Best Practices - the operational checklist that complements this conceptual model

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.