PostgreSQL extensions in Azure Cosmos DB for PostgreSQL

APPLIES TO: Azure Cosmos DB for PostgreSQL (powered by the Citus database extension to PostgreSQL)

PostgreSQL provides the ability to extend the functionality of your database by using extensions. Extensions allow for bundling multiple related SQL objects together in a single package that can be loaded or removed from your database with a single command. After being loaded in the database, extensions can function like built-in features. For more information on PostgreSQL extensions, see Package related objects into an extension.

Use PostgreSQL extensions

PostgreSQL extensions must be installed in your database before you can use them. To install a particular extension, run the CREATE EXTENSION command from the psql tool to load the packaged objects into your database.

Note

If CREATE EXTENSION fails with a permission denied error, try the create_extension() function instead. For instance:

SELECT create_extension('postgis');

Azure Cosmos DB for PostgreSQL currently supports a subset of key extensions as listed here. Extensions other than the ones listed aren't supported. You can't create your own extension with Azure Cosmos DB for PostgreSQL.

Extensions supported by Azure Cosmos DB for PostgreSQL

The following tables list the standard PostgreSQL extensions that are currently supported by Azure Cosmos DB for PostgreSQL. This information is also available by running SELECT * FROM pg_available_extensions;.

The versions of each extension installed in a cluster sometimes differ based on the version of PostgreSQL (11, 12, or 13). The tables list extension versions per database version.

Citus extension

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
citus Citus distributed database. 9.5.11 10.0.7 10.2.8 11.1.4 11.1.4

Data types extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
citext Provides a case-insensitive character string type. 1.5 1.6 1.6 1.6 1.6
cube Provides a data type for multidimensional cubes. 1.4 1.4 1.4 1.5 1.5
hll Provides a HyperLogLog data structure. 2.16 2.16 2.16 2.16 2.16
hstore Provides a data type for storing sets of key-value pairs. 1.5 1.6 1.7 1.8 1.8
isn Provides data types for international product numbering standards. 1.2 1.2 1.2 1.2 1.2
lo Large Object maintenance. 1.1 1.1 1.1 1.1 1.1
ltree Provides a data type for hierarchical tree-like structures. 1.1 1.1 1.2 1.2 1.2
seg Data type for representing line segments or floating-point intervals. 1.3 1.3 1.3 1.4 1.4
tdigest Data type for on-line accumulation of rank-based statistics such as quantiles and trimmed means. 1.2.0 1.2.0 1.2.0 1.4.0 1.4.0
topn Type for top-n JSONB. 2.4.0 2.4.0 2.4.0 2.4.0 2.5.0

Full-text search extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
dict_int Provides a text search dictionary template for integers. 1.0 1.0 1.0 1.0 1.0
dict_xsyn Text search dictionary template for extended synonym processing. 1.0 1.0 1.0 1.0 1.0
unaccent A text search dictionary that removes accents (diacritic signs) from lexemes. 1.1 1.1 1.1 1.1 1.1

Functions extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
autoinc Functions for autoincrementing fields. 1.0 1.0 1.0 1.0 1.0
earthdistance Provides a means to calculate great-circle distances on the surface of the Earth. 1.1 1.1 1.1 1.1 1.1
fuzzystrmatch Provides several functions to determine similarities and distance between strings. 1.1 1.1 1.1 1.1 1.1
insert_username Functions for tracking who changed a table. 1.0 1.0 1.0 1.0 1.0
intagg Integer aggregator and enumerator (obsolete). 1.1 1.1 1.1 1.1 1.1
intarray Provides functions and operators for manipulating null-free arrays of integers. 1.2 1.2 1.3 1.5 1.5
moddatetime Functions for tracking last modification time. 1.0 1.0 1.0 1.0 1.0
pg_partman Manages partitioned tables by time or ID. 4.6.0 4.6.0 4.6.0 4.6.2 4.7.0
pg_surgery Functions to perform surgery on a damaged relation. 1.0 1.0
pg_trgm Provides functions and operators for determining the similarity of alphanumeric text based on trigram matching. 1.4 1.4 1.5 1.6 1.6
pgcrypto Provides cryptographic functions. 1.3 1.3 1.3 1.3 1.3
refint Functions for implementing referential integrity (obsolete). 1.0 1.0 1.0 1.0 1.0
tablefunc Provides functions that manipulate whole tables, including crosstab. 1.0 1.0 1.0 1.0 1.0
tcn Triggered change notifications. 1.0 1.0 1.0 1.0 1.0
timetravel Functions for implementing time travel. 1.0
uuid-ossp Generates universally unique identifiers (UUIDs). 1.1 1.1 1.1 1.1 1.1

Index types extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
bloom Bloom access method - signature file-based index. 1.0 1.0 1.0 1.0 1.0
btree_gin Provides sample GIN operator classes that implement B-tree-like behavior for certain data types. 1.3 1.3 1.3 1.3 1.3
btree_gist Provides GiST index operator classes that implement B-tree. 1.5 1.5 1.5 1.6 1.7

Language extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
plpgsql PL/pgSQL loadable procedural language. 1.0 1.0 1.0 1.0 1.0

Miscellaneous extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
amcheck Functions for verifying relation integrity. 1.1 1.2 1.2 1.3 1.3
dblink A module that supports connections to other PostgreSQL databases from within a database session. See the "dblink and postgres_fdw" section for information about this extension. 1.2 1.2 1.2 1.2 1.2
old_snapshot Allows inspection of the server state that is used to implement old_snapshot_threshold. 1.0 1.0
pageinspect Inspect the contents of database pages at a low level. 1.7 1.7 1.8 1.9 1.10
pg_azure_storage Azure integration for PostgreSQL. 1.0 1.0 1.0
pg_buffercache Provides a means for examining what's happening in the shared buffer cache in real time. 1.3 1.3 1.3 1.3 1.3
pg_cron Job scheduler for PostgreSQL. 1.4 1.4 1.4 1.4 1.4
pg_freespacemap Examine the free space map (FSM). 1.2 1.2 1.2 1.2 1.2
pg_prewarm Provides a way to load relation data into the buffer cache. 1.2 1.2 1.2 1.2 1.2
pg_stat_statements Provides a means for tracking execution statistics of all SQL statements executed by a server. See the "pg_stat_statements" section for information about this extension. 1.6 1.7 1.8 1.9 1.10
pg_visibility Examine the visibility map (VM) and page-level visibility information. 1.2 1.2 1.2 1.2 1.2
pgrowlocks Provides a means for showing row-level locking information. 1.2 1.2 1.2 1.2 1.2
pgstattuple Provides a means for showing tuple-level statistics. 1.5 1.5 1.5 1.5 1.5
postgres_fdw Foreign-data wrapper used to access data stored in external PostgreSQL servers. See the "dblink and postgres_fdw" section for information about this extension. 1.0 1.0 1.0 1.1 1.1
sslinfo Information about TLS/SSL certificates. 1.2 1.2 1.2 1.2 1.2
tsm_system_rows TABLESAMPLE method, which accepts number of rows as a limit. 1.0 1.0 1.0 1.0 1.0
tsm_system_time TABLESAMPLE method, which accepts time in milliseconds as a limit. 1.0 1.0 1.0 1.0 1.0
xml2 XPath querying and XSLT. 1.1 1.1 1.1 1.1 1.1

PostGIS extensions

Extension Description PG 11 PG 12 PG 13 PG 14 PG 15
PostGIS Spatial and geographic objects for PostgreSQL. 2.5.5 3.0.5 3.0.5 3.1.5 3.3.1
address_standardizer Used to parse an address into constituent elements. Used to support geocoding address normalization step. 2.5.5 3.0.5 3.0.5 3.1.5 3.3.1
postgis_sfcgal PostGIS SFCGAL functions. 2.5.5 3.0.5 3.0.5 3.1.5 3.3.1
postgis_topology PostGIS topology spatial types and functions. 2.5.5 3.0.5 3.0.5 3.1.5 3.3.1

pg_stat_statements

The pg_stat_statements extension is preloaded on every Azure Cosmos DB for PostgreSQL server to provide you with a means of tracking execution statistics of SQL statements.

The setting pg_stat_statements.track controls what statements are counted by the extension. It defaults to top, which means that all statements issued directly by clients are tracked. The two other tracking levels are none and all.

There's a tradeoff between the query execution information pg_stat_statements provides and the effect on server performance as it logs each SQL statement. If you aren't actively using the pg_stat_statements extension, we recommend that you set pg_stat_statements.track to none. Some third-party monitoring services might rely on pg_stat_statements to deliver query performance insights, so confirm whether this is the case for you or not.

You can use dblink and postgres_fdw to connect from one PostgreSQL server to another, or to another database in the same server. The receiving server needs to allow connections from the sending server through its firewall. To use these extensions to connect between Azure Cosmos DB for PostgreSQL servers or clusters, set Allow Azure services and resources to access this cluster (or server) to ON. You also need to turn this setting ON if you want to use the extensions to loop back to the same server. The Allow Azure services and resources to access this cluster setting can be found in the Azure portal page for the cluster under Networking. Currently, outbound connections from Azure Cosmos DB for PostgreSQL aren't supported.