Can QGIS cache a PostGIS layer for faster rendering?

Question

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?

Answer 1

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.

Answer 2

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: Spatial Indexing

Here is a well described solution similar to yours: Why Spatial Indexes are REALLY Important