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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
