QGIS and remote PostgreSQL/PostGIS slow performance

Question

I'm using a cloud service for hosting my PostgreSQL/PostGIS database.

When connecting with QGIS, its extremely slow.

The observed behavior is:

1. Connecting to PostGIS DB: works great.

2. Adding a layer to the map view: A bit slow but noting to worry

3. Open attribute table: really really slow, up to 45 seconds to open a small table of 100 point (QGIS become freeze and won't respond until the table open)

When Editing a layer in the PostGIS

4. Adding a new feature or editing the values of the attribute table: 45 seconds to get the form for insert the attributes and when saving the edit another 45sec. (sometimes can get worse if the geometry added its complex with alot of nodes its can take up to 3 min)

So I'm investigate a little and make some test, maybe the latency between my client QGIS (my PC) and the PostGis (cloud db) its the problem:

I'm using the Azure Database for PostgreSQL and the server is in US East region, so I did the network test between client - host to see is the latency is the problem:

Using psql:

\timing
SELECT;
\watch 1

I get the following values:

83 - 84ms so it's not that bad, noting to get 45 sec in QGIS and the results are consistent with the ping from my location to the Azure server.

The DB performance looks ok since it's a really small db and queries with pgadmin o psql are fast.

Someone with experience using cloud db for using with QGIS maybe can guide me or give me an insight, maybe I'm missing a QGIS configuration or host server networking config.

Answer 1

As mentioned by malcolm

I did a little research on GitHub (I should have started here, but I did not think it was a bug) https://github.com/qgis/QGIS/pull/9203

The behavior of QGIS 3.4.5 being slow at opening an attribute table in a PostGIS in the cloud of Azure being very slow (36 seconds)

It is a situation in which the developers are working on, it has already been fixed in QGIS 3.6 however, the backport to QGIS 3.4 (LTR) is still needs more tests.

To recapitulate everything discussed in this question: Working with QGIS and a remote PostGIS in a cloud like Azure at a considerable distance (100ms ping to servers) Here are some considerations:

1. you want your client as close to the database as possible, client and host being close to eachother eliminates a lot of bottlenecks when working in a network

2. For high production environments maybe you want to consider a high connection +100 Mbits or 4000 IOPs between the client and the server where the PostGIS is located

About the specific case: QGIS 3.4.5 presents a problem when opening the attribute tables in a PostGIS in the cloud. the fix is expected to come out in the next point release (QGIS 3.6 does not present the problem)

This means that QGIS can be used perfectly with PostGIS in the cloud without the need for a "super" network or "super" server (+ 100Mbits or 4000IOPs), as long as production needs are low. This option of having a PostGIS in the cloud is a viable for small projects.