Re: images in database - Mailing list pgsql-general

> Jan Wieck wrote:
>
> The image data would not be included in a database dump. So
> you loose the ability to save a consistent snapshot.
With the penalty of (much?) larger and slower database dumps.

> The image data would not be accessible remotely except if
> made available via network filesystems. Then you get
> permission and path problems.
True, but they are all solvable ;)

> The image data does not follow the same transactional
> semantics as all other data. Due to MVCC, some process might
> actually have a write lock on the row containing the filename
> and is right now writing to the file while another process
> reads the old row and then the (intermediate mess in the) file.
>
> Rolling back a database transaction in case of whatever error
> will not rollback changes to the image data.
>
> Process (or system) crash in the middle of an update is not
> covered by the databases crash recovery capability.
All three nice reasons, although it is sometimes simply "not thát
important" and in that case you're running into the main reason not to
do this.
The performance is quite a lot lower for the database-stored version, I
think I recall seeing 5-10 times slower file load times (ie from
retrieving the filename/location from the database and then reading the
file from the FS vs retrieving it from a bytea field).
I did not consider the compression stuff of TOAST, but I did compare the
same functionality with mysql vs FS and postgres vs FS. These tests were
not at all well performed, but just to get an idea on how the
performance was, all done on my local system with connections to the
local databases and directly reading from file.

Perhaps my increased understanding of how things work could make a
difference (like only 4-7 times slower orso).
Another disadvantage is that processing time for the database-processes
are much longer and in that case you load the database-server with extra
load it wouldn't have when you'd use the FS.
How postgres' LOB functionality fits into this, I don't now. It probably
fits in between the two in terms of performance (and ease of use).

> Enough reasons to forget about filename storage?
Imho it entirely depends on the requirements, your reasons to not go for
the local filesystem are quite good, but the main reason to go for the
FS is also a very important one in some situations.

So it becomes a "correctness and ease of use vs raw performance and
system load (including distribution of tasks)" comparison, I guess.

Regards,

Arjen