Re: why the DB file size does not reduce when 'delete'the data in DB?

----- Original Message -----
From: Roberto Mello <rmello(at)cc(dot)usu(dot)edu>
To: xuyifeng <jamexu(at)telekbird(dot)com(dot)cn>
Sent: Sunday, March 04, 2001 10:40 AM
Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?

> On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng wrote:
> >
> > could anyone remove this nasty bug in 7.2? this is already a big pain and is the reason
> > why am I still using MySQL in my product server. another nasty thing is it does not
> > allow me to reference table in another database. sigh.
>
> I don "vacuum analyze" in my Postgres database every night very easily
> using a simple cron job. I create the cron job and forget about it. Really
> it's not that hard don't you think?
> Sure, it's not the desired way, but that's not big enough a reason to
> use PostgreSQL. I could think of a dozen reasons that counter that one and
> would favor PostgreSQL in technical terms. Compared to those, this is
> really really a minor issue.
> As to referring to a table in another DB, I don't know when is that
> going to be implemented.
>
> -Roberto
>
> --
> +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club|------+
> Roberto Mello - Computer Science, USU - http://www.brasileiro.net
> http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer
> << Politically Un-Correct Tagline Deleted! >>
>

I know your situations, your DB is not updated and inserted lots of records in few minutes,
mine is difference, I have a real time Stock Trading system, you know, stock, its price
is changed every minute or even every second , I need update and insert delta change into DB,
draw their trend graphics, suppose there are 3000 stocks in market, there maybe 9000 records
changed and inserted in one minutes, because PGSQL's storage manager problem( it does not
reuse deleted record space), in 4 hours trading period, my harddisk can be full filled. because in
the period, the table indeed gets very large, doing VACCUME is impossible in realtime, it will lock
out other clients too long time, my point of view is PGSQL is fit for static or small changed database,
not fit for lots of change in short time.

Regards,
Xu Yifeng