Re: Performance

Dann Corbit wrote:

>>-----Original Message-----
>>From: pgsql-general-owner(at)postgresql(dot)org
>>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
>>Richard Huxton
>>
>>LuisH wrote:
>>
>>
>>>Question about Performance:
>>>
>>>What about access sounds (WAV or VOX) from filesystem or from Blob
>>>Database ???????????? Whish performance can I expect on each case ??
>>>
>>>
>>Filesystem access should always be faster than through the
>>database. If
>>it isn't you should get a new filesystem.
>>
>>You need to ask yourself:
>>1. Do I want transactional control over updates to these
>>sounds? 2. Do I always want to store the files on the same
>>machine as the database? 3. Is it easier/more difficult to
>>read/download files vs access via the
>>database connection.
>>
>>
>
>It isn't always that simple. What if you need to find them by date?
>What if you need to search by title among millions of them. Often (as
>well) file systems slow down when you put hundreds of thousands or
>millions of files in a directory.
>
>Database is preferrable if you want to use SQL syntax to search for the
>blobs based upon criteria.
>
>If you have a small number of static things, then files are better.
>
One thing that I've done to overcome this limitation is to put the data
that I need to be able to search into the database, and still have the
files themselves reside outside of the database. A table like this
works pretty good.

column | type
---------+-----
id | int (unique row identifier)
size | int (size of the file)
mod_date | datetime (modification date)
location | text (location in filesystem)

Then you can still do the searching you need without filesystem
searches. Once you get to the appropriate entry, you just use the value
in the location field and go directly to the correct file. Another
field could be added to indicate what server the file resides on if you
don't want to store these files on the database server as mentioned
above. With the "server" field (or server info in the "location"
field), you could even split the files onto several servers and perform
the SQL on them with one search. This is really helpful on web-based
apps, because you can point the browser anywhere you want to point it to
retrieve whatever files you want.

Obviously if you require more details about the file to be able to
search, you can add more columns. This overcomes the need for the
database to handle the file data itself.

--
Thanks,
Laura Vance
Systems Engineer
Winfree Academy Charter Schools, Data-Business Office
1711 W. Irving Blvd. Ste 310
Irving, Tx 75061
Web: www.winfreeacademy.com