Is there any setting that makes it possible to cache features from a PostGIS layer? (or any other layer)

For example:

  • I add 20 layers from postgres
  • then for every one of them set symbology
  • and after this do filtering by attributes

Now if I pan across the map here and there, it starts re-reading points from Postgres db (I think). So, can QGIS cache data to memory, so it doesn't have to keep reading from the database?

  • Where is your postgers server? Local machine, on a server somewhere? – Matt Oct 28 '16 at 10:45
  • For now on my local machine. Later it goes to server. – MikaMikuMik Oct 28 '16 at 10:55
  • Your main goal is browsing data only or editing ? – Shiko Oct 28 '16 at 11:04
  • Browsing only, and complex styling may be – MikaMikuMik Oct 28 '16 at 11:49

You are going to get a lot of comments and answers regarding tuning of the PostgreSQL database, which you should do provided you have control over the database.

But there are situations where local caching is desired, such as when you don't control the database and can't tune it. Or you have slow network connectivity between your client and the database.

I don't think there is anything like ArcMap's feature cache (which I think is what you are asking for in the QGIS software): http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-arcmap/working-with-the-feature-cache.htm

You might look at the Offline Editing plugin which will cache that data into a sqlite database locally. If place your sqlite database on an SSD, that might be good enough.

Or mount a RAM disk and put the sqlite.db on that.

enter image description here

enter image description here

enter image description here

  • Wow, you're right. Right now we use ArcGIS server for production with Oracle through ArcSDE, and it performs rather slowly (with many layers and diffrent styles on it). So we decide to move on desktop app which can use user machine and his RAM and CPU time instead ArcServer which use one server machine for many users queries. That's the main idea with QGis (send features from PostGis to clients only, which they can view in QGis). Thanks for pointing to ArcMap cache, never used it. – MikaMikuMik Oct 28 '16 at 11:58
  • hm..test it with ArcMap 10.2.2 with 5 layers, performs badly and Data Definitions broken with invalid SQL Statement. Bad. – MikaMikuMik Oct 28 '16 at 12:05

Instead of memory caching, you could try creating spatial indexes on the tables to display. This would certainly boost the rendering.

The background documentation is here: http://revenant.ca/www/postgis/workshop/indexing.html

plus here a well described solution similar to yours: http://dragons8mycat.com/2015/05/why-spatial-indexes-are-really-important/

  • Indexes exists of course. For every table with geometry. – MikaMikuMik Oct 28 '16 at 9:58

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.