Extensions (postgis, pgvector)

-- 1) List installed + available extensions
SELECT * FROM pg_available_extensions ORDER BY name;
SELECT * FROM pg_extension;                       -- already installed in current DB
\dx                                                  -- psql shortcut

-- 2) Install an extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA extensions;

-- Need superuser for many extensions. On RDS / Cloud SQL / Supabase use rds_superuser /
-- the cloud-specific role. Some hosted providers expose a UI for enabling extensions.

-- 3) Drop / upgrade
DROP EXTENSION pg_trgm;
ALTER EXTENSION postgis UPDATE;

-- 4) Schema location
-- Many extensions install objects to the public schema by default. For tidiness:
CREATE SCHEMA IF NOT EXISTS extensions;
GRANT USAGE ON SCHEMA extensions TO public;
ALTER DATABASE myapp SET search_path = public, extensions;
CREATE EXTENSION pg_trgm SCHEMA extensions;

-- 5) MUST-KNOW extensions
--   pgcrypto       — hashing, encryption, gen_random_uuid()
--   uuid-ossp      — older UUID generators (UUID v1, v3, v5)
--   pg_trgm        — trigram-based fuzzy text search
--   citext         — case-insensitive TEXT type
--   pg_stat_statements — query performance stats
--   hstore         — key-value type (older; prefer jsonb)
--   postgis        — geospatial types + indexes
--   pgvector       — vector embeddings + ANN search
--   pg_partman     — partition management
--   pgaudit        — fine-grained audit logs
--   timescaledb    — time-series engine
--   citus          — distributed/sharded Postgres
--   pg_jsonschema  — JSON Schema validation in constraints

-- 6) Examples

-- pgcrypto — random UUID, password hashing
CREATE EXTENSION pgcrypto;
SELECT gen_random_uuid();
SELECT crypt('secret', gen_salt('bf', 12));               -- bcrypt-style; argon2 not built in (use app-level)
SELECT digest('hello', 'sha256');

-- pg_trgm — fuzzy search
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%laptp%';        -- fast even with typos
SELECT name FROM products ORDER BY similarity(name, 'lapotp') DESC LIMIT 5;

-- citext — case-insensitive comparisons
CREATE EXTENSION citext;
CREATE TABLE users ( email CITEXT UNIQUE );
SELECT * FROM users WHERE email = 'Mara@example.com';     -- case-insensitive match

-- pg_stat_statements — query performance
CREATE EXTENSION pg_stat_statements;
-- (also requires shared_preload_libraries = 'pg_stat_statements' in postgresql.conf)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- PostGIS — geospatial
CREATE EXTENSION postgis;
CREATE TABLE pois ( id BIGSERIAL PRIMARY KEY, location GEOGRAPHY(POINT, 4326) );
CREATE INDEX idx_pois_location ON pois USING GIST (location);
INSERT INTO pois (location) VALUES ('SRID=4326;POINT(151.2093 -33.8688)');  -- Sydney
SELECT id FROM pois
WHERE ST_DWithin(location, 'SRID=4326;POINT(151.2 -33.87)'::geography, 5000);

-- pgvector — vector similarity
CREATE EXTENSION vector;
CREATE TABLE docs ( id BIGSERIAL PRIMARY KEY, embedding vector(1536) );
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id FROM docs ORDER BY embedding <=> '[…]'::vector LIMIT 10;
-- <-> Euclidean, <=> cosine distance, <#> inner product

-- 7) Cloud / hosted considerations
-- • AWS RDS / Aurora — pre-installed; check 'rds.extensions' parameter
-- • Cloud SQL — same model, list via 'select * from pg_available_extensions'
-- • Supabase — wide extension support including pgvector, pgaudit, pgsodium
-- • Neon — focuses on PostgreSQL-compatible cloud; pgvector built in
-- • PlanetScale - MySQL only, no extensions

-- 8) Bundled vs contrib vs third-party
-- 'contrib' extensions ship with Postgres source (uuid-ossp, pg_trgm, etc.)
-- Third-party (timescaledb, citus, pgvector) — install from packages or source
-- Don't compile extensions on managed services; pick a host that supports them

-- 9) Updating + versioning
-- Extensions ship with version files. After Postgres upgrade:
ALTER EXTENSION postgis UPDATE TO '3.5.0';
-- pg_extension catalog has the current version

-- 10) Cluster vs per-database
-- Most extensions are per-database. Some require shared_preload_libraries:
--   pg_stat_statements, pg_audit, citus, timescaledb, pg_cron
-- These need a Postgres restart (or cluster reload) to take effect.

-- 11) Build your own extension (advanced)
-- C extension scaffolded by pgxn-client + PGXS Makefile. Useful for performance-critical
-- functions or new data types. Most apps don't need this — embedded languages (PL/pgSQL, PL/Python) cover most needs.

-- 12) Procedural language extensions
CREATE EXTENSION plpgsql;            -- always installed
CREATE EXTENSION plperl;
CREATE EXTENSION plpython3u;
-- Untrusted versions (plperlu, plpython3u) can call out to the OS — restrict by role.

-- 13) Permission considerations
-- • Extensions install objects under their schema; grant USAGE + EXECUTE as needed
-- • Some extensions add system catalog rows; backups should preserve them
-- • Restoring a database to a new Postgres version may require re-running CREATE EXTENSION

-- 14) Common bugs
-- • CREATE EXTENSION without superuser — many require it; check error or hosted docs
-- • Forgetting shared_preload_libraries for pg_stat_statements / citus — extension installs but does nothing
-- • Schema confusion — search_path doesn't include the extension schema → 'function does not exist'
-- • Backing up a DB without --extension - extensions need to be re-created on restore
-- • pgcrypto used for password hashing (bcrypt is OK; better: app-level Argon2id)
-- • pgvector dimension mismatch — vector(1536) won't accept vector(768) values
-- • PostGIS GEOMETRY vs GEOGRAPHY — distance semantics differ; pick one and stick to it
-- • Forgetting to ANALYZE after enabling pg_trgm-style indexes → query planner won't use them

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector for embeddings