On Wed, Aug 7, 2024 at 3:29 PM Rui DeSousa <rui.desousa@xxxxxxxxxx> wrote:
On Aug 7, 2024, at 9:24 AM, jacob ndinkwa <jndinkwa@xxxxxxxxx> wrote:To schedule a pg_repack job using pg_cron on an Amazon RDS for PostgreSQL instance, you need to follow a few steps. However, it’s important to note that pg_cron is only supported on certain versions of Amazon RDS, and pg_repack is also a separate extension that must be installed and enabled.
Is scheduling pg_repack just a bad idea and just introducing just more bloat? Why not just tune auto vacuum?80/20 rule… most schemas are going to have their large/hot tables, etc and data has a natural life cycle. If you have a heathy application then bloat is not an issue as free space is used by new tuples. Each database has a data flow to it depending on the maturity and nature of the application/database. Exiting tuples make room for new tuples, etc.If your have to vacuum full / pg_repack your tables on a scheduled bases then I think there is something very wrong with your application.Pg_repack will do more harm in the long run. i.e. the entire time pg_repack is running xmin is frozen thus creating more bloat everywhere else!Bloat is overrated; especially in a transaction system where all your data access patterns should be well defined and not doing full table scans. Just focus on identifying bloated indexes periodically and rebuilding those. There should be no need to vacuum full tables under normal circumstances.
For example, I gave a list of tables (all intertwined via FK constraints) to the application support people, and they returned the list stating how many weeks or months of data to retain in each table. Every Saturday night a cron job goes through and deletes the old data from, and then "manually" vacuum-analyzes them.
No bloat...
--
Death to America, and butter sauce.
Iraq lobster!
