All Products
Search
Document Center

AnalyticDB for PostgreSQL:Extensions

Last Updated:Feb 19, 2024

AnalyticDB for PostgreSQL is developed by Alibaba Cloud based on PostgreSQL and supports a variety of open source and self-developed extensions. You can use the extensions to perform hint-based SQL optimization, job scheduling, and cross-database queries.

Usage notes

For security reasons, you cannot manually install or upgrade extensions for AnalyticDB for PostgreSQL instances of V6.3.8.9 or later. To install or upgrade extensions, Submit a ticket.

For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor engine version.

Supported extensions

AnalyticDB for PostgreSQL supports the following extensions:

  • PostGIS: processes geographic data. For more information, see Use PostGIS.

  • MADlib: provides a machine learning function library.

  • fuzzystrmatch: performs fuzzy match of strings. For more information, see fuzzystrmatch.

  • oss_ext: reads data from Object Storage Service (OSS).

  • HyperLogLog: collects statistics. For more information, see Use HyperLogLog.

  • pgcrypto: supports cryptography functions that use encryption algorithms to ensure the security of data in tables or columns. The encryption algorithms include Message Digest Algorithm 5 (MD5), Secure Hash Algorithm 1 (SHA-1), SHA-224, SHA-256, SHA-384, SHA-512, Blowfish, Advanced Encryption Standard 128 (AES-128), AES-256, Raw Encryption, Pretty Good Privacy (PGP) symmetric keys, and PGP public keys. For more information, see pgcrypto.

  • intarray: supports integer array-related functions, operators, and indexes. For more information, see intarray.

  • RoaringBitmap: uses the RoaringBitmap compression algorithm for bitmap operations. For more information, see Use efficient roaring bitmaps.

  • postgres_fdw: supports data queries across databases. It is a native extension of PostgreSQL.

  • greenplum_fdw: supports high-speed data queries across instances and databases.

  • gp_paralell_retrieve_cursor: works with the greenplum_fdw extension to implement high-speed data queries across instances and databases. For more information, see Query data across databases and Query data across instances.

  • tablefunc: includes various functions that return tables. For more information, see tablefunc.

  • zhparser: supports Chinese word segmentation during full-text searches. For more information, see Use zhparser to perform Chinese word segmentation.

  • pg_hint_plan: uses hints to modify and optimize SQL statements to accelerate queries. For more information, see Use the hint feature.

  • uuid-ossp: stores universally unique identifiers (UUIDs). UUIDs are more unique than sequences in distributed systems. For more information, see Use the uuid-ossp extension.

  • pg_cron: configures scheduled jobs. For more information, see Use the pg_cron extension to configure scheduled jobs.

  • sr_plan: provides the SQL plan management feature. For more information, see SQL plan management.

  • FastANN: performs vector analysis. For more information, see Create a vector table.

  • orafce: provides compatibility with specific Oracle functions.

  • pg_trgm: provides functions and operators that you can use to determine the similarity of text and index operators that support fast search for similar strings. For more information, see pg_trgm.

  • pldbgapi: provides the PL/pgSQL features to simplify function development and debugging. For more information, see pldbgapi.

Create an extension

Execute the following statements to create an extension:

CREATE EXTENSION <extension name>;
CREATE SCHEMA <schema name>;
CREATE EXTENSION IF NOT EXISTS <extension name> WITH SCHEMA <schema name>;
Note

Before you create a MADlib extension, you must create a plpythonu extension.

CREATE EXTENSION plpythonu;
CREATE EXTENSION madlib;

Delete an extension

Execute the following statements to delete an extension.

Important

If an object depends on an extension that you want to delete, you must add the CASCADE keyword to delete the object.

DROP EXTENSION <extension name>;
DROP EXTENSION IF EXISTS <extension name> CASCADE;