Postgres Pro
Downloads
Home   >   mailing lists

Thread: About Large object Storage

About Large object Storage

From
Kalai R
Date:
Hello,

I am using postgresql 9.3 in Windows. I take data backup using pg_dump. The backup file size is 5 MB only. After that I insert 8 images using large object.

The total size of 8 images are 24 MB. After insert large object only, I take data backup using pg_dump. Now the backup file size becomes 246 MB. I really shocked. so I check database size using the following query

SELECT pg_size_pretty(pg_database_size('testdb'));

The return value is 710 MB.

What is the problem? How DB size increased extremely?

Thanks for your Suggestions.







Re: About Large object Storage

From
Rémi Cura
Date:
Hey,
about your backup,
you must have use plain text backup,
writing in ascii to represent binary data is costly (and pointless?).
You can use compressed version of it.
About the size of your database,
maybe you can try a vacuum full before measuring the size?

Cheers,
Rémi-C


2014-04-23 15:22 GMT+02:00 Kalai R <softlinne.kv@gmail.com>:
Hello,

I am using postgresql 9.3 in Windows. I take data backup using pg_dump. The backup file size is 5 MB only. After that I insert 8 images using large object.

The total size of 8 images are 24 MB. After insert large object only, I take data backup using pg_dump. Now the backup file size becomes 246 MB. I really shocked. so I check database size using the following query

SELECT pg_size_pretty(pg_database_size('testdb'));

The return value is 710 MB.

What is the problem? How DB size increased extremely?

Thanks for your Suggestions.








Re: About Large object Storage

From
Tom Lane
Date:
Kalai R <softlinne.kv@gmail.com> writes:
> I am using postgresql 9.3 in Windows. I take data backup using pg_dump. The
> backup file size is 5 MB only. After that I insert 8 images using large
> object.

> The total size of 8 images are 24 MB. After insert large object only, I
> take data backup using pg_dump. Now the backup file size becomes 246 MB.

The size of the output file will be about twice the length of the large
object(s), since plain-text pg_dump format prints the large object
contents in hex.  I'd take a second look at exactly how you inserted
the large objects; it seems highly likely that you inserted them multiple
times or bloated their contents somehow.

            regards, tom lane

Re: About Large object Storage

From
Kalai R
Date:
I have used custom format archieve in pg_dump. 

I have used the following command in plpgsql function to insert the image.
lo_import(xlogopath)

After I inserted one image more records are created in pg_largeobject and pg_largeobject_metadata tables other than the OID returned from lo_import


On Wed, Apr 23, 2014 at 7:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kalai R <softlinne.kv@gmail.com> writes:
> I am using postgresql 9.3 in Windows. I take data backup using pg_dump. The
> backup file size is 5 MB only. After that I insert 8 images using large
> object.

> The total size of 8 images are 24 MB. After insert large object only, I
> take data backup using pg_dump. Now the backup file size becomes 246 MB.

The size of the output file will be about twice the length of the large
object(s), since plain-text pg_dump format prints the large object
contents in hex.  I'd take a second look at exactly how you inserted
the large objects; it seems highly likely that you inserted them multiple
times or bloated their contents somehow.

                        regards, tom lane

Re: About Large object Storage

From
Adrian Klaver
Date:
On 04/23/2014 08:14 AM, Kalai R wrote:
> I have used custom format archieve in pg_dump.
>
> I have used the following command in plpgsql function to insert the image.
> lo_import(xlogopath)
>
> After I inserted one image more records are created in pg_largeobject
> and pg_largeobject_metadata tables other than the OID returned from
> lo_import

Large objects are stored in pg_largeobject in chunks, so this is not
surprising. See below for more detail:

http://www.postgresql.org/docs/current/static/catalog-pg-largeobject.html




--
Adrian Klaver
adrian.klaver@aklaver.com