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?
Thanks,
Hanno
[1] http://www.oracle.com/us/dm/h2fy11/securefiles-362607.pdf?evite=EMEAFM10041984MPP017
[2] http://www.postgresql.org/docs/9.0/static/catalog-pg-largeobject.html
[3] http://www.postgresql.org/docs/9.0/static/storage-toast.html