PostgreSQL extension selection guide

PostgreSQL extensions can place types, indexes, planner hooks, background workers, and storage behavior inside the database process. They also enter the critical path for backup, replication, recovery, and major upgrades. The selection rule is simple: do not install an extension without a defined workload and exit path.

Six gates before installation

1. The target PostgreSQL major, operating system, and CPU architecture have explicit support and packages.
2. The license fits self-hosting, SaaS, redistribution, and commercial-feature boundaries.
3. Backup, PITR, standbys, logical replication, and recovery environments can load the same version.
4. pg_upgrade, extension update, and any required index rebuild have a rehearsed path.
5. The managed-cloud region, SKU, and allowlist provide the required version, not merely an extension with the same name.
6. An export or migration path exists without the extension, limiting accidental platform lock-in.

Record SELECT extname, extversion FROM pg_extension, and place extension versions beside the database version in deployment manifests and AI context.

First check native B-tree/GIN/GiST/SP-GiST/BRIN, full-text search, partitions, FDWs, and materialized views in PostgreSQL index and storage access methods. Add an extension only when native behavior fails a measured workload requirement.

Choose by workload

“Production ready” is an upstream project status, not a certification for your workload, SLA, or cloud platform.

TimescaleDB quickstart and version boundaries

The adoption boundary above applies first: evaluate TimescaleDB only when hypertables, compression, or continuous aggregates answer a measured requirement. A minimal evaluation sequence on TimescaleDB 2.x:

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  time   timestamptz NOT NULL,
  device text NOT NULL,
  value  double precision
);

SELECT create_hypertable('metrics', by_range('time'));

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       device, avg(value), max(value)
FROM metrics
GROUP BY hour, device;

ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');
SELECT add_retention_policy('metrics', INTERVAL '90 days');

Maintenance and data governance

For severe bloat, first find long transactions, autovacuum, write patterns, and fillfactor causes before using pg_repack. Partitioning helps only where lifecycle and queries can use the partition key.

PostgreSQL 19 REPACK is not pg_repack

The core REPACK in PostgreSQL 19 Beta is a new SQL command. REPACK (CONCURRENTLY) uses logical decoding and has constraints around primary keys or replica identity, unlogged/partitioned/system tables, replication slots, and disk space.

Third-party pg_repack is an independent extension and command-line tool with its own compatibility matrix, packages, and operational boundaries. Similar names do not make pg_repack experience, monitoring, or risks directly transferable to core PostgreSQL 19 REPACK.

PostgreSQL 19 planner-advice modules

PostgreSQL 19 Beta adds two contrib modules aimed at plan stabilization. pg_plan_advice lets key planner decisions be described, reproduced, and altered through advice attached to a query; pg_stash_advice stores advice strings in dynamic shared memory keyed by query identifier and applies them automatically.

Treat both as an experimental channel, not a production plan-management contract: the advice format and coverage can still change before GA, and pinning plans this way bypasses the same re-validation a rerun of ANALYZE or an extension like HypoPG would give you. Evaluate them in the Beta lane alongside your plan-comparison benchmarks, not as a reason to skip them.

AI, backup, and upgrade checklist

Provide AI agents with server_version_num, extname/extversion, allowed operators and index methods, cloud limitations, and forbidden syntax. “This is PostgreSQL” is insufficient context.

Before every extension upgrade:

1. Read target release notes, SQL update scripts, and known rebuild requirements.
2. Restore a real backup into an isolated environment.
3. Upgrade PostgreSQL and the extension, then run integrity, performance, and RLS tests.
4. Rebuild required indexes and compare plans, recall, or business results.
5. Create a new backup and restore it once to prove the new-version chain.

Supabase, Neon, YugabyteDB, CockroachDB, Cloudberry, Gel, and FerretDB do not belong in an extension ranking. They are platforms, forks, independent databases, or protocol translation layers; classify them with PostgreSQL lineage and compatible databases.