Postgres Pro
Downloads
Home   >   mailing lists

Thread: TRUNCATE pg_largeobject

TRUNCATE pg_largeobject

From
Tamas Vincze
Date:
Is it safe?

This table is around 500GB and because of performance reasons
I slowly moved all large objects to regular files on a NetApp
share.

Now it shows 0 records:

# select count(*) from pg_largeobject;
  count
-------
      0
(1 row)

but disk space and RAM by the free space map is still occupied.
I'd like to clean it up.
A VACUUM FULL would probably do it, but it would take several
hours with heavy I/O and I'd like to avoid that on a production
server.

Can I safely issue a "TRUNCATE pg_largeobject"?
It is v8.1.9.

Thanks!

Re: TRUNCATE pg_largeobject

From
Dmitriy Igrishin
Date:
Hey Tamas,

2011/7/25 Tamas Vincze <vincze@neb.com>
Is it safe?

This table is around 500GB and because of performance reasons
I slowly moved all large objects to regular files on a NetApp
share.

Now it shows 0 records:

# select count(*) from pg_largeobject;
 count
-------
    0
(1 row)

but disk space and RAM by the free space map is still occupied.
I'd like to clean it up.
A VACUUM FULL would probably do it, but it would take several
hours with heavy I/O and I'd like to avoid that on a production
server.

Can I safely issue a "TRUNCATE pg_largeobject"?
It is v8.1.9.

Thanks!
I think that SELECT lo_unlink(loid) FROM (SELECT DISTINCT loid FROM pg_largeobject) AS foo
would be better than direct truncation.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
// Dmitriy.


Re: TRUNCATE pg_largeobject

From
Tamas Vincze
Date:
Hi Dmitriy,

pg_largeobject is already empty, I have lo_unlink()'ed everything
from it, but it still takes up the same disk space and memory for
the free page maps. I'm looking at the best way to reclaim the
disk/memory from this otherwise empty table.
Normal VACUUM didn't help and I'd like some advice whether to use
VACUUM FULL or TRUNCATE.

-Tamas


> Hey Tamas,
>
> 2011/7/25 Tamas Vincze <vincze@neb.com <mailto:vincze@neb.com>>
>
>     Is it safe?
>
>     This table is around 500GB and because of performance reasons
>     I slowly moved all large objects to regular files on a NetApp
>     share.
>
>     Now it shows 0 records:
>
>     # select count(*) from pg_largeobject;
>      count
>     -------
>         0
>     (1 row)
>
>     but disk space and RAM by the free space map is still occupied.
>     I'd like to clean it up.
>     A VACUUM FULL would probably do it, but it would take several
>     hours with heavy I/O and I'd like to avoid that on a production
>     server.
>
>     Can I safely issue a "TRUNCATE pg_largeobject"?
>     It is v8.1.9.
>
>     Thanks!
>
> I think that SELECT lo_unlink(loid) FROM (SELECT DISTINCT loid FROM
> pg_largeobject) AS foo
> would be better than direct truncation.
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/__mailpref/pgsql-general
>     <http://www.postgresql.org/mailpref/pgsql-general>
>
>
>
>
> --
> // Dmitriy.
>
>