Re: Tweaking bytea / large object block sizes? - Mailing list pgsql-general

On Sun, Jun 12, 2011 at 11:00 AM, Hanno Schlichting <hanno@hannosch.eu> wrote:
> Hi.
>
> I'm a new Postgres user. If I happen to ask stupid questions please
> feel free to point me to any documentation I should read or guidelines
> for asking questions.
>
> I'm looking into storing binary data in Postgres and trying to
> understand how data is actually stored in the database. The dataset
> I'm looking at is images, photos, pdf documents which should commonly
> be at a minimum 100kb, on average 10mb and can scale up to 100mb for
> each document. I want to store this data in the database, as I need
> transactional integrity and want to avoid the extra complexity of
> managing shared filesystems between a number of frontend application
> servers and database backends.
>
> The binary data will only be accessed as a whole. So either a complete
> new file is written to the DB or a complete file will be read and
> cached on a frontend server. I don't need streaming access or be able
> to stream partial data. The use-case seems to be well supported by
> Oracle 11g with the introduction of "secure files" (pdf: [1]).
>
> But from what I read of Postgres, my best bet is to store data as
> large objects [2]. Going all the way down this means storing the
> binary data as 2kb chunks and adding table row overhead for each of
> those chunks. Using the bytea type and the toast backend [3] it seems
> to come down to the same: data is actually stored in 2kb chunks for a
> page size of 8kb.
>
> I'm assuming I'll be able to get ~8kb jumbo frame packets over a
> gigabit network connection and would be able to use a ext4 volume with
> a block size of either 32kb or 64kb for the volume housing the binary
> data, but a smaller block size for the one housing the relational
> data.
>
> Given those other constraints, it seems silly to split data up into
> 2kb chunks on the database level. Is there any way the chunk size for
> binary data can be increased here independent of the one for "normal
> relational" data?

I would not even consider tweaking the internal block sizes until
you've determined there is a problem you expect you might solve by
doing so.  The single most important factor affecting blob performance
in postgres is how you send and receive the data -- you absolutely
want to use the binary protocol mode (especially for postgres versions
that don't support hex mode).  The next thing to look at is using
bytea/large object -- large objects are a bit faster and have a higher
theoretical limit on size but byea is a standard type and this offers
a lot of conveniences -- I'd say stick with bytea unless you've
determined there is a reason not to.  That said, if you are not
writing C some client side drivers might only allow binary
transmission through the lo interface so that's something to think
about.

merlin

merlin