Обсуждение: Reclaiming space

We are using 7.4.13 and run the pg_autovacuum.  Over the years, the database has grown so our maintenance plan was to "move" everything except for the last year.  Since the server is kept up always using a full vacuum is out of the question.  However the space is running out and we tried installing contrib/dbsize to see which tables were using up space.  The report of database_size is correct, we are using up to 120G of space, but adding up the reported sizes from relation_size does not add up.  So where does the unused space go?

The steps we do to move the data is as follows:
1) dump the data (per table)
2) restore the data to another server
3) delete the dumped records from the production server
4) vacuum analyze

Is there anyway to reclaim the space (the space used up by the tables themselves are less than 20G).

try reindex database;


and move away from 7.4, it is unsupported, and ancient history.


--
GJ

On Tue, 2010-09-21 at 20:39 +0800, Christopher Gorge A. Marges wrote:
> We are using 7.4.13 and run the pg_autovacuum.  Over the years, the
> database has grown so our maintenance plan was to "move" everything
> except for the last year.  Since the server is kept up always using a
> full vacuum is out of the question.  However the space is running out
> and we tried installing contrib/dbsize to see which tables were using
> up space.  The report of database_size is correct, we are using up to
> 120G of space, but adding up the reported sizes from relation_size
> does not add up.  So where does the unused space go?

Bloat. All databases have bloat. If you aren't maintaining "enough" you
are going to continue to use up more and more hd space.

Also, 7.4 is about to be deprecated. I *STRONGLY* Suggest you migrate to
at least 8.2. Preferably 8.4 but that will likely take more work do to
removal of implicit casts in 8.3.

>
> The steps we do to move the data is as follows:
> 1) dump the data (per table)
> 2) restore the data to another server
> 3) delete the dumped records from the production server
> 4) vacuum analyze

You could consider installing a version of Slony that still supports 7.4
and then "replicate" the data to the new server.

>
> Is there anyway to reclaim the space (the space used up by the tables
> themselves are less than 20G).

Not without an outage on the relation.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Tue, 2010-09-21 at 20:39 +0800, Christopher Gorge A. Marges wrote:
> We are using 7.4.13 and run the pg_autovacuum.  Over the years, the
> database has grown so our maintenance plan was to "move" everything
> except for the last year.  Since the server is kept up always using a
> full vacuum is out of the question.  However the space is running out
> and we tried installing contrib/dbsize to see which tables were using
> up space.  The report of database_size is correct, we are using up to
> 120G of space, but adding up the reported sizes from relation_size
> does not add up.  So where does the unused space go?

Bloat. All databases have bloat. If you aren't maintaining "enough" you
are going to continue to use up more and more hd space.

Also, 7.4 is about to be deprecated. I *STRONGLY* Suggest you migrate to
at least 8.2. Preferably 8.4 but that will likely take more work do to
removal of implicit casts in 8.3.

>
> The steps we do to move the data is as follows:
> 1) dump the data (per table)
> 2) restore the data to another server
> 3) delete the dumped records from the production server
> 4) vacuum analyze

You could consider installing a version of Slony that still supports 7.4
and then "replicate" the data to the new server.

>
> Is there anyway to reclaim the space (the space used up by the tables
> themselves are less than 20G).

Not without an outage on the relation.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

But how would the newer version prevent bloat and eliminate making the database unavailable while the *maintenance* goes on? 

The database is more than five years old, and we did not delete records until recently and when we do delete them, naturally the records are in front of the table and the lazy vacuum cannot reclaim the space.  The full vacuum does since it consolidates all the unused space at the expense of locking the table.  In our case our clients rely on our availability.  The newer version(s) of postgres still has this problem of not reclaiming space unless we lock the table so we do not see any compelling reason to upgrade.  But seeing 9.0 includes an improved vacuum makes it worth looking into.

Perhaps there is a more direct approach to remove the bloat, like with a sql statement or some third party utility?  We did use the pg_autovacuum and unless I am mistaken, that should be enough maintenance.  We store millions of records (1 record contains a blob of up to 5mb) per month and if it weren't for the vacuums then it would have been filled up long ago.  But we still wonder why after we deleted a years worth of data, the db still grew bigger. 

On 9/22/2010 12:16 AM, Joshua D. Drake wrote:

On Tue, 2010-09-21 at 20:39 +0800, Christopher Gorge A. Marges wrote:

We are using 7.4.13 and run the pg_autovacuum.  Over the years, the
database has grown so our maintenance plan was to "move" everything
except for the last year.  Since the server is kept up always using a
full vacuum is out of the question.  However the space is running out
and we tried installing contrib/dbsize to see which tables were using
up space.  The report of database_size is correct, we are using up to
120G of space, but adding up the reported sizes from relation_size
does not add up.  So where does the unused space go?

Bloat. All databases have bloat. If you aren't maintaining "enough" you
are going to continue to use up more and more hd space.

Also, 7.4 is about to be deprecated. I *STRONGLY* Suggest you migrate to
at least 8.2. Preferably 8.4 but that will likely take more work do to
removal of implicit casts in 8.3.

The steps we do to move the data is as follows:
1) dump the data (per table) 
2) restore the data to another server
3) delete the dumped records from the production server
4) vacuum analyze

You could consider installing a version of Slony that still supports 7.4
and then "replicate" the data to the new server.

Is there anyway to reclaim the space (the space used up by the tables
themselves are less than 20G).

Not without an outage on the relation.

Joshua D. Drake

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.851 / Virus Database: 271.1.1/3148 - Release Date: 09/21/10 01:04:00

On Tue, Sep 21, 2010 at 9:10 PM, Christopher Gorge A. Marges
<gorge@apollo.com.ph> wrote:
> But how would the newer version prevent bloat and eliminate making the
> database unavailable while the *maintenance* goes on?
>
> The database is more than five years old, and we did not delete records
> until recently and when we do delete them, naturally the records are in
> front of the table and the lazy vacuum cannot reclaim the space.  The full
> vacuum does since it consolidates all the unused space at the expense of
> locking the table.  In our case our clients rely on our availability.  The
> newer version(s) of postgres still has this problem of not reclaiming space
> unless we lock the table so we do not see any compelling reason to upgrade.
> But seeing 9.0 includes an improved vacuum makes it worth looking into.

There are a lot of good reasons to upgrade anyway, especially much
improved performance of newer versions of pg since 7.4 came out.  Also
the fact that 7.4 is going out of support soon.

Note that the other suggestion about slony is a good idea as well, as
you could both upgrade AND remove bloat at the same time.  Create a
new machine running 8.2, initiate replication, wait for it to catch
up, switch app over to new db which is now mostly bloat free.

Then, I think you might be able to get rid of the ongoing problem of
bloat if you were to partition your table.  Create partitions, create
triggers, "insert into maintable select * from only maintable" will
then insert them into the child tables, which you can then just drop
or truncate without impacting the other partitions.

On 9/22/2010 12:18 PM, Scott Marlowe wrote:

On Tue, Sep 21, 2010 at 9:10 PM, Christopher Gorge A. Marges
<gorge@apollo.com.ph> wrote:

But how would the newer version prevent bloat and eliminate making the
database unavailable while the *maintenance* goes on?

The database is more than five years old, and we did not delete records
until recently and when we do delete them, naturally the records are in
front of the table and the lazy vacuum cannot reclaim the space.  The full
vacuum does since it consolidates all the unused space at the expense of
locking the table.  In our case our clients rely on our availability.  The
newer version(s) of postgres still has this problem of not reclaiming space
unless we lock the table so we do not see any compelling reason to upgrade.
But seeing 9.0 includes an improved vacuum makes it worth looking into.

There are a lot of good reasons to upgrade anyway, especially much
improved performance of newer versions of pg since 7.4 came out.  Also
the fact that 7.4 is going out of support soon.

Note that the other suggestion about slony is a good idea as well, as
you could both upgrade AND remove bloat at the same time.  Create a
new machine running 8.2, initiate replication, wait for it to catch
up, switch app over to new db which is now mostly bloat free.

Then, I think you might be able to get rid of the ongoing problem of
bloat if you were to partition your table.  Create partitions, create
triggers, "insert into maintable select * from only maintable" will
then insert them into the child tables, which you can then just drop
or truncate without impacting the other partitions.

Never thought of using slony that way.  Also the partitioning stuff is *new* to me and it looks like a good idea.  I admit I am not up to speed with the new features as I am a software developer by profession and far from a db expert.  Will try this out.  Thank you very much.