Re: Storing Digital Video - Mailing list pgsql-performance

From Matt Davies | Postgresql List
Subject Re: Storing Digital Video
Whole thread Raw
In response to Storing Digital Video  (Rodrigo Madera)
List pgsql-performance
Rodrigo Madera wrote:

>I am concerned with performance issues involving the storage of DV on
>a database.
>I though of some options, which would be the most advised for speed?
>1) Pack N frames inside a "container" and store the container to the db.
>2) Store each frame in a separate record in the table "frames".
>3) (type something here)
>Thanks for the help,

My experience has been that this is a very bad idea. Many people want to
store all sorts of data in a database such as email messages, pictures,
etc... The idea of a relational database is to perform queries against
data. If you are needing to just store data then store it on a disk and
use the database as the indexer of the data.

Keep in mind the larger the database the slower some operations become.

Unless you are operating on the frame data (which you either store as
blobs or hex-encoded data) I'd recommend you store the data on a hard
drive and let the database store meta data about the video such as path
information, run time, author, etc...

We do this on an application storing close to a million images and the
performance is impressive.
    1. we don't have to do any sort of data manipulation storing the
data in or retrieving the data out of the database.
    2. our database is compact and extremely fast  - it is using the
database for what it was designed for - relational queries.

My $0.02

>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

pgsql-performance by date:

From: Rodrigo Madera
Subject: Storing Digital Video
From: "Steinar H. Gunderson"
Subject: Re: Huge Data sets, simple queries