PostgreSQL extensions in Azure Database for PostgreSQL
PostgreSQL provides the ability to extend the functionality of your database 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 as do built-in features. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.
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 from psql tool to load the packaged objects into your database.
Azure Database for PostgreSQL currently supports a subset of key extensions as listed below. Extensions beyond the ones listed are not supported; you cannot create your own extension with Azure Database for PostgreSQL service.
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
|chkpass||Provides a data type for auto-encrypted passwords.|
|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.|
|isn||Provides data types for international product numbering standards.|
|ltree||Provides a data type for hierarchical tree-like structures.|
|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.|
|intarray||Provides functions and operators for manipulating null-free arrays of integers.|
|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.|
|tablefunc||Provides functions that manipulate whole tables, including crosstab.|
|uuid-ossp||Generates universally unique identifiers (UUIDs).|
Full-text search extensions
|dict_int||Provides a text search dictionary template for integers.|
|unaccent||A text search dictionary that removes accents (diacritic signs) from lexemes.|
Index Types extensions
|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.|
|plpgsql||PL/pgSQL loadable procedural language.|
|pg_buffercache||Provides a means for examining what's happening in the shared buffer cache in real time.|
|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 below for a note on this extension).|
|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 below for a note on this extension).|
|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 below for a note on this extension).|
|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.|
|pgrouting||Extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.|
|TimescaleDB||A time-series SQL database that supports automated partitioning for faster ingest and queries. Provides time-oriented analytical functions, optimizations, and scales PostgreSQL for time-series workloads. TimescaleDB is developed by and a registered trademark of Timescale, Inc. (See below for a note on this extension).|
The pg_stat_statements extension is preloaded on every Azure Database for PostgreSQL server to provide you a means of tracking execution statistics of SQL statements.
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
all. This setting is configurable as a server parameter through the Azure portal or the Azure CLI.
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
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.
dblink and postgres_fdw
dblink and postgres_fdw allow you 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. When using these extensions to connect between Azure Database for PostgreSQL servers, this can be done by setting "Allow access to Azure services" to ON. This is also needed if you want to use the extensions to loop back to the same server. The "Allow access to Azure services" setting can be found in the Azure portal page for the Postgres server, under Connection Security. Turning "Allow access to Azure services" ON whitelists all Azure IPs.
Currently, outbound connections from Azure Database for PostgreSQL are not supported, except for connections to other Azure Database for PostgreSQL servers.
TimescaleDB is a time-series database that is packaged as an extension for PostgreSQL. TimescaleDB provides time-oriented analytical functions, optimizations, and scales Postgres for time-series workloads.
To install TimescaleDB, you need to include it in the server's shared preload libraries. A change to Postgres's shared preload libraries requires a server reboot to take effect.
TimescaleDB can be enabled on Azure Database for PostgreSQL versions 9.6 and 10
Using the Azure portal:
Select your Azure Database for PostgreSQL server.
On the sidebar, select Server Parameters.
Search for the
Copy and paste the following as the value for
Select Save to preserve your changes. You get a notification once the change is saved.
After the notification, restart the server to apply these changes. To learn how to restart a server, see Restart an Azure Database for PostgreSQL server.
You can now enable TimescaleDB in your Postgres database. Connect to the database and issue the following command:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
If you see an error, confirm that you restarted your server after saving shared_preload_libraries.
If you don't see an extension that you'd like to use, let us know. Vote for existing requests or create new feedback and requests in our Customer feedback forum.
Send feedback about: