I believe i is not really possible to connect to the remote DB. Even if it was, I do not see an advantage as compared to doing it locally. In fact if you do it locally you can use any DB analysis tool you like, and you also don't have to worry about locking the DB by running some slow to execute analysis queries.
PS: one final note, the cloud v4 does not automacially vacuum AFAIK, so you might clear up some dead tuples by simply doing a backup&restore.
If you contact Mendix support they can run queries for you on the database. What I've done in the past is run a query like this:
SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t WHERE schema_name NOT LIKE 'pg_%' ORDER BY table_size DESC;
in order to gain more insight into the usage of my database. You can experiment with this query in a local version of your database to adjust for the result you desire.
Mendix 7 has a java api to execute sql statements on the app database: https://apidocs.mendix.com/7/runtime/com/mendix/datastorage/DataStorage.html
You can run the query mentioned by stefan using this api.