Postgres DB VACUUM policies

[sarayourfriend]

In #956 @AetherUnbound and I started having a discussion about when, if ever, we run VACUUMs in our databases.

I wanted to move the discussion out of there so that it wasn't buried in a PR as I think it's an important thing to hash out. Once we understand this better, we'll open issues to implement the policies for each of our PG databases.

It struck me that in the API, because we currently completely recreate and destroy the biggest tables during data refresh, we might be able to avoid certain operations that would otherwise require long table locks. Adding a column, for example, if timed exactly with the data refresh so that the new incoming media tables has the new column, but the previous tables were left with the old schema, would avoid needing to add the column to an existing table with hundreds of millions of rows.

I'd also like to look and see if regularly deleting the tables has any implications for whether we need to vacuum them at all. Data refresh might effectively do it for us when it removes the old tables, but this is something that needs to be researched.

[zackkrida]

Autovacuuming should be enabled by default on AWS RDS. Instinctively it makes sense to me that manually running VACUUM after data refreshes could be beneficial.

We may want to look into ways of monitoring or getting more insights into the automatic vacuuming process.

[sarayourfriend]

I wasn't aware of that feature of RDS. Here's a blog post/documentation (I can't tell which) for others that describes it: https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/

It includes instructions for monitoring it. That seems like something we could explore doing as well if we're looking into this.

[AetherUnbound]

I definitely feel like running VACUUM operations on the catalog are probably higher priority than on the API for the reasons you described. There are also some VACUUM variants that don't require a full table lock, which might be good to do regardless (e.g. VACUUM ANALYZE, which would reclaim some space and update table statistics).

[sarayourfriend]

Should we look into whether RDS autovacuuming is working? If it is, then identifying gaps that exist between it and what we want is a better approach, in my opinion. That way we're not accidentally duplicating something that is already happening based on loose expectations about disk usage. Even if we know something is wrong (more than a hunch, which is what I understand the current situation to be, but I could be wrong), even then we should still make sure we're not duplicating autovacuuming and verify that it is working as it is meant to.

[AetherUnbound]

That's a great idea!

[zackkrida]

Here are the results of SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_all_tables WHERE schemaname = 'public'; on the prod API database:

relname	last_vacuum	last_autovacuum
django_migrations
django_content_type
auth_permission
auth_group
auth_group_permissions
auth_user
auth_user_groups
auth_user_user_permissions
django_admin_log
imagelist
imagelist_images
tag
content_provider
api_matureimage
oauth2_provider_grant
oauth2_provider_refreshtoken
oauth2_provider_idtoken
nsfw_reports_audio
api_deletedaudio
api_matureaudio
audiolist
audiolist_audios
oauth2_provider_accesstoken		2023-04-03 07:32:32.687107+00
audio		2023-04-02 00:05:31.323546+00
audioset		2023-04-02 00:05:10.661946+00
django_session		2023-03-09 17:49:26.622527+00
image		2023-02-25 03:27:05.282719+00
nsfw_reports		2023-02-17 20:46:50.532858+00
api_throttledapplication		2023-02-15 00:34:39.670755+00
api_oauth2verification		2023-02-09 09:22:27.056792+00
api_oauth2registration		2023-02-08 03:31:07.702391+00
api_audioaddon		2022-09-07 21:16:40.067901+00
api_deletedimage		2022-01-06 22:04:13.725056+00

This does suggest autovaccum is enabled. Not sure about the catalog but it's using the same parameter group postgres13.default where vaccum settings are configured, so it should be working there too.