On 17 December 2014 at 13:55, Thomas Kellerer <spam_eater@gmx.net> 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
testthis 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
whereasthe average time to read the blob from the filesystem was around 1ms. The test uses 50 threads to read the blobs
usingthe 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
fromthe filesystem.
With how many blobs/files did you test this? I'm asking because PG
stores all blobs in a single table. On a file-system, if all files are
stored in a single directory, the situation is similar.
However, a file-system has the ability to store files in several
directories instead of just one, which is often claimed to improve
file-locating performance.
Seeing as the read performance of a file (once it's been located) from
the file-system versus a blob appears similar, the difference in time
for locating the file might well be relevant here.
Interesting to see this was tested with MS SQL and therefore limited
to NTFS. It's probably useful to test this with other file-systems,
such as ZFS or UFS (with DIRHASH!), etc.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.