On 09/13/2012 06:33 AM, Yelai, Ramkumar IN BLR STS wrote:
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.
Some things to consider:
1. If you have indexes on the table you need to consider the additional
disk space recovered there.
2. CLUSTER is typically *way* faster than VACUUM FULL and rebuilds the
indexes as well but it temporarily requires sufficient disk-space to
write out a copy of the table being clustered.
3. If you can pre-plan for removing old data, for example you are
collecting log data and need a rolling 3-months, then table partitioning
is the way to go. You do this using an empty "parent" tables and putting
the data into child tables each of which covers a specific time-span,
perhaps one child-table per month or per week. When the data is no
longer required you simply dump the child table if desired and then drop
the child table. This is a virtually instant process that does not cause
table bloat. Partitioning by date is only one way. You could determine
that you need to drop data by user-ID and partition that way. Or by a
combination of ID and date-range. But this method does not work if you
need to remove arbitrary date ranges.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
