Skip to content

Instantly share code, notes, and snippets.

@benjie
Created Sep 21, 2018
Embed
What would you like to do?
-- It's a joke.
drop schema if exists bigdata cascade;
create schema bigdata;
create extension if not exists citext;
set search_path to bigdata, public, pg_catalog;
create table users (
id serial primary key,
username citext not null unique,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table forums (
id serial primary key,
name text not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table topics (
id serial primary key,
forum_id int not null references forums on delete cascade,
author_id int not null references users on delete cascade,
title text not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table posts (
id serial primary key,
topic_id int not null references topics on delete cascade,
author_id int not null references users on delete cascade,
body text not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
do $$
declare
NUMBER_OF_USERS int = 10000;
NUMBER_OF_TOPICS_PER_FORUM int = 10000;
begin
insert into users (id, username)
select
user_number,
'user' || user_number
from generate_series(1, 100000) user_number;
insert into forums (id, name) values
(1, 'People'),
(2, 'Cats'),
(3, 'Dogs'),
(4, 'Fish'),
(5, 'Birds'),
(6, 'Plants'),
(7, 'Secrets');
insert into topics (forum_id, author_id, title)
select
forums.id,
1 + ((forums.id * NUMBER_OF_TOPICS_PER_FORUM + topic_number) % NUMBER_OF_USERS),
'There are ' || topic_number || ' ' || forums.name || ', the rest of you are all wrong.'
from forums, generate_series(1, NUMBER_OF_TOPICS_PER_FORUM) topic_number;
insert into posts (topic_id, author_id, body)
select
topics.id,
1 + ((topics.id + post_number) % NUMBER_OF_USERS),
'Reply to ' || topics.title || ' number ' || post_number
from topics, generate_series(1, power(topics.id % 10, 2)::int) post_number;
end;
$$ language plpgsql;
create index on topics(forum_id);
create index on topics(author_id);
create index on posts(topic_id);
create index on posts(author_id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment