PostgreSQL example - stream events

setup.sql

CREATE TABLE stream_user(
	id SERIAL PRIMARY KEY NOT NULL
);
CREATE TABLE resource(
	id SERIAL PRIMARY KEY NOT NULL,
	condition_1 BOOLEAN,
	condition_2 BOOLEAN,
	condition_3 BOOLEAN,
	condition_4 BOOLEAN,
	condition_5 BOOLEAN,
	condition_6 BOOLEAN,
	payload TEXT
);
CREATE TABLE stream_event(
	id SERIAL PRIMARY KEY NOT NULL,
	event_timestamp TIMESTAMP NOT NULL,
	viewer_id INT NOT NULL REFERENCES stream_user(id)
);
CREATE TABLE resource_2_stream_event(
	resource_id INT NOT NULL REFERENCES resource(id),
	stream_event_id INT NOT NULL REFERENCES stream_event(id)
);


-- populate stream_user
INSERT INTO stream_user(id)
WITH x AS (
	SELECT True AS c
	FROM generate_series(1,1000)
)
SELECT
	row_number() OVER (ORDER BY c)
FROM x;

-- populate resource
INSERT INTO resource(id, condition_1, condition_2, condition_3, condition_4, condition_5, condition_6, payload)
WITH x AS (
	SELECT True AS c
	FROM generate_series(1,1000000)
)
SELECT
	row_number() OVER (ORDER BY c),
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	random() * 10000
FROM x;


-- populate stream_event
ALTER TABLE stream_event DROP CONSTRAINT stream_event_viewer_id_fkey;
INSERT INTO stream_event(id, event_timestamp, viewer_id)
WITH x AS (
	SELECT True AS c
	FROM generate_series(1, 3000000)
)
SELECT
	row_number() OVER (ORDER BY c),
	now() - random() * '2 years'::interval,
	trunc(random() * 5 + 1)
FROM x;


-- populate resource_2_stream_event

-- disable fkeys, then reenable
ALTER TABLE resource_2_stream_event DROP CONSTRAINT resource_2_stream_event_resource_id_fkey;
ALTER TABLE resource_2_stream_event DROP CONSTRAINT resource_2_stream_event_stream_event_id_fkey;

INSERT INTO resource_2_stream_event(resource_id, stream_event_id)
WITH x AS (
	SELECT True AS c
	FROM generate_series(1,1000000)
)
SELECT
	trunc(random() * 1000000 + 1),
	trunc(random() * 3000000 + 1)
FROM x;


-- reenable fkeys
ALTER TABLE stream_event ADD CONSTRAINT stream_event_viewer_id_fkey FOREIGN KEY (viewer_id) REFERENCES stream_user(id);
ALTER TABLE resource_2_stream_event ADD CONSTRAINT resource_2_stream_event_resource_id_fkey FOREIGN KEY (resource_id) REFERENCES resource(id);
ALTER TABLE resource_2_stream_event ADD CONSTRAINT resource_2_stream_event_stream_event_id_fkey FOREIGN KEY (stream_event_id) REFERENCES stream_event(id);

-- create indexes
CREATE INDEX _idx_spd_1_resource ON resource(id, condition_1, condition_2, condition_3, condition_4, condition_5, condition_6);
CREATE INDEX _idx_spd_1_stream_event ON stream_event(viewer_id);
CREATE INDEX _idx_spd_1_resource_2_stream_event_1 ON resource_2_stream_event(resource_id);
CREATE INDEX _idx_spd_1_resource_2_stream_event_2 ON resource_2_stream_event(stream_event_id);


-- ensure we have a good number of stream events per user
-- we need at least 200k per
SELECT
	viewer_id,
	count(viewer_id)
FROM
	stream_event
GROUP BY
	viewer_id
ORDER BY
	viewer_id ASC;