PostgreSQL analysing and optimization

[bgruening]

Optimize database access times

Supervisor: Björn Grüning For degree: Bachelor/Project Status: Open Keywords: SQL, Python, Optimization

Global Biological/Research context

We are running a huge database to keep track of provenance data of millions of jobs and workflows. This data is stored in a SQL database. To higher the throughput of jobs and offer a better UX to our users, we would like to optimize the queries to our database.

Proposed agenda for the project

1. Analyze and monitor access to the SQL database
2. Propose potential optimizations
3. Optimize queries

Prerequisites

• SQL expertise
• Python, especially SQLAlchemy

Further reading and useful links

• https://www.sqlalchemy.org
• https://galaxyproject.github.io/training-material/topics/admin/tutorials/database-schema/tutorial.html