Best Postgres Extensions for Production Workloads

Postgres extensions are the reason the answer to “which database” keeps being Postgres. Vectors, geospatial, time-series, job scheduling, partition management: each one is a product category somewhere else and a CREATE EXTENSION here.

The catch is that CREATE EXTENSION only works if the extension’s files are already installed on the server, and on a managed provider you cannot install them. An extension is compiled C loaded into the Postgres backend process. It runs with the server’s privileges, in the server’s address space, and a crash in extension code takes the whole database down. No managed provider is going to let a tenant load arbitrary binaries into that process, and they are right not to.

So the article that matters is not “here are ten good extensions”. It is: what does each one need from the platform, which providers grant it, and how do you find out before you have built a feature on top of it. The order of operations most teams get wrong is building the feature first and discovering the constraint during the migration to production.

That is why this decision and your provider decision are the same decision, taken once, in that order. Pick the extensions, then pick the provider that has them, not the other way round.

Key takeaways

• An extension is compiled code loaded into the Postgres backend process. That single fact explains why managed providers allowlist them and why an unsupported one has no workaround.
• Extensions sort into tiers by how much the platform must trust them: bundled and trusted, bundled and superuser-only, requires a preload and a restart, and requires deliberate provider engineering. Support falls off sharply along that sequence.
• Verify support by connecting to a trial instance and actually running CREATE EXTENSION, not by reading a documentation page. Docs pages describe intent; the catalog describes reality.
• The extension list also constrains your exit. Logical replication to a new provider only works if every extension your schema depends on exists on both sides.

How an extension actually gets loaded

There are three separate things people mean by “installing an extension”, and providers treat them very differently.

The files exist on the server. Someone put a shared library and a control file into the Postgres installation directory. On a managed provider this is a build-time decision by the provider, made once, for all tenants. You have no input.

The extension is created in your database. CREATE EXTENSION pgvector; registers its types, functions and operators in your database’s catalog. This needs the files to exist and needs sufficient privilege. Extensions marked trusted can be created by a database owner without superuser; untrusted ones require superuser, which managed providers do not grant, so they instead expose a controlled path that runs the creation on your behalf for an allowlisted set.

The library is preloaded into every backend. Some extensions must be listed in shared_preload_libraries because they install hooks or allocate shared memory at startup. pg_stat_statements and pg_cron are the common examples. This is a server parameter change and a restart, which on a managed provider means a parameter group edit, a maintenance event, and an allowlist that the provider controls separately from the file-level one.

Then there are the things that push beyond a library in a backend process:

• Background workers. An extension that runs its own worker process, such as a scheduler, needs the provider to permit worker slots and to decide what happens to that worker on a failover. A job scheduler running on both the old and new primary during a split is a genuine hazard, which is why providers think carefully about this class.
• Writing outside the data directory. Anything touching the filesystem or opening outbound network connections is a security boundary question, not a compatibility question.
• Replication interaction. An extension that changes the WAL, or whose state must be identical on a standby, constrains how the provider does high availability.
• Version pinning. The provider decides which version of the extension ships with which major version of Postgres. A feature added upstream last month may not reach you for a long time, and ALTER EXTENSION ... UPDATE only moves you between versions the provider has installed.

That sequence, from trusted-and-bundled at one end to background-workers-and-network at the other, is the actual shape of provider support. Support is near-universal at the first step and thins out fast.

Needs first-hand data: For each provider on your shortlist, create a trial instance and run SELECT name, default_version, installed_version, comment FROM pg_available_extensions ORDER BY name; and save the output. Then, for every extension your application actually needs, run CREATE EXTENSION and record whether it succeeds, what privilege it demanded, and whether it required a parameter change and restart. Doing this takes under an hour per provider and it is the only evidence that binds.

The four tiers, and what they predict

Tier one: bundled with Postgres and trusted. Ships in the standard contrib set, creatable by an ordinary database owner. Available essentially everywhere. Examples include pg_trgm, citext, hstore, uuid-ossp and btree_gin. If your requirement is satisfiable here, your provider choice is unconstrained.

Tier two: bundled but requiring superuser privilege. Also in contrib, but not trusted, so creation goes through the provider’s controlled path. Widely available, occasionally with a feature clipped. postgres_fdw and dblink sit here, and they are the classic case of an extension that exists but has its outbound networking restricted.

Tier three: third-party, needs a preload and a restart. The extension is not in contrib, so the provider had to choose to build and ship it, and it also needs a shared_preload_libraries entry. This is where availability starts genuinely diverging between providers. pg_cron is the canonical example.

Tier four: third-party with a background worker, a scheduler, or licensing that a cloud vendor will not carry. The narrowest tier, and the one where “which provider” and “which extension” collapse into one question. Some features of TimescaleDB sit here, which is why a managed Postgres provider may offer the extension while omitting parts of it.

The practical consequence: an application depending only on tiers one and two can move between providers freely. One depending on tier three or four has a provider list, not a provider choice, and you should write that list down before you build.

pgvector

Adds a vector type, distance operators and approximate nearest neighbour indexes (HNSW and IVFFlat) to Postgres. Its importance is architectural rather than about speed: your embeddings live in the same transaction as the rows they describe, your metadata filters are ordinary indexed SQL predicates, and there is no synchronization pipeline between a source of truth and a separate index to go stale. It is the extension that removes a whole product category from most architectures, and it has become common enough that most providers carry it.

Pros

• Embeddings and their rows commit together, eliminating the dual-write window where index and data disagree
• Filters are real SQL predicates the planner can evaluate first, which sidesteps the filtered-recall collapse that approximate graph search suffers from when a filter is selective
• One system to back up, secure and monitor, and results carry the rest of the record through joins

Cons

• HNSW index builds are memory-hungry, and a build that exceeds maintenance work memory falls back to a much slower path with no warning
• A retrieval workload competes with transactional traffic for shared buffers and I/O, so real volume wants a dedicated replica
• Scaling past one primary plus replicas means sharding, which the extension does not help with

Needs first-hand data: Build an HNSW index on a representative million-vector sample at your real embedding dimensionality, with maintenance_work_mem set to the value your production instance actually has. Record the build wall-clock time, the resulting index size from pg_relation_size, and whether the server log shows a fallback to the external sort path. That third line is the one that decides whether your provider tier is large enough.

Best for: Applications whose corpus fits on Postgres hardware and whose retrieval filters run on data Postgres already holds.

Pricing: No licence cost. The real constraint is provider support, and it is widely available, including on most managed platforms. Verify the version, because index type support differs across versions.

TimescaleDB

Turns a Postgres table into a hypertable partitioned automatically by time, adds continuous aggregates that are incrementally refreshed rather than recomputed, and adds columnar compression for older chunks. Continuous aggregates are the thing core Postgres genuinely cannot do: REFRESH MATERIALIZED VIEW recomputes the entire view, while a continuous aggregate tracks which time regions new writes invalidated and refreshes only those. For an event or metric stream, that difference is the whole argument for staying on Postgres.

Pros

• Incremental continuous aggregates remove the recompute-the-world problem that makes core materialized views a poor fit for growing event data
• Columnar compression on older chunks changes the economics of retaining history inside Postgres
• Retention becomes dropping a chunk rather than a DELETE that generates millions of dead tuples for autovacuum to clean

Cons

• Provider availability is the worst in this list, because parts of the extension are licensed in a way cloud vendors will not carry, so you may get the extension without the features you wanted
• Hypertables change partitioning semantics, so migrations, foreign keys and some query patterns need rework
• The benefit is concentrated in time-ordered workloads, and a generic OLTP schema gets very little

Best for: Products with a heavy append-only event or metric stream that want to keep it in Postgres instead of adding a column store.

Pricing: No licence cost for the open-source portions. The real constraint is provider support, and it is the sharpest in this article: the vendor’s own managed platform carries everything, several hyperscalers carry a reduced subset, and some carry nothing.

PostGIS

The geospatial extension, and the most complete open-source implementation of spatial types, indexes and operations anywhere. It adds geometry and geography types, GiST and SP-GiST index support, and a large function library for distance, containment, intersection and projection. When a product needs “find things near this point”, PostGIS is not one option among several, it is the option, and it has been for long enough that provider support is close to universal.

Pros

• Spatial indexes make proximity and containment queries indexed operations rather than full scans over computed distances
• The function library covers projections, topology and geometry operations that would otherwise be application code you get subtly wrong
• Nearly universal provider availability despite being third party, because the demand has been constant for years

Cons

• Large and intricate enough that version differences between providers matter, and upgrading it is a real migration rather than a parameter change
• Spatial indexes on large geometries are expensive to build and to keep, so write-heavy spatial workloads pay a maintenance cost that is easy to underestimate

Best for: Any product where location is a query predicate rather than a display field.

Pricing: No licence cost. The real constraint is provider support, which in practice is the least binding of any third-party extension here.

pg_partman

Manages declarative partitions: creates future partitions ahead of time, detaches or drops old ones on a retention policy, and keeps the set consistent. Postgres has native declarative partitioning; what it does not have is anyone to create next month’s partition before rows start arriving for it. pg_partman is that maintenance loop, and the reason it matters is that partitioning converts your retention policy from a DELETE generating millions of dead tuples into a DETACH generating none.

Pros

• Automates the partition lifecycle, which is otherwise a cron job you write, forget, and discover has stopped when inserts start failing
• Retention by dropping partitions avoids the vacuum load that bulk deletes create on a large table
• Partition pruning lets the planner skip whole partitions, which is the cheapest real win available on time-series-shaped tables

Cons

• Its background worker mode needs a preload entry, so on providers that do not permit it you fall back to scheduling the maintenance function yourself, which reintroduces the failure it was meant to prevent
• Partitioning itself constrains your schema: unique constraints must include the partition key, and that reaches into your application design
• Choosing the wrong partition interval is expensive to change once the table is large

Best for: Any table with a time dimension and a retention policy, which in most products is the largest table you have.

Pricing: No licence cost. The real constraint is provider support, and specifically whether the provider permits its background worker or only the scheduled-function mode.

pg_stat_statements

Tracks normalized query text with call counts, total and mean execution time, rows returned, and shared buffer hits and reads. It is in contrib and needs a shared_preload_libraries entry and a restart. It is also the single most valuable thing you can enable on a Postgres database, and it should be on before you have a problem, because it accumulates history and cannot tell you about the week you were not recording.

Pros

• Turns “the database feels slow” into a ranked list of statements by total execution time, which is almost always a different list from the one people guess
• Buffer hit and read counts per statement separate a plan problem from a cache problem without any external tooling
• Normalized statement text groups a query shape across all its parameter values, which is what you actually want to reason about

Cons

• Needs a preload and a restart, so enabling it on a running production database is a maintenance event rather than a quick change
• The statement tracking limit means high-cardinality query text can evict entries, and applications that inline literals instead of using parameters defeat normalization entirely
• It shows you aggregates, not individual slow executions, so pairing it with logging of slow statements is necessary for the tail

Best for: Every production Postgres database, enabled on day one rather than during the first incident.

Pricing: No licence cost, and it ships with Postgres. The real constraint is provider support, which is near-universal, often with the preload already configured by default.

pg_cron

A cron-style job scheduler running inside Postgres, so periodic SELECT and maintenance jobs live next to the data rather than in an external scheduler with its own credentials and its own failure mode. It runs as a background worker and requires a preload entry, which puts it in tier three and makes availability genuinely provider-dependent.

Pros

• Scheduled work lives with the schema, so a migration that adds a table can add its maintenance job in the same change
• Removes an external scheduler, its credentials and its network path from the architecture for simple periodic tasks
• Job history is queryable in SQL, which is a better debugging surface than most external schedulers offer

Cons

• Failover behaviour is the sharp edge: the scheduler follows the primary, and how your provider handles the transition decides whether a job is skipped, duplicated, or runs on both nodes during a split
• A long-running job holds a connection and a transaction snapshot, which blocks autovacuum from reclaiming dead tuples for its duration
• No real orchestration, so dependencies between jobs, retries and backoff are yours to build

Best for: Periodic maintenance and rollup jobs on a provider that supports it, where the alternative is a cron container nobody monitors.

Pricing: No licence cost. The real constraint is provider support, and this is one of the more common extensions to find missing or restricted.

PgBouncer

Not an extension, which is the point of including it. PgBouncer is a separate process that sits between your application and Postgres, multiplexing many client connections onto a small pool of server connections. It matters here because the alternative to running it yourself is whatever pooler your managed provider ships, and that choice determines which session-level features your application may use. Transaction pooling, the mode that makes the multiplexing worthwhile, breaks anything that holds state across statements.

Pros

• Multiplexes many client connections onto few backend processes, which is the only real answer to Postgres forking a process per connection
• Extremely small, well understood and stable, with a long operational track record
• Can run anywhere in your topology, so you are not limited to where your provider puts its pooler

Cons

• Transaction pooling breaks session-scoped state: session SET commands, session-level advisory locks, LISTEN, temporary tables and prepared statement reuse on older versions
• Single-threaded by design, so a very high connection rate against one instance becomes a CPU bottleneck in the pooler itself
• Running it yourself means it is a component that can fail, in a position where its failure is total

Best for: Any Postgres deployment where the number of application processes is not bounded by something you control, which is most of them.

Pricing: No licence cost. The real constraint is where it runs and who operates it, which is covered in detail in connection poolers for Postgres.

How to choose

The order matters more than the list. Extension support is a provider filter, so it comes first.

1. Write down every extension your application creates. Include the ones added by your ORM, your search library, your job queue and your test fixtures. SELECT extname FROM pg_extension; on your current database is the honest starting list, not your memory.

2. Sort them into the four tiers. Anything in tiers one and two, stop worrying about. Anything in tiers three and four becomes a hard filter on your provider list.

3. Verify on a trial instance, not in documentation. Create the extension. Use it. Check the version. A docs page saying an extension is supported and a catalog entry with a usable version are different claims.

4. Check the version, not just the name. Index types, functions and operators arrive in specific versions, and a provider shipping an older one may not have the feature your code assumes.

5. Ask what happens on failover for anything with a background worker. Scheduler extensions in particular. Get a concrete answer about duplicate or skipped runs.

Needs first-hand data: For any extension with a background worker, schedule a job that inserts a row containing clock_timestamp() and the output of inet_server_addr() every minute, then trigger a manual failover. Count the rows across the transition. Missing minutes mean skipped runs; two rows in one minute with different server addresses mean the job ran on both nodes, which is the outcome you need to design around.

6. Re-run the check against your exit path. Logical replication to a new provider requires the extensions to exist on both sides. Every tier three or four dependency narrows where you can go later.

Extension	What it needs from the platform	Ask the provider	Verify by running
pgvector	Third-party library, no preload required	Which version, and are both index types present	CREATE EXTENSION vector; then build an HNSW index on real dimensionality
TimescaleDB	Third-party, licensing-sensitive components	Which components are included and which are omitted	Create a hypertable, then a continuous aggregate, then enable compression
PostGIS	Third-party library, large, version-sensitive	Which version, and what the upgrade path between major versions is	SELECT PostGIS_Full_Version();
pg_partman	Preload entry for background worker mode	Is the background worker permitted, or only the maintenance function	Create a partition set and confirm future partitions appear without manual intervention
pg_stat_statements	shared_preload_libraries entry and a restart	Is it preloaded by default, and what is the tracking limit	SELECT count(*) FROM pg_stat_statements;
pg_cron	Preload entry plus a background worker	What happens to scheduled jobs during a failover	Schedule a job that writes a timestamped row, then trigger a failover
PgBouncer	Not an extension; a separate process	Where does the pooler run and which mode does it default to	Open a session, SET statement_timeout, then run a statement and see whether the setting survived

Frequently asked questions

Why can I not just install an extension myself on a managed provider?

Because an extension is compiled code loaded into the Postgres backend process, running with the server’s privileges in the server’s address space. A fault in that code takes down the database, and arbitrary code there would break tenant isolation. The provider builds the files into the image; you can only create what they built.

Which extensions should I turn on by default?

pg_stat_statements on every database, from day one, because it accumulates history you cannot retroactively collect. Add pg_trgm if you do any fuzzy text matching, since it is trusted and universally available. Everything else should follow a demonstrated need.

Do extensions slow Postgres down?

The ones that add types and functions cost nothing when unused. The ones that install hooks and run on every statement, such as pg_stat_statements, carry a small constant overhead that is worth many times its cost in diagnosis. The ones that maintain extra index structures cost write throughput proportional to the indexes you create, which is a choice you make per index, not per extension.

Can pgvector replace a dedicated vector database?

For a large share of applications, yes, and the argument is architectural rather than about raw speed: no sync pipeline, transactional consistency between embedding and row, and filters that are ordinary indexed predicates. Move to a dedicated engine when you can name the limit you hit. Vector databases covers where that line sits and how to measure it.

What happens to my extensions when I migrate providers?

Logical replication copies data, not extensions. Every extension in your schema must exist, at a compatible version, on the destination before you start, and anything using a custom type from an extension will fail to replicate without it. This is why the extension list is the first filter on provider choice, not the last.

Related reading

• Best managed Postgres providers — the hub, and the provider decision that extension support actually determines.
• Best connection poolers for Postgres — transaction versus session pooling and exactly what each one breaks.
• Best vector databases — where pgvector stops being enough, and how to measure recall on your own data.
• Postgres vs ClickHouse vs Tinybird — where TimescaleDB stretches Postgres and where the column store wins anyway.
• Best time-series databases — the alternatives to keeping the event stream in Postgres.
• Best database monitoring tools — what to build on top of pg_stat_statements once it is collecting.