PostgreSQL extensions in Azure Cosmos DB for PostgreSQL
APPLIES TO:
Azure Cosmos DB for PostgreSQL (powered by the Citus database
extension to PostgreSQL)
PostgreSQL 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');
To remove an extension installed this way, use drop_extension().
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 supported on 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, 13, 14, 15, or 16). The tables list extension versions per database version.
Citus extension
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| citus | Citus distributed database. | 9.5 | 10.2 | 11.3 | 12.1 | 12.1 | 12.1 |
Data types extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| citext | Provides a case-insensitive character string type. | 1.5 | 1.6 | 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 | 1.5 |
| hll | Provides a HyperLogLog data structure. | 2.18 | 2.18 | 2.18 | 2.18 | 2.18 | 2.18 |
| hstore | Provides a data type for storing sets of key-value pairs. | 1.5 | 1.6 | 1.7 | 1.8 | 1.8 | 1.8 |
| isn | Provides data types for international product numbering standards. | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 |
| lo | Large Object maintenance. | 1.1 | 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 | 1.2 |
| seg | Data type for representing line segments or floating-point intervals. | 1.3 | 1.3 | 1.3 | 1.4 | 1.4 | 1.4 |
| tdigest | Data type for on-line accumulation of rank-based statistics such as quantiles and trimmed means. | 1.4.1 | 1.4.1 | 1.4.1 | 1.4.1 | 1.4.1 | 1.4.1 |
| topn | Type for top-n JSONB. | 2.6.0 | 2.6.0 | 2.6.0 | 2.6.0 | 2.6.0 | 2.6.0 |
Full-text search extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| dict_int | Provides a text search dictionary template for integers. | 1.0 | 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 | 1.0 |
| unaccent | A text search dictionary that removes accents (diacritic signs) from lexemes. | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
Functions extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 15 |
|---|---|---|---|---|---|---|---|
| autoinc | Functions for autoincrementing fields. | 1.0 | 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 | 1.1 |
| fuzzystrmatch | Provides several functions to determine similarities and distance between strings. | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.2 |
| insert_username | Functions for tracking who changed a table. | 1.0 | 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 | 1.1 |
| intarray | Provides functions and operators for manipulating null-free arrays of integers. | 1.2 | 1.2 | 1.3 | 1.5 | 1.5 | 1.5 |
| moddatetime | Functions for tracking last modification time. | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
| orafce | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS. | 4.9 | 4.9 | 4.9 | |||
| pg_partman | Manages partitioned tables by time or ID. | 4.7.4 | 4.7.4 | 4.7.4 | 5.1.0 | 5.1.0 | 5.1.0 |
| pg_surgery | Functions to perform surgery on a damaged relation. | 1.0 | 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 | 1.6 |
| pgcrypto | Provides cryptographic functions. | 1.3 | 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 | 1.0 |
| tablefunc | Provides functions that manipulate whole tables, including crosstab. | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
| tcn | Triggered change notifications. | 1.0 | 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 | 1.1 |
Index types extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| bloom | Bloom access method - signature file-based index. | 1.0 | 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 | 1.3 |
| btree_gist | Provides GiST index operator classes that implement B-tree. | 1.5 | 1.5 | 1.5 | 1.6 | 1.7 | 1.7 |
Language extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| plpgsql | PL/pgSQL loadable procedural language. | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
Miscellaneous extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| amcheck | Functions for verifying relation integrity. | 1.1 | 1.2 | 1.2 | 1.3 | 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 | 1.2 |
| old_snapshot | Allows inspection of the server state that is used to implement old_snapshot_threshold. | 1.0 | 1.0 | 1.0 | |||
| pageinspect | Inspect the contents of database pages at a low level. | 1.7 | 1.7 | 1.8 | 1.9 | 1.11 | 1.12 |
| pg_azure_storage | Azure integration for PostgreSQL. | 1.3 | 1.3 | 1.3 | 1.3 | ||
| 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 | 1.4 |
| pg_cron | Job scheduler for PostgreSQL. | 1.5 | 1.6 | 1.6 | 1.6 | 1.6 | 1.6 |
| pg_freespacemap | Examine the free space map (FSM). | 1.2 | 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 | 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 | 1.10 |
| pg_visibility | Examine the visibility map (VM) and page-level visibility information. | 1.2 | 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 | 1.2 |
| pgstattuple | Provides a means for showing tuple-level statistics. | 1.5 | 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 | 1.1 |
| sslinfo | Information about TLS/SSL certificates. | 1.2 | 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 | 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 | 1.0 |
| xml2 | XPath querying and XSLT. | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 |
Pgvector extension
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| pgvector | Open-source vector similarity search for Postgres | 0.5.1 | 0.7.0 | 0.7.0 | 0.7.0 | 0.7.0 | 0.7.0 |
PostGIS extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 | PG 15 | PG 16 |
|---|---|---|---|---|---|---|---|
| PostGIS | Spatial and geographic objects for PostgreSQL. | 3.3.4 | 3.4.1 | 3.4.1 | 3.4.1 | 3.4.1 | 3.4.1 |
| address_standardizer | Used to parse an address into constituent elements. Used to support geocoding address normalization step. | 3.3.4 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 |
| postgis_sfcgal | PostGIS SFCGAL functions. | 3.3.4 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 |
| postgis_topology | PostGIS topology spatial types and functions. | 3.3.4 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 | 3.4.2 |
pg_stat_statements
The pg_stat_statements extension is preloaded on every Azure Cosmos DB for PostgreSQL cluster 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 it's the case for you or not.
dblink and postgres_fdw
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 clusters with public access, 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.
orafce extension
utl_file functions are disabled in orafce extension.
Next steps
- Learn about supported PostgreSQL versions.