Extensions Basics

PostgreSQL extensions add types, functions, and background workers without forking the server. Install them deliberately, track versions, and know which ones require a restart via shared_preload_libraries.

Recipe

-- List available extensions in the cluster
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;
 
-- Install into a schema (common pattern for PostGIS, pgvector)
CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION IF NOT EXISTS vector
  WITH SCHEMA extensions
  VERSION '0.8.0';
 
-- Extensions that need preload (example: pg_stat_statements)
-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- Then restart, then:
CREATE EXTENSION pg_stat_statements;

When to reach for this: You need capabilities beyond core SQL (vectors, geospatial, stats, audit) and want them versioned like application dependencies.

Working Example

-- Bootstrap a new database with pinned extensions
BEGIN;
 
CREATE SCHEMA IF NOT EXISTS extensions;
GRANT USAGE ON SCHEMA extensions TO app_role;
 
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions VERSION '0.8.0';
 
-- Verify what is installed
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 IN ('pgcrypto', 'vector');
 
COMMIT;

What this demonstrates:

Dedicated extensions schema keeps public clean
VERSION pins the extension at install time
pg_extension is the inventory source of truth
Some extensions are schema-agnostic; others (PostGIS) have ordering requirements

Deep Dive

How Extensions Work

Extensions ship SQL scripts plus optional C libraries registered in pg_available_extensions.
CREATE EXTENSION runs the install script and records metadata in pg_extension.
ALTER EXTENSION ... UPDATE applies migration scripts between versions.
DROP EXTENSION removes objects the extension owns; use CASCADE only after review.

shared_preload_libraries

Extension	Preload required?	Notes
`pg_stat_statements`	Yes	Tracks normalized query stats
`pg_cron`	Yes	Background job scheduler
`vector` (pgvector)	No	Loads on `CREATE EXTENSION`
`postgis`	No	Heavy SQL install, no preload

Preload extensions need a full server restart. Plan maintenance windows before enabling them in production.

Version and Packaging

-- See installed vs available
SELECT extname,
       extversion AS installed,
       (SELECT default_version
        FROM pg_available_extensions a
        WHERE a.name = e.extname) AS available_default
FROM pg_extension e;

On managed Postgres (RDS, Cloud SQL, Neon), only pre-approved extensions appear in pg_available_extensions. Self-hosted clusters install packages via OS (postgresql-18-pgvector, postgis) before CREATE EXTENSION succeeds.

SQL Notes

Grant USAGE on the extension schema; avoid granting CREATE on public to app roles.
Use IF NOT EXISTS in idempotent migration scripts.
Document extension versions in infrastructure-as-code alongside Postgres major version.

Gotchas

Missing OS package - CREATE EXTENSION vector fails with "could not open extension control file." Fix: Install the extension package for your Postgres major version first.
Wrong schema - PostGIS objects land in public by default and clutter search paths. Fix: CREATE EXTENSION postgis WITH SCHEMA extensions; on greenfield databases.
Preload without restart - Adding shared_preload_libraries and running CREATE EXTENSION immediately fails. Fix: Restart Postgres, then create the extension.
Superuser-only install - Most extensions require elevated privileges. Fix: DBA installs; app role gets schema USAGE only.
Version skew across replicas - Standby has older extension binaries after a primary upgrade. Fix: Upgrade all nodes to matching extension packages before ALTER EXTENSION UPDATE.
DROP EXTENSION CASCADE - Removes dependent objects you did not expect. Fix: pg_depend audit before drop; prefer ALTER EXTENSION updates over reinstall.

Alternatives

Alternative	Use When	Don't Use When
Core Postgres only	Simple CRUD, no special types	You need vectors, GIS, or advanced stats
Application-layer libraries	Extension not available on managed host	You need index-backed search in SQL
Foreign data wrappers	Data lives in another system	Latency and join complexity are unacceptable
Separate search/OLAP service	Massive scale, dedicated team	Operational cost of another datastore is too high

FAQs

Can I install extensions without superuser?

PostgreSQL 18 supports trusted extensions for non-superusers when CREATE privilege is granted on the extension. Most orgs still centralize installs with the DBA role.

Where are extension files on disk?

Control and SQL files live under SHAREDIR/extension/. Libraries live in PKGLIBDIR. Use pg_config --sharedir to locate them.

Does CREATE EXTENSION run in a transaction?

Yes. A failed install rolls back. Preload configuration changes still require a restart outside any transaction.

How do I move an extension to another schema?

Not supported directly. Drop and recreate on empty databases, or accept the original schema on brownfield systems.

What is the difference between extension and procedural language?

plpgsql is installed as an extension. Languages and types follow the same pg_extension inventory model.

Can two databases have different extensions?

Yes. Extensions are per-database. Cluster-wide settings like shared_preload_libraries apply to all databases on the instance.

How do I block extensions?

Set allow_in_place_tablespaces aside; use pg_extension_config hooks or revoke CREATE on untrusted extensions. See allowlist policy article.

Do extensions survive pg_upgrade?

Usually yes if packages exist for the new major. Re-run ALTER EXTENSION UPDATE after upgrade.

What about extension in templates?

Install on template1 so new databases inherit them. Be cautious: every new DB pays install cost.

How do I list extension-owned objects?

SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector');

Trusted vs Untrusted Extensions - security review before install
Extension Upgrade Path - minor and major version bumps
Extension Allowlist Policy - enterprise governance
Extensions Best Practices - operational checklist
pgvector Basics - vector type and operators

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.

Extensions Basics

Recipe

-- List available extensions in the cluster
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;
 
-- Install into a schema (common pattern for PostGIS, pgvector)
CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION IF NOT EXISTS vector
  WITH SCHEMA extensions
  VERSION '0.8.0';
 
-- Extensions that need preload (example: pg_stat_statements)
-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- Then restart, then:
CREATE EXTENSION pg_stat_statements;

When to reach for this: You need capabilities beyond core SQL (vectors, geospatial, stats, audit) and want them versioned like application dependencies.

Working Example

-- Bootstrap a new database with pinned extensions
BEGIN;
 
CREATE SCHEMA IF NOT EXISTS extensions;
GRANT USAGE ON SCHEMA extensions TO app_role;
 
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions VERSION '0.8.0';
 
-- Verify what is installed
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 IN ('pgcrypto', 'vector');
 
COMMIT;

What this demonstrates:

Dedicated extensions schema keeps public clean
VERSION pins the extension at install time
pg_extension is the inventory source of truth
Some extensions are schema-agnostic; others (PostGIS) have ordering requirements

Deep Dive

How Extensions Work

Extensions ship SQL scripts plus optional C libraries registered in pg_available_extensions.
CREATE EXTENSION runs the install script and records metadata in pg_extension.
ALTER EXTENSION ... UPDATE applies migration scripts between versions.
DROP EXTENSION removes objects the extension owns; use CASCADE only after review.

shared_preload_libraries

Extension	Preload required?	Notes
`pg_stat_statements`	Yes	Tracks normalized query stats
`pg_cron`	Yes	Background job scheduler
`vector` (pgvector)	No	Loads on `CREATE EXTENSION`
`postgis`	No	Heavy SQL install, no preload

Preload extensions need a full server restart. Plan maintenance windows before enabling them in production.

Version and Packaging

-- See installed vs available
SELECT extname,
       extversion AS installed,
       (SELECT default_version
        FROM pg_available_extensions a
        WHERE a.name = e.extname) AS available_default
FROM pg_extension e;

SQL Notes

Grant USAGE on the extension schema; avoid granting CREATE on public to app roles.
Use IF NOT EXISTS in idempotent migration scripts.
Document extension versions in infrastructure-as-code alongside Postgres major version.

Gotchas

Missing OS package - CREATE EXTENSION vector fails with "could not open extension control file." Fix: Install the extension package for your Postgres major version first.
Wrong schema - PostGIS objects land in public by default and clutter search paths. Fix: CREATE EXTENSION postgis WITH SCHEMA extensions; on greenfield databases.
Preload without restart - Adding shared_preload_libraries and running CREATE EXTENSION immediately fails. Fix: Restart Postgres, then create the extension.
Superuser-only install - Most extensions require elevated privileges. Fix: DBA installs; app role gets schema USAGE only.
Version skew across replicas - Standby has older extension binaries after a primary upgrade. Fix: Upgrade all nodes to matching extension packages before ALTER EXTENSION UPDATE.
DROP EXTENSION CASCADE - Removes dependent objects you did not expect. Fix: pg_depend audit before drop; prefer ALTER EXTENSION updates over reinstall.

Alternatives

Alternative	Use When	Don't Use When
Core Postgres only	Simple CRUD, no special types	You need vectors, GIS, or advanced stats
Application-layer libraries	Extension not available on managed host	You need index-backed search in SQL
Foreign data wrappers	Data lives in another system	Latency and join complexity are unacceptable
Separate search/OLAP service	Massive scale, dedicated team	Operational cost of another datastore is too high

FAQs

Can I install extensions without superuser?

PostgreSQL 18 supports trusted extensions for non-superusers when CREATE privilege is granted on the extension. Most orgs still centralize installs with the DBA role.

Where are extension files on disk?

Control and SQL files live under SHAREDIR/extension/. Libraries live in PKGLIBDIR. Use pg_config --sharedir to locate them.

Does CREATE EXTENSION run in a transaction?

Yes. A failed install rolls back. Preload configuration changes still require a restart outside any transaction.

How do I move an extension to another schema?

Not supported directly. Drop and recreate on empty databases, or accept the original schema on brownfield systems.

What is the difference between extension and procedural language?

plpgsql is installed as an extension. Languages and types follow the same pg_extension inventory model.

Can two databases have different extensions?

Yes. Extensions are per-database. Cluster-wide settings like shared_preload_libraries apply to all databases on the instance.

How do I block extensions?

Set allow_in_place_tablespaces aside; use pg_extension_config hooks or revoke CREATE on untrusted extensions. See allowlist policy article.

Do extensions survive pg_upgrade?

Usually yes if packages exist for the new major. Re-run ALTER EXTENSION UPDATE after upgrade.

What about extension in templates?

Install on template1 so new databases inherit them. Be cautious: every new DB pays install cost.

How do I list extension-owned objects?

SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector');

Trusted vs Untrusted Extensions - security review before install
Extension Upgrade Path - minor and major version bumps
Extension Allowlist Policy - enterprise governance
Extensions Best Practices - operational checklist
pgvector Basics - vector type and operators

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.

Extensions Basics

Search Documentation

Extensions Basics