Database Viewer

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);


-- 100,000 products like 'blue lamp 41', in 10 categories.
INSERT INTO products (name, category, price_cents)
SELECT
  (ARRAY['red', 'blue', 'green', 'black', 'white',
         'small', 'large', 'vintage', 'modern', 'wooden'])[n % 10 + 1]
  || ' ' ||
  (ARRAY['lamp', 'chair', 'table', 'sofa', 'desk', 'shelf', 'rug',
         'mirror', 'clock', 'vase', 'bed', 'stool', 'bench', 'cabinet',
         'frame', 'pillow', 'blanket', 'curtain', 'basket', 'candle'])[n % 20 + 1]
  || ' ' || n,
  (ARRAY['living', 'bedroom', 'kitchen', 'office', 'garden',
         'kids', 'bath', 'outdoor', 'decor', 'storage'])[n / 10 % 10 + 1],
  500 + n * 7919 % 50000
FROM generate_series(1, 100000) AS n;

-- ANALYZE samples 300 rows per statistics target: 400 covers every row,
-- so the planner's estimates are the same on every run.
SET default_statistics_target = 400;
ANALYZE products;

-- pg_trgm splits text into 3-letter chunks: 'lamp' -> ' la', 'lam', 'amp'...
-- A GIN index on them can answer ILIKE '%...%', which a B-tree can't.
CREATE EXTENSION pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING gin (name gin_trgm_ops);


CREATE EXTENSION pg_stat_statements;

-- Also track statements run inside functions: the app's queries run in timed().
SET pg_stat_statements.track = 'all';

-- Every request is logged with its real query and duration,
-- like an APM tool or log_min_duration_statement would.
CREATE TABLE request_log (
  id SERIAL PRIMARY KEY,
  endpoint TEXT NOT NULL,
  query TEXT NOT NULL,
  duration_ms NUMERIC NOT NULL
);

-- Runs a query, returns how long it took in milliseconds.
CREATE FUNCTION timed(query TEXT) RETURNS NUMERIC AS $$
DECLARE
  started TIMESTAMPTZ := clock_timestamp();
BEGIN
  EXECUTE query;
  RETURN round(extract(epoch FROM clock_timestamp() - started)::numeric * 1000, 2);
END;
$$ LANGUAGE plpgsql;

SELECT pg_stat_statements_reset();


-- POST /products, 200 times
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'create', q, timed(q)
FROM generate_series(1, 200) AS n,
  format(
    'INSERT INTO products (name, category, price_cents) VALUES (%L, %L, %s)',
    'new lamp ' || n, 'garden', 1000 + n) AS q;

-- GET /products?category=kitchen&page=..., 200 times, pages 0 to 99
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'list', q, timed(q)
FROM generate_series(1, 200) AS n,
  LATERAL (SELECT n % 100 AS page) AS p,
  format(
    'SELECT id, name, price_cents FROM products WHERE category = %L AND id > %s ORDER BY id LIMIT 20',
    'kitchen', page * 200) AS q;

-- DELETE /products/:id, 100 times
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'delete', q, timed(q)
FROM generate_series(1, 100) AS n,
  format('DELETE FROM products WHERE id = %s', n * 97) AS q;

-- GET /products/search?q=..., 10 search terms, twice each
INSERT INTO request_log (endpoint, query, duration_ms)
SELECT 'search', q, timed(q)
FROM unnest(ARRAY['lamp', 'oak', 'blue sofa', 'mirror 42', 'candle',
                  'rug', 'velvet', 'desk 7', 'wooden bench', 'clock']) AS term,
  generate_series(1, 2),
  format(
    'SELECT id, name FROM products WHERE name ILIKE %L ORDER BY name LIMIT 20',
    '%' || term || '%') AS q;