Skip to main content

Extensions on Aiven for PostgreSQL®

PostgreSQL® extensions allow you to extend the functionality by adding more capabilities to your Aiven for PostgreSQL.

important

Some extensions:

  • Have dependencies and need to be created in a predetermined order.
  • Require resetting the client connection before they are fully available.

How it works​

List extensions​

To list the extensions and their details, such as extension version numbers, run the following command in your Aiven for PostgreSQL server:

SELECT *
FROM pg_available_extensions;
note

Superuser-only and untrusted extensions listed in pg_available_extensions cannot be installed with a few exceptions.

  • To list all superuser-only and untrusted extensions, run

    SELECT name, version
    FROM pg_available_extension_versions
    WHERE superuser AND NOT trusted

  • To list superuser-only and untrusted extensions available for installation, run

    SELECT *
    FROM pg_available_extension_versions
    WHERE name = ANY(string_to_array(current_setting('extwlist.extensions'), ','));

Install and manage extensions​

See Manage Aiven for PostgreSQL extensions for the instructions.

Supported extensions​

Auditing​

  • pgaudit provides session and object audit logging often required for compliance with government, financial, or ISO certifications.
  • tcn. Triggered change notifications.

Connectivity​

  • dblink. Connect to other PostgreSQL databases from within a database.
  • postgres_fdw. Foreign-data wrapper for remote PostgreSQL servers.

Data types​

  • citext. Data type for case-insensitive character strings.
  • cube. Data type for multidimensional cubes.
  • hll. Type for storing hyperloglog data.
  • hstore. Data type for storing sets of (key, value) pairs.
  • ip4r. IPv4 and IPv6 address range data types with indexing support.
  • isn. Data types for international product numbering standards.
  • ltree. Data type for hierarchical tree-like structures.
  • seg. Data type for representing line segments or floating-point intervals.
  • timescaledb. Enables scalable inserts and complex queries for time-series data.
  • unit. SI units extension.
  • uuid-ossp. Generate universally unique identifiers (UUIDs).

Geographical features​

  • address_standardizer. Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
  • address_standardizer_data_us. Address standardizer US dataset example.
  • earthdistance. Calculate great-circle distances on the surface of the Earth.
  • h3. PostgreSQL bindings for H3, a hierarchical hexagonal geospatial indexing system.
  • h3_postgis. H3 PostGIS integration.
  • pgrouting. Extends the PostGIS/PostgreSQL geospatial database to provide geospatial routing and other network analysis functionality.
  • postgis. PostGIS geometry and geography spatial types and functions.
  • postgis_legacy. Legacy functions for PostGIS.
  • postgis_raster. PostGIS raster types and functions.
  • postgis_sfcgal. PostGIS SFCGAL functions.
  • postgis_tiger_geocoder. PostGIS tiger geocoder and reverse geocoder.
  • postgis_topology. PostGIS topology spatial types and functions.

Machine learning (ML) and artificial intelligence (AI)​

  • pgvector designed for vector similarity search for PostgreSQL.

  • pgvectorscale complements pgvector as a vector data extension for PostgreSQL. PG16 and newer

    important

    Supported pgvectorscale versions:

    • PG16: pgvectorscale-0.6.0
    • PG17: pgvectorscale-0.6.0

    Read about all pgvectorscale releases.

Procedural language​

  • plperl. PL/Perl procedural language.
  • plpgsql. PL/pgSQL procedural language.

Search and text handling​

  • bloom. Bloom access method - signature file based index.
  • btree_gin. Support for indexing common data types in GIN.
  • btree_gist. Support for indexing common data types in GiST.
  • dict_int. Text search dictionary template for integers.
  • fuzzystrmatch. Determine similarities and distance between strings.
  • pg_similarity. Support similarity queries.
  • pg_trgm. Text similarity measurement and index searching based on trigrams.
  • pgcrypto. Cryptographic functions.
  • rum. RUM index access method.
  • unaccent. Text search dictionary that removes accents.

Utilities​

  • aiven_extras. This extension is meant for use in environments where you want non-superusers to be able to use certain database features.

  • bool_plperl. Transform between bool and plperl.

  • intagg. Integer aggregator and enumerator (obsolete).

  • intarray. Functions, operators, and index support for 1-D arrays of integers.

  • jsonb_plperl. Transform between jsonb and plperl.

  • lo. Large Object maintenance.

  • pageinspect. Inspect the contents of database pages at a low level.

  • pg_buffercache. Examine the shared buffer cache.

  • pg_cron. Job scheduler for PostgreSQL.

  • pg_partman. Extension to manage partitioned tables by time or ID.

  • pg_prewarm. Prewarm relation data.

  • pg_repack. Reorganize tables in PostgreSQL databases with minimal locks.

  • pg_stat_statements. Track planning and execution statistics of all SQL statements executed.

  • pgrowlocks. Show row-level locking information.

  • pgstattuple. Show tuple-level statistics.

  • postgresql_anonymizer. Mask or replace personally identifiable information (PII) or commercially sensitive data from a PostgreSQL database. PG15 and newer

    The user who installs this extension (preferably avnadmin) becomes its owner.

    • Only the avnadmin user can update extension settings, such as anon.salt, even if they are not the extension owner.
    • Only the extension owner can read internal tables and settings, even if they are not the avnadmin user. Use the anon.current_setting(SETTING_NAME) function to access extension settings.

  • sslinfo. Information about SSL certificates.

  • tablefunc. Functions that manipulate whole tables, including crosstab.

  • tsm_system_rows. TABLESAMPLE method which accepts number of rows as a limit.

  • tsm_system_time. TABLESAMPLE method which accepts time in milliseconds as a limit.