AlexsLemonade / AlexsLemonade/refinebio

Look into autovacuum's behavior for downloader_jobs and original_files tables

Open
#2,874 3 comments 0 reactions 1 assignee Claimed by @kurtwheeler View on GitHub
exploration
Dominant language
Python
Stars
135
Forks
21
PR merge metrics
No merged PRs in 30d

Description

### Context

In https://github.com/AlexsLemonade/refinebio/pull/2867 @wvauclain wrote:

> A last thing that we could try is to [manually vacuum](https://www.postgresql.org/docs/current/sql-vacuum.html) some of the worst tables, like the processor jobs, downloader jobs, and samples. I have a feeling that these tables are pretty fragmented because we delete things from it so often when unsurveying experiments. A full vacuum requires writing out a new database file and copying everything over while the database is fully locked, though, so I am not sure about the RDS costs associated with that or what the downtime would be.

### Problem or idea

We have autovacuum on by defaulting and it is running for some tables. It doesn't seem to have triggered for the original_files nor downloader_jobs tables. I'm not sure if this is expected or not. I started looking into it using this query:
```SQL
SELECT * FROM pg_stat_all_tables where relname not like 'pg_%' order by last_autovacuum desc;
```

but I'm not sure what most of the columns returned mean, only that `last_autovacuum` being null must mean that a table hasn't ever been autovacuumed.

### Solution or next step

Look into https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/, https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html#Appendix.PostgreSQL.CommonDBATasks.Autovacuum.NeedVacuuming, and the results of that query to see if there's something we can do to improve the query performance of those tables, perhaps by getting autovacuum to trigger for them.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.