Re: Status of tablespaces

> > Anyway, the point of my original post is that being able to do
> > stuff like that and have it return near instantly is wonderful
> > when dealing with very large quantities of data. Seriously, try
> > deleting 100M rows without this. With this, it'll happen in less
> > than a second. :)
>
> Without even getting into the index side of things (because I don't
> want to get into a big long explanation here), there's no way it
> could delete all that data that quickly if another table had foreign
> keys referencing it. You have to check *every* other table for
> records matching the ones you're deleting, which at the very least
> means reading all of the ones you're deleting and doing index
> searches or scans on the other tables.

Agreed. With the volume of data necessary to want to use this kind of
a technique, you wouldn't want foreign keys anyway.

> Basically, this looks like PostgreSQL's TRUNCATE, which also ignores
> the FK problems (though it can deal with the indexes, since you just
> delete all of them as well).
>
> Because of this, the technique is usable only in a limited number of
> situations.

But also invaluable in the cases where it is used. :) SQL backed
Cricket/MRTG anyone? -sc

--
Sean Chittenden