Extensions
Extensions allow for expanded functionality within Postgres, without requiring a new version of Postgres to be released. Extensions can enable new functionality including datatypes and functions.
You can see a list of all available extensions by querying your database:
SELECT * FROM pg_available_extensions
You can see all extensions that are already enabled by executing:
SELECT * FROM pg_extension;
Extensions are enabled by the postgres user by running:
CREATE EXTENSION extensionname;
Procedural languages
While also a category of extension, procedural languages allow you to write custom functions to be executed within your database. Each of these has a number of additional details and functionality. We currently support:
Current catalog of extensions
| Extension | Type of extension | Summary | Command to create |
|---|---|---|---|
| Address Standardizer | Functions | Used to parse an address into constituent elements | CREATE EXTENSION address_standardizer; |
| Address Standardizer (US) | Functions | Data for standardizing US addresses | CREATE EXTENSION address_standardizer_data_us; |
| Adminpack | Functions | Administrative functions for Postgres | CREATE EXTENSION adminpack; |
| Amcheck | Functions | Functions for verifying relation integrity | CREATE EXTENSION amcheck; |
| Anonymizer | Functions | Data anonymization tools | CREATE EXTENSION anon CASCADE; SELECT anon.init(); |
| Audit | Functions | Audit user actions | CREATE EXTENSION pgaudit; |
| Auto explain | Logging | Automatically log execution plans of slow statements | See auto_explain |
| Auto Increment | Functions | Provides function for storing the next value of a sequence in an integer field | CREATE EXTENSION autoinc; |
| Bloom | Datatype | Provides a bloom filter index type | CREATE EXTENSION bloom; |
| Btree GIN | Index types | Support for indexing common datatypes in GIN | CREATE EXTENSION btree_gin; |
| Btree GIST | Index types | Support for indexing common datatypes in GiST | CREATE EXTENSION btree_gist; |
| Buffer Cache | Views | Examine the shared buffer cache | CREATE EXTENSION pg_buffercache; |
| Case insensitive text | Datatype | Case insensitive text datatype | CREATE EXTENSION citext; |
| Citus | Functions | Sharding, see Citus for more | CREATE EXTENSION citus; |
| Citus Columnar | Functions | Columnar compress, see Citus for more | CREATE EXTENSION citus_columnar; |
| ClickHouse FDW | Foreign Data Wrapper | Foreign Data Wrapper for ClickHouse column oriented database | CREATE EXTENSION clickhouse_fdw; |
| Cron | Functions | Create scheduled tasks | CREATE EXTENSION pg_cron; |
| Crypto | Functions | Functions for encrypting data inside columns | CREATE EXTENSION pgcrypto; |
| Cube | Datatype | Datatype for multi-dimensional cubes | CREATE EXTENSION cube; |
| DDL Extractor | Functions | DDL eXtractor functions | CREATE EXTENSION ddlx; |
| Dblink | Functions | Connect to other PostgreSQL databases from within a database | CREATE EXTENSION dblink; |
| dict-int | Dictionaries | Full text search dictionary template for integers | CREATE EXTENSION dict_int; |
| dict-xsyn | Dictionaries | Full text search dictionary template for extended synonym processing | CREATE EXTENSION dict_xsyn; |
| Earth Distance | Functions | Functions that assist with computing the distance between points. | CREATE EXTENSION earthdistance; |
| Free Space Map | Functions | Examine the free space map (FSM) | CREATE EXTENSION pg_freespacemap; |
| Fuzzy String Match | Functions | Functions for comparing similarity between strings | CREATE EXTENSION fuzzystrmatch; |
| H3 | Functions | H3 bindings for Postgres | CREATE EXTENSION h3; |
| Hint plan | Functions | Adjust PostgreSQL execution plans using "hints" in SQL comments (more info) | CREATE EXTENSION pg_hint_plan; |
| Hstore | Datatype | Key value datatype | CREATE EXTENSION hstore; |
| Hstore (PL/Perl) | Datatype | Transform between Hstore and PL/Perl (trusted) | CREATE EXTENSION hstore_plperl; |
| Hstore (PL/PerlU) | Datatype | Transform between Hstore and PL/PerlU (untrusted) | CREATE EXTENSION hstore_plperlu; |
| Hstore (PL/Python3U) | Datatype | Transform between Hstore and PL/Python3U (untrusted) | CREATE EXTENSION hstore_plpython3u; |
| HTTP Client | Functions | HTTP client for PostgreSQL, allows web page retrieval inside the database. | CREATE EXTENSION http; |
| Hypopg | Functions | Hypothetical indexes | CREATE EXTENSION hypopg; |
| Incremental | Functions | Incremental batch processing | CREATE EXTENSION pg_incremental; |
| Insert Username | Functions | Will place the current Postgres username in a text field | CREATE EXTENSION insert_username; |
| Integer Aggregator | Functions | Integer aggregator and enumerator | CREATE EXTENSION intagg; |
| Integer Array | Functions | Sorting and manipulation of integer arrays | CREATE EXTENSION intarray; |
| ISN | Datatype | Datatype for product numbering (including UPC, ISBN, ISSN) | CREATE EXTENSION isn; |
| IVM | Functions | Incremental View Maintenance | CREATE EXTENSION pg_ivm; |
| JSONB (PL/Perl) | Datatype | Transform between JSONB and PL/Perl (trusted) | CREATE EXTENSION jsonb_plperl; |
| JSONB (PL/PerlU) | Datatype | Transform between JSONB and PL/PerlU (untrusted) | CREATE EXTENSION jsonb_plperlu; |
| JSONB (PL/Python3U) | Datatype | Transform between JSONB and PL/Python3U (untrusted) | CREATE EXTENSION jsonb_plpython3u; |
| Large Object | Datatype | Specialized large object datatype | CREATE EXTENSION lo; |
| Label Tree | Datatype | Datatype for tree-like structures | CREATE EXTENSION ltree; |
| Label Tree (PL/Python3U) | Datatype | Transform between Ltree and PL/Python3U (untrusted) | CREATE EXTENSION ltree_plpython3u; |
| Logical | Functions | Helper functions for PostgreSQL Logical Replication | CREATE EXTENSION pglogical; |
| Modification Time | Functions | Will place the current timestamp into a timestamp field | CREATE EXTENSION moddatetime; |
| MongoDB FDW | Foreign Data Wrapper | Foreign data wrapper for MongoDB access | CREATE EXTENSION mongo_fdw; |
| MySQL FDW | Foreign Data Wrapper | Foreign Data Wrapper for MySQL | CREATE EXTENSION mysql_fdw; |
| OGR FDW | Foreign Data Wrapper | foreign-data wrapper for GIS data access | CREATE EXTENSION ogr_fdw; |
| Orafce | Functions | Emulate Oracle functions | CREATE EXTENSION orafce; |
| Page Inspect | Functions | Inspect the contents of database pages at a low level | CREATE EXTENSION pageinspect; |
| Row Locking | Functions | Show row-level locking information | CREATE EXTENSION pgrowlocks; |
| Partman | Functions | Create and manage both time-based and serial-based table partition sets | CREATE EXTENSION pg_partman; |
| Parquet | Functions | Read and write Parquet files in S3 on your cluster (PG16+) | CREATE EXTENSION pg_parquet; |
| Podman | Functions | Run containers inside the database | See container apps |
| PostGIS | Geospatial utilities | PostGIS geometry, geography, and raster spatial types and functions | See PostGIS |
| PostGIS Raster | Geospatial utilities | PostGIS raster types and functions | CREATE EXTENSION postgis_raster; |
| PostGIS SFCGAL | Geospatial utilities | PostGIS SFCGAL functions | CREATE EXTENSION postgis_sfcgal; |
| PostGIS Tiger Geocoder | Geospatial utilities | PostGIS tiger geocoder and reverse geocoder | CREATE EXTENSION postgis_tiger_geocoder; |
| PostGIS Topology | Geospatial utilities | PostGIS topology spatial types and functions | CREATE EXTENSION postgis_topology; |
| Postgres FDW | Foreign Data Wrapper | Foreign data wrapper for connecting to other Postgres databases | CREATE EXTENSION postgres_fdw; |
| Prewarm | Functions | Utilities to prewarm your cache, helpful for standby failover | CREATE EXTENSION pg_prewarm; |
| Proctab | Functions | Access operating system process tables from PostgreSQL | CREATE EXTENSION pg_proctab; |
| Refint | Functions | Functions for referential integrity | CREATE EXTENSION refint; |
| Repack | Functions | Remove bloat from tables and indexes (See also pg_squeeze) | CREATE EXTENSION pg_repack; |
| Routing | Geospatial utilities | Routing functionality | CREATE EXTENSION pgrouting; |
| Similarity | Functions | Support for similarity queries | CREATE EXTENSION pg_similarity; |
| Surgery | Functions | Corrective actions on corruption or damaged data | CREATE EXTENSION pg_surgery; |
| Seg | Datatype | Datatype for representing floating point intervals or segments | CREATE EXTENSION seg; |
| SSL Info | Functions | Ability to query SSL information based on who is | CREATE EXTENSION sslinfo; |
| Stat statements | Views | Track planning and execution statistics of all SQL statements executed | CREATE EXTENSION pg_stat_statements; |
| Stat Tuple | Functions | Show tuple-level statistics | CREATE EXTENSION pgstattuple; |
| Squeeze | Functions | Remove bloat from tables and indexes. A modern alternative to pg_repack. See pg_squeeze docs. | CREATE EXTENSION pg_squeeze; |
| Table functions | Functions | Functions for cubing and rollups of tables | CREATE EXTENSION tablefunc; |
| Table sampling (system rows) | Functions | Functions to provide sampling of system tables | CREATE EXTENSION tsm_system_rows; |
| Table sampling (system time) | Functions | Functions to provide sampling of system time | CREATE EXTENSION tsm_system_time; |
| TimescaleDB (Apache 2 Edition) | Functions | Provides automatic partitioning of time-series data, events, and analytics | CREATE EXTENSION timescaledb; |
| Trigger change notifications | Functions | Functions for listening to changes on tables | CREATE EXTENSION tcn; |
| Trigram | Functions | Matching and similarity of strings | CREATE EXTENSION pg_trgm; |
| Unaccent | Dictionaries | Text search dictionary that removes accents | CREATE EXTENSION unaccent; |
| Visibility | Functions | Examine the visibility map (VM) and page-level visibility info | CREATE EXTENSION pg_visibility; |
| Vector | Functions | Vector (pgvector) data type and ivfflat access method | CREATE EXTENSION vector; |
| ULID | Functions | Generate universally unique lexicographically sortable identifiers (ULIDs) | CREATE EXTENSION pgx_ulid; |
| uuid-ossp | Functions | Generate universally unique identifiers (UUIDs) | CREATE EXTENSION uuid-ossp; |
| uuidv7 | Functions | Generate version 7 universally unique identifiers (UUIDs) | CREATE EXTENSION pg_uuidv7; |
| WAL inspect | Functions | Inspect contents of WAL | CREATE EXTENSION pg_walinspect; |
| xml2 | Functions | XPath querying and XSLT | CREATE EXTENSION xml2; |
Shared preloaded libraries
Some extensions require altering Postgres' shared preloaded libraries. Find your current settings with:
postgres# SHOW shared_preload_libraries;
shared_preload_libraries
------------------------------------------------------------------------
pgaudit,pgpodman,anon,pg_squeeze,pg_parquet,pg_cron,pg_stat_statements
(1 row)
With the postgres user you can add additional extensions. In the following example we are adding http:
ALTER SYSTEM SET shared_preload_libraries=pgaudit,pgpodman,anon,pg_squeeze,pg_parquet,pg_cron,pg_stat_statements,http;
Postgres must be restarted for a change to shared_preload_libraries to take effect. You can restart Postgres from the cluster settings page in the dashboard.
Info
Be careful when making changes to shared_preload_libraries. Errors can prevent Postgres from starting up. Reach out to Support if you need assistance.
Need something you don't see?
If you find an unsupported procedural language or extension please reach out to Crunchy Bridge support as we're actively expanding the list of what is supported.
Additional extension documentation
Pages in this section: