pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStoring Digital Video
10+ messages / 8 participants
[nested] [flat]
* Storing Digital Video
@ 2006-02-01 00:32 Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Rodrigo Madera @ 2006-02-01 00:32 UTC (permalink / raw)
To: pgsql-performance
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,
Rodrigo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-01 00:37 Jeffrey W. Baker <jwb@gghcwest.com>
parent: Rodrigo Madera <rodrigo.madera@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Jeffrey W. Baker @ 2006-02-01 00:37 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2006-01-31 at 16:32 -0800, 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)
How about some more color? _Why_, for example, would you store video in
a relational database?
-jwb
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-01 00:51 Matt Davies | Postgresql List <matt-postgresql@mattdavies.net>
parent: Rodrigo Madera <rodrigo.madera@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Matt Davies | Postgresql List @ 2006-02-01 00:51 UTC (permalink / raw)
To: ; +Cc: 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
>Rodrigo
>
>---------------------------(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
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-06 08:30 Albert Cervera Areny <albert@sedifa.com>
parent: Rodrigo Madera <rodrigo.madera@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Albert Cervera Areny @ 2006-02-06 08:30 UTC (permalink / raw)
To: pgsql-performance
A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure:
> 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,
What if you store meta data in the database and use some PL/Python/Java/Perl
functions to store and retrieve video files from the server. The function
would store files to the files system, not a table. This avoids the need for
a file server for your application while making your relational queries fast.
Any experiences/thoughts on this solution?
>
> Rodrigo
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-09 13:45 Nate Byrnes <nate@qabal.org>
parent: Albert Cervera Areny <albert@sedifa.com>
0 siblings, 1 reply; 10+ messages in thread
From: Nate Byrnes @ 2006-02-09 13:45 UTC (permalink / raw)
To: pgsql-performance
I must claim some ignorance, I come from the application world... but,
from a data integrity perspective, it makes a whole lot of sense to
store video, images, documents, whatever in the database rather than on
the file system external to it. Personally, I would use LOB's, but I do
not know the internals well enough to say LOBs or large columns.
Regardless, there are a lot of compelling reasons ranging from software
maintenance, disk management, data access control, single security layer
implementation, and so on which justify storing data like this in the
DB. Am I too much of an Oracle guy? I think that Postgres is more than
capable enough for this type of implementation. Is this confidence
unfounded?
Aside from disk utilization, what are the performance issues with
LOB and / or large columns? Does the data on disk get too fragmented to
allow for efficient querying? Are the performance issues significant
enough to push parts of the data integrity responsibility to the
application layer?
Thanks,
Nate
Albert Cervera Areny wrote:
> A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure:
>
>> 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,
>>
>
>
> What if you store meta data in the database and use some PL/Python/Java/Perl
> functions to store and retrieve video files from the server. The function
> would store files to the files system, not a table. This avoids the need for
> a file server for your application while making your relational queries fast.
>
> Any experiences/thoughts on this solution?
>
>
>> Rodrigo
>>
>> ---------------------------(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
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> !DSPAM:43e70ada303236796316472!
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-09 15:18 Craig A. James <cjames@modgraph-usa.com>
parent: Nate Byrnes <nate@qabal.org>
0 siblings, 2 replies; 10+ messages in thread
From: Craig A. James @ 2006-02-09 15:18 UTC (permalink / raw)
To: Nate Byrnes <nate@qabal.org>; +Cc: pgsql-performance
Nate Byrnes wrote:
> I must claim some ignorance, I come from the application world... but,
> from a data integrity perspective, it makes a whole lot of sense to
> store video, images, documents, whatever in the database rather than on
> the file system external to it. Personally, I would use LOB's, but I do
> not know the internals well enough to say LOBs or large columns.
> Regardless, there are a lot of compelling reasons ranging from software
> maintenance, disk management, data access control, single security layer
> implementation, and so on which justify storing data like this in the
> DB. Am I too much of an Oracle guy?
Yes, you are too much of an Oracle guy ;-). Oracle got this notion that they could conquer the world, that EVERYTHING should be in an Oracle database. I think they even built a SAMBA file system on top of Oracle. It's like a hammer manufacturer telling you the hammer is also good for screws and for gluing. It just ain't so.
You can store videos in a database, but there will be a price. You're asking the database to do something that the file system is already exceptionally good at: store big files.
You make one good point about security: A database can provide a single point of access control. Storing the videos externally requires a second mechanism. That's not necessarily bad -- you probably have a middleware layer, which can ensure that it won't deliver the goods unless the user has successfully connected to the database.
Craig
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-09 15:58 Nate Byrnes <nate@qabal.org>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 0 replies; 10+ messages in thread
From: Nate Byrnes @ 2006-02-09 15:58 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
Thanks, until Postgres can pay my bills (hopefully soon...) I will
have to be an Oracle guy. Aside from the filesystem being better at
managing large files (which I do agree) are there performance
implications for the storage in the DB?
Where I work, the question is not can you add the security code to
the middleware, but how many middlewares and applications will need to
be updated.
Regards,
Nate
Craig A. James wrote:
> Nate Byrnes wrote:
>> I must claim some ignorance, I come from the application world...
>> but, from a data integrity perspective, it makes a whole lot of sense
>> to store video, images, documents, whatever in the database rather
>> than on the file system external to it. Personally, I would use
>> LOB's, but I do not know the internals well enough to say LOBs or
>> large columns. Regardless, there are a lot of compelling reasons
>> ranging from software maintenance, disk management, data access
>> control, single security layer implementation, and so on which
>> justify storing data like this in the DB. Am I too much of an
>> Oracle guy?
>
> Yes, you are too much of an Oracle guy ;-). Oracle got this notion
> that they could conquer the world, that EVERYTHING should be in an
> Oracle database. I think they even built a SAMBA file system on top
> of Oracle. It's like a hammer manufacturer telling you the hammer is
> also good for screws and for gluing. It just ain't so.
>
> You can store videos in a database, but there will be a price. You're
> asking the database to do something that the file system is already
> exceptionally good at: store big files.
>
> You make one good point about security: A database can provide a
> single point of access control. Storing the videos externally
> requires a second mechanism. That's not necessarily bad -- you
> probably have a middleware layer, which can ensure that it won't
> deliver the goods unless the user has successfully connected to the
> database.
>
> Craig
>
> !DSPAM:43eb5e8970644042098162!
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-09 19:26 Jim C. Nasby <jnasby@pervasive.com>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jim C. Nasby @ 2006-02-09 19:26 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Nate Byrnes <nate@qabal.org>; pgsql-performance
On Thu, Feb 09, 2006 at 07:18:49AM -0800, Craig A. James wrote:
> Nate Byrnes wrote:
> >I must claim some ignorance, I come from the application world... but,
> >from a data integrity perspective, it makes a whole lot of sense to
> >store video, images, documents, whatever in the database rather than on
> >the file system external to it. Personally, I would use LOB's, but I do
> >not know the internals well enough to say LOBs or large columns.
> >Regardless, there are a lot of compelling reasons ranging from software
> >maintenance, disk management, data access control, single security layer
> >implementation, and so on which justify storing data like this in the
> >DB. Am I too much of an Oracle guy?
>
> Yes, you are too much of an Oracle guy ;-). Oracle got this notion that
> they could conquer the world, that EVERYTHING should be in an Oracle
> database. I think they even built a SAMBA file system on top of Oracle.
> It's like a hammer manufacturer telling you the hammer is also good for
> screws and for gluing. It just ain't so.
>
> You can store videos in a database, but there will be a price. You're
> asking the database to do something that the file system is already
> exceptionally good at: store big files.
>
> You make one good point about security: A database can provide a single
> point of access control. Storing the videos externally requires a second
> mechanism. That's not necessarily bad -- you probably have a middleware
> layer, which can ensure that it won't deliver the goods unless the user has
> successfully connected to the database.
You're forgetting about cleanup and transactions. If you store outside
the database you either have to write some kind of garbage collector, or
you add a trigger to delete the file on disk when the row in the
database pointing at it is deleted and hope that the transaction doesn't
rollback.
Of course, someone could probably write some stand-alone code that would
handle all of this in a generic way... :)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-09 23:14 Jan Peterson <jan.l.peterson@gmail.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Peterson @ 2006-02-09 23:14 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; Nate Byrnes <nate@qabal.org>; pgsql-performance
In my experience, you don't want to store this stuff in the database.
In general, it will work fine, until you have to VACUUM the
pg_largeobject table. Unless you have a very powerful I/O subsystem,
this VACUUM will kill your performance.
> You're forgetting about cleanup and transactions. If you store outside
> the database you either have to write some kind of garbage collector, or
> you add a trigger to delete the file on disk when the row in the
> database pointing at it is deleted and hope that the transaction doesn't
> rollback.
Our solution to this problem was to have a separate table of "external
files to delete". When you want to delete a file, you just stuff an
entry into this table. If your transaction rolls back, so does your
insert into this table. You have a separate thread that periodically
walks this table and zaps the files from the filesystem.
We found that using a procedural language (such as pl/Perl) was fine
for proof of concept. We did find limitations in how data is returned
from Perl functions as a string, combined with the need for binary
data in the files, that prevented us from using it in production. We
had to rewrite the functions in C.
-jan-
--
Jan L. Peterson
<jan.l.peterson@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Storing Digital Video
@ 2006-02-11 20:32 Jim C. Nasby <jnasby@pervasive.com>
parent: Jan Peterson <jan.l.peterson@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2006-02-11 20:32 UTC (permalink / raw)
To: Jan Peterson <jan.l.peterson@gmail.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; Nate Byrnes <nate@qabal.org>; pgsql-performance
On Thu, Feb 09, 2006 at 04:14:09PM -0700, Jan Peterson wrote:
> In my experience, you don't want to store this stuff in the database.
> In general, it will work fine, until you have to VACUUM the
> pg_largeobject table. Unless you have a very powerful I/O subsystem,
> this VACUUM will kill your performance.
Good point about the vacuum issue; I haven't had to deal with vacuuming
very large objects.
> > You're forgetting about cleanup and transactions. If you store outside
> > the database you either have to write some kind of garbage collector, or
> > you add a trigger to delete the file on disk when the row in the
> > database pointing at it is deleted and hope that the transaction doesn't
> > rollback.
>
> Our solution to this problem was to have a separate table of "external
> files to delete". When you want to delete a file, you just stuff an
> entry into this table. If your transaction rolls back, so does your
> insert into this table. You have a separate thread that periodically
> walks this table and zaps the files from the filesystem.
Sure, there's lots of ways around it. My point was that there *is* a
tradeoff.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-02-11 20:32 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-01 00:32 Storing Digital Video Rodrigo Madera <rodrigo.madera@gmail.com>
2006-02-01 00:37 ` Jeffrey W. Baker <jwb@gghcwest.com>
2006-02-01 00:51 ` Matt Davies | Postgresql List <matt-postgresql@mattdavies.net>
2006-02-06 08:30 ` Albert Cervera Areny <albert@sedifa.com>
2006-02-09 13:45 ` Nate Byrnes <nate@qabal.org>
2006-02-09 15:18 ` Craig A. James <cjames@modgraph-usa.com>
2006-02-09 15:58 ` Nate Byrnes <nate@qabal.org>
2006-02-09 19:26 ` Jim C. Nasby <jnasby@pervasive.com>
2006-02-09 23:14 ` Jan Peterson <jan.l.peterson@gmail.com>
2006-02-11 20:32 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox