Обсуждение: Question about Vacuum, Index, perfromance, future xml support in postgresql

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?

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

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

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

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

As you see,I am really a newbie and donno much on the database field,
please answer this questions. Thank you so much.

Kyle Cheng

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

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073