postgresql-benchmarks

Benchmarking Query Times

VM Environment

• Azure VM - Standard B2ms (2 vcpus, 8 GiB memory)
• Disk - Premium SSD LRS - 30 GB

Compose File to Setup Database

version: '3.8'
services:
  postgresql:
    image: postgres:15-alpine
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - '5432:5432'
    volumes:
      - /data/postgresql:/var/lib/postgresql/data

CREATE TABLE public.school (
id serial,
PRIMARY KEY (id),
name varchar(255)
);

CREATE TABLE public.student (
id serial,
name varchar(255),
school_id int,
PRIMARY KEY (id),
FOREIGN KEY (school_id) REFERENCES  school(id)
);

CREATE TABLE public.teacher (
id serial,
name varchar (255),
school_id int,
PRIMARY KEY (id),
FOREIGN KEY (school_id) REFERENCES  school(id)
);

INSERT INTO school (id, name) VALUES (generate_series(1, 150000), 'default');
INSERT INTO teacher (id, school_id, name) VALUES (generate_series(1, 350000), floor(random() * 150000) + 1, 'default');
INSERT INTO student (id, school_id, name) VALUES (generate_series(1, 15000000), floor(random() * 150000) + 1, 'default');

explain analyze select * from student where school_id = 3567;

explain analyze select * from teacher where school_id = 3567;

EXPLAIN ANALYZE INSERT INTO student (id, school_id, name) VALUES (15000001, 3567, 'default');

EXPLAIN ANALYZE INSERT INTO teacher (id, school_id, name) VALUES (350001, 3567, 'default');

Now, let’s create indexes and see how it impacts query times.

CREATE INDEX student_school_id ON student (school_id);

CREATE INDEX teacher_school_id ON teacher (school_id);

explain analyze select * from student where school_id = 3567;

explain analyze select * from teacher where school_id = 3567;

EXPLAIN ANALYZE INSERT INTO student (id, school_id, name) VALUES (15000001, 3567, 'default');

EXPLAIN ANALYZE INSERT INTO teacher (id, school_id, name) VALUES (350001, 3567, 'default');

Now, let’s try to see response times in scenario where we have partitioned it on the basis of school_id

Note: You will have to use PostgreSQL with PgPartman Setup (Dockerfile)

CREATE TABLE public.school (
id serial,
PRIMARY KEY (id),
name varchar(255)
);

CREATE TABLE public.student (
id serial,
name varchar(255),
school_id int,
FOREIGN KEY (school_id) REFERENCES  school(id)
) PARTITION BY RANGE(school_id);

SELECT partman.create_parent('public.student', 'school_id', 'native', '1000');

CREATE TABLE public.teacher (
id serial,
name varchar (255),
school_id int,
FOREIGN KEY (school_id) REFERENCES  school(id)
) PARTITION BY RANGE(school_id);

SELECT partman.create_parent('public.teacher', 'school_id', 'native', '1000');

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

INSERT INTO teacher (id, school_id, name) VALUES (generate_series(1, 350000), floor(random() * 150000) + 1, 'default');

CALL partman.partition_data_proc('public.teacher');

INSERT INTO student (id, school_id, name) VALUES (generate_series(1, 15000000), floor(random() * 150000) + 1, 'default');

CALL partman.partition_data_proc('public.student');

CREATE INDEX student_school_id ON student (school_id);

CREATE INDEX teacher_school_id ON teacher (school_id);

explain analyze select * from student where school_id = 3567;

explain analyze select * from teacher where school_id = 3567;

EXPLAIN ANALYZE INSERT INTO student (id, school_id, name) VALUES (15000001, 3567, 'default');

EXPLAIN ANALYZE INSERT INTO teacher (id, school_id, name) VALUES (350001, 3567, 'default');