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.|
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.
Using 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.
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.
We'd love to hear your thoughts. Choose the type you'd like to provide:
Our feedback system is built on GitHub Issues. Read more on our blog.