PostgreSQL extensions reference
Reviewed on 04 September 2024
The following pg_extensions are available with Scaleway Managed Databases for PostgreSQL:
| Extension | Description |
|---|---|
| address_standardizer | Parses addresses into their constituent elements, typically used in the geocoding normalization process. |
| address_standardizer_data_us | Provides a dataset example for the US Address Standardizer extension. |
| bloom | Implements a Bloom filter-based index, ideal for situations where space efficiency is crucial. |
| btree_gin | Adds support for indexing common data types using the GIN (Generalized Inverted Index) method. |
| btree_gist | Adds support for indexing common data types using the GiST (Generalized Search Tree) method. |
| citext | Provides a case-insensitive character string data type for easier text comparison. |
| cube | Defines a data type for representing multidimensional cubes, useful in mathematical and geometrical computations. |
| dblink | Enables connections to other PostgreSQL databases from within a database session, facilitating cross-database queries. |
| dict_int | Offers a text search dictionary template optimized for integer values. |
| dict_xsyn | Provides a text search dictionary template for advanced synonym processing, improving search accuracy. |
| earthdistance | Calculates great-circle distances between points on Earth, useful in geographical applications. |
| fuzzystrmatch | Determines similarities and differences between strings, helpful for tasks like data deduplication. |
| hstore | Stores sets of key-value pairs in a single value, enabling a flexible schema for semi-structured data. |
| intagg | Aggregates and enumerates integer values, though it is considered obsolete. |
| intarray | Provides functions, operators, and indexing support for one-dimensional arrays of integers, enhancing array processing. |
| isn | Supports data types for international product numbering standards, such as ISBN and EAN. |
| ltree | Defines a data type for representing tree-like structures in a hierarchical manner, facilitating complex data modeling. |
| ogr_fdw | Allows PostgreSQL to access vector data from various spatial formats using the GDAL/OGR library. |
| pg_buffercache | Inspects the shared buffer cache to monitor and analyze the usage of cached data. |
| pg_cron | Schedules jobs within PostgreSQL using a cron-like syntax, automating routine database tasks. |
| pg_freespacemap | Examines the free space map (FSM) to monitor and manage available storage within the database. |
| pg_prewarm | Preloads relation data into memory to reduce initial disk I/O latency and improve performance. |
| pg_stat_statements | Tracks planning and execution statistics for all SQL statements, aiding in performance tuning. |
| pg_trgm | Measures text similarity and provides index searching based on trigrams, enhancing text search capabilities. |
| pg_visibility | Inspects the visibility map (VM) and provides page-level visibility information, useful for debugging. |
| pgaudit | Enables detailed logging of session and object-level activities for audit purposes, using PostgreSQL’s standard logging facility. |
| pgcrypto | Provides cryptographic functions for data encryption, decryption, and hashing, essential for secure data storage. |
| pgrowlocks | Displays information about row-level locking, helping in the diagnosis of concurrency issues. |
| pgstattuple | Reports detailed tuple-level statistics, useful for assessing table bloat and optimizing storage. |
| pgvector | Supports vector similarity search, often used for storing and searching embeddings in machine learning applications. |
| pgrouting | Extends PostgreSQL and PostGIS with geospatial routing capabilities, enabling the calculation of shortest paths and other routing operations. |
| plpgsql | Enables the use of PL/pgSQL, a procedural language for PostgreSQL that allows complex control structures and query manipulation. |
| postgis | Adds support for geographic objects, allowing location queries to be run in SQL, and is the foundation of geographic information system (GIS) functionality in PostgreSQL. |
| postgis_raster | Implements raster data support in PostGIS, enabling analysis and storage of raster images alongside vector data. |
| postgis_sfcgal | Adds advanced 3D geometry functions to PostGIS, leveraging the SFCGAL library for complex spatial operations. |
| postgis_tiger_geocoder | Provides geocoding and reverse geocoding capabilities based on the US Census TIGER data within PostGIS. |
| postgis_topology | Supports topological data models in PostGIS, allowing for advanced spatial relationships and analyses. |
| postgres_fdw | Facilitates the connection to and querying of external PostgreSQL servers using a foreign-data wrapper. |
| sslinfo | Provides information about SSL certificates used by the PostgreSQL server, enhancing security management. |
| tablefunc | Offers functions for manipulating entire tables, including crosstab operations for pivot tables. |
| timescaledb | Enables efficient handling and analysis of time-series data, extending PostgreSQL with specialized time-series functions. |
| tsm_system_rows | Implements a TABLESAMPLE method that limits the number of rows returned, useful for sampling large datasets. |
| tsm_system_time | Implements a TABLESAMPLE method that limits rows based on a time duration, useful for time-based sampling. |
| unaccent | Provides a text search dictionary that removes accents from characters, improving text search accuracy. |
| uuid-ossp | Generates universally unique identifiers (UUIDs), essential for ensuring data uniqueness across distributed systems. |
Extension best practices
- When you use functions installed by postgres extensions, the best practice is to specify the namespace of the function if you do not modify the
search_path. When functions come from extensions or are used in postgresql objects, it is necessary to specify the namespace of these functions independently ofsearch_pathmanagement to avoid errors when restoring backups. - Some extensions, such as
pg_audit, must be configured from the advanced settings tab in the console. Refer to the Setting up and using pgaudit documentation page for more information.
Extension usage example
For example, if the unaccent extension is installed in a public schema and the search_path is not set to public, you should run the following function instead of running SELECT unaccent('a'):
Likewise, when the view is restored from a database backup, you can use the following function to avoid an error: