How extensions work

conceptThe concept

An extension is a control file plus SQL scripts (and often a shared library) on the server. CREATE EXTENSION name runs the install script and records every object it creates as a member of the extension, so DROP EXTENSION removes them together and pg_dump emits just the CREATE EXTENSION line.

• pg_available_extensions lists what the server has files for; pg_extension (or \dx) what is installed in this database.
• Versions: ALTER EXTENSION name UPDATE runs the upgrade scripts after new binaries are installed.
• Trusted extensions (since 13) can be installed by non-superusers with CREATE privilege on the database.
• Extensions that hook into the server, such as pg_stat_statements, auto_explain or pg_cron, must also be listed in shared_preload_libraries, which needs a restart.

whyWhy it matters

Extensions decide what you can run where. A managed service supports a fixed list; a major-version upgrade needs every extension available for the new version; and a missing extension on a restore target makes pg_restore fail.

Use caseReal-world scenario

Before moving to a managed service, the team runs SELECT extname, extversion FROM pg_extension on every database and checks each one against the provider's supported list. One unsupported extension changes the migration plan.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, default_version, installed_version, left(comment, 60) AS comment
FROM pg_available_extensions
WHERE name IN ('pg_trgm', 'pg_stat_statements', 'pageinspect', 'vector', 'pgcrypto', 'citext', 'hstore', 'ltree', 'btree_gist', 'amcheck')
ORDER BY installed_version IS NULL, name;

BEGIN;
CREATE EXTENSION citext;
SELECT e.extname, e.extversion, n.nspname AS schema FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace WHERE e.extname = 'citext';
SELECT count(*) AS member_objects FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'citext') AND deptype = 'e';
ROLLBACK;

SELECT DISTINCT name, trusted, superuser
FROM pg_available_extension_versions
WHERE name IN ('citext', 'pgcrypto', 'hstore', 'pageinspect', 'pg_stat_statements', 'vector')
ORDER BY name;

\dx

internalsUnder the hood

Extension objects are linked to the extension in pg_depend with deptype e. That is how pg_dump knows to skip them and how DROP EXTENSION finds them. Configuration tables an extension wants dumped are registered with pg_extension_config_dump.

Avoid / preferDo this, not that

CREATE EXTENSION pgcrypto;
-- installed into whatever schema is first
-- on the search_path

CREATE SCHEMA extensions;
CREATE EXTENSION pgcrypto SCHEMA extensions;

-- upgrade binaries, then forget
SELECT extversion FROM pg_extension;  -- still old

ALTER EXTENSION vector UPDATE;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

PitfallsCommon mistakes

• Assuming an extension installed in one database is available in all: extensions are per database.
• Forgetting shared_preload_libraries for pg_stat_statements: CREATE EXTENSION succeeds but the views error until restart.
• Upgrading the major version without checking that every extension has a build for the new version.

LimitsWhen not to use it

Avoid extensions with no maintainer, no support on your target platforms, or that duplicate core features (for example uuid-ossp when gen_random_uuid() and uuidv7() are built in).

SecuritySecurity notes

Extensions run code inside the server process; a C extension has the same access as PostgreSQL itself. Only install packages from trusted sources, prefer trusted extensions for application roles, and audit pg_extension regularly. CREATE EXTENSION scripts run with elevated rights, so a writable schema on the search_path during installation is a risk: install into a dedicated schema.

ChallengeTry it yourself

BEGIN;
CREATE SCHEMA ext;
CREATE EXTENSION hstore SCHEMA ext;
SELECT e.extname, n.nspname, e.extversion,
       (SELECT count(*) FROM pg_depend d JOIN pg_proc p ON p.oid = d.objid
        WHERE d.refobjid = e.oid AND d.deptype = 'e' AND d.classid = 'pg_proc'::regclass) AS functions
FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'hstore';
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• CREATE EXTENSION
• Additional supplied modules
• Packaging related objects