Re: Storing images in PostgreSQL databases (again)

On Oct 5, 2006, at 19:47 , DEV wrote:

> I have seen several posts pertaining to the "overhead" difference
> in storing
> in a db table versus the file system. What is this difference?

Well, there's not much space overhead to speak of. I tested with a
bunch of JPEG files:

$ find files | wc -l
2724
$ du -hs files
213M files

With an empty database and the following schema:

create table files (id serial, data bytea);
alter table files alter column data set storage external;

When loaded into the database:

$ du -hs /opt/local/var/db/postgresql/base/16386
223M /opt/local/var/db/postgresql/base/16386

On my MacIntel with PostgreSQL from DarwinPorts -- a configuration/
port where PostgreSQL performance does *not* shine, incidentally --
PostgreSQL can insert the image data at a pretty stable 2.5MB/s. It's
still around 30 times slower than the file system at reading the
data. (I would love to run a benchmark to provide detailed timings,
but that would tie up my laptop for too long.)

Alexander.