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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
