Re: Question about Vacuum, Index, perfromance, future xml

Kyle Cheng wrote:
> Dear Guru, I have lots questions:
>
> <1a> As said and recommend vacuuming postgresql database
> daily/frequently, I do see the increasing of performance. BUT why
> does postgresql designed in such way not freeing used storage to "gain
> the benefits of multiversion concurrency control"; it seemed to me as
> a bad design from start for postgresql, why, oh why? Will "VACUUM" be
> obsoleted in the future as postgresql improves?

We need to make VACUUM automatic some day, yes.

> <1b> It is said in postgresql documenation docnote that Vacuum does
> not give back the storage of Dropped index, it suggested to reindex
> frequently ( it does not say to reindex table or reindex index?) Is it
> true? if yes, why is it designed as such?

Again, something we need to work on. VACUUM does reuse index space, but
pages used for deleted indexed ranges that are never readded to the
table are reclaimed only with reindex.

> <2> This is really confusing, I read a book about database design in
> DB2, It said it is best not to allow indexing in a table where table
> rows are frequently insert/update/delete because re-indexing is
> costly, is it correct for all circumstances? Does creating Sequence
> (SERIAL) suffered costly re-indexing in Insert statments as well?

Well, I think they are saying that if the index needs to be maintained a
log with INSERT/UPDATE/DELETE, it may be better to require the server to
do a sequential scan of the table if it needs the data only
infrequently. For example, if you modify the table 100 times more
frequently than to do a SELECT on the table, an index is probably not a
good idea.

> <3> Why do we need manually tuning database for perfromance when we
> know database optimizer generally smart enough to use proper type of
> join?

It is smart, but it doesn't know all the things the admin does.

> <4> Will postgresql adds feature for XML, XQL/Xquery, in the future?
> (oh, if postgresql will not, will mysql do? )

We have an /contrib/xml utility to some XML stuff.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073