AlexsLemonade / AlexsLemonade/refinebio
Look into autovacuum's behavior for downloader_jobs and original_files tables
- 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.