Skip site navigation (1) Skip section navigation (2)

Re: Query meltdown: caching results

From: Richard Huxton <dev(at)archonet(dot)com>
To: Gordon <gordon(dot)mcvey(at)ntlworld(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Query meltdown: caching results
Date: 2008-02-26 12:03:53
Message-ID: 47C40029.3060307@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
Gordon wrote:
> I need to find a way of not running queries that I don't need to,
> either in the PHP script, or in the Postgres database.  What I need is
> for a result set to be cached somewhere, either by Postgres or PHP, so
> when it sees the same query again in a given session it just returns
> the previously fetched result set.  The cache also needs to be able to
> disregard its cached result sets when an event that changes a table
> occurs (insert, update, delete, etc).

It's the second part that's fiddly (in the general case) if you do it in 
  PHP.

If you're looking for a large-scale cache then memcached would suit your 
needs. There's an add-on for PG that can keep it notigi
   http://pgfoundry.org/projects/pgmemcache/

> On the PHP side I've written a simple Database class that extends PDO
> and that I use in its place.  It's a simple class that basically I use
> to allow me to nest calls to beginTransaction(), commit () and
> rollback () (It only starts an actual transaction of a counter is 0.
> Otherwide it just increments the counter.  Commit only actually
> commits when the counter is 1, and decrements it otherwise.  Rollback
> sets an error flag and decrements the counter, and only rolls back
> when the counter is 1.  If the error flag is set then commit will
> actually roll back instead.  )
> 
> My options are, as far as I can tell,
> 
> 1) replace the Database PDO extending class with something else that
> provides query results caching in PHP, or

There are a whole bunch of Pear classes for caching - Cache_Lite is 
simple to plug into an existing structure.

> 2) get Postgres itself to cache the results of queries to avoid
> running them repeatedly during a session.
 >
> I seem to remember MySQL providing some kind of results caching, can
> Postgres do the same?    Has anyone else run into similar problems and
> how did they overcome them?

No, but if you're serious about the caching you'll want to do it well 
above the data-access layer.

The main gains I've seen with a simple caching system have been:
  1. Big, static lookup lists (countries, catalogue sections etc).
  2. Whole pages / sections of pages
The trick with both is to cache as close to rendering as possible. So, 
the HTML in the case of pages/controls.

Make sure your data-access layer invalidates any relevant cache entries 
and you'll be fine (as long as you don't do any database manipulation 
outside your app - always have an "invalidate whole cache" function / 
script available for this).

Oh, and *do* make sure you've identified real gains first. It's 
distressing to spend two days optimising your caching only to realise 
you've gained 2% because you've missed the real bottle-neck.

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-general by date

Next:From: Gregory StarkDate: 2008-02-26 12:05:15
Subject: Re: autovacuum not freeing up unused space on 8.3.0
Previous:From: Andreas LauDate: 2008-02-26 11:45:53
Subject: Re: syntax error at or near "PROCEDURAL"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group