PostgreSQL extensions

Last update: October 08 2024

PostgreSQL extensions

To databases PostgreSQL extensions can be plugged in. Extensions allow you to add functionality to the database without changing the source code of the DBMS itself.

The list of extensions available in cloud databases can be viewed in the table below Extension Description.

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

You can add the extension in the control panel, via Cloud Database API and through Terraform.

Add extension⁠

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

1. In control panel go to Cloud platform → Databases.
2. Open the cluster page → tab Databases → database card.
3. In the block Extensions click Add extension.
4. Select an extension. The list of available extensions and their descriptions can be viewed in the table below Extension Description.
5. Click Add.

Delete extension⁠

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

1. In control panel go to Cloud platform → Databases.
2. Open the cluster page → tab Databases → database card.
3. In the block Extensionsin the extension bar, click .

Extension Description⁠

address_standardizer	Used to structure postal addresses passed as a string
address_standardizer_data_us	Used to structure U.S. and Canadian postal addresses for expansion address_standardizer
autoinc	Contains the function autoinc(). This function returns the next value for the sequence, which is stored in an integer type field. Unlike built-in types that control sequences, autoinc(): blocks attempts to insert a value from the query into the field; allows you to change the value in the field when updating the record
bloom	Adds access to database indexes based on Bloom filters, which require much less memory than hash tables but allow false positives
btree_gin	Contains sample classes of the GIN (Generalized Inverted Index) operator, which is used for reverse lookups
btree_gist	Contains classes of the GiST (Generalized Search Tree) operator. Unlike B-tree indexes, GiST supports <> (not equal) and < — > (distance) operators, although it does not provide uniqueness of returned values
citext	Contains the data type citextwhich is used for register-independent operation with strings
cube	Contains the data type cubewhich is used to represent multidimensional cubes
dict_int	Contains an example of an additional dictionary template for full-text search, which can be used to avoid the list of unique words from growing and increase the search speed
dict_xsyn	Contains an example of an Extended Synonym Dictionary template for full-text search: when you search for a word, all its synonyms will be found
fuzzystrmatch	Contains functions that are used to determine the similarities and differences between strings
hstore	Contains type hstorewhich allows you to store key/value pairs in a single table field and work with them efficiently
intarray	Contains functions and operators for working with arrays of integers that do not contain empty (NULL) values
ip4r	Supports IPv4 and IPv6 address indexing to improve query performance
isn't	Contains data types for international product numbering standards EAN13, UPC, ISBN, ISMN and ISSN. Numbers are checked and generated by the specified list of prefixes
jsquery	Adds support for the JsQuery language, which is used to work with data of type jsonb. JsQuery allows you to organize efficient search in nested objects and contains additional comparison operators with index support
lo	Contains the data type lo and function lo_manage()which are used to manage Binary Large Object (BLOB) in accordance with the requirements of JDBC and ODBC driver specifications (the standard PostgreSQL behavior does not comply with them).
ltree	Contains the data type ltree to represent data labels stored in a tree hierarchical structure
moddatetime	Contains the function moddatetime()which allows you to track the time of the last modification of a table row
pg_partman	Adds advanced table partitioning capabilities, including time-based or sequence-based table partitioning
pg_stat_statements	Adds the ability to track scheduling and collect execution statistics for all SQL queries running in the cluster. To use the extension, the following role is required dbaas_admin — this role is automatically assigned to the database owner, it cannot be assigned to other users
pgtrgm	Contains tools to quickly find similar strings based on trigram matching
pgcrypto	Provides a set of cryptographic functions to protect data. Learn more about data encryption with the pgcrypto extension and use cases in the manual Data encryption
pgrowlocks	Contains the function pgrowlocks()which returns information about row locking in the specified table
pgTAP	Provides a set of tools for testing database schema and SQL functions
plv8	Allows you to use JavaScript to write stored procedures and triggers
postgis	Allows storing and processing geographic information system (GIS) objects in PostgreSQL databases. To use the extension, you need the role dbaas_admin — this role is automatically assigned to the database owner, it cannot be assigned to other users
postgis_raster	Allows you to work with raster images and analyze geographic information system (GIS) objects in databases
postgis_fdw	Adds support for Foreign Data Wrapper to access external PostgreSQL servers. To use the extension, you need the role dbaas_admin — this role is automatically assigned to the database owner, it cannot be assigned to other users
prefix	Adds support for prefixes and masks to optimize queries
rum	Adds an access method for working with RUM indexes
seg	Contains the data type seg to represent line segments or floating point intervals
tablefunc	Contains a set of functions that return tables (sets of rows)
unaccent	Contains a dictionary for searching text without diacritical marks
uuid-ossp	Contains functions for generating UUIDs using standard algorithms
xml2	Adds support for XPath queries and XSLT language

Dependent extensions⁠

earthdistance	Contains a module for calculating 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. A role is required to use the extension dbaas_admin — this role is automatically assigned to the database owner, it cannot be assigned to other users
pgrouting	Contains functions for geospatial database routing PostGIS. Works only together with the main extension postgis
postgis_tiger_geocoder	Contains functions for geocoding based on data in format TIGER. Works only together with the main extension postgis
postgis_topology	Contains data types and extension functions postgis for managing topological objects. Works only together with the main extension postgis. A role is required to use the extension dbaas_admin — this role is automatically assigned to the database owner, it cannot be assigned to other users