Re: Re: Need help in reclaiming disk space by deleting the selected records

Yelai, Ramkumar wrote:
> 1. Do I need run REINDEX to reduce space or auto vacuum will handle
re indexing?

Autovacuum will not rebuild the index as REINDEX does.
It will only free index entries tht can be reused later.

> 2. Cluster, Re index and Vacuum full locks the table, Hence do we
need to avoid database operations
> ( select, delete, insert ) while doing disk clean up? Just curious
what if I keep inserting while
> running this command?

The INSERT would get blocked until the CLUSTER, REINDEX or VACUUM FULL
is done.

> 3. All the three commands needs some additional space to do this
operation? Am I correct?

Yes.

> 4. Would all database server ( oracle, sqlserver and mysql ) needs
downtime while doing disk clean
> up?

"Disk cleanup" is a very vague term.
All database management systems handle these things differently.

Note that PostgreSQL normally does not need any downtime if the
vacuum strategy is right.

> 5. I am very happy to use Truncate and table partitioning, it is
satisfying my requirements. But in
> order to achieve this, for 10 years ( currently 6 unique archiving
tables I have ) I have to create
> 1440 month tables. Will it creates any issue and is there anything I
need to consider carefully while
> doing this?

Anything exceeding a few hundred partitions is not considered a good
idea.
The system needs to keep track of all the tables, and query planning
for such a partitioned table might be expensive.

1440 is probably pushing the limits, but maybe somebody with more
experience can say more.

Yours,
Laurenz Albe