CODEWITHSUNDEEP
sqlperformancepostgresqlcaching
jcr
jcr

Reputation: 1015

Make an SQL request that builds a 'result cache' without returning results

I have a stateless webserver that requires 2 sets of user input to do a computation:

Page 1: GET INPUT A
Page 2: GET INPUT B
Page 3: Results calculated form user input A and B

It so happens that the bottleneck in my application is a lookup related to user input A.

As a speed up hack I make the SQL request on A that "Page 3" later does, while I wait for the user to input B such that when the user clicks submit on 'Page 2', the lookup result from 'data A' is already cached (saving impatient users 2-5 seconds).

My question

Is it possible to make my SQL lookup in such a way that the server does the query and caches it with out returning anything, as I only need it to be in the cache to make the final request 2-5 sec faster.

Upvotes: 0

Views: 119

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

If the result is not big the best would be to save the user state in a server side session.

Update:

That is one of the cases (multiple web servers vs single db server) where a db stored session fits.

Save the result:

insert into temp_result (session_id, a, b)
select %(session_id)s, a, b
from t

Retrieve it:

select a, b
from t
where session_id = %(session_id)s

When the session expires or after a timeout delete it.

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324385

PostgreSQL doesn't have a result cache, so you can't pre-warm it.

It does have a disk cache. To pre-warm that, you can just run SELECT statements that discard the results. Use a PL/PgSQL PERFORM statement, a pointless aggregate, etc. A search for "postgresql pre-warm cache" may be informative.

You may also want to look into materialized views. PostgreSQL doesn't support materialized views yet, but you can simulate them with triggers and scripts. There are patches in progress to add materialized view support in progress too.

Upvotes: 2

Related Questions