Extension Management in PostgreSQL for New DBAs

PostgreSQL’s true power doesn’t just come from its rock-solid relational engine, but it’s the fact that Postgres can grow with you. Extensions allow you to bolt on new capabilities, enhance performance, integrate external tools, and transform the database into something far more powerful than its default installation, which is something I’m really learning to love.

From pg_stat_statements to pgvector, logical decoding plugins, job schedulers, and custom procedural languages, Postgres extensions behave like feature packs you can enable at the database level. That also means DBAs must know how to inspect, maintain, and manage them just as carefully as any schema or server configuration.

This post walks through the essentials of checking available extensions, verifying versions, installing them safely, and quickly assessing what’s loaded in your environment.

Viewing All Available Extensions

To see every extension your current PostgreSQL instance supports — including those not yet installed — query the pg_available_extensions view.

SELECT * FROM pg_available_extensions;

This gives you:

• Extension name
• Default version shipped with your Postgres build
• Installed version (if any)
• Description/documentation comment

This view is your first stop when validating what’s possible on a given cluster, especially if you’ve upgraded PostgreSQL or added new packages.

Checking Installed Extensions & Version Status

To view only those extensions currently installed (and whether they match the default version or need an upgrade) use:

SELECT name,
 default_version,
 installed_version,
 comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

Example output:

        name        | default_version | installed_version |                                comment
-------------------+-----------------+-------------------+---------------------------
pg_stat_statements | 1.11            | 1.11              | track planning and execution statistics of all SQL statements executed
 pgagent           | 4.2             | 4.2               | A PostgreSQL job scheduler
plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
(3 rows)

In this case, the installed extensions:

• pg_stat_statements
• pgagent
• plpgsql

…are all running their default versions, which makes comparison against documentation or best practice guides incredibly easy.

If you’re troubleshooting performance, managing security posture, or preparing for a Postgres upgrade, confirming versions here is critical.

Creating an Extension

Installing an extension is as straightforward as it gets:

CREATE EXTENSION <extension name>;

This is executed at the database level, so remember, this is not at the cluster level. That means you choose which databases receive which capabilities. This also means extensions can be installed incorrectly (wrong DB, wrong owner, wrong schema), so always double-check before deploying production changes.

Verifying Extension Installation

After installation, confirm what’s loaded with:

SELECT oid, extname FROM pg_extension;

Example:

  oid  |      extname

-------+--------------------

 15101 | plpgsql

 16389 | pgagent

 44351 | pg_stat_statements

(3 rows)

Any extension successfully created appears here. If it doesn’t, the create operation didn’t complete, or privileges prevented installation.

Faster: Using \dx in psql

I am learning to love \* shortcuts, so if you’re working in the psql command line, the quickest view is:

postgres=# \dx

                         List of installed extensions

      Name        | Version |   Schema   |                              Description

------------------+---------+------------+-----------------------------------------

pg_stat_statements | 1.11    | public     | track planning and execution statistics of all SQL statements executed

pgagent            | 4.2     | pgagent    | A PostgreSQL job scheduler

plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language

(3 rows)

\dx is essentially a shortcut to the same information as the catalog query  and it’s just much faster to type.
For day-to-day DBA work, this becomes your go-to readability tool.

Why It Matters

Postgres extensions enable capability… but they also introduce risk if unmanaged:

• Version drift after upgrades can cause unexpected behavior.
• Security considerations vary per extension (schemas, grants, shared libraries).
• Operational impact such as extensions like pg_stat_statements add overhead if misconfigured.
• Dependency management is real for things like PostGIS or logical decoding plugins.

A smart DBA treats extensions like packages in Linux or modules in Oracle or SQL Server:
You track them. You audit them. You maintain them.

Final Thoughts

Extension management is one of the features that makes PostgreSQL amazingly flexible, but flexibility comes with responsibility. By learning how to inspect, validate, and maintain extensions properly, DBAs can ensure they’re running secure, performant, and consistent database environments.

If you’d like a follow-up article on:

• How extensions load into shared_preload_libraries
• Best practices for extension upgrades
• The must-have extensions for DBAs
• Schema placement and permission considerations
• Logical decoding, monitoring, or pgvector deep dives

Postgres is a playground, and it’s fun to play and learn what it’s all about!