PostgreSQL extensions

PostgreSQL extensions add new data types, functions and index types to a database. On our PostgreSQL servers the extensions below are installed and marked as trusted, so you can enable them in your database yourself — no request to support is needed.

Enable an extension

Extensions are enabled per database with a single SQL command, run under the main user of the database (the owner, created together with the database):

CREATE EXTENSION IF NOT EXISTS vector;

The easiest place to run it is Run SQL in the left menu of the database in the Control Panel, or WebPGSQL. It works from psql, pgAdmin or your application in the same way.

To see which extensions are enabled in your database, run \dx in psql or:

SELECT extname, extversion FROM pg_extension;

pgvector

pgvector adds the vector data type together with distance operators and HNSW / IVFFlat indexes. It is used to store embeddings and to do similarity search for AI applications and RAG pipelines.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    content text,
    embedding vector(1536)
);

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- the 5 nearest items by cosine distance
SELECT id, content
FROM items
ORDER BY embedding <=> '[0.1, 0.2, 0.3, ...]'
LIMIT 5;

The dimension in vector(1536) must match the embedding model you use. In .NET install the Pgvector NuGet package and call UseVector() on the Npgsql data source; in PHP and other languages the vector column is written and read as a string in the [x, y, z] format.

PostGIS

PostGIS adds geographic data types (geometry, geography), spatial functions and spatial indexes. The core postgis extension and postgis_sfcgal (advanced 3D operations) are available.

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;   -- optional

SELECT PostGIS_Full_Version();

CREATE TABLE places (
    id serial PRIMARY KEY,
    name text,
    geom geometry(Point, 4326)
);

CREATE INDEX ON places USING gist (geom);

INSERT INTO places (name, geom)
VALUES ('Prague', ST_SetSRID(ST_MakePoint(14.4378, 50.0755), 4326));

The spatial_ref_sys table with the EPSG coordinate systems is owned by the main user of your database, so you can insert your own SRID definitions if you need a coordinate system that is not in EPSG. In .NET use the Npgsql.NetTopologySuite package to map geometries to NetTopologySuite types.

The extensions postgis_topology, postgis_raster, postgis_tiger_geocoder and pgrouting are not installed.

Other available extensions

These standard PostgreSQL extensions can be enabled the same way:

Extensions that require superuser rights (for example postgres_fdw, dblink, file_fdw, pg_stat_statements or the untrusted procedural languages) cannot be enabled in a hosted database. If your application needs an extension that is not listed here, open a support ticket.

Backups and extensions

A backup of the database records which extensions are enabled, and a restore enables them again automatically. All our PostgreSQL servers have the same set of extensions installed, so a backup created on one server can be restored on another.

Related

• Connect to a PostgreSQL database
• Back up and restore a PostgreSQL database
• Manage a database in the browser