Database Viewer

-- CREATE EXTENSION also makes PGlite load pgvector for this database.
CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- Exactly 3 dimensions: [databases, web, AI].
  embedding vector(3) NOT NULL
);


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]');


INSERT INTO documents (title, body, embedding)
SELECT
  initcap(tech) || ' ' || topic || ' note ' || n,
  'Notes about ' || topic || ' in ' || tech || '.',
  ARRAY[abs(sin(n)), abs(sin(n * 2)), abs(cos(n * 3))]::vector(3)
FROM generate_series(1, 5000) AS n,
  LATERAL (SELECT
    (ARRAY['postgresql', 'mysql', 'sqlite', 'redis', 'kafka',
           'react', 'vue', 'svelte', 'python', 'rust'])[n % 10 + 1] AS tech,
    (ARRAY['indexes', 'joins', 'tuning', 'components',
           'layouts', 'networks', 'chatbots', 'search'])[n / 10 % 8 + 1] AS topic
  ) AS words;

ANALYZE documents;


-- HNSW: a graph of vectors linked to their neighbours. A search walks the
-- graph towards the question instead of measuring every row.
-- The operator class must match the operator you ORDER BY:
--   vector_l2_ops for <->, vector_cosine_ops for <=>, vector_ip_ops for <#>.
CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_cosine_ops);