PostgreSQL Extensions

What an extension is#

A PostgreSQL extension is a packaged bundle of SQL objects — new types, functions, operators, or even entire index access methods — that can be loaded into a specific database without modifying PostgreSQL's core source code at all. This is the mechanism behind pgcrypto (already used on the advanced-data-types page for gen_random_uuid()), PostGIS (geospatial queries), and hundreds of others in the broader ecosystem. Extensions are how PostgreSQL stays a comparatively small, standards-focused core while still supporting a huge range of specialized workloads for whoever actually needs them.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

IF NOT EXISTS matters here specifically — re-running CREATE EXTENSION without it against an already-extended database raises an error rather than silently doing nothing, which is a real annoyance in a migration script meant to run repeatedly across environments.

-- What's currently installed in this database
\dx

-- What's available to install on this server (may need the extension's
-- package installed at the OS level first, e.g. postgresql-contrib)
SELECT * FROM pg_available_extensions;

DROP EXTENSION IF EXISTS pg_trgm;

Creating or dropping an extension typically requires superuser privileges (or a managed-hosting equivalent role) — a real practical constraint worth knowing before assuming any extension is available: many managed providers (Amazon RDS, for instance) only allow a curated allowlist of extensions, and installing an arbitrary one may simply not be possible on a given hosted instance.

pg_trgm: fuzzy, typo-tolerant text matching#

The full-text-search page covers PostgreSQL's built-in linguistic search — stemming "running" to match "run." That solves a different problem than typos: a search for "databse" should still plausibly find "database", and full-text search's stemming does nothing for that. pg_trgm (trigram matching) fills this gap by breaking text into overlapping three-character sequences ("trigrams") and measuring how many trigrams two strings share.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('Wireless Mouse', 'Wireles Mouse');
-- a number between 0 (no similarity) and 1 (identical)

The % operator tests whether two strings are "similar enough" (above a configurable threshold), and similarity() gives the actual score for ranking:

SELECT name FROM products
WHERE name % 'Wireles Mouse'
ORDER BY similarity(name, 'Wireles Mouse') DESC;

Because a sequential scan comparing every row's trigram similarity doesn't scale, pg_trgm also provides its own GIN (or GiST) operator class, so this kind of fuzzy match — and even a plain ILIKE '%...%' — can use an index instead of a full scan:

CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Both of these can now use the trigram index
SELECT name FROM products WHERE name % 'wireles mous';
SELECT name FROM products WHERE name ILIKE '%mous%';

uuid-ossp: generating UUIDs, the older way#

The advanced-data-types page already generates UUIDs with pgcrypto's gen_random_uuid() — as of PostgreSQL 13, that function is built directly into core, so no extension is required for it at all on a reasonably current version. uuid-ossp predates that: it's an older extension providing uuid_generate_v4() (and other UUID versions, like the MAC-address-based uuid_generate_v1()), and it still shows up constantly in existing codebases and tutorials written before gen_random_uuid() became available without an extension.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE api_tokens (
    id      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INT NOT NULL,
    token   VARCHAR(64) NOT NULL
);

For new code on a current PostgreSQL version, gen_random_uuid() (no extension needed) is the simpler default; uuid-ossp is worth recognizing when reading existing schemas rather than necessarily reaching for in new ones.

A few other extensions worth knowing by name#

None of these need to be understood in depth to get value from this page — the goal is recognizing what's available so the right one gets reached for when its specific problem actually shows up, rather than reinventing trigram matching or geospatial distance calculations from scratch in application code.

Common mistakes#

• Assuming any extension can be installed on any PostgreSQL instance — managed hosting providers commonly restrict CREATE EXTENSION to an approved allowlist, and attempting to install something outside it fails outright.
• Omitting IF NOT EXISTS, breaking an idempotent migration script the moment it's run a second time against the same database.
• Confusing pg_trgm's fuzzy/typo-tolerant matching with full-text search's linguistic stemming — they solve genuinely different problems (misspellings vs. word variants) and neither substitutes for the other.
• Reaching for uuid-ossp out of habit (or copied from an older tutorial) on a modern PostgreSQL version where gen_random_uuid() already does the same job without needing any extension at all.