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.