Thread: Weight BLOB objects in postgreSQL? How?

I need to figure out how can I weight BLOB objects in a table of a DB, I'm not expert on this topics, so I appreciate any help you can give me.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 3/4/2015 7:03 AM, María Griensu wrote:

I need to figure out how can I weight BLOB objects in a table of a DB, I'm not expert on this topics, so I appreciate any help you can give me.

postgres's equivalent of BLOB is BYTEA.     I'm not sure what you mean by 'weight' here.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Wed, 4 Mar 2015 12:36:36 -0300
María Griensu <mdovale.griensu@gmail.com> wrote:

> Thanks, I mean, how heavy it is in kB.

If you mean how much overhead is involved in storing the data, that's
a bit complicated.

First off, how the data is stored depends on the size of it, and what
other fields there are in the table and their size as well. This is
because the TOAST code makes decisions on how to store things on the
fly based on various rules.

Read up: http://www.postgresql.org/docs/9.4/static/storage-toast.html

There are a number of factors. If the data is large enough for the
TOAST code to kick in, the server may decide to compress the data,
whether that actually helps depends on the nature of the data ...
a zip file or png isn't going to get any smaller, for example.

From there, if the data is large enough to trigger out-of-line
storage, the data will be broken down into chunks and stored in a
toast table, this increases the overhead because each row in the
toast table will have it's own overhead, and the number of rows
required in the toast table depends on the size of the data, which
in-turn depends on how well the data compressed ...

So, your answer is:
Take _your_ data and store a bunch of it, then check the resultant
size of the tables on disk vs. the actual size of the data. That's
really the only way to know since the actual efficiency of data
storage depends a lot on the data itself.

--
Bill Moran

On 3/4/15 10:12 AM, Bill Moran wrote:
>  From there, if the data is large enough to trigger out-of-line
> storage, the data will be broken down into chunks and stored in a
> toast table, this increases the overhead because each row in the
> toast table will have it's own overhead, and the number of rows
> required in the toast table depends on the size of the data, which
> in-turn depends on how well the data compressed ...

If you want to back-of-the-envelope estimate, (with 8k blocks) you need
a new chunk for every ~2000 bytes of *compressed* data. Each chunk will
take 2048 bytes in the heap and a bit more in the index on the toast
table (probably around 16 bytes). So it's somewhere around 95% efficient
(assuming zero compression).

> So, your answer is:
> Take_your_  data and store a bunch of it, then check the resultant
> size of the tables on disk vs. the actual size of the data. That's
> really the only way to know since the actual efficiency of data
> storage depends a lot on the data itself.

And perhaps a better question is: why do you want to know? In my
experience there are other factors with keeping blobs in the database
that are much more important than toast overhead...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

María Dovale wrote:
> Thanks, I mean, how heavy it is in kB.

You can use lo_lseek64 to find out how big a large object is.
To find out the size of large object 24858, you can

SELECT lo_lseek64(lo_open(24858, 262144), 0, 2);

The last "2" here is SEEK_END from /usr/include/unistd.h, so it may be
that this value is different for different operating systems.

Yours,
Laurenz Albe