Re: Storing Video's or vedio file in DB.

On Wednesday, December 17, 2014 01:55:46 PM Thomas Kellerer wrote:
> Albe Laurenz schrieb am 17.12.2014 um 11:07:
> > and the performance will be worse than reading files from the file system.
>
> There is a Microsoft research [1] (from 2006) which tested this "myth" using
> SQL Server. It showed that the database might actually be faster than the
> file system.
>
> As this topic comes up at my workplace every now and then as well, I created
> a little web application (Java/JDBC) to test this on Postgres and possibly
> other DBMS.
>
> Turns out the Postgres as well isn't really slower at this than the file
> system.
>
> For small files around 50k both perform similar: the average time to read
> the blob from a bytea column was around 2ms whereas the average time to
> read the blob from the filesystem was around 1ms. The test uses 50 threads
> to read the blobs using the PK of the table.
>
> "Reading from the filesystem" means looking up the path for the file in the
> database table and then reading the file from the filesystem.
>
> For larger files around 250k Postgres was actually faster in my tests: 130ms
> reading the bytea column vs. 260ms reading the file from disk.
>
> The tests were done locally on my Windows laptop.
> I didn't have time yet to do this on a Linux server. I expect the filesystem
> to have some impact on the figures and NTFS is not known for being
> blazingly fast. So maybe those figures will change.
>
>
> My tests however do not take into account the actual time it takes to send
> the binary data from the server to the client (=browser). It might well be
> possible that serving the file through an Apache Web Server directly is
> faster than serving the file through a JEE Servlet. My intention was to
> measure the raw read speed of the binary data from the medium where it is
> stored.

You can get the data from disk about as fast, but actually serving it results
in a large CPU hit that isn't present when serving files.

And if you're using bytea, your app server has to allocate memory to hold at
least one full copy of the file (I seem to recall that it works out to 2
copies, actually, but it's been a while since I tried it). Most languages
aren't good about releasing that memory, so that hit stays around until the
process gets recycled.

For a low volume app, both might be acceptable - any modern CPU can swamp most
outbound bandwidth even while decoding bytea. But it is a large amount of
overhead compared to a web server just dumping files into a network buffer
straight from disk cache.

Also, maintaining large tables still sucks. You can partition them to make
things friendlier. pg_upgrade makes things nicer, but it can't always be used,
so major version upgrades can still be a problem.

On the plus side, all your data is in one place, which makes it cluster-
friendly and easy to delete files when needed, and makes taking consistent
backups much simpler.