Database Viewer

CREATE EXTENSION pg_trgm;
CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- From an embedding model: [databases, web, AI].
  embedding vector(3) NOT NULL,
  -- Kept up to date by Postgres on every INSERT and UPDATE.
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
  ) STORED
);

-- One index per search style, all in the same table.
CREATE INDEX ON documents USING gin (title gin_trgm_ops);   -- ILIKE, fuzzy
CREATE INDEX ON documents USING gin (search);               -- full-text
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops); -- semantic


INSERT INTO documents (title, body, embedding)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.', '[0.95, 0.03, 0.02]'),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.', '[0.66, 0.17, 0.17]'),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.', '[0.62, 0.15, 0.23]'),
  ('React components', 'Components split a user interface into small reusable pieces.', '[0.25, 0.26, 0.49]'),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.', '[0.15, 0.82, 0.03]'),
  ('Neural networks', 'Neural networks learn patterns from training data.', '[0.14, 0.08, 0.78]'),
  ('Chatbots', 'Language models answer questions written in natural language.', '[0.32, 0.26, 0.42]'),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.', '[0.91, 0.04, 0.04]');