postgresql-benchmarks

Environment (2 Core CPU, 8 GB Ram)

Tables:

Student Teacher School Assessment

1. Create table teacher, school and student with (id, name)

2. Insert Teachers <- Took 1 second 15 ms

INSERT INTO teacher (id, name) VALUES (generate_series(1, 200000), 'default')

1. Insert Students <- Took 58 secs 227 ms

INSERT INTO student (id, name) VALUES (generate_series(1, 10000000), 'default')

1. Insert Schools <- Took 485 ms

INSERT INTO school (id, name) VALUES (generate_series(1, 50000), 'default')

1. Create hypertable assessment

CREATE TABLE assessment (
	id serial,
	submission_time timestamptz,
	school_id int,
	teacher_id int,
	student_id int,
	grade int,
	is_passed bool,
	FOREIGN KEY (school_id) REFERENCES school(id),
	FOREIGN KEY (teacher_id) REFERENCES teacher(id),
	FOREIGN KEY (student_id) REFERENCES student(id)
)

SELECT create_hypertable('assessment', 'submission_time')

1. Insert 10000 randomised assessment <- Took 1 secs 282 msec

INSERT INTO assessment 
(id, submission_time, school_id, student_id, teacher_id, grade, is_passed) 
VALUES 
(generate_series(1, 10000), timestamptz '2023-07-01 00:00:00 UTC' + random() * INTERVAL '30 days', floor(random() * 50000) + 1, floor(random() * 10000000) + 1, floor(random() * 200000) + 1, floor(random() * 3) + 1, RANDOM()::INT::BOOLEAN);

1. Insert 90000 randomised assessment <- Took 17 secs

INSERT INTO assessment 
(id, submission_time, school_id, student_id, teacher_id, grade, is_passed) 
VALUES 
(generate_series(10001, 100000), timestamptz '2023-07-01 00:00:00 UTC' + random() * INTERVAL '30 days', floor(random() * 50000) + 1, floor(random() * 10000000) + 1, floor(random() * 200000) + 1, floor(random() * 3) + 1, RANDOM()::INT::BOOLEAN);

1. Insert 100000 randomised assessment <- Took 10 secs

INSERT INTO assessment 
(id, submission_time, school_id, student_id, teacher_id, grade, is_passed) 
VALUES 
(generate_series(100001, 200000), timestamptz '2023-07-01 00:00:00 UTC' + random() * INTERVAL '30 days', floor(random() * 50000) + 1, floor(random() * 10000000) + 1, floor(random() * 200000) + 1, floor(random() * 3) + 1, RANDOM()::INT::BOOLEAN);

1. Insert 8,00,000 randomised assessment <- Took 1 min 8 Secs

INSERT INTO assessment 
(id, submission_time, school_id, student_id, teacher_id, grade, is_passed) 
VALUES 
(generate_series(200001, 1000000), timestamptz '2023-07-01 00:00:00 UTC' + random() * INTERVAL '30 days', floor(random() * 50000) + 1, floor(random() * 10000000) + 1, floor(random() * 200000) + 1, floor(random() * 3) + 1, RANDOM()::INT::BOOLEAN);

1. Query Count of all assessment for a particular week

explain analyze select count(*) from assessment where submission_time between '2023-07-03' and '2023-07-09';

1. Insert 10,00,000 randomised assessment <- Took 1 min 28 Sec
2. Insert 20,00,000 randomised assessment <- Took 2 min 56 Sec
3. Insert 20,00,000 randomised assessment <- Took 2 min 56 Sec

4. Insert 40,00,000 randomised assessment <- Took ~ 6 min

5. Query Count of all assessment for a particular week

explain analyze select count(*) from assessment where submission_time between '2023-07-03' and '2023-07-09';

1. Chunks created by timescaledb

1. Query a non-hypertable with exactly same data

explain analyze select count(*) from assessment_non_hypertable where submission_time between '2023-07-03' and '2023-07-09';

1. Insert 10000000 assessment
2. Query Count of all assessment for a particular week

explain analyze select count(*) from assessment where submission_time between '2023-07-03' and '2023-07-09';

16.Query Count of all assessment for a particular week in a non-hypertable with exact same data

1. Insert 1,00,00,000 assessment in different month
2. Query Count of all assessment for a particular week

explain analyze select count(*) from assessment where submission_time between '2023-07-03' and '2023-07-09';

1. Query Count for non-hypertable

1. Insert 1,00,00,000 assessment in different month
2. Query count of all assessment for a particular week

1. Same query for non-hypertable

1. Let’s create index for non-hypertable

CREATE INDEX assessment_non_hypertable_submission_idx ON assessment_non_hypertable (submission_time)

We see that Non-hypertable is performing better as of now, Let’s try doubling the size of tables and see how it affects the result.