Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 90 ยท Extensions and Ecosystem

Extensions

Learn how PostgreSQL extensions work: CREATE EXTENSION, contrib modules, pg_available_extensions, trusted extensions and managed-service allowlists.

Most databases ship with a fixed set of features. PostgreSQL ships with a fixed set too, but it was designed so that you can add more without touching the server's source code. New data types, operators, index types, functions, even whole query languages can be packaged up and loaded with one command. These packages are called extensions, and they are a big reason PostgreSQL is used for jobs as different as fuzzy text search, geographic maps and vector search for AI.

In this lesson you will learn what an extension actually is, how CREATE EXTENSION works, how to see what your server offers, who is allowed to install what, and what to expect on managed cloud services. Extensions need a real PostgreSQL server, so everything that talks to the server is shown as read-only code. The live editors in this lesson use small stand-ins in SQLite so that you can practice the queries you would run against the catalog views.

What an extension is

An extension is a named bundle of database objects that PostgreSQL installs and removes as one unit. Typically it contains:

  • SQL objects: functions, operators, types, casts, tables, views.
  • Often a compiled shared library (a .so or .dll file) that implements the functions in C or Rust.
  • A control file that names the extension, its default version and its dependencies.
  • Version scripts that create the objects, plus upgrade scripts that move from one version to the next.

The important property is that the server remembers that all those objects belong together. Dropping the extension drops every object it created. A dump of your database (see Backup, Restore and Point-in-Time Recovery) records only a CREATE EXTENSION line, not the extension's contents, so restoring needs the same extension files installed on the target server.

What is inside an extension?

Control file Name, version, dependencies.
SQL script Creates types, functions, operators.
Shared library Optional compiled code for speed.
Upgrade scripts Move from one version to the next.
1 / 2

The last note on that slide trips up many beginners. Putting the files on the server (with apt, yum, a Docker image or your cloud provider's settings) makes the extension available. Running CREATE EXTENSION inside a database makes it installed there. Each database in a cluster needs its own CREATE EXTENSION, because the objects live in that database's catalogs. See Clusters, Databases and Schemas for how databases are separated.

CREATE EXTENSION in practice

The basic form is short. Using IF NOT EXISTS makes scripts safe to run twice:

SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;

The full syntax has a few options worth knowing:

SQL
CREATE EXTENSION IF NOT EXISTS citext
    WITH SCHEMA extensions
    VERSION '1.6'
    CASCADE;
  • WITH SCHEMA chooses where the objects go. Without it, they land in the first schema of the current search_path (usually public), which clutters your application's schema. Many teams create a schema called extensions and install everything there, then add it to the search_path. Not every extension is relocatable, so check its documentation first.
  • VERSION picks a specific version instead of the default. You rarely need it.
  • CASCADE automatically installs other extensions that this one depends on. For example, some geography and vector add-ons require a base extension.

Once an extension is installed, you manage it with a few more commands:

SQL
-- Move to the newest version shipped on the server
ALTER EXTENSION pg_trgm UPDATE;

-- Update to a specific version
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

-- Remove it. Fails if other objects (an index, a column type) use it.
DROP EXTENSION pg_trgm;

-- Remove it together with everything that depends on it. Dangerous.
DROP EXTENSION pg_trgm CASCADE;

DROP EXTENSION ... CASCADE can delete your data. If a table has a column of a type that the extension defined (a citext or vector column, for example), the cascade drops that column. Run the plain DROP EXTENSION first and read what it complains about.

In psql, the meta-command \dx lists the extensions installed in the current database, and \dx+ name lists the objects inside one of them. The psql lesson covers more meta-commands.

Seeing what is available and what is installed

Two places answer the question "what can I use?":

SQL
-- Everything the server could install, with the installed version if any
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

-- Only what is installed in the current database
SELECT extname, extversion, extnamespace::regnamespace AS schema
FROM pg_extension;

pg_available_extensions reads the control files on the server's disk, so it lists extensions whose files are present. The installed_version column is NULL when the extension is available but not created in your database. There is also pg_available_extension_versions, which lists every version and shows whether each one is trusted (more on that below).

Every database starts with at least one entry in pg_extension: plpgsql, the procedural language you will meet in PL/pgSQL Basics.

You can practice the shape of these queries without a server. The next editor builds a tiny table with the same four columns as pg_available_extensions (the data is made up for the exercise) and asks the questions you would ask the real view.

SQL Stand-in for pg_available_extensions

Press Run (or Ctrl + Enter) to see the result here.

The third query is the one that matters after a server upgrade: after you install a newer minor or major PostgreSQL version, extensions keep running the old SQL scripts until you run ALTER EXTENSION ... UPDATE in each database. (Here the result is empty, because the made-up data has no outdated entries; try changing one installed version to see a row appear.)

Contrib modules and third-party extensions

PostgreSQL's source tree contains a directory of extensions maintained by the project itself, called contrib. You will meet many of them in this track:

Extension What it adds
pg_trgm Fuzzy text matching and index support for LIKE '%x%'
hstore A simple key-value column type
citext A case-insensitive text type
pgcrypto Hashing, random bytes, encryption
uuid-ossp Functions that generate UUIDs
postgres_fdw Query tables in another PostgreSQL server
pg_stat_statements Statistics about every statement that ran
btree_gist, btree_gin Make plain types usable in GiST/GIN indexes (needed for some exclusion constraints)
pageinspect, pg_buffercache Low-level inspection tools for experts

Whether contrib is on your server depends on how PostgreSQL was installed. Some Linux distributions ship contrib inside the main server package; others have a separate package (postgresql-contrib on some systems), and Docker's official image includes it. If pg_available_extensions does not list what you expect, the files are not installed.

Beyond contrib there are thousands of third-party extensions, installed from their own packages or built from source: PostGIS for maps, pgvector for embeddings, TimescaleDB for time-series, pg_cron for scheduling, pg_partman for partition management, and many more. Quality varies, so look at who maintains it, how recently it released, and whether it supports your PostgreSQL version before you build on it.

Some extensions also need server configuration, not just CREATE EXTENSION. The ones that hook deep into the server (for example pg_stat_statements, pg_cron, auto_explain, TimescaleDB) must be listed in shared_preload_libraries in postgresql.conf, and the server must be restarted before they work:

SQL
-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements,pg_cron'

Who may install what: trusted extensions

Creating an extension used to require a superuser, because extension scripts can run arbitrary code with high privileges. Since PostgreSQL 13, an extension can be marked trusted in its control file. A trusted extension can be created by any role that has the CREATE privilege on the current database, no superuser needed.

Several contrib extensions are trusted, including pg_trgm, hstore, citext, pgcrypto, uuid-ossp, unaccent, btree_gin, btree_gist and ltree. Extensions that touch the file system, the network or server internals are not trusted: postgres_fdw, file_fdw, dblink, pg_stat_statements, pageinspect and pg_buffercache need a superuser (or a role your provider has granted special rights).

SQL
-- Which versions are trusted on this server?
SELECT name, version, trusted, superuser
FROM pg_available_extension_versions
WHERE name IN ('pg_trgm', 'postgres_fdw');

If you are an ordinary role and CREATE EXTENSION fails with "permission denied to create extension", the extension is not trusted and you need a more privileged role. Roles, Privileges and GRANT explains the privilege model.

Managed services and allowlists

Cloud-hosted PostgreSQL (Amazon RDS and Aurora, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon and others) does not give you a superuser or file-system access. Instead the provider publishes an allowlist: the extensions it has built and approved. Typical consequences:

  • Popular ones such as pg_trgm, pgcrypto, hstore, citext, PostGIS and pgvector are almost always available.
  • Anything the provider has not packaged cannot be installed at all, because you cannot upload files to the server.
  • Some need a setting changed through the provider's console (a parameter group or server parameter) instead of editing postgresql.conf yourself.
  • Version numbers differ from provider to provider and lag behind upstream releases.

Check the allowlist before you design around an extension. A schema that depends on an extension your host does not offer is a painful thing to discover during a migration. A simple habit is to run the pg_available_extensions query above on your target environment first.

The next editor models that check: a list of extensions your application needs, compared with a provider's allowlist.

SQL Does the host support what we need?

Press Run (or Ctrl + Enter) to see the result here.

timescaledb shows up as MISSING, which tells you to either pick another host or redesign that part, for example with plain partitioning (see Time-Series, Queues and Caching in PostgreSQL).

Extension hygiene

A few habits keep extension use healthy:

  1. Put CREATE EXTENSION IF NOT EXISTS into your migrations, so a fresh database gets everything it needs. See Schema Changes and Migrations.
  2. Install into a dedicated schema when the extension allows it, and remember to add that schema to the search_path of the roles that use it.
  3. Update extensions after upgrading PostgreSQL, in every database.
  4. Test restores. A dump with a missing extension fails to restore, so staging servers must have the same extension files as production.
  5. Prefer trusted, widely used extensions unless you have a clear need. Every extension is another thing to upgrade.
  6. Do not install what you do not use. Unused extensions still add attack surface and upgrade work.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A package manager has put the pgvector files on the server. What must you still do before using it in your shop database?

    Files on disk make the extension available, but the objects only exist in a database after CREATE EXTENSION runs there. Extensions are per-database, not per-cluster.
  2. 2. What does installed_version IS NULL mean for a row in pg_available_extensions?

    pg_available_extensions lists what the server's files offer. A NULL installed_version means CREATE EXTENSION has not been run in the database you are connected to.
  3. 3. Which statement about trusted extensions is correct?

    Since PostgreSQL 13 the control file can mark an extension as trusted, and ordinary roles with CREATE on the database may create it. Extensions like pg_trgm and citext are trusted; postgres_fdw is not.
  4. 4. Why is DROP EXTENSION citext CASCADE risky?

    CASCADE follows dependencies. A column whose type came from the extension is a dependent object, so the column and its data would be removed.
  5. 5. Your extension must be listed in shared_preload_libraries. What does that imply?

    Libraries in shared_preload_libraries are loaded when the server starts, so changing the setting needs a restart (or your provider's equivalent).

Summary

  • An extension bundles SQL objects, optional compiled code and version scripts, and installs or removes them as one unit.
  • Files on the server make an extension available; CREATE EXTENSION installs it in one database. Use IF NOT EXISTS in migrations.
  • pg_available_extensions shows what you can install; pg_extension and \dx show what is installed. Run ALTER EXTENSION ... UPDATE after upgrading the server.
  • Contrib extensions come from the PostgreSQL project; third-party ones such as PostGIS and pgvector come from other maintainers.
  • Since PostgreSQL 13, trusted extensions can be created by non-superusers with CREATE on the database.
  • Managed services only allow what their allowlist contains. Check it before designing around an extension.
  • Some extensions need shared_preload_libraries and a restart.

Next: pg_trgm, hstore, citext, pgcrypto and uuid-ossp, five extensions you will use again and again.