Thread: SQL & Binary Data Questions

Hi,

I see people use postgreSQL for storing their binary data including
images, etc. Here are a few questions I would very much like to have
expert opinions on:

- Is it not faster / more efficient to store binary data using file
system and let DB keep the path?
- I know of only one way to interact with PostgreSQL and that is using
SQL. Since it has powerful bytea support, I'd to utilize it by storing
my sterilized data structure into the DB. Is this practice recommended
or discouraged? Is there any better method of pushing binary data to
postgres besides parsing it into ascii sql which seems extremely
inefficient to me.
- Does it make my queries any slower if the rows are bundled with
binary data (that has nothing to do with the query)?

I am using python as my backend, and I am programming a web application
for which speed matters.

Thank you,
Sia

"Siah" <siasookhteh@gmail.com> writes:
> Is there any better method of pushing binary data to
> postgres besides parsing it into ascii sql which seems extremely
> inefficient to me.

Yeah, send it as an out-of-line binary parameter.  Dunno whether you can
get at that from Python though :-(

            regards, tom lane

Some pointers could help. & any arguments pro/against saving bin data
in db?

Thanks,
Sia

On Fri, May 19, 2006 at 12:21:28 -0700,
  Siah <siasookhteh@gmail.com> wrote:
> Some pointers could help. & any arguments pro/against saving bin data
> in db?

If you want transactional semantics you want the data in the DB. If not,
then you will probably get better perfomance if it isn't.

On fös, 2006-05-19 at 12:21 -0700, Siah wrote:
> Some pointers could help. & any arguments pro/against saving bin data
> in db?

pro: backups can be made with pg_dump only.

if binary data is stored in filesystem, your backup
procedure gets more complicated, specially if your
binary files can get updated during backup.

gnari