PostgreSQL Extensions: PostGIS, pgvector & the Ecosystem

Q: Which extensions should almost every database have?

pg_stat_statements is the standout — it surfaces your most expensive queries and is invaluable for performance work. Beyond that it depends on your needs: pgvector for AI/embeddings, PostGIS for anything geospatial, pg_trgm for fuzzy search and typo tolerance. Enable pg_stat_statements by default and add the others as your workload calls for them.

Q: Do extensions install for the whole server or per database?

Per database. CREATE EXTENSION installs into the current database only — an extension enabled in one database isn't automatically available in another on the same cluster. Install it in each database that needs it. (The server may need the extension's files present first, but enabling it is a per-database action.)

PostgreSQL Extensions

Extensions are the reason PostgreSQL is less a database and more a platform. An extension packages new data types, functions, index methods, or entire capabilities that you install into a database with a single command — turning plain Postgres into a geospatial database, a vector store, a time-series engine, or a full-text search platform without changing databases. This extensibility, designed into Postgres from the start, is why "just use Postgres" keeps winning: whatever specialized capability you need, there's often an extension for it.

Rather than adopting a separate specialized system (and the operational cost of running and syncing it), you frequently add an extension and get the capability inside the database you already trust — with your existing transactions, backups, and SQL. This page covers how extensions work and the ones worth knowing.

TL;DR

Extensions add capabilities — types, functions, index methods, whole features — to Postgres via CREATE EXTENSION name;.
They turn Postgres into a platform: geospatial (PostGIS), vector search (pgvector), time-series (TimescaleDB), and much more — no separate system needed.
Essentials: pg_stat_statements (query performance), pgvector (AI/embeddings), PostGIS (geospatial), pg_trgm (fuzzy/trigram matching), postgres_fdw (query other databases).
Availability depends on your host — some extensions must be installed on the server; managed providers (RDS, Supabase, Neon) offer a curated allowlist.
Extensions run with database privileges — install only trusted ones, and know that some require superuser or specific configuration.
Check pg_available_extensions for what you can install and pg_extension for what's active.

Quick Example

Installing an extension is one command; then its capabilities are available in that database:

pg_trgm's % operator matches "laptop" against the typo "labtop" — a capability that simply appears once the extension is installed.

Core Concepts

How Extensions Work

An extension is a packaged bundle of SQL objects — data types, functions, operators, index access methods, sometimes C code — that installs as a unit and is tracked by Postgres. CREATE EXTENSION name installs it into the current database (into a schema); DROP EXTENSION removes it cleanly; ALTER EXTENSION ... UPDATE upgrades it. Because Postgres knows an object belongs to an extension, it manages them together (dumps, dependencies, upgrades) rather than as loose objects.

Extensions install per database, not per cluster — installing vector in one database doesn't make it available in another on the same server.

Availability and Managed Hosts

Not every extension can be installed just because you want it. Some ship with Postgres (the contrib modules like pg_stat_statements, pg_trgm); others must be installed on the server's filesystem first (their binaries) before CREATE EXTENSION works. On managed platforms (AWS RDS, Supabase, Neon, Cloud SQL) you can't install arbitrary server binaries — the provider offers a curated allowlist of supported extensions. Always check what your host allows before designing around an extension.

pg_available_extensions — what can be installed here.
pg_extension — what is installed in this database.

Extensions Run With Privilege

An extension can introduce C code and powerful capabilities that run inside the database server. Installing one is a trust decision — some require superuser, some can touch the filesystem or network (postgres_fdw, untrusted procedural languages). Install only extensions you trust from reputable sources, and prefer your managed host's vetted allowlist.

The Essential Extensions

PostGIS deserves special mention — it's arguably the most powerful geospatial database capability anywhere, and it's an extension, turning Postgres into a first-class GIS platform. pg_stat_statements should be on virtually every production database as the primary tool for finding expensive queries.

Best Practices

Enable pg_stat_statements Everywhere

It's the single most valuable extension for operations — it tells you which queries actually cost the most, so you optimize by evidence. Enable it on every production database as a default. See PostgreSQL Performance Tuning.

Prefer an Extension Over a Separate System

Before adopting a specialized database for geo, vectors, or time-series, check whether a Postgres extension covers it. Keeping the capability in Postgres means one system to operate, one set of backups, transactional consistency, and no sync pipeline. Reach for a dedicated system only when you genuinely outgrow the extension.

Verify Host Support Early

Confirm your target environment (especially managed providers) supports the extensions your design depends on before building on them. Discovering that your host's allowlist excludes an extension after you've architected around it is a painful surprise.

Manage Versions and Upgrades

Extensions have versions and upgrade paths (ALTER EXTENSION ... UPDATE). Track which versions you run, test upgrades, and account for extensions when planning major Postgres version upgrades — an extension must support the target Postgres version.

Install Only What You Trust

Because extensions execute with database privileges (sometimes as superuser, sometimes touching the OS), treat installation as a security decision. Stick to reputable, widely-used extensions and your host's vetted list; don't install obscure ones on a whim.

Common Mistakes

Designing Around an Unsupported Extension

Reaching for a New Database Prematurely

Standing up a separate geospatial or vector database when PostGIS or pgvector would serve — adding an entire system to operate and sync for a capability Postgres already offers. Evaluate the extension first.

Forgetting Extensions Are Per-Database

Installing vector in one database and expecting it in another on the same server. CREATE EXTENSION affects only the current database; install it in each database that needs it.

FAQ

What is a PostgreSQL extension?

It's a packaged bundle of database capabilities — new data types, functions, operators, index methods, sometimes compiled code — that you install as a unit with CREATE EXTENSION. Postgres tracks the objects as belonging to the extension, so it manages them together for upgrades and dumps. Extensions are how Postgres gains specialized powers (geospatial, vector search, time-series) without becoming a different database.

Which extensions should almost every database have?

pg_stat_statements is the standout — it surfaces your most expensive queries and is invaluable for performance work. Beyond that it depends on your needs: pgvector for AI/embeddings, PostGIS for anything geospatial, pg_trgm for fuzzy search and typo tolerance. Enable pg_stat_statements by default and add the others as your workload calls for them.

Can I install any extension on a managed database?

No. Managed providers (RDS, Supabase, Neon, Cloud SQL) restrict you to a curated allowlist of supported extensions, because installing arbitrary ones requires server-level access they don't grant. Check the provider's supported-extensions list before designing around an extension, and query pg_available_extensions on your instance to see what's actually installable.

Do extensions install for the whole server or per database?

Per database. CREATE EXTENSION installs into the current database only — an extension enabled in one database isn't automatically available in another on the same cluster. Install it in each database that needs it. (The server may need the extension's files present first, but enabling it is a per-database action.)

Are extensions safe to install?

They run with database privileges and can include compiled code, so installation is a trust decision — some require superuser or can reach the filesystem or network. Stick to reputable, widely-used extensions and, on managed hosts, the provider's vetted allowlist. The popular ones (pgvector, PostGIS, pg_stat_statements, pg_trgm) are mature and safe; obscure ones warrant scrutiny.