Extensions

The Postgres extensions available in CapyDB, how to enable them per database, and why the list is an allowlist.

The allowlist

Every CapyDB database on the supported Postgres 16, 17, and 18 majors provides these extensions. Restart marks the ones that load a shared library, so enabling or disabling them restarts your database briefly.

Core types and utilities

Extension	Trusted	Restart	What it is for
`pgcrypto`	yes	no	Cryptographic functions (`gen_random_uuid()`, digests, PGP)
`uuid-ossp`	yes	no	Legacy UUID generators (`uuid_generate_v4()` etc.)
`pg_uuidv7`	yes	no	Time-sortable UUIDv7. Postgres 18 has this built in; this brings it to 16 and 17
`citext`	yes	no	Case-insensitive text type
`hstore`	yes	no	Key/value pairs in a column
`ltree`	yes	no	Hierarchical label paths with rich search operators

AI and vector search

Extension	Trusted	Restart	What it is for
`vector` (pgvector)	yes	no	Vector similarity search for embeddings
`vectorscale`	yes	no	StreamingDiskANN indexes for pgvector - disk-backed vector search that stays fast once embeddings outgrow memory. Requires `vector`

Full-text and fuzzy search

Extension	Trusted	Restart	What it is for
`pg_trgm`	yes	no	Trigram matching - fuzzy search and `LIKE` acceleration
`unaccent`	yes	no	Text-search dictionary that strips accents
`fuzzystrmatch`	yes	no	String similarity and distance (soundex, levenshtein)
`rum`	yes	no	Full-text index that stores ranking data inside the index, so relevance-ordered searches skip the extra table lookup GIN needs
`pg_similarity`	yes	no	Similarity operators beyond trigrams (Jaccard, Jaro-Winkler, Levenshtein, and more)

Query performance

Extension	Trusted	Restart	What it is for
`pg_stat_statements`	no	no	Query statistics; also powers the slow-query view in Observability
`pg_qualstats`	no	yes	Collects the query predicates behind the index advisor
`hypopg`	yes	no	Hypothetical indexes - estimate an index's cost and whether the planner would use it, without building it
`pg_ivm`	no	no	Incrementally maintained materialized views that refresh as the underlying tables change
`btree_gin`	yes	no	GIN operator classes for common scalar types
`btree_gist`	yes	no	GiST operator classes for common scalar types

Security and audit

Extension	Trusted	Restart	What it is for
`pgaudit`	no	yes	Session and object audit logging for compliance - see Audit logging
`pg_permissions`	yes	no	Views showing every privilege granted in the database, so access reviews happen in one place

Analytics data types

Extension	Trusted	Restart	What it is for
`hll`	yes	no	HyperLogLog sketches for counting distinct values in fixed space
`roaringbitmap`	yes	no	Compressed bitmaps with fast set operations - segmentation and audience queries
`tdigest`	yes	no	Percentile and quantile estimation over large data sets

Geospatial and scheduling

Extension	Trusted	Restart	What it is for
`postgis`	no	no	Geographic objects, spatial types, and spatial indexing
`pg_cron`	no	yes	In-database cron scheduler

Developer tools

Extension	Trusted	Restart	What it is for
`pgtap`	yes	no	Unit testing framework for your schema and functions
`plpgsql_check`	no	no	Linter for PL/pgSQL functions - finds errors without executing them

plpgsql ships inside every Postgres server and is always available. New projects start with vector, pgcrypto, uuid-ossp, and pg_trgm already enabled.

Four tooling extensions - pg_qualstats, pg_permissions, hypopg, and plpgsql_check - install into an extensions schema rather than public, so their views never collide with declarative migration tools like drizzle-kit push. Call them schema-qualified, e.g. extensions.hypopg_create_index(...).

Extensions that restart the database

pg_cron, pgaudit, and pg_qualstats each load a shared library (shared_preload_libraries), which Postgres can only pick up at startup. Enabling or disabling one therefore restarts your database: open connections drop and reconnect within a few seconds, so do it during a quiet window. The dashboard flags these with a "restarts database" marker and asks you to confirm; the CLI and API enable jobs perform the restart themselves.

pg_cron's scheduler runs against your project database, so cron.schedule(...) works from your own connection once enabled. Every other allowlisted extension enables with no restart.

Trusted vs superuser-managed

The Trusted column is the practical split:

Trusted extensions can be enabled by your own database role, on a direct connection, the normal way:
```
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;
```
Most of these are trusted because PostgreSQL ships them that way. CapyDB additionally marks a curated set trusted on the host - vector types, index access methods, analytics types, and read-only tooling - so you can install them from a migration without a round trip through the API. Whether you use SQL or the managed path, the Extensions page reflects what your database actually has: the listing reconciles itself against the live catalog, so an extension you create yourself shows up as enabled and can be removed from the dashboard.
Superuser-managed extensions - everything not marked trusted - need privileges your role deliberately does not have. Enable them through CapyDB instead.

The split is not arbitrary. An extension is only trusted when installing it cannot lead anywhere privileged: types, operators, index methods, and pure computation. postgis (raster and file-based loaders), pg_ivm (installs into pg_catalog and performs DDL for you), and plpgsql_check (reads arbitrary function bodies) stay superuser-managed.

pgaudit, pg_qualstats, and pg_cron could not be trusted even in principle: they load a shared library, so CREATE EXTENSION fails with "must be loaded via shared_preload_libraries" until the library is preloaded and the database restarted - and only the managed path can do that.

Managing extensions per database

Three equivalent surfaces, all backed by the same async job:

Dashboard - the project's Extensions page lists the allowlist with enablement toggles.

CLI:

capydb extensions list --project my-app
capydb extensions enable postgis --project my-app --wait
capydb extensions disable hstore --project my-app --wait

API:

GET    /v1/projects/{projectID}/extensions          # allowlist + enabled/trusted/version per extension
POST   /v1/projects/{projectID}/extensions          # {"name": "postgis"} → 202 + job
DELETE /v1/projects/{projectID}/extensions/{name}   # → 202 + job

Enable and disable are asynchronous jobs - poll the returned job (or pass --wait in the CLI) until it reaches completed. The extension is recorded as enabled only after CREATE EXTENSION actually succeeded in your database, so the listing never claims something the database does not have.

Disable is `RESTRICT`, on purpose

Disabling runs DROP EXTENSION without CASCADE. If anything in your schema depends on the extension - a column of its type, an index using its operator class - the job fails with the dependency error instead of silently dropping your objects. Remove the dependents first if you really mean it.

Index advisor

pg_qualstats records which predicates your queries filtered on and how many rows each discarded. CapyDB turns that into concrete index suggestions, then measures each candidate by building it as a hypothetical index with hypopg - the index exists only in the planner's memory for the length of one connection, so nothing is written and nothing is created. The whole call is read-only and safe against production.

Enable both extensions (pg_qualstats restarts the database; hypopg does not):

capydb extensions enable pg_qualstats --project my-app --wait
capydb extensions enable hypopg --project my-app --wait

Then let the database serve real traffic for a while and ask:

capydb advisor indexes --project my-app

TABLE          METHOD  EST. SIZE  STATEMENT
public.orders  btree   4.2 MB     CREATE INDEX ON public.orders USING btree (customer_id);

Suggestions appear only once a predicate has crossed the thresholds, so an empty list on a quiet database is expected rather than an error. Lower --min-filter (default 1000 rows filtered) to widen the search.

CapyDB sets pg_qualstats.sample_rate = 1 when you enable the extension, so every query is sampled. Postgres' own default is 1/max_connections, which on a CapyDB database works out to roughly 0.2% - far too little for the advisor to ever see a pattern. If your workload is hot enough that the sampling overhead matters, lower it:

ALTER SYSTEM SET pg_qualstats.sample_rate = 0.1;
SELECT pg_reload_conf();

The same report is on the project's Usage & Observability page, in the API at GET /v1/projects/{projectID}/advisor/indexes, and as the suggest_indexes MCP tool.

CapyDB never creates these indexes for you. CREATE INDEX locks writes on the table while it builds - on a large table use CREATE INDEX CONCURRENTLY instead.

Audit logging

pgaudit records what happened in your database in detail enough for SOC 2 and HIPAA evidence. Enabling it restarts the database and applies a defensible default:

Setting	Value	Why
`pgaudit.log`	`ddl, role, write`	Schema changes, `GRANT`/role changes, and data modifications. Reads are excluded - auditing every `SELECT` floods the log and is not required for most compliance regimes
`pgaudit.log_parameter`	`off`	Bind parameters carry customer data and secrets; logging them would turn an audit trail into a data leak
`pgaudit.log_catalog`	`off`	Suppresses noise from catalog introspection, which every ORM does constantly
`pgaudit.log_relation`	`on`	Records each relation touched by a statement

Audit entries land in your database log, readable through Logs and capydb logs. To audit reads as well, set it on your own database:

ALTER DATABASE my_app SET pgaudit.log = 'ddl, role, write, read';

Why an allowlist

Extensions run inside the database process. A short, audited list keeps upgrades predictable and means a restore or import never lands on a node missing a library it needs. The list covers the unexciting majority of real workloads: UUIDs, crypto, fuzzy search, embeddings, and - since PostGIS joined - geospatial.

Imports and extensions

The import preflight fails when the source database uses an extension that is not on this list, naming the offenders. Options at that point:

drop the extension on (a copy of) the source if it was installed but unused - common with provider-default extensions
replace its usage (uuid-ossp → pgcrypto's gen_random_uuid() is the classic)
if your workload genuinely needs something missing, contact support - the allowlist grows on demand, not on speculation

After a successful import, the per-project extension list is reconciled with what the restored dump actually created, so the Extensions page reflects reality rather than the pre-import defaults.

On this page