Thread: New feature: cached foreign keys
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. Pro: faster searching for common keys when parent table is constant. Less locks on index. Contra: slower searching when parent table is heavy updated. More memory used for cached ctid's. -- ------------ pasman
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/
Reality is crude, seems than this idea is not as good as i think :( Thanks for answer. 2011/7/9, Craig Ringer <craig@postnewspapers.com.au>: > 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/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman
On 9/07/2011 8:26 PM, pasman pasmański wrote: > Reality is crude, seems than this idea is not as good as i think :( > > Thanks for answer. No worries. For what it's worth, PostgreSQL caches recently used tuples in shared memory anyway. The OS caches disk data in RAM too. So if the foreign key is often checked and frequently used, it will often stay in cache. -- 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/