# 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.

{% hint style="info" %}
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.
{% endhint %}

***

## 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:

```sql
\dx
```

or

```sql
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**.

```sql
CREATE DATABASE dbname TEMPLATE template0;
```

***

## 2. Extensions You Can Enable

You can manually enable an extension by running:

```sql
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:

```sql
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.