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

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

FAQs

Related

• 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.