From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Eric McKeeth <eldin00@gmail.com>
Cc: emilu@encs.concordia.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: column type for pdf file
Date: Thu, 26 May 2011 13:07:56 -0500
Message-ID: <20110526180756.GC1938@rice.edu> (raw)
In-Reply-To: <BANLkTimpYJk2=D8TUFDjWRQLmQNfRa_d=Q@mail.gmail.com>
References: <4DD42A1C.30202@encs.concordia.ca>
<BANLkTimpYJk2=D8TUFDjWRQLmQNfRa_d=Q@mail.gmail.com>
On Wed, May 18, 2011 at 05:06:36PM -0600, Eric McKeeth wrote:
> On Wed, May 18, 2011 at 2:20 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
>
> > Hello,
> >
> > To save pdf files into postgresql8.3, what is the best column type?
> >
> > bytea, blob, etc?
> >
> > Thank you,
> > Emi
> >
>
> Everyone else has pointed out reasons for not doing this, and I agree with
> them that in the large majority of cases just storing a reference to a file
> stored outside the database is preferable. However, to answer the question
> you asked, my rule of thumb is that if you need to store binary data in the
> database is to use a bytea column, unless you need the random access
> capabilities that the large object interface provides. A bytea column is
> typically easier to use, and has proper transactional behavior, enforcement
> of referential integrity, etc.
>
I'm with Eric on this one: for smaller use cases, the convenience of bytea
in the db is nice. As to random access, I wrote a client-side wrapper
for our middleware that implements a file iterator interface for python
on top of substr(bytea,position,blocksize). I was sort of surprised at
how well it performed. We're using it in production right now.
I actually store files in a leaf table w/ and id and hash, with
filenames in a separate linking table, so I'm even getting data
deduplication (all the rage in biz these days) for free.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, eldin00@gmail.com, emilu@encs.concordia.ca
Subject: Re: column type for pdf file
In-Reply-To: <20110526180756.GC1938@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox