Run reindex / vacuum periodically to ensure queries perform well

[1t5j0y]

In avniproject/avni-media#179, noticed a drastic improvement for base search queries from media app (queries media and address table) after executing

reindex (verbose) table goonj.address;
reindex (verbose) table goonj.media;
VACUUM (FULL, ANALYSE) goonj.address;

Analyse:

1. Further analyse optimal frequency and if reindexing is required or vacuuming is sufficient.
2. Special attention might be required on recreate ETL schema scenarios where there is heavy deletion and creation.

AC:

1. Execute reindexing / vacuuming periodically on large ETL tables to ensure performance is good.

Solutioning:

SELECT schemaname, relname, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE relname = 'address' and schemaname = 'goonj';
exposes when last analysis happened and can be used in the logic to determine whether vacuum analyse needs to be executed.

Out of scope:

• Tables other than media viewer tables. We can see the impact and then do it on other tables as needed.

[mahalakshme]

@1t5j0y Why is this in 'Analysis'? I think you told something, forgot.

[1t5j0y]

@1t5j0y Why is this in 'Analysis'? I think you told something, forgot.

Observing performance pattern / deterioration before deciding on fix and deciding on what should trigger the vacuum. Shouldn't be blocker for 10.2 since we can run vacuum on demand in the meanwhile if required.

[1t5j0y]

After observing goonj media search performance for a while, suggest not playing this story immediately because:

1. no significant deterioration found over time; performance seems to only vary based on load
2. has the potential to cause more problems (locking of tables during vacuum, reindex) and the processes take a few minutes to run.
3. autovacuum is already running as expected
4. determining when this should execute is not so straightforward.
5. previous significant deterioration had occurred due to large change in address table which autovacuum would have taken care of.

If there are similar cases in the future, we can address on a case by case basis if there is a support ticket.