Extensions in Managed Service for PostgreSQL®

Managed Service for PostgreSQL clusters are created with pre-installed PostgreSQL extensions. You can enable any of these extensions for your databases, but you cannot install any other extensions. See the full list of available extensions below, or you can request the list.

How to enable an extension for a database

To enable an extension for a database, execute the CREATE EXTENSION statement:

CREATE EXTENSION <extension_name>;

This command does not affect other databases in the cluster. To use an extension in other databases, execute CREATE EXTENSION in each one as needed.

Example: pgvector extension

To get started with the pgvector open-source extension, which provides vector operations, follow these steps:

Add the extension:
```
CREATE EXTENSION vector;
```

Create a table with a vector column:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

Insert some vectors into the new table:

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

Get the contents of the table sorted by how close each value is to a given vector (that is, sorted by Euclidean distance):
```
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
```

How to request the list of installed extensions

To get the list of extensions available on your cluster, execute the following statement:

SELECT * FROM pg_available_extensions;

To look for a particular extension, execute the following statement:

SELECT * FROM pg_available_extensions WHERE name LIKE '<extension_name>';

Available extensions

Extension	Version	Description
adminpack	2.1	Provides support functions for administration and management tools.
amcheck	1.3	Provides functions that verify the logical consistency of the structure of relations.
autoinc	1.0	Provides functions that autoincrement fields.
bloom	1.0	Implements an index access method based on Bloom filters.
btree_gin	1.3	Supports indexing common data types by using GIN (generalized inverted index).
btree_gist	1.7	Supports indexing common data types by using GiST (generalized search tree).
citext	1.6	Defines a data type for case-insensitive character strings.
cube	1.5	Defines a data type for multidimensional cubes.
dblink	1.2	Allows connections to other PostgreSQL databases from within a database.
dict_int	1.0	Defines a text search dictionary template for processing integers.
dict_xsyn	1.0	Defines a text search dictionary template for extended synonym processing.
earthdistance	1.1	Calculates great-circle distances on the surface of the Earth.
file_fdw	1.0	Provides a foreign-data wrapper to access data files in the server’s file system.
fuzzystrmatch	1.2	Provides functions that determine similarities and distances between strings.
hstore	1.8	Defines a data type that stores sets of key-value pairs.
insert_username	1.0	Provides functions that track which user changed a table.
intagg	1.1	Defines an integer aggregator and enumerator. The module is provided for compatibility and is obsolete.
intarray	1.5	Provides functions and operators that manipulate null-free arrays of integers.
isn	1.2	Defines data types for international product numbering standards.
lo	1.1	Supports large object management.
ltree	1.2	Defines a data type for hierarchical tree-like structures.
moddatetime	1.0	Provides functions that track the last modification time of records.
old_snapshot	1.0	Provides utilities that manage old snapshot thresholds.
pageinspect	1.12	Provides functions that inspect the contents of database pages at a low level.
pg_buffercache	1.4	Provides a means of examining the shared buffer cache.
pg_freespacemap	1.2	Provides a means of examining the free space map.
pg_prewarm	1.2	Loads relation data into the buffer cache.
pg_repack	1.5.2	Reorganizes tables and indexes with minimal locks. See how to run pg_repack on a Managed Service for PostgreSQL cluster.
pg_stat_statements	1.10	Tracks planning and execution statistics for SQL statements.
pg_surgery	1.0	Provides functions that perform “surgery” on damaged relations.
pg_trgm	1.6	Measures text similarity and supports index searching based on trigram matching.
pg_visibility	1.2	Provides a means of examining the visibility map and page-level visibility information.
pg_walinspect	1.1	Provides functions that inspect the PostgreSQL write-ahead log.
pgaudit	16.0	Provides detailed session and object audit logging via the standard PostgreSQL logging facility.
pgcrypto	1.3	Provides cryptographic functions.
pgrowlocks	1.2	Displays row-level locking information.
pgstattuple	1.5	Provides functions to obtain tuple-level statistics.
plpgsql	1.0	PL/pgSQL procedural language for PostgreSQL.
postgres_fdw	1.1	Provides a foreign-data wrapper for connecting to external PostgreSQL servers.
refint	1.0	Provides functions for referential integrity. The module is provided for compatibility and is obsolete.
rum	1.3	Provides an access method for working with RUM indexes.
seg	1.4	Defines a data type that represents line segments or floating-point intervals.
sslinfo	1.2	Provides information about SSL certificates.
tablefunc	1.0	Implements functions that manipulate whole tables, including `crosstab` operations.
tcn	1.0	Provides a trigger function that notifies listeners of changes.
tsm_system_rows	1.0	Provides a `TABLESAMPLE` method that reads no more than the given number of rows.
tsm_system_time	1.0	Provides a `TABLESAMPLE` method that spends no longer than the given time to read the table.
unaccent	1.1	Defines a text search dictionary that removes diacritics.
uuid-ossp	1.1	Generates universally unique identifiers (UUIDs).
vector (pgvector)	0.8	Defines a vector data type with `ivfflat` and `hnsw` access methods for vector searches.
xml2	1.1	Provides functions for XPath querying and XSLT processing.

How to run pg_repack

Managed Service for PostgreSQL clusters do not provide superuser access. Therefore, run the pg_repack extension only on the tables that you own and include the --no-superuser-check option. For example:

pg_repack \
  --host=<cluster_endpoint> \
  --dbname=<DB_name> \
  --username=<username> \
  --table=<table_name> \
  --jobs=4 \
  --wait-timeout=600 \
  --no-superuser-check

In this command, specify the same parameters that you use for connecting to the cluster. Ensure that <username> owns <table_name>.

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

Object Storage

Managed Service for PostgreSQL®

Container Registry

Extensions in Managed Service for PostgreSQL®

How to enable an extension for a database

Example: pgvector extension

How to request the list of installed extensions

Available extensions

How to run pg_repack

Object Storage

Managed Service for PostgreSQL®

Container Registry

​How to enable an extension for a database

​Example: pgvector extension

​How to request the list of installed extensions

​Available extensions

​How to run pg_repack

How to enable an extension for a database

Example: pgvector extension

How to request the list of installed extensions

Available extensions

How to run pg_repack