The PostgreSQL application I'm currently on is solely based on functions. Every request from the front end maps to a PostgreSQL function call. Many of these functions call other functions. Like: web application server calls function A, function A calls function B, function C queries data from table D and E and so on. Now, I'm running into big performance issues. While anyway trying to optimize the functions themselves, I'm wondering if query caching might help me.
The problem is: The cache mechanism should analyze which tables are actually concerned by a certain function call and thus refresh or invalidate the cache entry if one of these tables is updated.
Let me give you an example. Consider functions like this:
CREATE FUNCTION public.pets_get_all(user_id integer) $BODY$ SELECT dogs_get_all($1) AS dogs $BODY$ LANGUAGE sql IMMUTABLE;
The dependent functions being
CREATE FUNCTION public.dogs_get_all(user_id integer) $BODY$ SELECT id, name FROM dog WHERE user_may_read_dog($1,dog_id) $BODY$ LANGUAGE sql IMMUTABLE; CREATE FUNCTION public.user_may_read_dog(user_id integer, dog_id integer) $BODY$ SELECT owner_id = $1 FROM dogs WHERE dog_id = $2 $BODY$ LANGUAGE sql IMMUTABLE;
Let's say, for the first time
pets_get_all is executed with 1337 as the parameter, the query works like normal and it returns.
+----------+-----------+ | ID | Name | +----------+-----------+ | 1 | Bella | | 2 | Max | +----------+-----------+
So, for the next time
pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus, it will be pretty much faster.
However, when the dog with the id 2 is updated, the cache entry will be updated too (taking some time of course). So, the cache will be always up to date. Of course, changes in the dog with the id 3 will not affect the cache entry for pets_get_all(1337)`, since the user the id 1337 may not read dog 3.
Does such a thing exist?
I had a rough look into "PostgreSQL Query Cache" ( https://groups.google.com/forum/#!forum/pqc-dev ) However, it seems outdated and the expiration of cache entries is only time-based (which is not what I want).