PostgreSQL extension selection guide
Choose PostgreSQL extensions for vector, GIS, time series, search, analytics, maintenance, and masking with explicit upgrade and cloud boundaries
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
- The target PostgreSQL major, operating system, and CPU architecture have explicit support and packages.
- The license fits self-hosting, SaaS, redistribution, and commercial-feature boundaries.
- Backup, PITR, standbys, logical replication, and recovery environments can load the same version.
pg_upgrade, extension update, and any required index rebuild have a rehearsed path.- The managed-cloud region, SKU, and allowlist provide the required version, not merely an extension with the same name.
- 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
| Workload | Common candidate | Adoption boundary |
|---|---|---|
| SQL statistics | pg_stat_statements | Official contrib and an observability baseline; govern query-text access |
| Vector search / RAG | pgvector | Measure recall, latency, memory, and index build with real filters |
| Geospatial | PostGIS | Standard GIS choice; verify extension and data-format upgrades |
| Time series | TimescaleDB | Evaluate for hypertables, compression, or continuous aggregates; check licensing feature by feature |
| Distributed multi-tenancy | Citus | Add only after measuring a single-node bottleneck and stabilizing a shard key |
| BM25 / search | ParadeDB / pg_search | Verify license, index recovery, replication, and cloud support |
| In-PostgreSQL BM25 | pg_textsearch | Upstream currently calls it production ready; independently validate target version and corpus |
| Embedded analytics / Parquet | pg_duckdb | Fit for analytics paths; test transaction boundaries, resource isolation, and object-store credentials |
| Iceberg columnstore mirror | pg_mooncake | Maintains a columnstore mirror from logical changes; validate consistency, object storage, pg_duckdb dependency, and recovery |
| Graph queries | Apache AGE | Adopt only when a graph model and Cypher produce measured value |
“Production ready” is an upstream project status, not a certification for your workload, SLA, or cloud platform.
Maintenance and data governance
| Tool | Purpose | Do not mistake it for |
|---|---|---|
| HypoPG | Evaluate planner choices with hypothetical indexes | Proof of build cost or production benefit |
| pg_repack | Reorganize tables and indexes with shorter exclusive-lock windows | A replacement for routine autovacuum |
| pg_partman | Manage native time/serial partition lifecycles | An automatic fix for a poor partition key |
| pg_cron | Schedule simple SQL inside PostgreSQL | A general business queue or workflow engine |
| Greenmask | Produce masked and subsetted test data | Permission to copy sensitive production data without review |
| PostgreSQL Anonymizer | Declarative static and dynamic masking | Automatic compliance with every requirement |
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.
A Beta feature is not a production dependency
As of 2026-08-02, PostgreSQL 19 remains Beta 2. Validate core REPACK semantics and constraints against final GA documentation and a restored copy of your own data.
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:
- Read target release notes, SQL update scripts, and known rebuild requirements.
- Restore a real backup into an isolated environment.
- Upgrade PostgreSQL and the extension, then run integrity, performance, and RLS tests.
- Rebuild required indexes and compare plans, recall, or business results.
- 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.
Last updated on
PostgreSQL index access methods
Choose among heap tables, B-tree, Hash, GIN, GiST, SP-GiST, BRIN, Bloom, HNSW, and IVFFlat with clear operator and extension boundaries
PostgreSQL compatible database guide
Classify Supabase, Neon, YugabyteDB, CockroachDB, Cloudberry, IvorySQL, Materialize, Gel, and FerretDB by PostgreSQL lineage and compatibility