vDB PostgreSQL - Supported Extensions

PostgreSQL is renowned for its flexibility, allowing you to extend its core functionalities with various extensions.

By default, when you create a database with vDB PostgreSQL, several extensions are already enabled (listed below). Additionally, you can manually enable other extensions as needed.

vDB PostgreSQL supports two deployment types: Standalone (Single Node) and Cluster (1 Writer + N Readers). The list of supported extensions may differ depending on the deployment type.

---

1. Extensions Enabled by Default

For PostgreSQL, newly created databases will have extensions enabled from the template1 database.

After creating a database, you can check the enabled extensions by running:

\dx

or

select * from pg_extension;

Below is the list of extensions enabled by default:

No	Name	Description	Standalone	Cluster
1	plpgsql	PL/pgSQL procedural language	✅	✅
2	btree_gin	Support for indexing common datatypes in GIN	✅	✅
3	btree_gist	Support for indexing common datatypes in GiST	✅	✅
4	citext	Data type for case-insensitive character strings	✅	✅
5	cube	Data type for multidimensional cubes	✅	✅
6	dict_int	Text search dictionary template for integers	✅	✅
7	dict_xsyn	Text search dictionary template for extended synonym processing	✅	✅
8	hstore	Data type for storing sets of (key, value) pairs	✅	✅
9	isn	Data types for international product numbering standards	✅	✅
10	lo	Large Object maintenance	✅	✅
11	ltree	Data type for hierarchical tree-like structures	✅	✅
12	pg_trgm	Text similarity measurement and index searching based on trigrams	✅	✅
13	postgis	PostGIS geometry, geography, and raster spatial types and functions	✅	✅
14	postgres_fdw	Foreign-data wrapper for remote PostgreSQL servers	✅	✅
15	unaccent	Text search dictionary that removes accents	✅	✅
16	vector	Vector data type and ivfflat and hnsw access methods	✅	✅
17	chkpass	Data type for auto-encrypted passwords	✅	❌

Note: If you want to create a completely blank database, use template0.

CREATE DATABASE dbname TEMPLATE template0;

---

2. Extensions You Can Enable

You can manually enable an extension by running:

CREATE EXTENSION <extension_name>;

2.1. Extensions available on both Standalone & Cluster

Name	Description
earthdistance	Calculate great-circle distances on the surface of the Earth
fuzzystrmatch	Determine similarities and distance between strings
pg_stat_statements	Track execution statistics of all SQL statements executed
pgcrypto	Cryptographic functions
postgis_topology	PostGIS topology spatial types and functions
tablefunc	Functions that manipulate whole tables, including crosstab
uuid-ossp	Generate universally unique identifiers (UUIDs)

2.2. Extensions available on Cluster only

Name	Version	Description
pg_cron	1.6	Job scheduler for PostgreSQL
timescaledb	2.24.0	Enables scalable inserts and complex queries for time-series data
postgis_raster	3.6.1	PostGIS raster types and functions
pgaudit	17.1	Auditing functionality
pg_partman	5.4.0	Manage partitioned tables (time/ID-based)
pg_repack	1.5.3	Reorganize tables with minimal locks

2.3. Extensions available on Standalone only

Name	Description
address_standardizer	Used to parse an address into constituent elements for geocoding
address_standardizer_data_us	Address Standardizer US dataset example
amcheck	Functions for verifying relation integrity
autoinc	Functions for autoincrementing fields
insert_username	Functions for tracking who changed a table
intagg	Integer aggregator and enumerator (obsolete)
intarray	Functions, operators, and index support for 1-D arrays of integers
moddatetime	Functions for tracking last modification time
pageinspect	Inspect the contents of database pages at a low level
pg_buffercache	Examine the shared buffer cache
pg_freespacemap	Examine the free space map (FSM)
pg_prewarm	Prewarm relation data
pg_visibility	Examine the visibility map (VM) and page-level visibility info
pgrouting	pgRouting Extension
pgrowlocks	Show row-level locking information
pgstattuple	Show tuple-level statistics
postgis_sfcgal	PostGIS SFCGAL functions
postgis_tiger_geocoder	PostGIS tiger geocoder and reverse geocoder
refint	Functions for implementing referential integrity (obsolete)
sslinfo	Information about SSL certificates
tcn	Triggered change notifications
timetravel	Functions for implementing time travel
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

---

You can check the list of supported extensions on your vDB instance by running:

SELECT * FROM pg_available_extensions;

If an extension you need is not supported, please contact GreenNode Cloud Support for assistance.

---

3. Notes

• The vector extension is only available for vDB instances created after 01/08/2024.

• To use it on pre-existing vDB instances, please contact GreenNode Cloud Support to enable it.

• The extension list for Cluster may be updated in future releases.