← Back to project catalogue
GP-CS-1S7MWF3Computer ScienceOpen for request

Database query performance laboratory

A controlled database laboratory for running reproducible workloads and comparing query plans, indexes, execution time, and resource behaviour.

  • Nuxt
  • Vue
  • FastAPI
  • PostgreSQL
  • Python
  • Docker

Project definition

Problem statement

Database performance is often taught through isolated SQL examples without controlled data distributions, repeated measurements, or direct comparison of execution plans. Small query changes and indexes can produce very different behaviour as data volume changes.

The engineering problem is to create an isolated laboratory that prepares reproducible workloads, executes bounded experiments safely, parses PostgreSQL plans, and records enough context to compare results correctly.

Project objectives

  • Create repeatable schemas, datasets, distributions, and query workloads.
  • Run bounded queries and capture structured execution plans and metrics.
  • Compare performance before and after index or query changes.
  • Explain scans, joins, sorting, estimates, and actual row counts from the captured plan.
  • Measure reproducibility, execution isolation, plan parsing, and workload scaling.

System design

System modules

01

Dataset generator

Creates versioned schemas and deterministic data distributions at selected scales from prepared definitions.

02

Workload runner

Executes bounded query sets with warm-up, repetition, timeout, transaction, and cache-condition controls.

03

Plan collector

Captures PostgreSQL EXPLAIN output in structured form with planning time, execution time, buffers, rows, and costs.

04

Plan analyser

Builds a plan tree and highlights scans, joins, sorting, estimates, actual rows, and likely bottlenecks.

05

Index experimenter

Creates approved indexes in an isolated experiment, reruns the workload, and compares plans and measurements.

06

Experiment recorder

Stores dataset version, query, database settings, repetitions, plans, metrics, and notes for later comparison.

Methodology

System workflow

  1. 01
    Select experiment

    The user selects a prepared schema, dataset scale, workload, and database configuration.

  2. 02
    Prepare database

    The system creates an isolated database, loads deterministic data, and records the environment.

  3. 03
    Run baseline

    Queries run with bounded repetitions while plans, timing, rows, and buffer measures are captured.

  4. 04
    Apply change

    An approved index or query variation is applied to the isolated experiment.

  5. 05
    Compare

    The workload runs again and the system presents plan and metric differences with the full experiment context.

Demonstration scenario

A prepared orders dataset is generated at two scales. A join and filter workload runs without a supporting index, and the plan tree and measurements are recorded. A composite index is added, the workload runs again, and the system compares scans, row estimates, buffers, and execution time using the same experiment settings.

Engineering

Technical architecture

Web application
Nuxt and Vue for experiment selection, SQL display, plan trees, metric comparison, and report export.
Experiment API
FastAPI for dataset preparation, bounded execution, plan collection, experiment state, and result retrieval.
Database engine
Isolated PostgreSQL instances or schemas with prepared roles, statement timeouts, transaction controls, and resource limits.
Plan processing
Python parsing of structured EXPLAIN output into nodes, relationships, estimates, actual measures, and comparison records.
Reproducibility
Versioned schema definitions, deterministic data seeds, recorded settings, repeated runs, and exported experiment manifests.
Delivery
Docker-based laboratory setup with prepared datasets and experiments that can be recreated on another machine.

Testing

Evaluation

Evaluation measures

  • Dataset reproducibility from the same schema definition and seed
  • Correct parsing and display of prepared execution plans
  • Consistency of repeated timing and buffer measurements
  • Isolation and timeout enforcement for invalid or expensive queries
  • Accuracy of before-and-after index comparisons
  • Laboratory setup time and experiment runtime across dataset scales

System boundaries

  • Experiments run only against isolated prepared databases and datasets.
  • The interface does not provide unrestricted access to production databases.
  • The initial implementation focuses on PostgreSQL execution plans and behaviour.
  • Plan explanations are based on captured measurements and do not replace database-administrator review.

Included

  1. 01Schema, workload, query, and experiment interfaces
  2. 02Controlled PostgreSQL execution and EXPLAIN plan analysis
  3. 03Index comparison and metric recording modules
  4. 04Prepared datasets, experiments, and reproducibility results
  5. 05Complete source code in a private GitHub repository
  6. 06Complete project documentation with synopsis, abstract, methodology, database design, experiment results, screenshots, and conclusion

Project record

No buyer information is collected on this page.

Permanent project ID
GP-CS-1S7MWF3
Catalogued
21 Aug 2026
Completed
Pending
Verified
Pending
Demonstration
Added when ready

Handover

After purchase

  1. 01
    Payment is confirmed

    The project is marked unavailable and cannot be purchased again.

  2. 02
    Repository access is granted

    The buyer's submitted GitHub account receives access to the private repository.

  3. 03
    The purchase record is delivered

    The certification sheet is prepared from the reviewed buyer details and sent privately by email.