If you determine that the operation is taking too long, connect to your database and use the commands below for the BTP installation:

1. Connect to sap psql database following article  https://help.figaf.com/portal/en/kb/articles/connect-to-sap-psql

2. Identify Unused Dead Tuples:

   Execute the following query on problematic tables (e.g., tracked_object and blob_store_entry) to identify unused dead tuples. A high number of dead tuples may indicate that many records have been deleted or updated:

   SELECT schemaname,
          relname,
          n_live_tup,
          n_dead_tup
   FROM pg_stat_user_tables
   WHERE relname = 'tracked_object'; -- also check blob_store_entry
   

3. Clean Up Dead Tuples:

   If you find a significant number of dead tuples, run:

   VACUUM FULL ANALYZE irt.tracked_object;
   VACUUM FULL ANALYZE irt.blobstore_entry;
   

   These commands may take some time to execute, but they will reclaim disk space and update query statistics.

4. Assess Disk Reads vs. Memory Hits:

   If problems persist, run the following query to calculate the ratio of disk reads to memory hits. If the memory hit rate is below 80%, consider adding more memory. You may also adjust the PostgreSQL parameters shared_buffers and effective_cache_size.

   SELECT datname,
          numbackends,
          xact_commit,
          xact_rollback,
          blks_read,
          blks_hit
   FROM pg_stat_database;
   

Recommendation: For databases over 13 GB in size, it is advisable to use at least 4 GB of memory and allocate around 20 GB of disk space.