QGIS and remote PostgreSQL/PostGIS (in cloud) slow performance

Question

I am using QGIS 3.28.11 version and in backend I am using PostgreSQL 15.0 with PostGIS 3.3.3. I have total 36 layers which contained in 3 groups: A, B, C. Now X user has read only access on all layers but can perform operation on all A group layers. I am facing below issue:

1. First of all when I tried to open my project .qgs file then it took around 3 minutes to load all layers.
2. When I am trying to place a Polygon feature then it took around 50 seconds and goes into not responding state.

Note** My QGIS application is on my local machine but PostgreSQL with PostGIS on a cloud server (in US zone) whose latency is 246ms. But when I am using my local PostgreSQL database then it is very fast, and latency is around 0.245ms. How can make a feature placement so smooth that it directly syncs with cloud database without too much delay.I am using a .qgs file where all layers are present and there is database connection string inside the .qgs file.

• Mapperz: are you using a vpn?
• til_b: Have you tried connecting to the database via pgadmin? Is that also slow?
• Vince: I'd completely expect this outcome. If you use a tightly coupled application pair (database + GIS), they need to be in the same local network. So, either use a cloud-based workstation "near" the database, or don't fall into the remote database trap, and rely on the local database.
• bugmenot123: One can work perfectly fine with a database at such latency. But you need to make sure that potential issues with its setup don't exist. Are you missing ID columns? Did you look around for general tips on postgis+qgis performance and apply them?
• bugmenot123: Project load is faster in newer qgis, if you can upgrade.

Answer 1

One thing I've noticed is if you don't have typmoded tables, QGIS tends to be slow as it tries to inspect the data type of the geometry in the table which often means it's scanning the whole table. That can be really slow across a latent network.

So when you create the tables make sure you are defining your columns like geom geometry(POLYGON,4326), so QGIS can just use the metadata rather than inspecting the tables.

The other option is to check the box in QGIS connection that reads "Don't resolve type of unrestrictied columns", that will prevent it from trying to scan the table of data to figure out the geometry types

enter image description here