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: '[email protected]'
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.
