About Large object Storage
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.
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>:
Show quoted text
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 querySELECT 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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general