Re: size of bytea + performance issues

On Tue, Jan 31, 2006 at 07:58:30AM -0800, tschak wrote:
> Hi everyone,
>
> my first question concerns the the size of a table with a bytea row. In
> the documentation it says something like 4 Bytes + 1 Byte for each
> escaped octet sequence per row. For example an insertion into a table
> storing just one column with bytea data looks like this:
> insert into test values (''\\003''); ---- this allows 185 inserts per
> page until a new one is needed
> insert into test values (''\\003\\123\\123\\111''); ---- this yields
> exactly the same nr. of rows per page!
> insert into test values (''\\003\\123\\123\\111\\001''); ---- this one
> finally needs more pages!
>
> How can that be, if the system internally allocates ONE BYTE per
> octet...
> Does it acually take 4 Bytes?

Alignment. A new row needs to start on a multiple of 4 (or 8) boundary.
So even though your data value might take 5 bytes, you may end up with
some slack before the next tuple.

> My second question is more generall:
> My dbms (acutally it is supposed to be a decision support system, so I
> do not really need rollbacks and transactions etc. --- can those
> features be turned off to enhance performance?) needs to store 1

Well, you might need to take this up on the -performance list, but
there are people running databases that large. In general such features
can't be turned off, though they don't really cost much on data that
doesn't change.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.