Right, let’s get you set up with the real power tools. Think of PostgreSQL as a brilliantly engineered, but somewhat minimalist, workshop. It has all the basics: a phenomenal tablesaw (tables), a precise drill press (indexes), and a sturdy workbench (transactions). But sometimes you need a biscuit joiner or a laser level. That’s where extensions come in. They’re not second-class citizens; they’re first-party add-ons that bolt directly into the core, supercharging your database with new data types, functions, operators, and indexes.

Installing one is almost comically simple. The most common way is the CREATE EXTENSION command. It feels like magic, but it’s the good kind of magic where you can actually see the wires and know how the trick works.

-- This is it. This is the whole incantation.
CREATE EXTENSION postgis;

Boom. You now have a full-fledged geographic information system living inside your database. You can now store points, polygons, and trajectories, calculate distances on a sphere, and ask questions like “find all the coffee shops within a 5-minute walk of my current, miserable location.” The command locates the extension’s files (which your package manager should have placed in the PostgreSQL SHAREDIR), and then runs its installation script. This script creates all the necessary objects—tables, functions, types, operators—inside your current schema.

The Extension Catalog: pg_extension

So, where did that CREATE EXTENSION command actually put everything? It’s not a black box. PostgreSQL keeps a meticulous catalog of what’s installed. The pg_extension system view is your master list.

SELECT * FROM pg_extension;

-- A more useful query to see what you've got:
SELECT name, version, comment
FROM pg_extension
ORDER BY name;

This is your first stop when you connect to a new database and want to see what’s installed. The version column is particularly important. It shows the version of the extension’s objects, not necessarily the version of the software on the filesystem. This is because extensions can be updated.

Seeing What’s in the Toolbox: pg_available_extensions

Before you can install an extension, it needs to be available on the system. This is the most common point of failure, and it’s almost always because someone (maybe you, 6 months ago, in a hurry) forgot to install the necessary system package. On Ubuntu, for postgis, you’d need postgresql-16-postgis-3 (or whatever version matches your PG server). The pg_available_extensions view shows you everything the server can see on the filesystem, whether it’s installed in the current DB or not.

SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name LIKE '%pg%'
ORDER BY name;

This is your “menu.” If you don’t see your desired extension here, it’s not installed on the OS. Go install the requisite package and restart PostgreSQL. No amount of SQL-fu will fix its absence.

Upgrading: The Occasionally Hairy Process

Extensions evolve. pgvector is a great example, adding new index types and distance metrics at a breakneck pace. When a new version comes out, you’ll want to upgrade. Sometimes it’s painless:

ALTER EXTENSION pgvector UPDATE TO '0.7.0';

This command runs the extension’s update script, which should gracefully migrate any internal state. But here’s the rough edge I must be honest about: always read the extension’s release notes. Some upgrades, especially major version jumps (e.g., PostGIS 2.5 to 3.0), can require additional steps, like running a special script to rebuild your spatial indexes. Blindly running ALTER EXTENSION can sometimes leave you in a weird, half-upgraded state. It’s rare, but it happens. The designers made a choice for flexibility over absolute safety here, so the onus is on you, the brilliant friend, to check first.

Best Practices and Pitfalls

  1. Schema Management: By default, CREATE EXTENSION installs objects into the first schema in your search_path, which is usually public. This is fine for toy projects, but for anything serious, create a dedicated schema for your extensions. It keeps public clean and prevents naming conflicts.

    CREATE SCHEMA extensions;
-- Then, for each extension:
CREATE EXTENSION postgis SCHEMA extensions;
-- And update your search_path for users
ALTER ROLE my_app_user SET search_path = "$user", public, extensions;

  2. Version Control Your Extensions: Your database schema migration tool (you are using one, right?) should include the CREATE EXTENSION statements. This ensures every environment is identical. The absurd part is that while your schema is versioned, the extension’s objects inside that schema are not. This is why you must ensure the same extension version is available everywhere.

  3. The Superuser Requirement: This is the designers’ questionable choice I’ll call out. CREATE EXTENSION typically requires superuser privileges. This is a massive pain for security and automation. The workaround is that a superuser can grant the necessary permissions on the extension’s objects to a normal user after installation, but it’s a clunky process. For cloud databases, you’re often at the mercy of what the provider has pre-installed and made available.

The takeaway? Extensions are PostgreSQL’s killer feature. Using CREATE EXTENSION is the easy part. The real skill is knowing what’s available, managing their versions diligently, and integrating them into a robust, maintainable database workflow. Now go install pg_trgm and make your text searches unnervingly fast.