PostgreSQL extensions in Azure Database for PostgreSQL - Flexible Server

PostgreSQL provides the ability to extend the functionality of your database using extensions. Extensions bundle multiple related SQL objects together in a single package that can be loaded or removed from your database with a command. After being loaded in the database, extensions function like built-in features.

How to 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. This command loads the packaged objects into your database.

Azure Database for PostgreSQL supports a subset of key extensions as listed below. This information is also available by running SHOW azure.extensions;. Extensions not listed in this document are not supported on Azure Database for PostgreSQL - Flexible Server. You cannot create or load your own extension in Azure Database for PostgreSQL.

Postgres 13 extensions

The following extensions are available in Azure Database for PostgreSQL - Flexible Servers which have Postgres version 13.

Extension Extension version Description
address_standardizer 3.1.1 Used to parse an address into constituent elements.
address_standardizer_data_us 3.1.1 Address Standardizer US dataset example
amcheck 1.2 functions for verifying relation integrity
bloom 1.0 bloom access method - signature file based index
btree_gin 1.3 support for indexing common datatypes in GIN
btree_gist 1.5 support for indexing common datatypes in GiST
citext 1.6 data type for case-insensitive character strings
cube 1.4 data type for multidimensional cubes
dblink 1.2 connect to other PostgreSQL databases from within a database
dict_int 1.0 text search dictionary template for integers
dict_xsyn 1.0 text search dictionary template for extended synonym processing
earthdistance 1.1 calculate great-circle distances on the surface of the Earth
fuzzystrmatch 1.1 determine similarities and distance between strings
hstore 1.7 data type for storing sets of (key, value) pairs
intagg 1.1 integer aggregator and enumerator. (Obsolete)
intarray 1.3 functions, operators, and index support for 1-D arrays of integers
isn 1.2 data types for international product numbering standards
lo 1.1 large object maintenance
ltree 1.2 data type for hierarchical tree-like structures
pageinspect 1.8 inspect the contents of database pages at a low level
pg_buffercache 1.3 examine the shared buffer cache
pg_cron 1.3 Job scheduler for PostgreSQL
pg_freespacemap 1.2 examine the free space map (FSM)
pg_partman 4.5.0 Extension to manage partitioned tables by time or ID
pg_prewarm 1.2 prewarm relation data
pg_stat_statements 1.8 track execution statistics of all SQL statements executed
pg_trgm 1.5 text similarity measurement and index searching based on trigrams
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pgaudit 1.5 provides auditing functionality
pgcrypto 1.3 cryptographic functions
pglogical 2.3.2 Logical streaming replication
pgrowlocks 1.2 show row-level locking information
pgstattuple 1.5 show tuple-level statistics
plpgsql 1.0 PL/pgSQL procedural language
postgis 3.1.1 PostGIS geometry, geography
postgis_raster 3.1.1 PostGIS raster types and functions
postgis_sfcgal 3.1.1 PostGIS SFCGAL functions
postgis_tiger_geocoder 3.1.1 PostGIS tiger geocoder and reverse geocoder
postgis_topology 3.1.1 PostGIS topology spatial types and functions
postgres_fdw 1.0 foreign-data wrapper for remote PostgreSQL servers
sslinfo 1.2 information about SSL certificates
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent 1.1 text search dictionary that removes accents
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)

Postgres 12 extensions

The following extensions are available in Azure Database for PostgreSQL - Flexible Servers which have Postgres version 12.

Extension Extension version Description
address_standardizer 3.0.0 Used to parse an address into constituent elements.
address_standardizer_data_us 3.0.0 Address Standardizer US dataset example
amcheck 1.2 functions for verifying relation integrity
bloom 1.0 bloom access method - signature file based index
btree_gin 1.3 support for indexing common datatypes in GIN
btree_gist 1.5 support for indexing common datatypes in GiST
citext 1.6 data type for case-insensitive character strings
cube 1.4 data type for multidimensional cubes
dblink 1.2 connect to other PostgreSQL databases from within a database
dict_int 1.0 text search dictionary template for integers
dict_xsyn 1.0 text search dictionary template for extended synonym processing
earthdistance 1.1 calculate great-circle distances on the surface of the Earth
fuzzystrmatch 1.1 determine similarities and distance between strings
hstore 1.6 data type for storing sets of (key, value) pairs
intagg 1.1 integer aggregator and enumerator. (Obsolete)
intarray 1.2 functions, operators, and index support for 1-D arrays of integers
isn 1.2 data types for international product numbering standards
lo 1.1 large object maintenance
ltree 1.1 data type for hierarchical tree-like structures
pageinspect 1.7 inspect the contents of database pages at a low level
pg_buffercache 1.3 examine the shared buffer cache
pg_cron 1.3 Job scheduler for PostgreSQL
pg_freespacemap 1.2 examine the free space map (FSM)
pg_partman 4.5.0 Extension to manage partitioned tables by time or ID
pg_prewarm 1.2 prewarm relation data
pg_stat_statements 1.7 track execution statistics of all SQL statements executed
pg_trgm 1.4 text similarity measurement and index searching based on trigrams
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pgaudit 1.4 provides auditing functionality
pgcrypto 1.3 cryptographic functions
pglogical 2.3.2 Logical streaming replication
pgrowlocks 1.2 show row-level locking information
pgstattuple 1.5 show tuple-level statistics
plpgsql 1.0 PL/pgSQL procedural language
postgis 3.0.0 PostGIS geometry, geography
postgis_raster 3.0.0 PostGIS raster types and functions
postgis_sfcgal 3.0.0 PostGIS SFCGAL functions
postgis_tiger_geocoder 3.0.0 PostGIS tiger geocoder and reverse geocoder
postgis_topology 3.0.0 PostGIS topology spatial types and functions
postgres_fdw 1.0 foreign-data wrapper for remote PostgreSQL servers
sslinfo 1.2 information about SSL certificates
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent 1.1 text search dictionary that removes accents
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)

Postgres 11 extensions

The following extensions are available in Azure Database for PostgreSQL - Flexible Servers which have Postgres version 11.

Extension Extension version Description
address_standardizer 2.5.1 Used to parse an address into constituent elements.
address_standardizer_data_us 2.5.1 Address Standardizer US dataset example
amcheck 1.1 functions for verifying relation integrity
bloom 1.0 bloom access method - signature file based index
btree_gin 1.3 support for indexing common datatypes in GIN
btree_gist 1.5 support for indexing common datatypes in GiST
citext 1.5 data type for case-insensitive character strings
cube 1.4 data type for multidimensional cubes
dblink 1.2 connect to other PostgreSQL databases from within a database
dict_int 1.0 text search dictionary template for integers
dict_xsyn 1.0 text search dictionary template for extended synonym processing
earthdistance 1.1 calculate great-circle distances on the surface of the Earth
fuzzystrmatch 1.1 determine similarities and distance between strings
hstore 1.5 data type for storing sets of (key, value) pairs
intagg 1.1 integer aggregator and enumerator. (Obsolete)
intarray 1.2 functions, operators, and index support for 1-D arrays of integers
isn 1.2 data types for international product numbering standards
lo 1.1 large object maintenance
ltree 1.1 data type for hierarchical tree-like structures
pageinspect 1.7 inspect the contents of database pages at a low level
pg_buffercache 1.3 examine the shared buffer cache
pg_cron 1.3 Job scheduler for PostgreSQL
pg_freespacemap 1.2 examine the free space map (FSM)
pg_partman 4.5.0 Extension to manage partitioned tables by time or ID
pg_prewarm 1.2 prewarm relation data
pg_stat_statements 1.6 track execution statistics of all SQL statements executed
pg_trgm 1.4 text similarity measurement and index searching based on trigrams
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pgaudit 1.3.1 provides auditing functionality
pgcrypto 1.3 cryptographic functions
pglogical 2.3.2 Logical streaming replication
pgrowlocks 1.2 show row-level locking information
pgstattuple 1.5 show tuple-level statistics
plpgsql 1.0 PL/pgSQL procedural language
postgis 2.5.1 PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgal 2.5.1 PostGIS SFCGAL functions
postgis_tiger_geocoder 2.5.1 PostGIS tiger geocoder and reverse geocoder
postgis_topology 2.5.1 PostGIS topology spatial types and functions
postgres_fdw 1.0 foreign-data wrapper for remote PostgreSQL servers
sslinfo 1.2 information about SSL certificates
tablefunc 1.0 functions that manipulate whole tables, including crosstab
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent 1.1 text search dictionary that removes accents
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)

dblink and postgres_fdw allow you to connect from one PostgreSQL server to another, or to another database in the same server. Flexible server supports both incoming and outgoing connections to any PostgreSQL server. The sending server needs to allow outbound connections to the receiving server. Similarly, the receiving server needs to allow connections from the sending server.

We recommend deploying your servers with VNet integration if you plan to use these two extensions. By default VNet integration allows connections between servers in the VNET. You can also choose to use VNet network security groups to customize access.

pg_prewarm

The pg_prewarm extension loads relational data into cache. Prewarming your caches means that your queries have better response times on their first run after a restart. The auto-prewarm functionality is not currently available in Azure Database for PostgreSQL - Flexible Server.

pg_cron

pg_cron is a simple, cron-based job scheduler for PostgreSQL that runs inside the database as an extension. The pg_cron extension can be used to run scheduled maintenance tasks within a PostgreSQL database. For example, you can run periodic vacuum of a table or removing old data jobs.

pg_cron can run multiple jobs in parallel, but it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started as soon as the first run completes. This ensures that jobs run exactly as many times as scheduled and don’t run concurrently with themselves.

Some examples:

To delete old data on Saturday at 3:30am (GMT)

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

To run vacuum every day at 10:00am (GMT)

SELECT cron.schedule('0 10 * * *', 'VACUUM');

To unschedule all tasks from pg_cron

SELECT cron.unschedule(jobid) FROM cron.job;

pg_stat_statements

The pg_stat_statements extension is preloaded on every Azure Database for PostgreSQL flexible server to provide you a means of tracking execution statistics of SQL statements. The setting pg_stat_statements.track, which controls what statements are counted by the extension, defaults to top, meaning 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.

There is a tradeoff between the query execution information pg_stat_statements provides and the impact on server performance as it logs each SQL statement. If you are not actively using the pg_stat_statements extension, we recommend that you set pg_stat_statements.track to none. Note that some third party monitoring services may rely on pg_stat_statements to deliver query performance insights, so confirm whether this is the case for you or not.

Next steps

If you don't see an extension that you'd like to use, let us know. Vote for existing requests or create new feedback requests in our feedback forum.