Results caching

[mkleczek]

Problem

Response caching can be a very effective way to shorten (sometimes significantly) response times and - what's even more important - to take load off the database server.

Currently PostgREST does not provide any way to cache responses. In line with the philosophy of "do one thing and do it well" response caching is supposed to be handled by either:

• upper layers (ie. a caching proxy) - similar to eg. TLS. To support caching users can set appropriate response headers in pre-request or rpc functions.
• "lower-middle" layers (ie. query result caching by some smart connection pooler)
• lower layer (ie. by adding RAM to the database server and using it as either shared buffers or OS page cache)

Unfortunately, JWTs in practice make it impossible to cache anything by caching proxies:

• RFC 7234 explicitly forbids implicit caching of requests with Authorization header unless explicitly allowed by response headers https://datatracker.ietf.org/doc/html/rfc7234#section-3.2
• Even if Authorization header value is included in the caching key, the cache is not effective due to JWTs lifetimes. It would be much better to have some claims (such as role) be a part of the caching key, so that requests with different JWTs representing the same user could be served with the same cached response. But that would require proxies to be able to validate JWTs and to understand the same claims that PostgREST understands.

Caching by "lower-middle" layers is problematic due to SQL (and especially PostgreSQL dialect) being a very rich language with semantics that is not trivial to handle properly by any middle man.
HTTP/REST layer is much better suited for caching decisions as HTTP methods have well defined and narrow semantics (which is used by PostgREST already: HEAD/GET methods are executed in read only transactions).

Caching by lower layers (ie. "below" the DBMS) is very effective in hiding IO latency but does not allow caching compute intensive results (eg. joins, sorting, aggregates etc.).

Solution

Taking the above discussion into account, it looks like the best place to handle response caching is PostgREST - it has all the knowledge required to make proper caching decisions.

First version should implement in-memory cache of responses using SIEVE cache implementation we use for JWT caching.
In the future we can think of implementing a two-level caching allowing users to configure an external caching solution (such as Memcached or Redis) in addition to the in-memory cache.

We could also implement cache invalidation based on LISTEN/NOTIFY since we are already listening on pgrst channel.

We need to think about a way for the users to provide caching directives in their responses. Besides the standard caching headers (ie. Cache-Control and other headers) we also must allow to somehow provide analogous information about JWT claims affecting caching decisions.

Related issues

#133 Implement Vary header (while important, it does not address the issue as there is no way to specify a JWT claim in Vary header). There is also a No-Vary-Search header (marked as experimental in MDN)
#1089 Shortcomings in HTTP compliance (contains discussion about Range and Content-Range headers and their impact on caching)

[steve-chavez]

@mkleczek WDYT about the option of caching based on etag #1176 (comment)?

[mkleczek]

@mkleczek WDYT about the option of caching based on etag #1176 (comment)?

To be honest I am not sure if etags and caching are related: in my understanding they are totally orthogonal.
To check if etag changed you have to hit the database, which means you cannot rely on any cached response.

Etags are only good for avoiding sending responses if requested etag matches current database state but can not affect caching decisions (they are helpful with subsequent retrievals of expired cache entries).

Response cache is supposed to serve responses without hitting the database at all. To implement it and to implement sensible cache eviction it has to have information about cached response validity.
We need to make sure a particular request can be served from the cache without hitting the database - otherwise it does not make sense to have the cache in the first place.

[steve-chavez]

Not opposed to doing caching inside PostgREST, reusing the SIEVE cache sounds like a great idea. What we need to make sure is that the cache configuration can be done through SQL (LISTEN/NOTIFY as you mentioned or perhaps transaction variables with set_config).

[mkleczek]

Not opposed to doing caching inside PostgREST, reusing the SIEVE cache sounds like a great idea. What we need to make sure is that the cache configuration can be done through SQL (LISTEN/NOTIFY as you mentioned or perhaps transaction variables with set_config).

Today you can add Cache-Control, Vary and other related headers with set_config.
We could additionally provide a way to set a JSPath expression interpreted as a kind of Vary-Claim header.

There is a subtle interaction required between interpretation of cache related output variables and headers set by PostgREST on the response.
For example: presence of this Vary-Claim would require PostgREST to make sure that any returned Cache-Control specifies private (as any other upstream proxies won't understand Vary-Claim so we must ensure they won't cache the response).
We should also think about Vary header we return: we shouldn't blindly set it to whatever was provided with set_config.

In addition to controlling cache entry expiration with Cache-Control header we could handle NOTIFY messages to invalidate cache entries. Granularity is subject for discussion: from coarse grained eviction of responses per table/view/function (need to also handle resource embedding properly) to possibly fine grained eviction of responses per primary key.

The subject is really broad - as someone said "Half of computer science is caching" and "there are only two hard problems in computing, one of them is cache invalidation". I guess we should start with something simple and probably conservative in terms of cache invalidation (it is better to loose some performance than to serve invalid content). Bare minimum IMHO is to handle Cache-Control header and Vary-Claim setting.

[steve-chavez]

We should also only allow caching if db-max-rows is set IMO, to avoid caching big results.

[wolfgangwalther]

It would be much better to have some claims (such as role) be a part of the caching key, so that requests with different JWTs representing the same user could be served with the same cached response. But that would require proxies to be able to validate JWTs and to understand the same claims that PostgREST understands.

I have implemented the following in a project:

• files are stored in the database
• nginx caches these files
• nginx hits PostgREST with auth requests only - once these succeed, nginx returns the response itself

These auth requests currently hit the database.

I wonder whether there could be a simple auth endpoint that will only validate the JWT, but not do anything else, aka never hit the database? This could allow setting up nginx caching with authentication by PostgREST semantics - without adding a full caching implementation to PostgREST.

[steve-chavez]

I wonder whether there could be a simple auth endpoint that will only validate the JWT, but not do anything else, aka never hit the database?

@wolfgangwalther Would that be like the token introspection endpoint idea on #1402?