In these two videos I discuss performance considerations when choosing a hosting service for your PostGIS database, how to determine if you should invest in higher download speeds on the client side or better performance on the server side, and strategies for mitigating slow response times.
I benchmark response times for screen redraws in QGIS when using identical data from PostGIS databases running on localhost and two different remote hosting services. Tests were conducted from two locations with download speeds that varied by a factor of 2 and yet the response times were remarkably consistent. I concluded that in this case at least i would be better off investing in more performance on the server side as download speeds had no effect.
I did attempt to upgrade my service at A2 hosting from their Swift package to their Turbo package which claims performance boosts of up to 20x. This occurs through a combination of improved server software, more server resources, fewer users per server, and caching. I did not expect to actually see 20x faster response times but I would have been happy with speeds 3 or 4x faster. Instead a week has gone by and that service is still not working.
They do mention that their service is not compatible with Django, a python based web framework, and I suspect that the issue is that the QGIS DB Manager is written in python. The issue I am having is that I cannot see my PostGIS layers in QGIS, and I cannot connect at all in pgAdmin 4. I do see them in phpPgAdmin so I know that they are there. At this point I cannot recommend A2 hostings Turbo service for GIS applications. I am still working with their customer support to find a solution although that process is very slow. I will keep you informed if there is any progress. I also hope to test a PostGIS deployment in the Amazon cloud soon.
The first video shows the results of performance testing and the second video discusses strategies for mitigating slow connection speeds.
See Part 1
See Part 2
See Part 3
See Part 4
If you want to learn more about PostGIS please check out my course Introduction to spatial databases with PostGIS and QGIS, with over 11 hours of content, available now at Udemy.com for $20. And if you are interested in my other courses on Web GIS and QGIS please check out my course page on this blog.