Re: cacheing metadata - Mailing list pgsql-jdbc

From Oliver Jowett
Subject Re: cacheing metadata
Date
Msg-id 40BF985F.1000802@opencloud.com
Whole thread Raw
In response to cacheing metadata  (Dave Cramer <pg@fastcrypt.com>)
Responses Re: cacheing metadata
List pgsql-jdbc
Dave Cramer wrote:
> There is some interest in caching meta-data. I am wondering what the
> community thinks of the idea.

Are there really metadata-query-heavy apps out there that would benefit?
  Do you have an example of one? I'd have thought that most if not all
of the metadata-related tables would all be sitting in cache anyway.

It seems like this is a special case of the more general "I run
expensive queries and don't want to rerun them to pick up changes". And
a very special case at that -- metadata doesn't usually change very often.

If the app is essentially a UI frontend I'd say you could get away with
caching results in the app (not the driver) and having a "reload" button.

If the app really needs up-to-the-moment metadata state to control
whatever actions that follow, it'll need to hit the DB with the full
metadata query while in a transaction to get the right isolation
characteristics, won't it?

> Notionally it would be used to speed up DataBaseMetaData queries. It
> will require some work on the backend to create triggers on schema
> changes. This is being considered as we speak.

If you're going to be adding support triggers to every DB anyway, why
not bite the bullet and do a stored proc that maintains a cache table of
materialized metadata, or something similar?

-O

pgsql-jdbc by date:

Previous
From: Jan Heise
Date:
Subject: Re: int4/int8 problem with jdbc was Re: "Fix" for INT8 literals
Next
From: Oliver Jowett
Date:
Subject: Re: cacheing metadata