2

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

  1. 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.

  • 1
    In general, you want your client as close to the database as possible. Latency and bandwidth limiting are likely to cause these kinds of errors, and there just isn't all that much that can be done. – Vince Mar 15 at 20:45
  • Even with a "low latency"? 83ms seems to be little for impacting QGIS with more than 45 sec of waiting, you think? maybe something underlying between the way QGIS connect to PostGis it's too sensitive to latency. But it seems to be very extreme – Eduardo Mar 15 at 21:34
  • What setup is the QGIS client on? are you on a (Solid State Drive) SSD? and strong 100Mb/sec+ internet? – Mapperz Mar 16 at 0:59
  • No, QGIS is in a “old” gaming laptop, no ssd and the internet it’s limited as well. despite this I am not doing anything that in my opinion requires a very high performance, QGIS works excellent and is fast in my PC or in a local PostGIS. It’s when it’s go to the cloud when the slowness appears. – Eduardo Mar 16 at 16:56
  • Cloud computing requires 100Mbit network linkage. RDS requires more. I've had cloud projects tottering on the edge of failure because the network IOPS was under 4000. – Vince Mar 17 at 12:40
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.