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).