Extensions Best Practices

Q: Minimum extensions for RAG?

Usually vector only. Add pgcrypto if you hash API keys at rest.

Q: IaC pin example?

Terraform engine_version + Ansible postgresql-18-pgvector package version + Flyway SQL VERSION '0.8.0'.

Q: Detect shadow installs?

Nightly job comparing pg_extension to allowlist; alert on diff.

Q: Drop unused extensions?

Yes, after pg_depend review. Reduces upgrade and CVE surface.

Q: Multiple extension schemas?

Prefer one extensions schema. Multiple schemas complicate grants.

How to Use This List

Run through before approving any new CREATE EXTENSION in production.
Pin versions in Terraform, Ansible, and migration SQL together.
Revisit quarterly against pg_extension inventory exports.

A - Governance

Maintain a written allowlist ADR. Every installed extension maps to owner, version pin, and review date.
Block untrusted extensions by default. plpython3u, file_fdw, and dblink require CISO exception.
Centralize install authority. Dedicated dba_deploy role, not shared superuser passwords.
Log extension DDL. Event trigger or pgaudit captures CREATE EXTENSION with actor and database.
Same policy in staging. Experimental installs drift into backup restores and migration scripts.

B - Install and Layout

Use a dedicated extensions schema. Keeps public clean and clarifies search_path for app roles.
Pin version at install: CREATE EXTENSION vector WITH SCHEMA extensions VERSION '0.8.0';
Install OS packages before SQL. Extension control files must exist for your Postgres major (18).
Configure preload before restart. Add shared_preload_libraries entries only with a planned reboot window.
Grant USAGE, not CREATE, to application roles. Apps call functions; they do not install extensions.

C - Upgrades and Operations

Upgrade packages on all nodes first. Patroni replicas and standbys need matching .so binaries before ALTER EXTENSION UPDATE.
Smoke test after every extension bump. PostGIS distance query, pgvector distance query, pgcrypto encrypt round-trip.
Plan reindex for index opclass changes. pgvector and PostGIS release notes flag mandatory REINDEX.
Loop all databases. template1, postgres, and analytics DBs are easy to miss.
Archive inventory quarterly. SELECT * FROM pg_extension per database stored with change ticket IDs.

D - Security and Compliance

Revoke PUBLIC execute on sensitive functions. Extension install does not imply app-wide callable.
Keep extensions out of logical replication DDL roles. Subscribers must match versions before replay.
Scan for CVEs on pinned versions. pgvector, PostGIS, and pgcrypto have independent release cycles from Postgres core.
Document license implications. PostGIS GPL may matter for SaaS packaging; record in ADR.
Validate restore runbooks. PITR restore re-applies old extension set; run inventory hook post-restore.

FAQs

Minimum extensions for RAG?

Usually `vector` only. Add `pgcrypto` if you hash API keys at rest.

Should extensions live in template1?

Only if every new database needs them. Otherwise install per application database explicitly.

IaC pin example?

Terraform `engine_version` + Ansible `postgresql-18-pgvector` package version + Flyway SQL `VERSION '0.8.0'`.

Detect shadow installs?

Nightly job comparing `pg_extension` to allowlist; alert on diff.

Cloud vendor lag?

Internal pin cannot exceed what RDS/Cloud SQL/Neon exposes in `pg_available_extensions`.

Drop unused extensions?

Yes, after `pg_depend` review. Reduces upgrade and CVE surface.

Multiple extension schemas?

Prefer one `extensions` schema. Multiple schemas complicate grants.

Extension and connection poolers?

pgbouncer transaction mode is fine; extensions are server-side. Watch prepared statements with some drivers.

Document in runbooks?

Link ADR, install SQL, upgrade SQL, and rollback limitations (restore-only).

Next review trigger?

Postgres major upgrade, pgvector minor with index changes, or new product feature requiring PostGIS.

Extensions Basics - CREATE EXTENSION and preload
Extension Allowlist Policy - enterprise ADR
Extension Upgrade Path - ALTER EXTENSION UPDATE
Trusted vs Untrusted Extensions - security review
Managed Postgres Basics - cloud extension matrices

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 Best Practices

How to Use This List

Run through before approving any new CREATE EXTENSION in production.
Pin versions in Terraform, Ansible, and migration SQL together.
Revisit quarterly against pg_extension inventory exports.

A - Governance

Maintain a written allowlist ADR. Every installed extension maps to owner, version pin, and review date.
Block untrusted extensions by default. plpython3u, file_fdw, and dblink require CISO exception.
Centralize install authority. Dedicated dba_deploy role, not shared superuser passwords.
Log extension DDL. Event trigger or pgaudit captures CREATE EXTENSION with actor and database.
Same policy in staging. Experimental installs drift into backup restores and migration scripts.

B - Install and Layout

Use a dedicated extensions schema. Keeps public clean and clarifies search_path for app roles.
Pin version at install: CREATE EXTENSION vector WITH SCHEMA extensions VERSION '0.8.0';
Install OS packages before SQL. Extension control files must exist for your Postgres major (18).
Configure preload before restart. Add shared_preload_libraries entries only with a planned reboot window.
Grant USAGE, not CREATE, to application roles. Apps call functions; they do not install extensions.

C - Upgrades and Operations

Upgrade packages on all nodes first. Patroni replicas and standbys need matching .so binaries before ALTER EXTENSION UPDATE.
Smoke test after every extension bump. PostGIS distance query, pgvector distance query, pgcrypto encrypt round-trip.
Plan reindex for index opclass changes. pgvector and PostGIS release notes flag mandatory REINDEX.
Loop all databases. template1, postgres, and analytics DBs are easy to miss.
Archive inventory quarterly. SELECT * FROM pg_extension per database stored with change ticket IDs.

D - Security and Compliance

Revoke PUBLIC execute on sensitive functions. Extension install does not imply app-wide callable.
Keep extensions out of logical replication DDL roles. Subscribers must match versions before replay.
Scan for CVEs on pinned versions. pgvector, PostGIS, and pgcrypto have independent release cycles from Postgres core.
Document license implications. PostGIS GPL may matter for SaaS packaging; record in ADR.
Validate restore runbooks. PITR restore re-applies old extension set; run inventory hook post-restore.

FAQs

Minimum extensions for RAG?

Usually `vector` only. Add `pgcrypto` if you hash API keys at rest.

Should extensions live in template1?

Only if every new database needs them. Otherwise install per application database explicitly.

IaC pin example?

Terraform `engine_version` + Ansible `postgresql-18-pgvector` package version + Flyway SQL `VERSION '0.8.0'`.

Detect shadow installs?

Nightly job comparing `pg_extension` to allowlist; alert on diff.

Cloud vendor lag?

Internal pin cannot exceed what RDS/Cloud SQL/Neon exposes in `pg_available_extensions`.

Drop unused extensions?

Yes, after `pg_depend` review. Reduces upgrade and CVE surface.

Multiple extension schemas?

Prefer one `extensions` schema. Multiple schemas complicate grants.

Extension and connection poolers?

pgbouncer transaction mode is fine; extensions are server-side. Watch prepared statements with some drivers.

Document in runbooks?

Link ADR, install SQL, upgrade SQL, and rollback limitations (restore-only).

Next review trigger?

Postgres major upgrade, pgvector minor with index changes, or new product feature requiring PostGIS.

Extensions Basics - CREATE EXTENSION and preload
Extension Allowlist Policy - enterprise ADR
Extension Upgrade Path - ALTER EXTENSION UPDATE
Trusted vs Untrusted Extensions - security review
Managed Postgres Basics - cloud extension matrices

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 Best Practices

Search Documentation

Extensions Best Practices