PostgreSQL extensions

You can enable extensions for PostgreSQL databases. Extensions allow you to add functionality to a database without modifying the source code of the DBMS itself.

The list of extensions available in Managed Databases can be found in the Extension descriptions table.

Some extensions require loading a shared library.

Some extensions depend on others — dependent extensions will not work without the main extension. You can see the list of dependent and main extensions in the Dependent extensions table.

You can add an extension in the control panel, via the Managed Databases API, and via Terraform.

Enable shared preload libraries for extensions

Some extensions require loading shared libraries. To load a library, when creating a cluster or changing settings in an existing cluster, specify its name in the setting shared_preload_libraries.

The PostgreSQL cluster supports the following libraries:

• pg_cron — for the extension pg_cron;

• pg_prewarm — for the extension pg_prewarm.

Add extension

Extensions are connected to each database separately. If you connect a dependent extension and the main extension is not connected yet, the main extension will be connected first automatically, followed by the dependent one.

1. In the Control Panel, on the top menu, click Products and select Managed Databases.
2. Open the tab Active.
3. Open the cluster page → Databases tab → database card.
4. In the Extensions block, click Add extension.
5. Select an extension. For a list of available extensions and their descriptions, see the Extension descriptions table.
6. Click Add.

Delete extension

A dependent extension can be removed separately. To remove a main extension, first remove the dependent extension. You can see the list of dependent and main extensions in the Dependent extensions table.

1. In the Control Panel, on the top menu, click Products and select Managed Databases.
2. Open the tab Active.
3. Open the cluster page → Databases tab → database card.
4. In the Extensions block, in the extension row, click .

Extension descriptions

address_standardizer	Used to parse postal addresses passed as a string
address_standardizer_data_us	Used to structure US and Canadian postal addresses for the address_standardizer extension
autoinc	Contains the autoinc() function. This function returns the next value of a sequence stored in an integer field. Unlike built-in sequence types, autoinc(): blocks attempts to insert a value from the query into the field; allows modifying the value in the field when updating a record
bloom	Adds access to database indexes based on Bloom filters, which require significantly less memory than hash tables but allow false positives
btree_gin	Contains example operator classes for GIN (Generalized Inverted Index), which is used for inverted search
btree_gist	Contains GiST (Generalized Search Tree) operator classes. Unlike B-tree indexes, GiST supports the <> (not equal) and < - > (distance) operators, although it does not enforce uniqueness of returned values
citext	Contains the citext data type, which is used for case-insensitive string operations
cube	Contains the cube data type, which is used for representing multidimensional cubes
dblink	Supports connecting to other PostgreSQL databases from the current session
dict_int	Contains an example of an add-on dictionary template for full-text search, which can be used to prevent excessive growth of unique word lists and increase search speed
dict_xsyn	Contains an example of an add-on Extended Synonym Dictionary template for full-text search: when searching for a word, all of its synonyms will be found
fuzzystrmatch	Contains functions used to determine the similarities and differences between strings
h3-pg	Provides an API for H3, an open-source system for indexing geographic objects in a hexagonal grid system (tiles). Allows dividing territory into uniform tiles for geospatial data analysis, such as building heatmaps, traffic, and mobility analysis. Can be used in conjunction with PostGIS for advanced geospatial data analysis. The extension is available for PostgreSQL versions 15 and higher
hstore	Contains the hstore data type, which allows storing key-value pairs within a single table field and working with them efficiently
intarray	Contains functions and operators for working with integer arrays that do not contain null (NULL) values
ip4r	Supports indexing IPv4 and IPv6 addresses to improve query performance
isn	Contains data types for international product numbering standards: EAN13, UPC, ISBN, ISMN, and ISSN. Number validation and generation are performed based on a specified list of prefixes
jsonb_plperl	Adds support for the json and jsonb data types to the PL/Perl procedural language. Allows direct use of JSON data when writing functions and triggers in Perl
jsquery	Allows using the JsQuery language to work with jsonb data. JsQuery provides efficient searching within nested objects and includes additional comparison operators with index support
lo	Contains the lo data type and the lo_manage() function, which are used to manage Binary Large Objects (BLOBs) in accordance with JDBC and ODBC driver specifications (the default PostgreSQL behavior does not meet them)
ltree	Contains the ltree data type for representing data labels stored in a tree-like hierarchical structure
moddatetime	Contains the moddatetime() function, which allows tracking the last modification time of a table row
mysql_fdw	Allows connecting directly from a PostgreSQL cluster to MySQL databases using Foreign Data Wrapper. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users. The extension is available for PostgreSQL versions 15 and higher
oracle_fdw	Allows connecting directly from a PostgreSQL cluster to Oracle databases using Foreign Data Wrapper. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner; it cannot be assigned to other users. The extension is available for PostgreSQL version 15 or higher
pg_buffercache	Allows monitoring the state of the shared buffer cache in real time. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users. The extension is available for PostgreSQL versions 15 and higher
pg_clickhouse	Allows connecting directly from a PostgreSQL cluster to ClickHouse databases using Foreign Data Wrapper. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner; it cannot be assigned to other users. The extension is available for PostgreSQL version 15 or higher
pg_cron	Allows you to run scheduled SQL queries inside the database. Requires loading the shared library pg_cron. The extension can be connected to only one database in a cluster. To connect the extension to another database, delete it from the current one first. Enabling the extension will restart the cluster. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner; it cannot be assigned to other users. The extension is available for PostgreSQL version 15 or higher
pg_prewarm	Allows you to load relation data into the operating system cache or the PostgreSQL buffer cache. Requires loading the shared library pg_prewarm. Enabling the extension will restart the cluster. The extension is available for PostgreSQL version 15 or higher
pg_repack	Allows you to reorganize tables and indexes without locks to reduce their size and fragmentation. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner; it cannot be assigned to other users
pg_stat_statements	Adds the ability to track planning and collect execution statistics for all SQL queries executed in the cluster. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner; it cannot be assigned to other users
pg_trgm	Contains tools for fast similarity search of strings based on trigram matching. The extension is available for PostgreSQL version 15 or higher
pg_uuidv7	Contains functions for generating UUID version 7. The extension is available for PostgreSQL version 15 or higher
pgcrypto	Provides a set of cryptographic functions for data protection. For more information about data encryption using the pgcrypto extension and usage examples, see the Data encryption
pgrowlocks	Contains the pgrowlocks() function, which returns information about row-level locks on a specified table
pgstattuple	Provides detailed disk space usage statistics and allows analyzing index bloat, the number of "dead" tuples, and free space. The extension is useful for performance diagnostics, index optimization, scheduling VACUUM operations, and using the extension pg_repack
pgTAP	Provides a set of tools for testing database schemas and SQL functions
pgvector	Allows you to work with vector data — store, index, and query it
plpgsql	Allows using the PL/pgSQL procedural language to create functions, procedures, and triggers. The extension is enabled by default and is automatically available in all databases created in the cluster. You do not need to enable it separately via the control panel
plv8	Allows using JavaScript to write stored procedures and triggers
postgis	Allows storing and processing geographic information system (GIS) objects in PostgreSQL databases. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users
postgis_raster	Allows working with raster images and analyzing geographic information system (GIS) objects in databases
postgres_fdw	Allows accessing data stored on foreign PostgreSQL servers (other clusters) directly from a PostgreSQL cluster using Foreign Data Wrapper. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users
prefix	Allows using prefixes and masks to optimize queries
rum	Adds an access method for working with RUM indexes
seg	Contains the seg data type for representing line segments or floating point intervals
tablefunc	Contains a set of functions that return tables (row sets)
tcn	Provides a trigger function for generating notifications about changes in tables to which the trigger is attached. The extension is available for PostgreSQL version 15 or higher
tsm_system_rows	Provides a sampling method by number of rows (SYSTEM_ROWS) that can be used in the TABLESAMPLE clause of the SELECT command. The extension is available for PostgreSQL version 15 or higher
tsm_system_time	Provides an execution time sampling method (SYSTEM_TIME) that can be used in the TABLESAMPLE clause of the SELECT command. The extension is available for PostgreSQL version 15 or higher
unaccent	Contains a dictionary for accent-insensitive text search
uuid-ossp	Contains functions to generate UUIDs using standard algorithms
xml2	Allows using XPath queries and the XSLT language

Dependent extensions

earthdistance	Contains a module for calculating great-circle distances between points on the Earth's surface. Works only together with the main extension cube
pg_stat_kcache	Adds the ability to collect statistics on read and write operations performed at the file system level. Works only together with the main extension pg_stat_statements. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users
pgrouting	Contains functions for geospatial routing in the PostGIS database. Works only together with the main extension postgis
postgis_tiger_geocoder	Contains functions for geocoding based on data in TIGER format. Works only together with the main extension postgis
postgis_topology	Contains data types and functions of the postgis extension for managing topological objects. Works only together with the main extension postgis. To use the extension, the dbaas_admin role is required. This role is automatically granted to the database owner and cannot be assigned to other users