Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our PostgreSQL 9.0 Windows production server is running low on space.

In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the space to the O/S.

We do not have enough space to do a full rewrite of the table, and all my reading of CLUSTER, VACUUM FULL and pg_repack is that they need to rewrite the table. My google-fu has been insufficient so far to find anything else.

Some downtime is acceptable (~2 hours), however the backup/restoration is too slow for our purposes, nor am I keen on dropping the database in between steps.

QUESTION: How can I return the disk space to the O/S without doing a full table rewrite?

(This is a production server so any solutions must be reputable/recommended/supported etc).

(P.S. A separate much larger disk is available which could be used, if the table could be rebuilt on it somehow. Tablespaces?)

share|improve this question

migrated from stackoverflow.com Apr 11 at 3:24

This question came from our site for professional and enthusiast programmers.

1  
This is advanced stuff that might better fit dba.SE. –  Erwin Brandstetter Apr 11 at 0:03

3 Answers 3

up vote 3 down vote accepted

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes.

TABLESPACE

Yes, your last idea could work. Create a new tablespace on the other disk.

CREATE TABLESPACE ts1 LOCATION '/data/disk2';

Then create an optimized copy of the table in the new tablespace:

CREATE TABLE new_tbl 
TABLESPACE ts1 AS
SELECT * FROM tbl
ORDER BY ....  -- ORDER BY is optional 

The copy will be packed tightly without dead rows.

Then you might just drop the old table and rename the new one to keep using the new disk.

Or, if you have to keep it on the old disk for some reason, delete the old table, rename the new one and move it back to the default tablespace. This step is much faster now with:

ALTER TABLE tbl SET TABLESPACE pg_default

Per documentation:

This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. See also CREATE TABLESPACE.

Either way, don't forget to (re)create all depending objects. Indexes, foreign keys, views, ...

Well, you could use ALTER TABLE tbl SET TABLESPACE ... to begin with, but then the table would not be optimized as requested, just moved as is. But you would have enough wiggle room then to run pg_repack etc.

COPY

A complete backup / restore might take too long, but you could do that for only the table in question.

COPY tbl TO '/path/to/other/disk/tbl.pgsql';
TRUNCATE tbl;
COPY tbl FROM '/path/to/other/disk/tbl.pgsql';

The table is now packed tightly.

Temporary table

If you should happen to have enough RAM, you could do something similar with a temporary table in RAM. Would be much faster. Detailed instructions here:
Best way to delete millions of rows by ID
Or here:
How to delete duplicate entries?

share|improve this answer
    
Thanks for the input. In the end we simply copied the table to a new tablespace on a different disk, the dropped the original table. (Ahh... breaking space) –  Brendan Hill Apr 11 at 9:19
    
Of course for some scenarios it may be necessary to copy it back again, but in our case, it was fine to leave it there permanently. –  Brendan Hill Apr 11 at 9:19

If your environment has disk space more than this table's "real data(after reduced)" size. you can use pg_reorg reduce this table's bloat space. or you can use londiste3 increment copy this table, and use less time to exchange. but if your env has no more space than this table, you can't reduce.

share|improve this answer

there is another way to reduce your bloat space when you can remove tuples in heap pages, this method use less adding space to reduce your bloat table. LIKE exp :

pg93@db-172-16-3-150-> psql
psql (9.3.3)
Type "help" for help.

digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp);
CREATE TABLE
digoal=# insert into t_bloat select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000
digoal=# delete from t_bloat where id<>100000;
DELETE 99999
digoal=# vacuum t_bloat;
VACUUM
digoal=# vacuum t_bloat;
VACUUM
digoal=# select relpages from pg_class where relname='t_bloat';
 relpages 
----------
      233
(1 row)

digoal=# insert into t_bloat select generate_series(1,100),md5(random()::text),clock_timestamp();
INSERT 0 100
digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
 split_part 
------------
 (0
 (232
(2 rows)

digoal=# begin;
BEGIN
digoal=# create temp table t_bloat_tmp(like t_bloat);
CREATE TABLE
digoal=# insert into t_bloat_tmp select * from t_bloat where split_part(ctid::text,',',1)='(232';
INSERT 0 1
digoal=# delete from t_bloat where split_part(ctid::text,',',1)='(232';
DELETE 1
digoal=# insert into t_bloat select * from t_bloat_tmp ;
INSERT 0 1
digoal=# end;
COMMIT
digoal=# vacuum t_bloat;
VACUUM
digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
 split_part 
------------
 (0
 (1
(2 rows)

digoal=# select relpages from pg_class where relname='t_bloat';
 relpages 
----------
        2
(1 row)

you shold attention locks, this is only a thinking.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.