PostgreSQL Extensions Worth Knowing

You need case-insensitive email addresses, so you lower-case every address in the application and trust that no query forgets. A few months later you need a search box that forgives a typo in a product name, so you fetch every row and score the matches in your own code. Both work, and both are things PostgreSQL already ships with the right extension. Most developers meet extensions after rebuilding one.

An extension turns a general database into one shaped for a particular problem. It is not a plugin beside the server or a service that proxies queries: it is a bundle of SQL objects installed inside one database.

What an extension actually is

Loading an extension "essentially amounts to running the extension's script file", and that script "will typically create new SQL objects such as functions, data types, operators and index support methods", in the words of the CREATE EXTENSION page. Each extension has a control file that names it and points at the script.

The part that matters operationally is that PostgreSQL remembers which objects came from which extension. CREATE EXTENSION "additionally records the identities of all the created objects, so that they can be dropped again if DROP EXTENSION is issued". Without it, an extension is SQL you cannot safely remove.

CREATE EXTENSION, and the permission question

The command installs into the current database: CREATE EXTENSION citext;. It is not a server-wide switch: a second database on the same server needs its own statement, and extension names "must be unique database-wide".

Permissions stop people first. The manual says loading an extension "ordinarily requires the same privileges that would be required to create its component objects. For many extensions this means superuser privileges are needed." Some are marked trusted: "If the extension is marked trusted in its control file, then it can be installed by any user who has CREATE privilege on the current database", with the objects inside owned by the bootstrap superuser. pgcrypto, pg_trgm and citext are all trusted, which is why an application role can usually install them.

Why availability depends on where the database runs

On Supabase, extensions are a product feature: the service is "pre-configured with over 50 extensions", you switch one on in the dashboard or run the same SQL through the editor, and most are installed in an extensions schema that public can reach. A newer version of an extension appears only after a software upgrade.

Neon publishes a table of supported extensions per PostgreSQL version, and a dash means "not yet supported for that version". A few are experimental and need SET neon.allow_unstable_extensions = 'true'; before installation, some need preloaded libraries configured through the project API, and a new release needs a compute restart.

Amazon RDS shows the same pattern, with versions. At the time of writing its matrix lists pgcrypto 1.4 on PostgreSQL 18 and 1.3 on 16, PostGIS 3.6.3 on 18 and 3.4.6 on 16, and PostGIS marked as not available for the PostgreSQL 19 release candidate. The page also warns that "extension versions don't automatically upgrade during engine version upgrade workflows" and that AWS may end support for an extension with 12 months' notice, or less for security issues.

None of this is a reason to avoid a managed provider. It is a reason to check the provider's extension list before the design depends on one. A query that needs PostGIS is a different commitment from a query that needs pg_trgm, and only the host's list tells you which commitment you can keep.

An extension is code running inside your database

The manual is blunt: installing an extension as superuser "requires trusting that the extension's author wrote the extension installation script in a secure fashion", and it recommends installing unvetted extensions into a schema where untrusted users have no CREATE privilege. The script runs with the installer's rights, so enable what you need rather than a list of interesting modules.

Removal deserves the same care. DROP EXTENSION removes "its member objects, and other explicitly dependent routines". By default it refuses if anything else depends on them; CASCADE drops those dependents too, which is how a column of an extension type disappears with its data.

To see what your server offers, query the catalog: SELECT name, default_version, installed_version FROM pg_available_extensions;. The view lists what is available, NULL in installed_version means available but not loaded, and pg_extension lists what is installed. It answers the question faster than the provider's documentation when you are already connected.

pgcrypto: hashes, keyed hashes and passwords

pgcrypto "provides cryptographic functions for PostgreSQL" and requires OpenSSL; it "won't be installed if OpenSSL support was not selected when PostgreSQL was built". The functions worth knowing are digest(data, type) for a plain hash, hmac(data, key, type) for a keyed one, gen_random_bytes(count) for random bytes, and crypt(password, salt) with gen_salt(type) for passwords. Digest types include md5, sha1, the SHA-2 family and anything else OpenSSL supports.

The right situation is a fingerprint you need to compare, not a secret you need to keep. A content hash that lets you skip re-processing an unchanged document, or an HMAC over an email address with the key held outside the database, are both good fits, and encode(digest(...), 'hex') gives you a value you can index. Passwords are the other good fit, but through crypt() with gen_salt('bf'), because those algorithms are deliberately slow, salted and adaptive, which the manual gives as what makes brute-forcing hard.

The wrong situation is digest() on a password column. A single SHA-256 is fast by design, has no salt and no work factor, so identical passwords produce identical hashes and a stolen table is cheap to attack. The same mistake appears as "we hashed the email for privacy": an unsalted hash of a low-entropy value is recoverable by guessing. If you need a password hash inside the database, use crypt() with bf or sha512crypt; if you can, do it in the application and keep the database out of it. Note also that pgcrypto's gen_random_uuid() is documented as "Obsolete" because it now calls the core function, so there is no reason to install pgcrypto for identifiers alone.

uuid-ossp, or the functions already in core

uuid-ossp provides uuid_generate_v1(), uuid_generate_v1mc(), uuid_generate_v3(), uuid_generate_v4() and uuid_generate_v5(), plus namespace constants such as uuid_ns_url(). Its own page says it "is only necessary for special requirements beyond what is available in core PostgreSQL" and points at the built-in UUID functions.

That pointer is the useful part. PostgreSQL 13 added gen_random_uuid() to core; the release notes say "previously UUID generation functions were only available in the external modules uuid-ossp and pgcrypto". PostgreSQL 18 added uuidv7(), a "version 7 (time-ordered)" UUID, and uuidv4() as an explicit alias for the random version. The default answer for a random primary key on a current server is therefore gen_random_uuid(), with no extension.

The right situation for uuid-ossp is a UUID you must derive rather than generate: uuid_generate_v3() or uuid_generate_v5() turn a namespace and a name into the same UUID every time, which gives an imported record a stable identifier. The wrong situation is installing it for uuid_generate_v4() out of habit. It costs an extension, another thing to keep available on your host, and buys nothing over the built-in call. On PostgreSQL 18, a high-insert table also benefits from uuidv7(): a time-ordered value sorts near the end of the index instead of scattering across it.

citext: case-insensitive text

citext is a string type that "internally calls lower when comparing values" and otherwise "behaves almost exactly like text". Declare a column as citext and 'Larry' = 'larry' is true, including inside a unique index, which is the case people actually want for an email or username column.

The right situation is a login identifier in a database whose collation matches your users, typically ASCII email addresses. The wrong situation is a column holding names or text in several languages, or one where Unicode correctness matters. The documentation says plainly that case folding "depends on the LC_CTYPE setting of your database", that how it compares is fixed when the database is created, and that it is "not truly case-insensitive in the terms defined by the Unicode standard"; it does not handle cases where one upper-case letter has two lower-case equivalents. It is also slower than text, because comparisons copy and lower-case the data, and only text supports B-tree deduplication.

For that reason the page suggests nondeterministic collations instead, which compare without case or accents and handle more Unicode cases correctly. With an existing citext column, remember that its schema must be in search_path, or the normal case-sensitive text operators are used and nobody notices until a duplicate gets in.

pg_trgm: fuzzy search that fits an index

pg_trgm breaks strings into three-character trigrams and compares them. similarity(a, b) returns a number from zero to one, and the % operator is true when the similarity passes pg_trgm.similarity_threshold, which defaults to 0.3. The important part is the index support: GiST and GIN indexes with the trigram operator classes accelerate LIKE, ILIKE, regular expressions and =, not only the similarity operators.

The right situation is a search box over names you control: customers, products, articles, where a typo should still find the row, or where ILIKE '%term%' on a large table has become a sequential scan. The wrong situation is exact lookup, where a B-tree is smaller and faster, or relevance-ranked full-text search, which is tsvector's job and understands words rather than character fragments. Two limits from the page are worth knowing before you promise the feature: a pattern "with no extractable trigrams will degenerate to a full-index scan", and the distance ordering ORDER BY t <-> 'term' is efficient on GiST but not on GIN.

PostGIS: when the location is the data

PostGIS "extends the capabilities of the PostgreSQL relational database" so it can store, index and search location data: shapes in two and three dimensions, spatial indexes, and functions for distances, intersections and geocoding. It is the largest item on this list and the one with a real operational footprint.

The right situation is a question the database must answer spatially: which delivery zones contain an address, or which points fall within a radius of a moving vehicle, with an index doing the work. The wrong situation is a latitude and longitude pair you store, display on a map and never query by geometry. Two numeric columns and a distance formula in the application are enough, and PostGIS would add an extension whose version your host controls; RDS marks it unavailable for its PostgreSQL 19 preview.

A short way to decide

Ask three questions before you add one. Does the host's extension list include it, and at which version? Is it trusted, so your application role can install it without a superuser? And is the job it does actually the job you have, rather than one the application layer already does well?

Then put the CREATE EXTENSION in a migration, next to the tables that need it, so a fresh environment matches production. We wrote about that workflow in running migrations local-first. An extension is a dependency like any other: name it, keep its version, drop it when the feature is gone. The alternative, a database quietly carrying six modules nobody remembers enabling, is how a general database becomes a specific one by accident.