Re: Including Snapshot Info with Indexes

On 10/14/07, Hannu Krosing <hannu(at)skype(dot)net> wrote:
>
> Ühel kenal päeval, L, 2007-10-13 kell 17:44, kirjutas Gokulakannan
> Somasundaram:
> > Hi,
> > I went through this article and it was good. Please have a look
> > at it.
> >
> > http://www.databasecolumn.com/2007/09/one-size-fits-all.html
> >
> > This article was written by Michael Stonebraker, considered to be the
> > founder of our database. He has mentioned that the DBMS designed in
> > 1970s haven't changed according to the change that has happened in
> > Hardware landscape.
>
> What has happened in reality, is that the speed difference between CPU,
> RAM and disk speeds has _increased_ tremendously, which makes it even
> more important to _decrease_ the size of stored data if you want good
> performance
>
> > The Vertica database(Monet is a open source version with the same
> > principle) makes use of the very same principle. Use more disk space,
> > since they are less costly and optimize the data warehousing.
>
> MonetDB is not about "using more disk to get better performance", but
> about reducing the need to read unused data and increasing the speed by
> that.
>
> There is also a MonetDB/X100 project, which tries to make MonetOD
> order(s) of magnitude faster by doing in-page compression in order to
> get even more performance, see:
>
> http://homepages.cwi.nl/~boncz/x100.html
>
> http://www.cwi.nl/themes/ins1/publications/docs/ZuBoNeHe:DEBULL:05.pdf

What i meant there was, it has duplicated storage of certain columns of the
table. A table with more than one projection always needs more space, than a
table with just one projection. By doing this they are reducing the number
of disk operations. If they are duplicating columns of data to avoid reading
un-necessary information, we are duplicating the snapshot information to
avoid going to the table.

> Even otherwise we are recommending Indexes with snapshot as an option.
> > We are not replacing the current index scheme. So if someone feels
> > that his database should run on lesser disk space, let them create the
> > normal index. If he feels he can afford to have more redundant disk
> > space, then he can create indexes with snapshots. We are reducing
> > random I/Os at the cost of extra disk space. So definitely that's a
> > good. But tech folks like us can better decide on something based on
> > experiments, as Tom has pointed out. So let's see whether Indexes with
> > snapshot is worth the trade-off in space.
>
> Agreed.

And more one more good news for people, who are following this thread. It
seems like we won't be having a hit on update performance, if the indexes
are not updated. BTStack remains the same for the old and new tuples, if the
index tuple is not updated. But i don't know whether i would be able to put
that tuning(re-using BTSTack) in the first patch

So Indexes with snapshots will be degrading the performance only for deletes
and only those updates, which are updating the index tuple.

I think delete overhead can be ruled out for those who will be working with
partitions, since they usually drop the partitions.

Thanks,
Gokul.

------------
> Hannu
>
>
>
>