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