Extensions
The PostgreSQL extensions that the console installs, how to add or drop an extension on a service, and how to see the extension versions.
Every database service has timescaledb and timescaledb_toolkit. You
cannot remove them.
The console offers an extension of the server packages (see
Known extensions) only when the server packages of every
PostgreSQL server of the service have it, at the versions that every server
has. The PostgreSQL contrib extensions are always available. pg_cron is in
the postgres database.
Choose the extensions of a new service
In the create wizard, the Extensions section is below Versions.
Open the extension list
Click Add extensions.
Find an extension
Type in the search field to find an extension by its name or description.
Select the extension
Select an extension. For an extension with versions, select the version (default: the newest).
Open the contrib extensions
The contrib extensions are in the closed group PostgreSQL contrib. Click it to open it. A contrib extension has the version of PostgreSQL.
When the server packages do not have timescaledb_toolkit, the summary
shows Not in the server packages, and the deploy skips it.
Add an extension to a service
You need the admin role to add or remove an extension, and the operator role to deploy.
Open the extensions
Open the service, then Configuration > Extensions.
Start the add
Click Add extension.
Select the extension
Select the extension and its version, then click Add extension.
Deploy the service
Click Deploy in the note above the table.
While an operation runs on the service, you cannot add, remove or drop an extension.
You can also run CREATE EXTENSION as postgres on the primary. The
console then shows the extension as Not in the list.
Extensions that need a restart
An extension with Restart Yes in Known extensions
needs a library in shared_preload_libraries. PostgreSQL loads it only at a
start.
- Add the extension to a running service and deploy. The deploy installs the package, but does not create the extension.
- Overview shows “PostgreSQL must restart to load …” Click Restart. On an HA service, the nodes restart one at a time, with one switchover. On a Standard service, the service stops for the restart.
- After the restart, the console creates the extension.
If PostgreSQL restarted outside the console, Overview shows Create now. Click it, or deploy.
Remove an extension
Open the menu of the row:
- Remove from the list: the service no longer asks for the extension. The extension, its objects and its data stay in the database.
- Drop from the database…: see below.
Drop an extension from the database
Open the extensions
Open the service, then Configuration > Extensions.
Start the drop
Open the menu of the row and click Drop from the database…. You cannot
drop timescaledb or timescaledb_toolkit.
Choose the dependent objects
The dialog shows the objects that depend on the extension, with the
estimated rows of each table. To drop them too, select Also drop these
objects (CASCADE). A column of the extension type goes with its data;
the table stays. An extension that requires the dropped one (for example
earthdistance for cube) goes too.
Confirm the drop
Type the name of the extension, then click Drop extension.
You need the admin role. For an extension with Restart Yes, the library stays until the next deploy: click Deploy in the note. Then Overview shows Restart pending: click Apply now.
Extension versions
The first deploy installs the newest version in the server packages. Later deploys, also on a new or rebuilt server, install the same version. A fork gets the extensions and versions of its source. To move to a newer version, see Upgrade a service.
Graph queries with Apache AGE
Apache AGE adds graphs with openCypher queries.
AGE needs ag_catalog first in the search_path of each session. Without
it, create_graph fails:
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('social');
SELECT * FROM cypher('social', $$
CREATE (:Person {name: 'Ada'})-[:KNOWS]->(:Person {name: 'Grace'})
$$) AS (v agtype);
To set it for each session, use ALTER ROLE ... SET search_path or ALTER DATABASE ... SET search_path. You cannot set it in the console.
Known extensions
Extension status shows only these extensions. The name
for CREATE EXTENSION is in parentheses when it is different.
| Install | Meaning |
|---|---|
| Every service | The deploy creates it on each service. |
| If packaged | The deploy skips it with a warning when the server packages do not have it. |
| Add extension | Add it with Add extension. The deploy installs and creates it. |
CREATE EXTENSION |
A contrib extension. Run CREATE EXTENSION in SQL. |
Restart Yes: adding or removing the extension restarts PostgreSQL (see Extensions that need a restart).
| Extension | Description | Install | Restart |
|---|---|---|---|
timescaledb |
Hypertables, compression and continuous aggregates for time series. | Every service | Yes |
timescaledb_toolkit |
Hyperfunctions for time-series analysis. Ignition needs it. | Every service, if packaged | No |
pg_stat_statements |
Execution statistics of each SQL statement. | CREATE EXTENSION |
No |
pgvector (vector) |
Vector data type and vector similarity search. | Add extension | No |
pg_cron |
Job scheduler that runs SQL statements on a cron schedule. | Add extension | Yes |
pg_repack |
Removes bloat from tables and indexes without a long lock. | Add extension | No |
postgis |
Spatial data types, indexes and functions. | Add extension | No |
age |
Graph queries with openCypher (Apache AGE). | Add extension | Yes |
pg_textsearch |
Full-text search with BM25 relevance ranking. | Add extension | Yes |
pg_trgm |
Text similarity with trigrams, and trigram indexes. | CREATE EXTENSION |
No |
hstore |
Data type for sets of key-value pairs. | CREATE EXTENSION |
No |
uuid-ossp |
Functions that generate UUIDs. | CREATE EXTENSION |
No |
pgcrypto |
Cryptographic functions. | CREATE EXTENSION |
No |
citext |
Case-insensitive text data type. | CREATE EXTENSION |
No |
ltree |
Data type for hierarchical tree labels. | CREATE EXTENSION |
No |
intarray |
Functions and operators for arrays of integers. | CREATE EXTENSION |
No |
earthdistance |
Great-circle distances on the surface of the Earth. | CREATE EXTENSION |
No |
cube |
Data type for multidimensional cubes. | CREATE EXTENSION |
No |
tablefunc |
Functions that return tables, for example crosstab. | CREATE EXTENSION |
No |
fuzzystrmatch |
Similarity and distance between strings. | CREATE EXTENSION |
No |
pgaudit |
Detailed session and object audit logs. | Add extension | Yes |
pg_prewarm |
Loads relation data into the buffer cache. | CREATE EXTENSION |
No |
bloom |
Index access method based on Bloom filters. | CREATE EXTENSION |
No |
pgstattuple |
Tuple-level statistics of tables and indexes. | CREATE EXTENSION |
No |
pg_buffercache |
Shows the contents of the shared buffer cache. | CREATE EXTENSION |
No |
pg_visibility |
Shows the visibility map of a table. | CREATE EXTENSION |
No |
seg |
Data type for line segments and floating-point intervals. | CREATE EXTENSION |
No |
isn |
Data types for international product numbers (ISBN, EAN, UPC). | CREATE EXTENSION |
No |
unaccent |
Text search dictionary that removes accents. | CREATE EXTENSION |
No |
Extension status
Configuration → Extensions compares the extensions of the service with the database. It shows the Installed version (in the database) and the Package version of each extension.
| Status | Meaning |
|---|---|
| Synced | In the list of the service and in the database. |
| Not installed | In the list, not in the database. The next deploy creates it. |
| Waiting for a restart | In the list. PostgreSQL must restart to load its library. |
| Not in the list | In the database, not in the list. For example, after CREATE EXTENSION. |
| Not in the server packages | A required extension that the server packages do not have. The deploy skips it. |
| Unknown | The console did not read the database, for example because the service is not running. |

