Re: New feature: cached foreign keys

On 9/07/2011 3:06 PM, pasman pasmański wrote:
> Hi.
>
> Today i have an idea for increase performance of foreign keys. After
> search parent record, store ctid in shared memory. Subsequent searches
> look first to the record at stored ctid, and when it is deleted do
> regular search using index.

How many do you keep cached at a time?

When do you evict the cached ctid from memory?

What about if someone else deletes that parent tuple? Do you notify all
backends whenever any tuple that could potentially be a foreign key
target is deleted so they can check their caches and flush it if it's
present?

This is a *REALLY *HARD* problem unless you can narrow it to a specific
case with clear and simple rules about what you cache, concurrency, how
long something stays cached, etc. The cache you describe superficially
seems like an easy and nice way to do things but in practice this sort
of thing usually works out to be really, really, really complicated.

"There are only two hard problems in Computer Science:
cache invalidation and naming things."

-- Phil Karlton

To learn more about why it's so hard, see:
http://en.wikipedia.org/wiki/Cache_invalidation

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/