Extensions

PostgreSQL extensions available on Zektor.io — install, manage, and configure extensions to extend your database functionality.

Overview

PostgreSQL extensions are add-on modules that extend the functionality of your database beyond the core features. They provide additional data types, functions, operators, indexes, and other capabilities without requiring modifications to the PostgreSQL source code.

Managing Extensions

Installing an Extension

Use CREATE EXTENSION to install an available extension:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Removing an Extension

DROP EXTENSION IF EXISTS pgcrypto;

Updating an Extension

ALTER EXTENSION pgcrypto UPDATE;

Viewing Installed Extensions

List all currently installed extensions:

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

List all available extensions:

SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

Installing Extensions in a Specific Schema

By default, extensions are installed in the public schema. You can specify a different schema:

CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA my_schema;

To move an installed extension to a different schema:

ALTER EXTENSION pgcrypto SET SCHEMA new_schema;

Supported Native PostgreSQL Extensions

The following extensions are bundled with PostgreSQL and available on all Zektor.io instances:

Extension	Description	v17	v18
`btree_gin`	GIN operator classes for B-tree-indexable data types	Yes	Yes
`btree_gist`	GiST operator classes for B-tree-indexable data types	Yes	Yes
`citext`	Case-insensitive character string type	Yes	Yes
`cube`	Data type for multidimensional cubes	Yes	Yes
`dict_int`	Text search dictionary for integers	Yes	Yes
`earthdistance`	Calculate great-circle distances on the surface of the Earth	Yes	Yes
`fuzzystrmatch`	Determine similarities and distance between strings	Yes	Yes
`hstore`	Key-value pair data type	Yes	Yes
`intarray`	Functions for manipulating arrays of integers	Yes	Yes
`isn`	Data types for international product numbering standards	Yes	Yes
`ltree`	Data type for hierarchical tree-like structures	Yes	Yes
`pg_stat_statements`	Track statistics of SQL statements	Yes	Yes
`pg_trgm`	Text similarity measurement and trigram-based index searching	Yes	Yes
`pgcrypto`	Cryptographic functions	Yes	Yes
`pgrowlocks`	Show row-level locking information	Yes	Yes
`pgstattuple`	Show tuple-level statistics	Yes	Yes
`plpgsql`	PL/pgSQL procedural language	Yes	Yes
`postgres_fdw`	Foreign-data wrapper for remote PostgreSQL servers	Yes	Yes
`tablefunc`	Functions that manipulate whole tables	Yes	Yes
`unaccent`	Text search dictionary that removes accents	Yes	Yes
`uuid-ossp`	Generate universally unique identifiers (UUIDs)	Yes	Yes

Supported Community Extensions

Extension	Description	v17	v18
`pgvector`	Open-source vector similarity search for AI/ML	Yes	Yes
`postgis`	Spatial database extender for geographic objects	Yes	Yes
`pg_cron`	Job scheduler for PostgreSQL	Yes	Yes
`hypopg`	Hypothetical indexes for PostgreSQL	Yes	Yes

Note: Run SELECT * FROM pg_available_extensions ORDER BY name; on your instance to see the exact list of available extensions for your PostgreSQL version.

Popular Extension Use Cases

UUID Generation

Generate unique identifiers for your records:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4();

Tip: On PostgreSQL v17+, you can also use the built-in gen_random_uuid() function without any extension.

Full-Text Search Enhancement

Improve search functionality with trigram matching:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a trigram index
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);

-- Search with similarity
SELECT * FROM users WHERE name % 'john';

Case-Insensitive Text

Store text that compares case-insensitively:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email CITEXT UNIQUE NOT NULL
);

Vector Search (pgvector)

Store and search vector embeddings for AI applications:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  embedding vector(1536)
);

-- Create an index for faster similarity search
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

-- Find similar items
SELECT * FROM items
ORDER BY embedding <-> '[0.1, 0.2, ...]'
LIMIT 10;

Scheduled Jobs (pg_cron)

Run recurring SQL tasks inside PostgreSQL:

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Clean up old sessions every hour
SELECT cron.schedule('cleanup-sessions', '0 * * * *',
  $$DELETE FROM sessions WHERE expires_at < NOW()$$
);

-- View scheduled jobs
SELECT * FROM cron.job;

-- Remove a job
SELECT cron.unschedule('cleanup-sessions');

Query Performance Tracking

Monitor slow and frequent queries:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Geospatial Queries (PostGIS)

Work with geographic data:

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name TEXT,
  coordinates GEOGRAPHY(Point, 4326)
);

-- Insert a location (longitude, latitude)
INSERT INTO locations (name, coordinates)
VALUES ('Berlin', ST_Point(13.405, 52.52)::geography);

-- Find locations within 50km
SELECT name, ST_Distance(coordinates, ST_Point(13.405, 52.52)::geography) AS distance_m
FROM locations
WHERE ST_DWithin(coordinates, ST_Point(13.405, 52.52)::geography, 50000);

Extensions on Restored Branches

When you restore from a backup or create a branch, extensions may need to be reinstalled:

-- Reinstall your extensions after restore
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Data stored using extension types (e.g., vector, geography) is preserved in the backup — you just need to re-register the extension.

Troubleshooting

Extension not found

ERROR: extension "extension_name" is not available

The extension may not be supported on your PostgreSQL version. Check the available extensions list or contact us.

Permission denied

ERROR: permission denied to create extension "extension_name"

Some extensions require elevated privileges. Ensure you're connected with the primary database user.

Extension already exists

ERROR: extension "extension_name" already exists

Use CREATE EXTENSION IF NOT EXISTS to avoid this error.

Need Additional Extensions?

If you need an extension that isn't currently available, contact us and we'll evaluate adding it to our supported list.

Next Steps

Connecting — Connect and start using extensions
Monitoring — Track query performance with pg_stat_statements

On this page