Extensions

PostgreSQL extensions add new types, functions, index capabilities, or operators to the database. Common extensions include pgcrypto, uuid-ossp, pg_trgm, and vector.

The extensions supported by CloudBase PostgreSQL depend on the console and the actual database version. Before enabling an extension, evaluate its permission, security, and performance impact.

For the complete extension list, see Supported extensions.

View installed extensions

Use the DMC SQL window to query installed extensions.

select extname, extversion
from pg_extension
order by extname;

You can also list available extensions.

select name, default_version, installed_version, comment
from pg_available_extensions
order by name;

Enable extensions

Extensions are usually enabled with create extension. Install them into an explicit schema when possible.

create extension if not exists pgcrypto;
create extension if not exists pg_trgm;

If an extension requires higher privileges or is not supported by the instance, SQL will return an error. Use the console support scope as the source of truth.

Common extensions

Extension	Use case
pgcrypto	Random values, digests, and cryptographic functions
uuid-ossp	UUID generation
pg_trgm	Text similarity and fuzzy search
vector	Vector type and similarity search

For vector search, see pgvector.

Fuzzy search with pg_trgm

pg_trgm is suitable for fuzzy matching and similarity ranking on short text.

create extension if not exists pg_trgm;

create index articles_title_trgm_idx
on public.articles
using gin (title gin_trgm_ops);

select id, title
from public.articles
where title ilike '%cloudbase%'
order by similarity(title, 'cloudbase') desc
limit 20;

For natural language tokenization, weighting, and ranking, see Full text search.

Remove extensions

Before removing an extension, confirm that no tables, indexes, functions, or views depend on it.

drop extension if exists pg_trgm;

Do not remove extensions directly in production. Check dependencies in staging first, then schedule a maintenance window.

Notes

• Extensions change database capabilities. Enable them only when the business needs them.
• Some extensions require higher privileges or specific PostgreSQL versions.
• Extensions can affect backup, restore, and cross-environment migration. Record extension statements in initialization scripts.