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

["Yelai, Ramkumar IN BLR STS" <ramkumar.yelai@xxxxxxxxxxx>]

Thanks Steve and Andres,

 

I read these articles

        http://www.linuxinsight.com/optimize_postgresql_database_size.html

http://yuval.bar-or.org/blog/2010/08/sluggish-postgresql-databases-and-reindexing-indexes/

http://www.if-not-true-then-false.com/2009/partitioning-large-postgresql-tables-and-handle-millions-of-rows-efficiently-and-quickly/

 

and I have some more questions on the Steve comments.

 

1. Do I need run REINDEX to reduce space or auto vacuum will handle re indexing?
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?
3. All the three commands needs some additional space to do this operation? Am I correct?
4. Would all database server ( oracle, sqlserver and mysql ) needs downtime while doing disk clean up?
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?

 

Thanks & Regards,

Ramkumar

_____________________________________________
From: Yelai, Ramkumar IN BLR STS
Sent: Thursday, September 13, 2012 7:03 PM
To: 'pgsql-general@xxxxxxxxxxxxxx'
Subject: Need help in reclaiming disk space by deleting the selected records

 

 

Hi All,

 

I am a beginner in Postgresql and Databases. I have a requirement that reclaiming disk space by deleting the rows in a selected time span.  I went through the documents and articles to know how to get the table size (http://wiki.postgresql.org/wiki/Disk_Usage) 

 

But before let the user delete, I have to show the size of the records size in the selected time span.  But here I don’t know how to calculate the selected records size. 

In addition to this, I assume that after deleted the records I have to run VACUUM FULL command to reclaiming the space( Please correct me if I am wrong or let me know the best approach) .

 

The table looks like this

 

CREATE TABLE IF NOT EXISTS "SN_SamplTable"

(

  "ID" integer NOT NULL,

  “Data” integer,

  "CLIENT_COUNT_TIMESTAMP" timestamp without time zone

);

 

Please help me to how to proceed on this.

 

Thanks & Regards,

Ramkumar.