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.
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
| Extension | Description |
|---|---|
plpgsql | PL/pgSQL procedural language |
pgcrypto | Cryptographic functions |
uuid-ossp | UUID generation |
dblink | Connect to other PostgreSQL databases |
postgres_fdw | Foreign data wrapper for remote PostgreSQL servers |
ogr_fdw | OGR/GDAL foreign data wrapper for GIS data sources |
sslinfo | SSL certificate information |
tablefunc | Crosstab and pivot table functions |
Monitoring & Statistics
| Extension | Description |
|---|---|
pg_stat_statements | Query performance statistics |
pgaudit | Audit logging for compliance |
pg_buffercache | Inspect shared buffer cache contents |
pg_freespacemap | Examine the free space map |
pg_prewarm | Pre-warm the buffer cache |
pg_visibility | Visibility map information |
pgrowlocks | Row-level locking information |
pgstattuple | Tuple-level statistics |
Search & Text
| Extension | Description |
|---|---|
pg_trgm | Trigram matching for fuzzy search |
fuzzystrmatch | Fuzzy string matching (Soundex, Levenshtein) |
unaccent | Remove accents from strings |
dict_int | Text search dictionary for integers |
dict_xsyn | Text search dictionary for extended synonyms |
Indexing
| Extension | Description |
|---|---|
bloom | Bloom filter index access method |
btree_gin | GIN operator classes for B-tree types |
btree_gist | GiST operator classes for B-tree types |
Data Types & Structures
| Extension | Description |
|---|---|
hstore | Key-value store in a single column |
ltree | Hierarchical tree structures |
citext | Case-insensitive text type |
cube | Multidimensional cubes |
isn | International standard numbers (ISBN, ISSN, EAN) |
intagg | Integer aggregator (legacy) |
intarray | Functions and operators for integer arrays |
earthdistance | Great-circle distance calculations |
tsm_system_rows | TABLESAMPLE method — by number of rows |
tsm_system_time | TABLESAMPLE method — by time spent |
Geographic & GIS
| Extension | Description |
|---|---|
postgis | Geographic objects and spatial queries |
postgis_topology | Topological data types and functions |
postgis_raster | Raster data support |
postgis_sfcgal | 3D geometry support (SFCGAL backend) |
postgis_tiger_geocoder | US Census TIGER geocoding |
pgrouting | Routing and network analysis |
address_standardizer | Address string parsing |
address_standardizer_data_us | US address standardization data |
AI & Analytics
| Extension | Description |
|---|---|
pgvector | Vector similarity search for AI embeddings |
timescaledb | Time-series data optimized storage and queries |
Scheduling
| Extension | Description |
|---|---|
pg_cron | Job 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
| Image | What it contains | Used when |
|---|---|---|
| Standard | PostgreSQL + contrib extensions, pgaudit, pgvector | Default — any combination of core/contrib extensions |
| PostGIS | Everything in Standard plus the PostGIS family | You request any PostGIS extension (PG 15–17 only) |
| TimescaleDB | TimescaleDB plus PostGIS (without the SFCGAL backend), pgvector, pg_cron, pgrouting, contrib | You 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.
| Extension | Image | PG 13 | PG 14 | PG 15 | PG 16 | PG 17 | Notes |
|---|---|---|---|---|---|---|---|
All core & contrib extensions (plpgsql, pgcrypto, uuid-ossp, hstore, ltree, pg_trgm, pg_stat_statements, …) | Standard | ✅ | ✅ | ✅ | ✅ | ✅ | Always available |
pgvector | Standard | ✅ | ✅ | ✅ | ✅ | ✅ | Bundled in the Standard image |
pgaudit | Standard | ✅ | ✅ | ✅ | ✅ | ✅ | Bundled in the Standard image |
postgis, postgis_topology, postgis_raster, postgis_tiger_geocoder, address_standardizer, address_standardizer_data_us | PostGIS | ❌ | ❌ | ✅ | ✅ | ✅ | Switches the database to the PostGIS image, which is only approved for PG 15–17 |
postgis_sfcgal | PostGIS | ❌ | ❌ | ✅ | ✅ | ✅ | 3D geometry (SFCGAL backend) on the PostGIS image (PG 15–17). Cannot be combined with timescaledb — the TimescaleDB image has no SFCGAL backend |
timescaledb | TimescaleDB | ❌ | ❌ | ✅ | ✅ | ✅ | Dropped support for PG 13/14 in recent releases |
pg_cron | TimescaleDB | ❌ | ❌ | ✅ | ✅ | ✅ | Only shipped in the TimescaleDB image, which requires PG 15+ |
pgrouting | TimescaleDB | ❌ | ❌ | ✅ | ✅ | ✅ | 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:
| PostgreSQL | TimescaleDB | Status |
|---|---|---|
| 13, 14 | — | Not supported |
| 15 | 2.27.1 | Supported (older release line) |
| 16 | 2.27.1 | Fully supported |
| 17 | 2.27.1 | Fully supported |
Incompatible combinations
The API returns a 400 error — with no cluster change — for these requests:
| Request | Why it is rejected |
|---|---|
| Any PostGIS extension on PG 13 or 14 | The PostGIS image is only approved for PG 15, 16, and 17 |
timescaledb on PG 13 or 14 | TimescaleDB no longer supports these majors |
pg_cron or pgrouting on PG 13 or 14 | Only the TimescaleDB image ships them, and it requires PG 15+ |
postgis_sfcgal together with timescaledb | The 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;
Popular Extensions Guide
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');
pg_trgm - Fuzzy Search
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?
- Contact [email protected]
- Provide the extension name and use case
- 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.