Thread: 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

Hi,

On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote:
> 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.
Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same
thing CLUSTER does just without sorting.

Greetings,

Andres
--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 09/14/2012 05:35 AM, Andres Freund wrote:
> Hi,
>
> On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote:
>> 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.
> Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same
> thing CLUSTER does just without sorting.
>
>
That's true - I should have pointed that out. But it also means that you
can get into a corner if you need to vacuum full large tables when you
have limited free disk space - something the OP should consider since
reclaiming disk space was one of his motivations.

Cheers,
Steve