PostgreSQL extensions in Azure Database for PostgreSQL – Hyperscale (Citus)
APPLIES TO: 
Azure Database for PostgreSQL - Hyperscale (Citus)
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 Database for PostgreSQL - Hyperscale (Citus) 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 Database for PostgreSQL.
Extensions supported by Azure Database for PostgreSQL
The following tables list the standard PostgreSQL extensions that are currently supported by Azure Database for PostgreSQL. This information is also available by running SELECT * FROM pg_available_extensions;.
The versions of each extension installed in a server group 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 |
|---|---|---|---|---|---|
| citus | Citus distributed database. | 9.5.11 | 10.0.7 | 10.2.6 | 11.0.4 |
Data types extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| citext | Provides a case-insensitive character string type. | 1.5 | 1.6 | 1.6 | 1.6 |
| cube | Provides a data type for multidimensional cubes. | 1.4 | 1.4 | 1.4 | 1.5 |
| hll | Provides a HyperLogLog data structure. | 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 |
| isn | Provides data types for international product numbering standards. | 1.2 | 1.2 | 1.2 | 1.2 |
| lo | Large Object maintenance. | 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 |
| seg | Data type for representing line segments or floating-point intervals. | 1.3 | 1.3 | 1.3 | 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 |
| topn | Type for top-n JSONB. | 2.4.0 | 2.4.0 | 2.4.0 | 2.4.0 |
Full-text search extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| dict_int | Provides a text search dictionary template for integers. | 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 |
| unaccent | A text search dictionary that removes accents (diacritic signs) from lexemes. | 1.1 | 1.1 | 1.1 | 1.1 |
Functions extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| autoinc | Functions for autoincrementing fields. | 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 |
| fuzzystrmatch | Provides several functions to determine similarities and distance between strings. | 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 |
| intagg | Integer aggregator and enumerator (obsolete). | 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 |
| moddatetime | Functions for tracking last modification time. | 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 |
| pg_surgery | Functions to perform surgery on a damaged relation. | 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 |
| pgcrypto | Provides cryptographic functions. | 1.3 | 1.3 | 1.3 | 1.3 |
| refint | Functions for implementing referential integrity (obsolete). | 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 |
| tcn | Triggered change notifications. | 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 |
Index types extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| bloom | Bloom access method - signature file-based index. | 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 |
| btree_gist | Provides GiST index operator classes that implement B-tree. | 1.5 | 1.5 | 1.5 | 1.6 |
Language extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| plpgsql | PL/pgSQL loadable procedural language. | 1.0 | 1.0 | 1.0 | 1.0 |
Miscellaneous extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| amcheck | Functions for verifying relation integrity. | 1.1 | 1.2 | 1.2 | 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 |
| old_snapshot | Allows inspection of the server state that is used to implement old_snapshot_threshold. | 1.0 | |||
| pageinspect | Inspect the contents of database pages at a low level. | 1.7 | 1.7 | 1.8 | 1.9 |
| 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 |
| pg_cron | Job scheduler for PostgreSQL. | 1.4 | 1.4 | 1.4 | 1.4 |
| pg_freespacemap | Examine the free space map (FSM). | 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 |
| 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 |
| pg_visibility | Examine the visibility map (VM) and page-level visibility information. | 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 |
| pgstattuple | Provides a means for showing tuple-level statistics. | 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 |
| sslinfo | Information about TLS/SSL certificates. | 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 |
| tsm_system_time | TABLESAMPLE method, which accepts time in milliseconds as a limit. | 1.0 | 1.0 | 1.0 | 1.0 |
| xml2 | XPath querying and XSLT. | 1.1 | 1.1 | 1.1 | 1.1 |
PostGIS extensions
| Extension | Description | PG 11 | PG 12 | PG 13 | PG 14 |
|---|---|---|---|---|---|
| PostGIS | Spatial and geographic objects for PostgreSQL. | 2.5.5 | 3.0.5 | 3.0.5 | 3.1.5 |
| 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 |
| postgis_sfcgal | PostGIS SFCGAL functions. | 2.5.5 | 3.0.5 | 3.0.5 | 3.1.5 |
| postgis_topology | PostGIS topology spatial types and functions. | 2.5.5 | 3.0.5 | 3.0.5 | 3.1.5 |
pg_stat_statements
The pg_stat_statements extension is preloaded on every Azure Database 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. This setting is configurable as a server parameter through the Azure portal or the Azure CLI.
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.
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 Database for PostgreSQL servers or Hyperscale (Citus) server groups, set Allow Azure services and resources to access this server group (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 server group setting can be found in the Azure portal page for the Hyperscale (Citus) server group under Networking. Currently, outbound connections from Azure Database for PostgreSQL Single server and Hyperscale (Citus) aren't supported, except for connections to other Azure Database for PostgreSQL servers and Hyperscale (Citus) server groups.
Povratne informacije
Pošalјite i prikažite povratne informacije za