Extensions

PostgreSQL extensions available in Piglet Run.

Overview

Piglet Run includes 340+ PostgreSQL extensions from the Pigsty ecosystem.

Pre-installed Extensions

Core Extensions

ExtensionVersionDescription
pg_stat_statements1.10Track execution statistics
pgcrypto1.3Cryptographic functions
uuid-ossp1.1UUID generation
hstore1.8Key-value store
ltree1.2Hierarchical data
pg_trgm1.6Trigram matching

Vector & AI

ExtensionVersionDescription
pgvector0.7.0Vector similarity search
pgvectorscale0.2.0Vector indexing
pg_embedding0.3.6Embedding functions

Time Series

ExtensionVersionDescription
timescaledb2.14Time-series database
pg_partman5.0Partition management

Geospatial

ExtensionVersionDescription
postgis3.4Geographic objects
postgis_topology3.4Topology support
postgis_raster3.4Raster data
pgrouting3.6Routing algorithms

Full Text Search

ExtensionVersionDescription
pg_jieba1.1Chinese word segmentation
zhparser2.2Chinese parser

Installing Extensions

Via SQL

-- Create extension
CREATE EXTENSION pgvector;

-- Create extension in specific schema
CREATE EXTENSION postgis SCHEMA public;

-- Update extension
ALTER EXTENSION pgvector UPDATE;

Via CLI

pig ext install pgvector
pig ext install postgis

Listing Extensions

Installed Extensions

SELECT * FROM pg_extension;

Available Extensions

SELECT * FROM pg_available_extensions ORDER BY name;

Extension Details

SELECT * FROM pg_available_extension_versions
WHERE name = 'pgvector';

Extension Configuration

pg_stat_statements

-- Enable tracking
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- Configure
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;

pgvector

-- Create extension
CREATE EXTENSION vector;

-- Create vector column
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    embedding vector(384)
);

-- Create index
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

PostGIS

-- Create extension
CREATE EXTENSION postgis;

-- Create geometry column
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom geometry(Point, 4326)
);

-- Spatial query
SELECT name FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-122.4, 37.8)::geography, 1000);

TimescaleDB

-- Create extension
CREATE EXTENSION timescaledb;

-- Create hypertable
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    value DOUBLE PRECISION
);

SELECT create_hypertable('metrics', 'time');

Managing Extensions

# List installed extensions
pig ext list

# Show extension info
pig ext info pgvector

# Remove extension
pig ext remove pgvector

See Also