PostgreSQL extensions in 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.

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;.

Data types extensions

Extension Description
citext Provides a case-insensitive character string type.
cube Provides a data type for multidimensional cubes.
hstore Provides a data type for storing sets of key-value pairs.
hll Provides a HyperLogLog data structure.
isn Provides data types for international product numbering standards.
lo Large Object maintenance.
ltree Provides a data type for hierarchical tree-like structures.
seg Data type for representing line segments or floating-point intervals.
topn Type for top-n JSONB.

Full-text search extensions

Extension Description
dict_int Provides a text search dictionary template for integers.
dict_xsyn Text search dictionary template for extended synonym processing.
unaccent A text search dictionary that removes accents (diacritic signs) from lexemes.

Functions extensions

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

Hyperscale extensions

Extension Description
citus Citus distributed database.
shard_rebalancer Safely rebalance data in a server group in case of node addition or removal.

Index types extensions

Extension Description
bloom Bloom access method - signature file-based index.
btree_gin Provides sample GIN operator classes that implement B-tree-like behavior for certain data types.
btree_gist Provides GiST index operator classes that implement B-tree.

Language extensions

Extension Description
plpgsql PL/pgSQL loadable procedural language.

Miscellaneous extensions

Extension Description
adminpack Administrative functions for PostgreSQL.
amcheck Functions for verifying relation integrity.
file_fdw Foreign-data wrapper for flat file access.
pageinspect Inspect the contents of database pages at a low level.
pg_buffercache Provides a means for examining what's happening in the shared buffer cache in real time.
pg_cron Job scheduler for PostgreSQL.
pg_freespacemap Examine the free space map (FSM).
pg_prewarm Provides a way to load relation data into the buffer cache.
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.
pg_visibility Examine the visibility map (VM) and page-level visibility information.
pgrowlocks Provides a means for showing row-level locking information.
pgstattuple Provides a means for showing tuple-level statistics.
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.
sslinfo Information about TLS/SSL certificates.
tsm_system_rows TABLESAMPLE method, which accepts number of rows as a limit.
tsm_system_time TABLESAMPLE method, which accepts time in milliseconds as a limit.
hypopg Provides a means of creating hypothetical indexes that don't cost CPU or disk.
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.
xml2 XPath querying and XSLT.

PostGIS extensions

Extension Description
PostGIS, postgis_topology, postgis_tiger_geocoder, postgis_sfcgal Spatial and geographic objects for PostgreSQL.
address_standardizer, address_standardizer_data_us Used to parse an address into constituent elements. Used to support geocoding address normalization step.
postgis_sfcgal PostGIS SFCGAL functions.
postgis_tiger_geocoder PostGIS tiger geocoder and reverse geocoder.
postgis_topology PostGIS topology spatial types and functions.

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.

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.