Re: Growing Database Size

On 14 Jun 2003 at 13:50, Kenneth Godee wrote:

> >
> > You probably want to use a reindex. If you have an index on a column
> > that only increases or decreases over time, the index won't be able
> > to use reuse pages that are no longer in use. This is fixed in 7.4.
> >
>
> I'm kinda having the same type of problem, I'm updating a entire
> database nightly with.....
>
> BEGIN
> DELETE (deleting all rows from a table)
> DELETE (deleting all rows from a table)
> COPY (populating with new rows)
> COPY (populating with new rows)
> COMMIT
> VACUUM (analyze)

Wow.. That would really kill the things.

How about

begin
drop table a;
drop table b;
create table a;
create table b;
copy -> table a;
copy ->table b;
commit;
vacuum analyze;

I can almost bet that this would be faster than solution above as it would not
churn the disk as much, put simply.

Hmm..Just wondering, will truncate in above steps would do the same job?

> Am I missing something? Does anyone know where to find any doc's on how to
> adjust the FSM settings?
> I usally try to do my home work before asking these questions, but.....

This is out of my head so take it with a grain of salt. Correct me if I am
wrong.

no. of FSM relations = number of relations/tables in database that gets
populated
no. of fsm pages in 8KB blocks = space required to store each deleted page. So
if you delete/update 100K pages, for each page you need to give some 4 byte
entry in FSM.

Unless you have big enough FSM map, vacuum won't be as effective as it won't be
able to track all the space wasted.

HTH

Bye
Shridhar

--
Peers's Law: The solution to a problem changes the nature of the problem.