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

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