Postgres query cache analyzing table basis?

Question

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

Answer 1

Clearing up confusion about IMMUTABLE

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.

That's not true. In fact, even in the same query the result isn't typically "cached": which as far as I know isn't a correct description of what's going on. It's more of a planner reference trick. There is certainly no transactional memoization going on here. These are two different concepts and there is nothing about a function call especially one which may be inlined that implies what you're assuming.

IMMUTABLE and even STABLE does imply that the query-plan itself is cached. This means that future calls within the same transaction will not be re-planned.

CREATE FUNCTION stable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
STABLE;


CREATE FUNCTION immutable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
IMMUTABLE;

Just looking at the timing:

\timing

-- these will take 2 seconds.
SELECT * FROM immutable_foo() AS a, immutable_foo() AS b;
SELECT immutable_foo(), immutable_foo() FROM ( VALUES (1) ) AS t(x);
SELECT immutable_foo()::text || immutable_foo()::text;

-- these will take 1 second.
SELECT immutable_foo() FROM ( VALUES (1),(2) ) AS t(x);

-- but declared as `STABLE` like foo(), it takes two seconds.
SELECT stable_foo() FROM ( VALUES (1),(2) ) AS t(x);

So only under specific circumstances in the same query will a function call get optimized out.. However, you'll never find a situation where this happens across statement boundaries..

As far as I know, the contexts for which an immutable function is optimized away are undocumented. Historically, this is even something experienced folks have struggled with and there is a general try it and see mentality as to whether or not the optimization takes effect.

BEGIN;
  SELECT bar();
  SELECT bar();
END;

I prefer to think of IMMUTABILITY in terms of what it permits, things such as (a) functional indexes, (b) functional index predicates (c) check constraints and the like. They all require a function to return the same arguments given the same inputs to make sense -- and they'll check that they're given an immutable function. But, it doesn't follow that there is memoization that operates in the transaction, session, or server.

PgPool 2

If you do need an actually query-cache you should consider checking out PgPool 2 which is the industry-method of doing this.

It's essentially middleware (or a forward-cache depending on your age).

As it applies to you

Writing a system to exploit undocumented query optimizations is a bad idea. You're better of not doing this at all, stepping back and just using SQL. Sure, the results of a complex query are not cached. But the buffered heap fetches and plans are. If it's in the same transaction it's working off the same data.

I would find weak links in your application and ask questions about how to make them faster. What you're doing seems something akin to pre-optimization.