Mobile Center product is using PostgreSQL 9.6 database to store some information needed.

In general, there is no runtime data, but the management data like:

1. Users (definitions or references if LDAP is used)

2. Connectors properties

3. Devices properties

4. Applications

5. Settings and workspaces information

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. There is a wealth of information to be found describing how to use PostgreSQL through the official documentation. The PostgreSQL community provides many helpful places to become familiar with the technology and discover how it works - this is why we choose it over other solutions.

The ongoing management of Postgres is not part of Mobile Center domain expertize, therefore we suggest to use external and community knowledge for that.

Still, there are some best practices that we can outline as part of ongoing usage of Postgres with Mobile Center.

The most concerning point for the customer is database size and performance.

The most significant contributer to Postres DB size arer an mobile applicaitons - when user uploads an applicaiton to Mobile Center server, it is stored as a BLOB (binary string type) in Postgres DB. 

To determine DB size, the following command can be used: 

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

It is highly reccomended to verify DB size before performing an upgrade to Mobile Center, since it can impact the duration of the upgrade process and even lead to the timeout error in some cases.

The reccomendation is to keep the DB size as small as possible. This can be achieved by removing unnessesary instances of mobile applicaiton - using Mobile Center interface, delete the unwanted uploads from there.

But this will not be enough!

In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so the VACUUM command should be run to do this. The VACUUM command will reclaim space still used by data that had been updated:

VACUUM(FULL) public.file_repository;

To avoid conflicting database updates, or corrupted data, it is preferable to run these commands during a maintenance window when the application is stopped.

In the default PostgreSQL configuration, the AUTOVACUUM daemon is enabled and all required configuration parameters are set as needed. The daemon will run VACUUM and ANALYZE at regular intervals. If you have the damon enabled, these commands can be run to supplement the daemon's work. To confirm whether the autovacuum daemon is running on UNIX, you can check the processlist

$ ps aux|grep autovacuum|grep -v grep
postgres           334   0.0  0.0  2654128   1232   ??  Ss   21Apr19   0:05.63 postgres: autovacuum launcher process  

On UNIX or Windows, you can find the status of autovacuum in the pg_settings database with the query below:

select name, setting from pg_settings where name = 'autovacuum' ;

ANALYZE gathers statistics for the query planner to create the most efficient query execution paths. Per PostgreSQL documentation, accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing. 

ANALYZE VERBOSE [tablename]

The REINDEX command rebuilds one or more indices, replacing the previous version of the index. 

REINDEX DATABASE hp4m

Enjoy.