Koen Vermeer wrote:
Hi,
I would like to store binary data in a PostgreSQL database. The size of
the data is about 2 to 20 MB and is always stored or retrieved as a
block (i.e., I do not need to get only part of the data). As I
understand, I have two options for storing this data: As BYTEA or as
large objects. As I understand, the disadvantage of the first method is
that I need to write a custom routine to escape some binary values - or
is there some code available to do this? The disadvantage of large
objects is their existence outside of the main database, which may be
problematic when backing up a database. In addition, I need special
routines to store/retrieve the data.
My two questions are: Is this summary correct? And: Which method should
I choose?
Best,
Koen
Having used the large objects, I can tell you they do backup (pg_dump,
etc) and they are not hard to use. There is even a contrib that helps
you hook them up to a table so they get deleted/etc at appropriate times
(I have not used it though so not sure 100%) (they were easy enough to
use even without the contrib).
The only reason I used lo was to avoid the escaping. I'm not sure how
slow escaping a 20 meg file would be, but it just sounded slow. And you
have to do it twice, once to send it, and once when you get it back.
I'd love to hear from others about that... is it something I should even
worry about? We store lots of photos in the db, they usually run a meg
or less, but we store lots of 'em.
-Andy
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
