How to Manage Extensions in PostgreSQL

One of the things that convinced me PostgreSQL was the right database for the long haul was discovering just how much capability I could add to it without switching tools entirely. Need UUIDs? There’s an extension. Need fuzzy text matching? There’s an extension. Need to query another database, encrypt columns, or add specialized index types for geospatial data? Extensions again. In this article, I want to walk through how PostgreSQL’s extension system actually works — installing, managing, upgrading, and removing extensions — along with the practical lessons I’ve learned about which ones are worth reaching for and how to manage them safely across environments.

What an Extension Actually Is

A PostgreSQL extension is a packaged bundle of SQL objects — functions, data types, operators, index types, or entire subsystems — that can be installed into a database with a single command and removed just as cleanly. Before extensions existed as a first-class concept, adding this kind of functionality meant manually running a pile of SQL scripts and hoping you could reverse them later if something went wrong. Extensions solve that by tracking exactly what belongs to them, so PostgreSQL can install, upgrade, and cleanly uninstall the whole bundle as a unit.

Extensions can be written in C (compiled shared libraries plus SQL), in pure SQL/PL/pgSQL, or through more advanced procedural language wrappers — but from a user’s perspective, you rarely need to care about the implementation. You just need to know how to install and manage them.

Two Separate Steps: Available vs. Installed

This trips people up constantly, so I want to be explicit about it: there’s a difference between an extension being available on the server (the files exist on disk, usually installed via your OS package manager) and it being installed in a specific database (registered via CREATE EXTENSION).

Seeing what’s available:

SELECT * FROM pg_available_extensions ORDER BY name;

Seeing what’s actually installed in your current database:

SELECT * FROM pg_extension;

If an extension shows up in pg_available_extensions but you try CREATE EXTENSION and it fails, that usually means the extension files genuinely aren’t installed at the OS level despite what you might expect — double-check by actually looking for the extension’s .control file (usually somewhere like /usr/share/postgresql/<version>/extension/).

Installing an Extension

Once you’ve confirmed an extension is available, installing it is a single command:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

I always include IF NOT EXISTS in migration scripts so re-running them doesn’t error out on an extension that’s already installed.

Extensions are installed per-database, into a specific schema (by default, whatever schema is first in your search_path, though you can override it):

CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA extensions;

I’ve picked up the habit of putting extensions in a dedicated extensions schema rather than public, especially on shared or multi-tenant databases — it keeps extension-provided objects clearly separated from application objects and makes permission management more predictable.

Extensions I Reach for Constantly

A quick tour of the ones that show up in nearly every project I work on:

• pgcrypto — cryptographic functions: hashing, symmetric encryption, and generating secure random values. I use it for things like hashing sensitive lookup values or generating secure tokens directly in SQL.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('my_password', gen_salt('bf'));

• uuid-ossp (or the newer built-in gen_random_uuid() in modern PostgreSQL, which comes from pgcrypto or core depending on version) — generating UUIDs for primary keys.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

• pg_trgm — trigram-based text similarity, which I pair with full-text search for typo-tolerant matching, and which also enables efficient LIKE '%term%' queries via GIN/GiST indexes.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

• postgis — the gold standard for geospatial data in any relational database, not just PostgreSQL. If you’re storing coordinates, shapes, or doing any kind of “find things near this point” query, this is essential.
• hstore — a simple key-value store type, which I use less now that jsonb covers most of the same ground, but it’s still around and occasionally simpler for pure flat key-value needs.
• pg_stat_statements — tracks execution statistics for every query run against the server, which is invaluable for finding your slowest or most frequent queries.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Note that pg_stat_statements also needs to be added to shared_preload_libraries in postgresql.conf and requires a server restart, since it hooks into query execution at a lower level than most extensions.

• postgres_fdw and file_fdw — covered in depth in my other articles on foreign data wrappers.

Extensions That Require shared_preload_libraries

This is one of the more confusing aspects of extension management. Most extensions are entirely self-contained and just work after CREATE EXTENSION. But a handful — pg_stat_statements being the most common example — need to be loaded when the PostgreSQL server process starts, not just when a database connection is made. For those, you need to edit postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

and then restart PostgreSQL (a config reload isn’t enough for this particular setting). Only after that restart can you successfully run CREATE EXTENSION pg_stat_statements;. If you skip the restart and just try to create the extension, it’ll actually succeed at creating the SQL objects, but the statistics collection itself won’t be active until the library is actually preloaded.

Upgrading Extensions

Extensions have their own version numbers, independent of the PostgreSQL server version. When a newer version of an extension is available (usually after an OS package upgrade brings in new extension files), you upgrade it inside each database explicitly:

ALTER EXTENSION pg_trgm UPDATE;

You can also target a specific version if multiple are available:

ALTER EXTENSION pg_trgm UPDATE TO '1.6';

Checking your currently installed version versus what’s available:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trgm';
SELECT * FROM pg_available_extension_versions WHERE name = 'pg_trgm';

I always run this check after an OS-level package upgrade on the server — new extension binaries being available doesn’t mean your databases have picked up the new version, since ALTER EXTENSION ... UPDATE has to be run explicitly, per database.

Removing an Extension

DROP EXTENSION IF EXISTS pg_trgm;

Because extensions track exactly which objects belong to them, PostgreSQL will refuse to drop one if something in your database depends on it — for example, an index that uses gin_trgm_ops. You’ll get an error listing the dependency, and you have two options: drop the dependent object first, or use CASCADE if you’re sure you want everything gone.

DROP EXTENSION pg_trgm CASCADE;

I’m always cautious with CASCADE here — it’s easy to underestimate how many indexes or functions quietly depend on an extension you thought was only used in one place. I always run a dependency check first:

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

Extensions and Superuser Privileges

Historically, CREATE EXTENSION required superuser privileges, which was a real pain point for managed database services (like RDS or Cloud SQL) where you don’t get superuser access. Modern PostgreSQL improved this with the concept of “trusted” extensions — ones that are safe enough to be installed by a non-superuser role with the right grants, without needing full superuser access.

SELECT name, trusted FROM pg_available_extensions WHERE trusted = true;

If you’re on a managed database service, check your provider’s documentation for exactly which extensions they allow — most support a curated allowlist rather than the full universe of what’s technically available, since some extensions (especially ones providing untrusted procedural languages or filesystem access) are reasonably restricted for security reasons.

Managing Extensions Across Environments

One mistake I made early on was manually running CREATE EXTENSION on a production database during an incident, then forgetting to add it to my actual schema migrations. Months later, a fresh staging environment build failed mysteriously because the migrations assumed pgcrypto already existed. Now I always put extension creation directly into my migration files, right alongside the schema changes that depend on them:

-- migration: 0001_add_pgcrypto.sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

This way, spinning up a new environment from scratch — a fresh staging database, a new developer’s local setup, a disaster recovery restore — always ends up with the same extensions installed, in the same order, without anyone needing to remember a manual step.

Common Use Cases

• pgcrypto for hashing and encryption needs directly in SQL.
• pg_trgm for fuzzy search and fast LIKE queries.
• postgis for any geospatial workload.
• pg_stat_statements for query performance monitoring.
• postgres_fdw / file_fdw for cross-database and file-based querying.
• uuid-ossp or built-in UUID generation for primary keys in distributed or externally-facing systems.

Troubleshooting Tips

CREATE EXTENSION fails with “extension is not available.” The extension files aren’t installed at the OS level. Install the relevant package (often named postgresql-<version>-<extension> on Debian/Ubuntu-based systems) and try again.

Extension seems installed but functionality doesn’t work (e.g., pg_stat_statements shows no data). Check whether the extension requires shared_preload_libraries and a server restart — CREATE EXTENSION can succeed at creating the SQL objects while the underlying hook still isn’t active.

DROP EXTENSION fails due to dependent objects. Query pg_depend to find what depends on it before deciding whether to drop the dependent objects individually or use CASCADE.

Different extension versions across environments causing inconsistent behavior. Run SELECT extname, extversion FROM pg_extension; in each environment and reconcile with ALTER EXTENSION ... UPDATE where needed. This is especially important after major version upgrades or restoring from an older backup.

Non-superuser can’t create an extension on a managed service. Check whether the extension is marked trusted and whether your role has been granted the ability to create extensions (some managed providers use a dedicated role like rds_superuser with a curated set of allowed extensions).

Best Practices

1. Put CREATE EXTENSION statements inside your version-controlled migrations, not as manual one-off commands against production.
2. Use IF NOT EXISTS so migrations are safely re-runnable.
3. Consider a dedicated schema for extensions rather than dumping everything into public, especially on shared databases.
4. Check for shared_preload_libraries requirements before assuming an extension “isn’t working.”
5. Audit installed extensions periodically with pg_extension and compare versions across environments.
6. Understand dependencies before dropping — check pg_depend rather than reaching straight for CASCADE.
7. On managed database platforms, check the provider’s supported extension list before planning around a specific one — availability varies significantly between providers and even between service tiers.

A Real-World Example: Auditing Extensions Across a Fleet

On a project with dozens of PostgreSQL databases across staging, production, and several regional replicas, I once discovered that a security-relevant extension upgrade had been applied inconsistently — some databases had picked it up, others hadn’t, purely because whoever ran the upgrade at the time didn’t touch every database. That inconsistency is exactly the kind of thing that’s invisible until it causes a hard-to-reproduce bug or, worse, a security gap. Since then, I run a fleet-wide audit query as a matter of routine:

SELECT current_database() AS db, extname, extversion
FROM pg_extension
ORDER BY extname;

wrapped in a small script that runs this against every database in the fleet and diffs the results. Any database with an unexpectedly old extversion, or missing an extension that should be universal (like pg_stat_statements for monitoring), gets flagged for remediation. This is a cheap, mechanical check, but it’s caught real drift more than once — usually after a new database was spun up from an older template, or after a manual hotfix extension install on one node was never propagated to its siblings.

Extensions and Major Version Upgrades

One thing that’s bitten me during major PostgreSQL version upgrades (say, going from PostgreSQL 15 to 17): extensions don’t automatically carry forward correctly with every upgrade path. If you’re using pg_upgrade, the extension’s shared library files need to already be installed and available for the new major version before the upgrade runs, or the upgrade will fail partway through. I always check this ahead of time:

SELECT extname, extversion FROM pg_extension;

on the old version, then confirm every one of those extensions has a compatible package available for the target version, before scheduling the upgrade window. This is a small amount of upfront checking that avoids a very unpleasant discovery mid-upgrade, when rolling back is far more disruptive than double-checking would have been.

A Quick Reference: When an Extension Update Actually Takes Effect

I’ve found it useful to keep this mental model straight, since it’s a recurring source of confusion:

• Installing the OS package makes an extension available — visible in pg_available_extensions — but does nothing inside any database yet.
• CREATE EXTENSION registers it inside one specific database, creating its SQL objects there.
• ALTER EXTENSION ... UPDATE upgrades an already-installed extension to a newer version, but only after newer extension files are available at the OS level, and only in the database you run it against.
• shared_preload_libraries changes require a full server restart, regardless of which of the above steps you’ve also done, because they affect how the PostgreSQL server process itself starts up, not just what’s registered inside a database.

Keeping these four layers distinct in my head has saved me from a lot of “I updated the extension but nothing changed” confusion over the years.

Frequently Asked Questions

Do I need superuser to install any extension? Not always — “trusted” extensions can be installed by a sufficiently privileged non-superuser role. Check pg_available_extensions for the trusted flag, and check your managed provider’s documentation for exactly which extensions and roles they support.

Will dropping an extension delete my data? It depends on the extension. Dropping something like pg_trgm just removes its operators, functions, and index support — it won’t delete your table data, though it may break indexes or queries that depend on it, which is why checking pg_depend first matters.

Can two databases on the same server have different extension versions? Yes — extensions are installed and versioned per database, not per server, so it’s entirely possible (and a common source of drift) for the same extension to be at different versions across databases sharing one PostgreSQL instance.

Is it safe to run CREATE EXTENSION directly on production without testing? For simple, self-contained extensions, generally yes, but I still test in a staging environment first whenever possible, especially for anything requiring shared_preload_libraries and a restart, since that’s a more disruptive change than a typical extension install.

Wrapping Up

The extension system is, in my opinion, one of the most underappreciated things about PostgreSQL. It turns the database from a fixed set of built-in capabilities into a genuinely extensible platform, without sacrificing the reliability and transactional guarantees that make it trustworthy in the first place. Once you’re comfortable with the install/upgrade/remove lifecycle and know to check for shared_preload_libraries requirements, adding new capability to your database becomes as routine as writing a schema migration — because, if you do it right, that’s exactly what it is.