Skip to main content

PostgreSQL Extensions

Enhance your PostgreSQL database with powerful extensions.

Overview​

PostgreSQL extensions add functionality beyond the core database. SailOps supports many popular extensions out of the box.

Available Extensions​

SailOps supports 45 PostgreSQL extensions. They are organized by category below.

Availability depends on the runtime image

Not every extension is available on every PostgreSQL major version, and some require a specialized container image. SailOps picks the right image automatically from the extensions you select. See Image Compatibility & Limitations for the full matrix before enabling PostGIS, TimescaleDB, pg_cron, pgrouting, or ogr_fdw.

Core & Utility​

ExtensionDescription
plpgsqlPL/pgSQL procedural language
pgcryptoCryptographic functions
uuid-osspUUID generation
dblinkConnect to other PostgreSQL databases
postgres_fdwForeign data wrapper for remote PostgreSQL servers
ogr_fdwOGR/GDAL foreign data wrapper for GIS data sources
sslinfoSSL certificate information
tablefuncCrosstab and pivot table functions

Monitoring & Statistics​

ExtensionDescription
pg_stat_statementsQuery performance statistics
pgauditAudit logging for compliance
pg_buffercacheInspect shared buffer cache contents
pg_freespacemapExamine the free space map
pg_prewarmPre-warm the buffer cache
pg_visibilityVisibility map information
pgrowlocksRow-level locking information
pgstattupleTuple-level statistics

Search & Text​

ExtensionDescription
pg_trgmTrigram matching for fuzzy search
fuzzystrmatchFuzzy string matching (Soundex, Levenshtein)
unaccentRemove accents from strings
dict_intText search dictionary for integers
dict_xsynText search dictionary for extended synonyms

Indexing​

ExtensionDescription
bloomBloom filter index access method
btree_ginGIN operator classes for B-tree types
btree_gistGiST operator classes for B-tree types

Data Types & Structures​

ExtensionDescription
hstoreKey-value store in a single column
ltreeHierarchical tree structures
citextCase-insensitive text type
cubeMultidimensional cubes
isnInternational standard numbers (ISBN, ISSN, EAN)
intaggInteger aggregator (legacy)
intarrayFunctions and operators for integer arrays
earthdistanceGreat-circle distance calculations
tsm_system_rowsTABLESAMPLE method — by number of rows
tsm_system_timeTABLESAMPLE method — by time spent

Geographic & GIS​

ExtensionDescription
postgisGeographic objects and spatial queries
postgis_topologyTopological data types and functions
postgis_rasterRaster data support
postgis_sfcgal3D geometry support (SFCGAL backend)
postgis_tiger_geocoderUS Census TIGER geocoding
pgroutingRouting and network analysis
address_standardizerAddress string parsing
address_standardizer_data_usUS address standardization data

AI & Analytics​

ExtensionDescription
pgvectorVector similarity search for AI embeddings
timescaledbTime-series data optimized storage and queries

Scheduling​

ExtensionDescription
pg_cronJob scheduling within PostgreSQL

Image Compatibility & Limitations​

Extensions are not all shipped in the same container image. When you create or update a PostgreSQL database, SailOps inspects the extensions you requested and selects the appropriate runtime image automatically — you never set an image by hand. If you request a combination that no image can satisfy, the API rejects the request with a clear 400 error instead of producing a broken cluster.

Runtime images​

ImageWhat it containsUsed when
StandardPostgreSQL + contrib extensions, pgaudit, pgvectorDefault — any combination of core/contrib extensions
PostGISEverything in Standard plus the PostGIS familyYou request any PostGIS extension (PG 15–17 only)
TimescaleDBTimescaleDB plus PostGIS (without the SFCGAL backend), pgvector, pg_cron, pgrouting, contribYou request timescaledb, pg_cron, or pgrouting

Because the PostGIS and TimescaleDB images are built on top of the Standard image, pgvector, pgaudit, and all contrib extensions remain available on them too. In particular, PostGIS and pgvector are compatible and can be enabled on the same database.

The PostGIS image is only approved for PostgreSQL 15, 16, and 17 — the PostGIS family is not available on PG 13 or 14. The TimescaleDB image bundles PostGIS but without the SFCGAL backend, so postgis_sfcgal cannot be combined with timescaledb (see Incompatible combinations).

Compatibility matrix​

Legend: ✅ available · ❌ not available. The Image column shows the minimum image required; SailOps upgrades the image automatically to cover everything you select.

ExtensionImagePG 13PG 14PG 15PG 16PG 17Notes
All core & contrib extensions (plpgsql, pgcrypto, uuid-ossp, hstore, ltree, pg_trgm, pg_stat_statements, …)Standard✅✅✅✅✅Always available
pgvectorStandard✅✅✅✅✅Bundled in the Standard image
pgauditStandard✅✅✅✅✅Bundled in the Standard image
postgis, postgis_topology, postgis_raster, postgis_tiger_geocoder, address_standardizer, address_standardizer_data_usPostGIS❌❌✅✅✅Switches the database to the PostGIS image, which is only approved for PG 15–17
postgis_sfcgalPostGIS❌❌✅✅✅3D geometry (SFCGAL backend) on the PostGIS image (PG 15–17). Cannot be combined with timescaledb — the TimescaleDB image has no SFCGAL backend
timescaledbTimescaleDB❌❌✅✅✅Dropped support for PG 13/14 in recent releases
pg_cronTimescaleDB❌❌✅✅✅Only shipped in the TimescaleDB image, which requires PG 15+
pgroutingTimescaleDB❌❌✅✅✅Only shipped in the TimescaleDB image, which requires PG 15+; depends on postgis
ogr_fdw—❌❌❌❌❌Not available in any current image

TimescaleDB version mapping​

When you enable timescaledb, SailOps runs the official timescaledb-ha image. The PostgreSQL major you choose determines the bundled TimescaleDB version:

PostgreSQLTimescaleDBStatus
13, 14—Not supported
152.27.1Supported (older release line)
162.27.1Fully supported
172.27.1Fully supported

Incompatible combinations​

The API returns a 400 error — with no cluster change — for these requests:

RequestWhy it is rejected
Any PostGIS extension on PG 13 or 14The PostGIS image is only approved for PG 15, 16, and 17
timescaledb on PG 13 or 14TimescaleDB no longer supports these majors
pg_cron or pgrouting on PG 13 or 14Only the TimescaleDB image ships them, and it requires PG 15+
postgis_sfcgal together with timescaledbThe approved TimescaleDB image bundles PostGIS without the SFCGAL backend
ogr_fdw (any version)No SailOps image currently ships this extension

Switching images on an existing database​

Adding or removing an extension that changes the required image (for example, enabling timescaledb on a database that was running the Standard image) triggers a rolling restart of the cluster so it can move to the new image. Plan such changes during a maintenance window. Removing the extension and moving back to the Standard image works the same way.

Need ogr_fdw or another unsupported extension?​

If you need an extension that is not yet available, contact [email protected] with the extension name and your use case — see Requesting New Extensions.

Installing Extensions​

Using CREATE EXTENSION​

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable trigram search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Enable cryptography
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Viewing Installed Extensions​

SELECT * FROM pg_extension;

Removing Extensions​

DROP EXTENSION IF EXISTS extension_name;

uuid-ossp - UUID Generation​

Generate unique identifiers:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Generate random UUID
SELECT uuid_generate_v4();
-- Output: 6ba7b810-9dad-11d1-80b4-00c04fd430c8

-- Use in tables
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email VARCHAR(255) NOT NULL
);

pgcrypto - Encryption​

Secure password hashing and encryption:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash a password
SELECT crypt('mypassword', gen_salt('bf'));
-- Output: $2a$06$...

-- Verify password
SELECT crypt('mypassword', stored_hash) = stored_hash;

-- Encrypt data
SELECT encrypt('secret data', 'encryption_key', 'aes');

-- Decrypt data
SELECT decrypt(encrypted_data, 'encryption_key', 'aes');

Fast similarity searches:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

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

-- Search for similar names
SELECT * FROM users
WHERE name % 'Jhon' -- Matches "John"
ORDER BY similarity(name, 'Jhon') DESC;

-- Similarity threshold
SELECT set_limit(0.3); -- Set threshold (0-1)

pg_stat_statements - Query Analysis​

Monitor query performance:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View top queries by total time
SELECT
query,
calls,
total_exec_time / 1000 as total_seconds,
mean_exec_time as avg_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

PostGIS - Geographic Data​

Store and query geographic data:

CREATE EXTENSION IF NOT EXISTS postgis;

-- Create a table with location
CREATE TABLE places (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
location GEOGRAPHY(POINT, 4326)
);

-- Insert a point
INSERT INTO places (name, location)
VALUES ('Paris', ST_GeographyFromText('POINT(2.3522 48.8566)'));

-- Find places within 10km
SELECT name
FROM places
WHERE ST_DWithin(
location,
ST_GeographyFromText('POINT(2.2945 48.8584)'),
10000 -- 10km in meters
);

-- Calculate distance
SELECT
a.name,
b.name,
ST_Distance(a.location, b.location) / 1000 as km
FROM places a, places b
WHERE a.id != b.id;

citext - Case-Insensitive Text​

Simplify case-insensitive comparisons:

CREATE EXTENSION IF NOT EXISTS citext;

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

-- These are treated as equal
INSERT INTO users (email) VALUES ('[email protected]');
INSERT INTO users (email) VALUES ('[email protected]'); -- Error: duplicate

-- Case-insensitive queries
SELECT * FROM users WHERE email = '[email protected]'; -- Works!

hstore - Key-Value Storage​

Flexible key-value columns:

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
attributes HSTORE
);

-- Insert with attributes
INSERT INTO products (name, attributes)
VALUES ('Laptop', 'brand => Dell, ram => 16GB, screen => 15.6');

-- Query by attribute
SELECT * FROM products
WHERE attributes -> 'brand' = 'Dell';

-- Access specific attribute
SELECT name, attributes -> 'ram' as ram
FROM products;

-- Check if key exists
SELECT * FROM products
WHERE attributes ? 'ssd';

ltree - Tree Structures​

Hierarchical data:

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE categories (
id SERIAL PRIMARY KEY,
path LTREE,
name VARCHAR(255)
);

-- Insert hierarchical data
INSERT INTO categories (path, name) VALUES
('root', 'All Categories'),
('root.electronics', 'Electronics'),
('root.electronics.computers', 'Computers'),
('root.electronics.phones', 'Phones');

-- Find all descendants
SELECT * FROM categories
WHERE path <@ 'root.electronics';

-- Find ancestors
SELECT * FROM categories
WHERE path @> 'root.electronics.computers';

Extension Management​

Check Available Extensions​

SELECT * FROM pg_available_extensions
ORDER BY name;

Extension Versions​

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

Upgrade Extensions​

ALTER EXTENSION extension_name UPDATE;

Best Practices​

1. Enable Only What You Need​

Each extension has overhead. Enable only necessary extensions.

2. Test in Development First​

Install and test extensions in development before production.

3. Monitor Performance​

Some extensions impact performance. Monitor after enabling.

4. Check Compatibility​

Verify extension compatibility with your PostgreSQL version.

Requesting New Extensions​

Need an extension not listed?

  1. Contact [email protected]
  2. Provide the extension name and use case
  3. We'll evaluate and respond within 48 hours

Troubleshooting​

Extension Not Found​

ERROR: extension "xyz" is not available

The extension may not be supported. Contact support.

Permission Denied​

ERROR: permission denied to create extension

Only the superuser (postgres) can create extensions.

Version Mismatch​

ERROR: extension "xyz" has no installation script for version "x.y"

The extension version may not be compatible with your PostgreSQL version.