Create SOP for updating postgres table statistics

[arnejduranovic]

DevSecOps Issue

Summary

Sometimes, seemingly out of the blue, our queries will start performing poorly (have slow execution times). We have previously determined this is due to our old version of Postgres not running ANALYZE automatically, thus the statistics used by the query planner become out of date and the query planner makes poor decisions on how to execute the query. To resolve this, DevSecOps needs to have a standard operating procedure (SOP) in place to manually run VACUUM ANALYZE on our tables on a monthly basis to ensure our queries are performant.

ATTENTION: We expect this to be a temporary process. Once we update Postgres, the statistics should stay up to date automatically.

Environment

• Local
• Dev
• Stage
• Prod

Priority

• Critical - affecting prod systems
• Major - blocking major functionality, deployment, etc
• Minor - improvements, bug fixes
• Nice-to-have - feature request

Blocks the following

N/A

Contact

Team: Platform

Slack Team Channel: prime-reportstream-pipeline

Slack Contact (Full Name): Arnej

Technical Team Lead: Arnej

Definition of Done

• SOP created and stored in DevSecOps folder in RS Sharepoint (optional)
• SOP Executed for the month of January

Notes

• More info on why we need to run VACUUM ANALYZE manually: https://www.crunchydata.com/blog/insert-only-tables-and-autovacuum-issues-prior-to-postgresql-13\

TODO ARNEJ: Put the tables here