In  issue 243769  , a config setting was landed to have SQLite not share cache pages behind the scenes.  This setting is blocking  issue 652359  for me, due to  issue 533682  .

I believe the idea behind SQLITE_SEPARATE_CACHE_POOLS was that it caused the system to retain the sum of cache_size settings for databases with a given page_size.  I'm going to re-create the thinking as best I can.

I believe the issue is that cache_size is implemented in two parts.  At the pcache level, it can only have cache_size pages for a specific cache.  But pages which are not pinned (either as part of a transaction, or because SQLite is reading them) are returned to the cache group, where they can be purged at any time.  The group doesn't evict pages until sum(cache_size) is passed.

The group cache has benefits at two levels.  If a page is found in the cache which was a previous version of the target page, and the db hasn't been modified, that page can be reconstituted directly back into the cache.  If a page cannot be found, then an unused group page can be re-used to save malloc and setup, with the target page read into it from disk.

With mmap turned on, sqlite3_db_release_memory() is called to prevent SQLite from retaining pages which could be easily be read back from the memory map.  The implementation at the group level sets the group max to 0 and trims pages, then sets it back.  Since we use separate groups for caches, this works to release the cached pages.  I believe that simply removing the SQLITE_SEPARATE_CACHE_POOLS patch would cause the page group to only be useful for malloc reduction, because the idle pages could only be re-used when SQLite is going to overwrite them.  Unfortunately, this maybe would be the worst of all worlds - as the cache wouldn't be benefiting from the page group but the page group would revert to retaining a potentially large number of pages.

I suspect the only real solution might be a custom pcache which simply tosses unpinned pages.  Unfortunately, I don't think that helps my fix to  issue 533682  (I was attempting to fix using sqlite3_release_memory(), which is a noop for our current system).