Database Viewer

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);


INSERT INTO documents (title, body)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.'),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.'),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.'),
  ('React components', 'Components split a user interface into small reusable pieces.'),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.'),
  ('Neural networks', 'Neural networks learn patterns from training data.'),
  ('Chatbots', 'Language models answer questions written in natural language.'),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.');


INSERT INTO documents (title, body)
SELECT
  initcap(tech) || ' ' || topic || ' note ' || n,
  'Notes about ' || topic || ' in ' || tech || '.'
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;


-- Computed once on INSERT/UPDATE instead of on every search.
-- setweight labels title words A and body words B, so ts_rank can
-- rank a title match above a body match.
ALTER TABLE documents ADD COLUMN search tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body), 'B')
) STORED;

-- GIN: an inverted index, lexeme -> rows that contain it.
-- Create it after a bulk load: building it once is faster than updating it per row.
CREATE INDEX documents_search_idx ON documents USING gin (search);

ANALYZE documents;