Re: Question about Vacuum, Index, perfromance, future xml support in postgresql

kylechihchen@netscape.net (Kyle Cheng) writes:
> <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?

Due to MVCC, you need a non-overwriting storage manager. That means
that at some point, you need a garbage collection-like process to
remove dead tuples. The benefits of MVCC are judged to be worth it.

As for removing VACUUM, there are some plans for adding an 'auto
VACUUM' facility, which would run a VACUUM on some kind of automatic
basis. But ISTM that we would still retain the VACUUM command, we
would just provide the option of running it automatically for the
DBA.

> <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?

This is a legitimate problem. This may be fixed in 7.4 (at least,
Manfried was taking a look at it, last I heard).

> <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?

Well, the time to update the index will necessarily slow down inserts
and updates. Whether that speed penalty is worth the performance
improvement for DELETEs, SELECTs, and UPDATEs is something you need to
decide based on your query workload.

> Does creating Sequence (SERIAL) suffered costly re-indexing in
> Insert statments as well?

Well, a sequence doesn't create an index, so no.

In versions of PostgreSQL prior to 7.3, SERIAL automatically creates
an index, so you'd need the same reasoning as above.

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

Can you rephrase that? I don't understand the question.

> <4> Will postgresql adds feature for XML, XQL/Xquery, in the future?

contrib/xml has some stuff relating to this.

> (oh, if postgresql will not, will mysql do? )

Ask the MySQL developers...

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC