Extensions

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA ext;   -- keep it out of public
CREATE EXTENSION IF NOT EXISTS pg_trgm VERSION '1.6';

ALTER EXTENSION pg_trgm UPDATE;            -- to the newest available version
ALTER EXTENSION pg_trgm UPDATE TO '1.6';   -- to a specific one
ALTER EXTENSION hstore SET SCHEMA public;

DROP EXTENSION pg_trgm;            -- fails if anything depends on it
DROP EXTENSION pg_trgm CASCADE;    -- drops the dependent indexes too

-- What can I install, and is it already installed?
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE name IN ('pg_trgm','pgcrypto','vector','postgis')
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Indexed leading-wildcard search
SELECT id, name FROM products WHERE name ILIKE '%widg%';

-- Ranked fuzzy match, needs gist_trgm_ops for the KNN ordering
CREATE INDEX products_name_trgm_gist ON products USING gist (name gist_trgm_ops);

SET pg_trgm.similarity_threshold = 0.25;
SELECT name, round(similarity(name, 'wigdet')::numeric, 3) AS sim
FROM products
WHERE name % 'wigdet'
ORDER BY name <-> 'wigdet'
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                    email citext UNIQUE NOT NULL);
SELECT 'Ada@Example.COM'::citext = 'ada@example.com';   -- t

CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT unaccent('Jose Muñoz Ángel');                    -- Jose Munoz Angel

-- unaccent is STABLE, so wrap it before putting it in an index
CREATE FUNCTION immutable_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;

CREATE INDEX people_name_unaccent
  ON people (lower(immutable_unaccent(full_name)));

CREATE EXTENSION IF NOT EXISTS hstore;
SELECT 'a=>1, b=>2'::hstore -> 'b';                     -- 2
SELECT akeys('a=>1, b=>2'::hstore);                     -- {a,b}

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- tenant_id is a plain int, tags is text[], one GIN index serves both
CREATE INDEX docs_tenant_tags ON docs USING gin (tenant_id, tags);
SELECT * FROM docs WHERE tenant_id = 7 AND tags @> ARRAY['urgent'];
-- Bitmap Index Scan, Index Cond: ((tenant_id = 7) AND (tags @> '{urgent}'))

-- btree_gist makes = available to an EXCLUDE constraint
CREATE TABLE bookings (
  room_id  int  NOT NULL,
  during   tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT encode(digest('hello', 'sha256'), 'hex');
SELECT encode(gen_random_bytes(16), 'base64');          -- an API token

-- Password verify: hash the candidate with the stored hash as the salt
INSERT INTO accounts (email, pw_hash)
VALUES ($1, crypt($2, gen_salt('bf', 10)));

SELECT id FROM accounts
WHERE email = $1 AND pw_hash = crypt($2, pw_hash);

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id    bigint NOT NULL REFERENCES docs (id) ON DELETE CASCADE,
  body      text   NOT NULL,
  embedding vector(1536) NOT NULL          -- text-embedding-3-small width
);

CREATE INDEX chunks_embedding_hnsw ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Top 5 nearest, with cosine similarity reported alongside
SET hnsw.ef_search = 100;                  -- default 40, higher = better recall
SELECT id, body, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE doc_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

# postgresql.conf, needs a restart
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 1)  AS total_ms,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       rows,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT pg_stat_statements_reset();          -- baseline before a load test

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER reporting FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'db2.internal', port '5432', dbname 'analytics');

CREATE USER MAPPING FOR app SERVER reporting
  OPTIONS (user 'app_ro', password 'secret');

CREATE SCHEMA remote;
IMPORT FOREIGN SCHEMA public LIMIT TO (sales, customers)
  FROM SERVER reporting INTO remote;

SELECT count(*) FROM remote.sales WHERE placed_at >= current_date - 7;
ANALYZE remote.sales;              -- foreign tables need their own stats

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT * FROM dblink('dbname=analytics host=db2.internal user=app_ro',
                     'SELECT count(*) FROM sales') AS t(total bigint);

-- PostGIS: nearest stores to a point, index-assisted
CREATE EXTENSION IF NOT EXISTS postgis;
ALTER TABLE stores ADD COLUMN geog geography(Point, 4326);
CREATE INDEX stores_geog_gix ON stores USING gist (geog);

SELECT name, ST_Distance(geog, ST_MakePoint(-73.98, 40.75)::geography) AS m
FROM stores
WHERE ST_DWithin(geog, ST_MakePoint(-73.98, 40.75)::geography, 2000)
ORDER BY geog <-> ST_MakePoint(-73.98, 40.75)::geography
LIMIT 10;

-- TimescaleDB: a time-partitioned table, then drop old chunks
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE readings (
  ts     timestamptz NOT NULL,
  device text NOT NULL,
  value  double precision
) WITH (tsdb.hypertable, tsdb.orderby = 'ts DESC');

SELECT drop_chunks('readings', INTERVAL '3 months');
SELECT show_chunks('readings');

-- pg_cron: schedules live in one database, jobs can target any
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule('nightly-vacuum', '0 3 * * *',
                     $$VACUUM (ANALYZE) events$$);
SELECT cron.schedule_in_database('purge', '*/15 * * * *',
                                 'DELETE FROM sessions WHERE expires_at < now()',
                                 'app_prod');
SELECT * FROM cron.job;
SELECT cron.unschedule('nightly-vacuum');