This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

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