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