Re: Database size with large objects

On 11/4/07 8:26 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Michael Goldner" <MGoldner(at)agmednet(dot)com> writes:
>> I have a database with a single table that includes an oid reference to a
>> large object. After loading 100GB of large objects using lo_import(), I
>> find that my total database size has grown by about 270GB. What is the
>> reason for the difference in space usage?
>
> Try VACUUM VERBOSE on your table and also on pg_largeobject to get a
> sense of where the space went. I'm wondering whether you allowed for
> indexes and for other data in your table.
>
> regards, tom lane

OK, I vacuumed both tables. The single table vacuumed in about 2 seconds
and the stats look normal:

INFO: vacuuming "public.image"
INFO: index "image_pkey" now contains 386749 row versions in 1691 pages
DETAIL: 21799 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.10u sec elapsed 0.14 sec.
INFO: "image": removed 21799 row versions in 716 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "image": found 21799 removable, 386749 nonremovable row versions in
19589 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 183153 unused item pointers.
0 pages are entirely empty.
CPU 0.09s/0.19u sec elapsed 0.32 sec.
INFO: vacuuming "pg_toast.pg_toast_176283637"
INFO: index "pg_toast_176283637_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_176283637": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

The pg_largeobject table, however, seems a bit odd:

INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: index "pg_largeobject_loid_pn_index" now contains 105110204 row
versions in 404151 pages
DETAIL: 778599 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 21.24s/48.07u sec elapsed 273.15 sec.
INFO: "pg_largeobject": removed 778599 row versions in 775264 pages
DETAIL: CPU 54.73s/29.70u sec elapsed 2203.32 sec.
INFO: "pg_largeobject": found 778599 removable, 105094846 nonremovable row
versions in 34803136 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 70 unused item pointers.
0 pages are entirely empty.
CPU 1031.40s/339.21u sec elapsed 10875.66 sec.
VACUUM

In particular, "105094846 nonremovable row versions in 34803136 pages" seems
really high given that there are only about 400,000 large objects. The
majority of the lobs are less than 1MB. However, a small subset can range
as high as 1GB.

I see that a single object is spread across multiple pages (loid, pageno) in
the pg_largeobject table. Is it necessary or possible to adjust the page
size for greater efficiency?

Thanks,

Mike