PostgreSQL Extensions Explained — Install, Build, and Scale Like a Pro

-- ============================================================
-- Inspect everything PostgreSQL knows about installed extensions
-- ============================================================

-- 1. List all currently installed extensions with version and schema
SELECT
    extname          AS extension_name,
    extversion       AS installed_version,
    nspname          AS target_schema,
    extrelocatable   AS can_be_moved_to_other_schema
FROM pg_extension
JOIN pg_namespace ON pg_namespace.oid = pg_extension.extnamespace
ORDER BY extname;

-- 2. Show every object owned by the pg_stat_statements extension
-- This reveals what CREATE EXTENSION actually created
SELECT
    classid::regclass   AS catalog_table,   -- which system catalog the object lives in
    objid::regprocedure AS object_signature -- the object itself, cast to readable form
FROM pg_depend
WHERE deptype = 'e'   -- 'e' means "owned by an extension"
  AND refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pg_stat_statements')
LIMIT 20;

-- 3. Check which extensions are AVAILABLE to install (not yet installed)
SELECT
    name             AS extension_name,
    default_version  AS latest_version,
    comment          AS description
FROM pg_available_extensions
WHERE installed_version IS NULL   -- NULL means not installed yet
ORDER BY name;

-- ============================================================
-- Full production lifecycle: install, verify, upgrade, remove
-- ============================================================

-- STEP 1: Add to postgresql.conf BEFORE attempting install
-- (Do this via ALTER SYSTEM so it's tracked in postgresql.auto.conf)
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, pg_prewarm';
-- This requires a cluster restart — check pending restart status:
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name = 'shared_preload_libraries';

-- STEP 2: After restart, install the extension into a dedicated schema
-- Using a dedicated schema prevents extension objects polluting 'public'
CREATE SCHEMA IF NOT EXISTS monitoring;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements
    SCHEMA monitoring;   -- only works if extension is relocatable

-- For non-relocatable extensions (like pg_stat_statements), install into public
-- and control access via GRANT instead:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION pg_stat_statements_reset() TO monitoring_role;

-- STEP 3: Verify it's collecting data (proves shared_preload_libraries worked)
SELECT
    calls,
    mean_exec_time::numeric(10,3) AS avg_ms,
    LEFT(query, 80)               AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- STEP 4: Upgrade workflow (after OS package update)
-- Check current vs available version
SELECT
    name,
    installed_version,
    default_version,
    (installed_version <> default_version) AS upgrade_available
FROM pg_available_extensions
WHERE name = 'pg_stat_statements';

-- Run the upgrade (executes the delta SQL script, e.g. pg_stat_statements--1.9--1.10.sql)
ALTER EXTENSION pg_stat_statements UPDATE;         -- updates to latest
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'; -- pin to specific version

-- STEP 5: Safe removal — CASCADE drops all dependent objects too
-- Without CASCADE, DROP fails if any user object depends on extension types
DROP EXTENSION IF EXISTS pg_stat_statements CASCADE;

-- Confirm it's gone
SELECT COUNT(*) AS remaining_installations
FROM pg_extension
WHERE extname = 'pg_stat_statements';

-- ============================================================
-- pgvector: store OpenAI embeddings and do similarity search
-- with an HNSW index tuned for production recall vs speed
-- ============================================================

CREATE EXTENSION IF NOT EXISTS vector;  -- installs the vector type and operators

-- Table storing article embeddings from a text-embedding-3-small model
-- (1536 dimensions for OpenAI's small model)
CREATE TABLE article_embeddings (
    article_id      BIGINT PRIMARY KEY,
    title           TEXT        NOT NULL,
    content_snippet TEXT        NOT NULL,
    embedding       vector(1536) NOT NULL,  -- the actual float array
    embedded_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Build an HNSW index.
-- m = max connections per node (higher = better recall, more RAM)
-- ef_construction = build-time search width (higher = better recall, slower build)
-- These values are a production starting point; tune with recall benchmarks.
CREATE INDEX article_embeddings_hnsw_idx
    ON article_embeddings
    USING hnsw (embedding vector_cosine_ops)  -- cosine similarity for NLP embeddings
    WITH (
        m               = 16,    -- default; go up to 64 for high-recall use cases
        ef_construction = 128    -- default 64; doubling improves recall ~2-5%
    );

-- At query time, set ef_search to control recall vs speed tradeoff
-- This is a per-session setting — set it in your app's connection pool setup
SET hnsw.ef_search = 100;  -- default 40; higher = better recall, slower query

-- Find the 5 most semantically similar articles to a given embedding
-- '[0.12, -0.34, ...]' would be your query vector from the embedding model
WITH query_vector AS (
    -- In practice this comes from your application layer as a parameter
    SELECT '[0.021, 0.043, -0.117, 0.089]'::vector(4) AS vec  -- toy 4-dim example
)
SELECT
    ae.article_id,
    ae.title,
    -- <=> is the cosine distance operator; 1 - distance = cosine similarity
    1 - (ae.embedding <=> qv.vec)   AS cosine_similarity,
    ae.content_snippet
FROM article_embeddings ae
CROSS JOIN query_vector qv
ORDER BY ae.embedding <=> qv.vec   -- ORDER BY distance ASC = most similar first
LIMIT 5;

-- Monitor index build progress (for large tables, HNSW build can take minutes)
SELECT
    phase,
    blocks_done,
    blocks_total,
    ROUND(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS pct_complete
FROM pg_stat_progress_create_index
WHERE relid = 'article_embeddings'::regclass;

#!/usr/bin/env bash
# ============================================================
# Build a pure-SQL custom extension called 'safemath'
# that provides overflow-safe integer arithmetic helpers.
# No C compilation needed — pure SQL extensions are portable.
# ============================================================

set -euo pipefail

EXT_NAME="safemath"
EXT_VERSION="1.0"
# Find where PostgreSQL expects extension files
PG_SHAREDIR=$(pg_config --sharedir)
PG_EXTDIR="${PG_SHAREDIR}/extension"

echo "Installing ${EXT_NAME} to ${PG_EXTDIR}"

# ---- FILE 1: Control file ----
# This is what PostgreSQL reads when you call CREATE EXTENSION safemath
cat > "/tmp/${EXT_NAME}.control" << 'EOF'
# safemath extension
default_version = '1.0'
relocatable     = true
schema          = safemath
comment         = 'Overflow-safe arithmetic functions for financial calculations'
EOF

# ---- FILE 2: SQL installation script ----
# Named exactly: extname--version.sql  (double dash is required)
cat > "/tmp/${EXT_NAME}--${EXT_VERSION}.sql" << 'EOF'
-- SQL script runs inside a transaction during CREATE EXTENSION
-- The @extschema@ placeholder is replaced by the schema Postgres chose

-- Safe addition: returns NULL instead of overflowing
CREATE OR REPLACE FUNCTION @extschema@.safe_add(
    addend_a BIGINT,
    addend_b BIGINT
)
RETURNS BIGINT
LANGUAGE plpgsql
IMMUTABLE STRICT PARALLEL SAFE  -- PARALLEL SAFE lets planner use this in parallel queries
AS $$
BEGIN
    -- pg_catalog.int8 max is 9223372036854775807
    -- Check for overflow before it happens to avoid exception handling overhead
    IF addend_b > 0 AND addend_a > (9223372036854775807 - addend_b) THEN
        RETURN NULL;  -- would overflow positive
    END IF;
    IF addend_b < 0 AND addend_a < (-9223372036854775808 - addend_b) THEN
        RETURN NULL;  -- would overflow negative
    END IF;
    RETURN addend_a + addend_b;
END;
$$;

-- Safe multiply with the same NULL-on-overflow contract
CREATE OR REPLACE FUNCTION @extschema@.safe_multiply(
    factor_a BIGINT,
    factor_b BIGINT
)
RETURNS BIGINT
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
AS $$
    SELECT
        CASE
            WHEN factor_b = 0 THEN 0
            WHEN ABS(factor_a) > (9223372036854775807 / ABS(factor_b)) THEN NULL
            ELSE factor_a * factor_b
        END;
$$;

-- Useful view: show current extension version at runtime
CREATE VIEW @extschema@.extension_info AS
    SELECT
        extname    AS name,
        extversion AS version
    FROM pg_extension
    WHERE extname = 'safemath';
EOF

# Copy files to where PostgreSQL can find them
sudo cp "/tmp/${EXT_NAME}.control"              "${PG_EXTDIR}/"
sudo cp "/tmp/${EXT_NAME}--${EXT_VERSION}.sql" "${PG_EXTDIR}/"

echo "Files installed. Now run in psql:"
echo "  CREATE EXTENSION safemath;"
echo "  SELECT safemath.safe_add(9223372036854775800, 100);  -- should return NULL"