Thread: Video storage in Postgres?

Anyone aware of companies using techniques in Postgres to store videos? My understanding from talking to a bunch of online video companies is that they typically store the links in the DBMS that points to the video file to a file system.
Thoughts? Most say they would love to store the video in the DBMS itself.

Z.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bob Zurek wrote:
> Anyone aware of companies using techniques in Postgres to store videos? My understanding from talking to a bunch of
onlinevideo companies is that they typically store the links in the DBMS that points to the video file to a file
system. 
> Thoughts? Most say they would love to store the video in the DBMS itself.

I used to know of one and I have certainly run into folks storing audio.
The main problem is the size of video tends to leave only LO as an option.

Joshua D. Drake


>
> Z.
>


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxQSKATb/zqfZUUQRAmv5AKCXzDsBMxPUjO4C9spn7oSSrR9jLQCeLSVC
ROAiPsPzOclR7Vtn5dxqHbk=
=ojSh
-----END PGP SIGNATURE-----

This, along with the "appropriate apps for mySQL", reminds me of the
Parable of the Porsche that I just made up.

My buddy Bob wanted to tow a horse trailer with a Porsche.  He could haul
the jockeys around to drive horses listed in the directories at different
stables, but he'd love to haul his horses with his Porsche.

Bob decided to seek the expertise of wise men Chris Titus and Tim Taylor.
"Yes, you can haul a horse trailer with a Porsche, but it won't be easy.
You see, the Porsche is not designed to haul trailers.  You need something
that is, but is also a Porsche."

So the wise men thought some more.  "You must get a Volkswagen Toureg with
the diesel V10, for diesels are designed to tow trailers.  Then, to
satisfy the requirement of it being a Porsche, you must purchase the
fenders, bumpers, and other bling from a Porsche Cayenne and fix it to
your Toureg.  Then you will have a Porsche that can tow a horse trailer."

Moral of the story: Yep, you can tow a horse trailer with a Porsche, or
serve video with your DB server, or base your enterprise app on MySQL -
but it just isn't right.  :)

In all seriousness, what advantages accrue by serving video from the DB?

Cheers,
JK

BTW - there's a guy near where I live who drives a Cayenne.  I've always
wanted to go up to him and comment on how nice his Taureg is - but that
just wouldn't be right, now would it? ;) The two SUV's are actually the
same platform; the Porsche just has differently styled bling.

JoshuaKramer wrote:

> In all seriousness, what advantages accrue by serving video from the DB?

Well all the advantages people associate with LOBs:
- single place for all data * for backups * for replication * for ACLs

I often just care about the first two, in which case I simply employ a
mod_rewrite rule, that points to the on disc version of the file and
fals back to serving directly from the database (but also dump a file to
the proper location on disc).

regards,
Lukas

What hear from the video companies is that they would find it easier
than using a file system and that they could have the database handle
backups, fault tolerance, security, recovery and replication.

Z.

-----Original Message-----
From: pgsql-advocacy-owner@postgresql.org
[mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of JoshuaKramer
Sent: Thursday, August 16, 2007 11:43 PM
To: pgsql-advocacy@postgresql.org
Subject: Re: [pgsql-advocacy] Video storage in Postgres?


This, along with the "appropriate apps for mySQL", reminds me of the
Parable of the Porsche that I just made up.

My buddy Bob wanted to tow a horse trailer with a Porsche.  He could
haul
the jockeys around to drive horses listed in the directories at
different
stables, but he'd love to haul his horses with his Porsche.

Bob decided to seek the expertise of wise men Chris Titus and Tim
Taylor.
"Yes, you can haul a horse trailer with a Porsche, but it won't be easy.

You see, the Porsche is not designed to haul trailers.  You need
something
that is, but is also a Porsche."

So the wise men thought some more.  "You must get a Volkswagen Toureg
with
the diesel V10, for diesels are designed to tow trailers.  Then, to
satisfy the requirement of it being a Porsche, you must purchase the
fenders, bumpers, and other bling from a Porsche Cayenne and fix it to
your Toureg.  Then you will have a Porsche that can tow a horse
trailer."

Moral of the story: Yep, you can tow a horse trailer with a Porsche, or
serve video with your DB server, or base your enterprise app on MySQL -
but it just isn't right.  :)

In all seriousness, what advantages accrue by serving video from the DB?

Cheers,
JK

BTW - there's a guy near where I live who drives a Cayenne.  I've always

wanted to go up to him and comment on how nice his Taureg is - but that
just wouldn't be right, now would it? ;) The two SUV's are actually the
same platform; the Porsche just has differently styled bling.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Friday 17 August 2007 01:58, Lukas Kahwe Smith wrote:
> JoshuaKramer wrote:
> > In all seriousness, what advantages accrue by serving video from the DB?
>
> Well all the advantages people associate with LOBs:
> - single place for all data
>   * for backups
>   * for replication
>   * for ACLs
>
> I often just care about the first two, in which case I simply employ a
> mod_rewrite rule, that points to the on disc version of the file and
> fals back to serving directly from the database (but also dump a file to
> the proper location on disc).
>

Don't forget about ACID garauntees. When inserting video into a db, you have
the garauntee that if something goes wrong the meta-data rolls back and your
in a complete state. When keeping video on the fs, if an error occures when
writing the file, you have to have some application code to ensure that
everything in the db cleans up appropriatly. This is not impossible, but
letting the db do it for you is certainly easier.

That said, most video sites end up employing some type of cacheing system to
get video's sent around (think akami), so it becomes moot to force everything
into the db, since they aren't going to serve all that video from the db
anyway.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

My understanding is that YouTube is using MySQL is that correct?

Bob

-----Original Message-----
From: pgsql-advocacy-owner@postgresql.org
[mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of Robert Treat
Sent: Friday, August 17, 2007 10:20 AM
To: pgsql-advocacy@postgresql.org
Cc: Lukas Kahwe Smith; JoshuaKramer
Subject: Re: [pgsql-advocacy] Video storage in Postgres?

On Friday 17 August 2007 01:58, Lukas Kahwe Smith wrote:
> JoshuaKramer wrote:
> > In all seriousness, what advantages accrue by serving video from the
DB?
>
> Well all the advantages people associate with LOBs:
> - single place for all data
>   * for backups
>   * for replication
>   * for ACLs
>
> I often just care about the first two, in which case I simply employ a
> mod_rewrite rule, that points to the on disc version of the file and
> fals back to serving directly from the database (but also dump a file
to
> the proper location on disc).
>

Don't forget about ACID garauntees. When inserting video into a db, you
have
the garauntee that if something goes wrong the meta-data rolls back and
your
in a complete state. When keeping video on the fs, if an error occures
when
writing the file, you have to have some application code to ensure that
everything in the db cleans up appropriatly. This is not impossible, but

letting the db do it for you is certainly easier.

That said, most video sites end up employing some type of cacheing
system to
get video's sent around (think akami), so it becomes moot to force
everything
into the db, since they aren't going to serve all that video from the db

anyway.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bob Zurek wrote:
> What hear from the video companies is that they would find it easier
> than using a file system and that they could have the database handle
> backups, fault tolerance, security, recovery and replication.

I assume then that the performance hit the database is going to take is
acceptable? Keep in mind that if you stored in Bytea, a 700 Meg video,
you are going to use 700 Meg of ram to pull that video out "per pull".

Joshua D. Drake


>
> Z.
>
> -----Original Message-----
> From: pgsql-advocacy-owner@postgresql.org
> [mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of JoshuaKramer
> Sent: Thursday, August 16, 2007 11:43 PM
> To: pgsql-advocacy@postgresql.org
> Subject: Re: [pgsql-advocacy] Video storage in Postgres?
>
>
> This, along with the "appropriate apps for mySQL", reminds me of the
> Parable of the Porsche that I just made up.
>
> My buddy Bob wanted to tow a horse trailer with a Porsche.  He could
> haul
> the jockeys around to drive horses listed in the directories at
> different
> stables, but he'd love to haul his horses with his Porsche.
>
> Bob decided to seek the expertise of wise men Chris Titus and Tim
> Taylor.
> "Yes, you can haul a horse trailer with a Porsche, but it won't be easy.
>
> You see, the Porsche is not designed to haul trailers.  You need
> something
> that is, but is also a Porsche."
>
> So the wise men thought some more.  "You must get a Volkswagen Toureg
> with
> the diesel V10, for diesels are designed to tow trailers.  Then, to
> satisfy the requirement of it being a Porsche, you must purchase the
> fenders, bumpers, and other bling from a Porsche Cayenne and fix it to
> your Toureg.  Then you will have a Porsche that can tow a horse
> trailer."
>
> Moral of the story: Yep, you can tow a horse trailer with a Porsche, or
> serve video with your DB server, or base your enterprise app on MySQL -
> but it just isn't right.  :)
>
> In all seriousness, what advantages accrue by serving video from the DB?
>
> Cheers,
> JK
>
> BTW - there's a guy near where I live who drives a Cayenne.  I've always
>
> wanted to go up to him and comment on how nice his Taureg is - but that
> just wouldn't be right, now would it? ;) The two SUV's are actually the
> same platform; the Porsche just has differently styled bling.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxcrPATb/zqfZUUQRAjuNAJ4+fMO4QA1lRRGJPyK30sqq0YsDkACcDi6G
BLn31ICs0fxElAYWpz/WcLM=
=cFqY
-----END PGP SIGNATURE-----

AFAIK they still use a mix of Oracle and MySQL, though now that they are a
part of google, they might be switching to something more bigtable oriented.

Robert Treat

On Friday 17 August 2007 11:55, Bob Zurek wrote:
> My understanding is that YouTube is using MySQL is that correct?
>
> Bob
>
> -----Original Message-----
> From: pgsql-advocacy-owner@postgresql.org
> [mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of Robert Treat
> Sent: Friday, August 17, 2007 10:20 AM
> To: pgsql-advocacy@postgresql.org
> Cc: Lukas Kahwe Smith; JoshuaKramer
> Subject: Re: [pgsql-advocacy] Video storage in Postgres?
>
> On Friday 17 August 2007 01:58, Lukas Kahwe Smith wrote:
> > JoshuaKramer wrote:
> > > In all seriousness, what advantages accrue by serving video from the
>
> DB?
>
> > Well all the advantages people associate with LOBs:
> > - single place for all data
> >   * for backups
> >   * for replication
> >   * for ACLs
> >
> > I often just care about the first two, in which case I simply employ a
> > mod_rewrite rule, that points to the on disc version of the file and
> > fals back to serving directly from the database (but also dump a file
>
> to
>
> > the proper location on disc).
>
> Don't forget about ACID garauntees. When inserting video into a db, you
> have
> the garauntee that if something goes wrong the meta-data rolls back and
> your
> in a complete state. When keeping video on the fs, if an error occures
> when
> writing the file, you have to have some application code to ensure that
> everything in the db cleans up appropriatly. This is not impossible, but
>
> letting the db do it for you is certainly easier.
>
> That said, most video sites end up employing some type of cacheing
> system to
> get video's sent around (think akami), so it becomes moot to force
> everything
> into the db, since they aren't going to serve all that video from the db
>
> anyway.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

If it is so now, would it make sense to have also a different version that
would require less memory? Or even change the current one? In any case the
data comes from the disk, and is going out using a network connection that
very often is slower than the speed from the disk.

I don't know, if there is enough demand for such a feature, but I don't see
that it would be sensible to use memory in that way.

Rgs,

Jussi




Joshua D. Drake (jd@commandprompt.com) kirjoitti:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Bob Zurek wrote:
> > What hear from the video companies is that they would find it easier
> > than using a file system and that they could have the database handle
> > backups, fault tolerance, security, recovery and replication.
>
> I assume then that the performance hit the database is going to take is
> acceptable? Keep in mind that if you stored in Bytea, a 700 Meg video,
> you are going to use 700 Meg of ram to pull that video out "per pull".
>
> Joshua D. Drake
>
>
> >
> > Z.
> >
> > -----Original Message-----
> > From: pgsql-advocacy-owner@postgresql.org
> > [mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of JoshuaKramer
> > Sent: Thursday, August 16, 2007 11:43 PM
> > To: pgsql-advocacy@postgresql.org
> > Subject: Re: [pgsql-advocacy] Video storage in Postgres?
> >
> >
> > This, along with the "appropriate apps for mySQL", reminds me of the
> > Parable of the Porsche that I just made up.
> >
> > My buddy Bob wanted to tow a horse trailer with a Porsche.  He could
> > haul
> > the jockeys around to drive horses listed in the directories at
> > different
> > stables, but he'd love to haul his horses with his Porsche.
> >
> > Bob decided to seek the expertise of wise men Chris Titus and Tim
> > Taylor.
> > "Yes, you can haul a horse trailer with a Porsche, but it won't be easy.
> >
> > You see, the Porsche is not designed to haul trailers.  You need
> > something
> > that is, but is also a Porsche."
> >
> > So the wise men thought some more.  "You must get a Volkswagen Toureg
> > with
> > the diesel V10, for diesels are designed to tow trailers.  Then, to
> > satisfy the requirement of it being a Porsche, you must purchase the
> > fenders, bumpers, and other bling from a Porsche Cayenne and fix it to
> > your Toureg.  Then you will have a Porsche that can tow a horse
> > trailer."
> >
> > Moral of the story: Yep, you can tow a horse trailer with a Porsche, or
> > serve video with your DB server, or base your enterprise app on MySQL -
> > but it just isn't right.  :)
> >
> > In all seriousness, what advantages accrue by serving video from the DB?
> >
> > Cheers,
> > JK
> >
> > BTW - there's a guy near where I live who drives a Cayenne.  I've always
> >
> > wanted to go up to him and comment on how nice his Taureg is - but that
> > just wouldn't be right, now would it? ;) The two SUV's are actually the
> > same platform; the Porsche just has differently styled bling.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> >
>
>
> - --
>
>       === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
> PostgreSQL solutions since 1997  http://www.commandprompt.com/
>             UNIQUE NOT NULL
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFGxcrPATb/zqfZUUQRAjuNAJ4+fMO4QA1lRRGJPyK30sqq0YsDkACcDi6G
> BLn31ICs0fxElAYWpz/WcLM=
> =cFqY
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Bob,

> Anyone aware of companies using techniques in Postgres to store videos?
> My understanding from talking to a bunch of online video companies is
> that they typically store the links in the DBMS that points to the video
> file to a file system. Thoughts? Most say they would love to store the
> video in the DBMS itself.

APC has a server room security/environment solution which stores up to 1TB
of annotated video frames in each PostgreSQL database.  I know there's
another as well, but I can't remember it right now.

For most applications, it makes more sense to store video in the filesystem
and not in the DB.  Also for *retrieval* of video, we have an issue; libpq
really needs a way to retrive large data 1MB at a time and not wait for
the whole field/row.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:

> For most applications, it makes more sense to store video in the filesystem
> and not in the DB.  Also for *retrieval* of video, we have an issue; libpq
> really needs a way to retrive large data 1MB at a time and not wait for
> the whole field/row.

I'm sure you realize you can do this using lo_* but you can also do it using
TOAST if the toasted data is uncompressed. substr() on text fields and bytea
will fetch only the TOAST chunks it needs to satisfy the range requested.

If you mark the column as storage EXTERNAL then issue a series of selects with
successive substring() ranges -- you could prepare the query once and then
stream executes for the successive ranges, you can process the data in chunks.

It's not as slick as having libpq do it in general but an awful lot of things
would have to change to make streaming out a video stored in a single datum
possible -- not just in libpq but in the server as well.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Gregory Stark wrote:

> It's not as slick as having libpq do it in general but an awful lot of things
> would have to change to make streaming out a video stored in a single datum
> possible -- not just in libpq but in the server as well.

Would be nice to have a proper streaming API again. BTW: Not sure if
there is room for cooperation, but the MySQL community guy behind the
only real community storage engine for MySQL PBXT is working on a
streaming API for MySQL [1].

regards,
Lukas

[1] http://pbxt.blogspot.com/

Greg,

> It's not as slick as having libpq do it in general but an awful lot of
> things would have to change to make streaming out a video stored in a
> single datum possible -- not just in libpq but in the server as well.

Let me add it to TODO then.  We'd capture a whole new user community if we
had a decent streaming API.  If we could use the same mechanism to return
a few rows at a time, then that would be keen too ...

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh Berkus wrote:
> Greg,
>
>> It's not as slick as having libpq do it in general but an awful lot of
>> things would have to change to make streaming out a video stored in a
>> single datum possible -- not just in libpq but in the server as well.
>
> Let me add it to TODO then.  We'd capture a whole new user community if we
> had a decent streaming API.  If we could use the same mechanism to return
> a few rows at a time, then that would be keen too ...

Well even more so, just having a more reasonable mechanism for pulling
large bits of data would be nice. If all of our data could be pulled in
a similar way to LO (where it is pulled a little bit at a time and
released), our efficiency would go up quite a bit.

Joshua D. Drake


>


- --

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxkXXATb/zqfZUUQRApWJAJ9wHErgFlEciDeKxWuZUOa0JV3sQACfaWos
4wRv5astDWSsP/HgI4SQv/k=
=6n3N
-----END PGP SIGNATURE-----

Totally agree on this topic area as I've spent time with several online
video companies around this topic.


-----Original Message-----
From: pgsql-advocacy-owner@postgresql.org
[mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Friday, August 17, 2007 6:59 PM
To: pgsql-advocacy@postgresql.org
Subject: Re: [pgsql-advocacy] Video storage in Postgres?

Greg,

> It's not as slick as having libpq do it in general but an awful lot of
> things would have to change to make streaming out a video stored in a
> single datum possible -- not just in libpq but in the server as well.

Let me add it to TODO then.  We'd capture a whole new user community if
we
had a decent streaming API.  If we could use the same mechanism to
return
a few rows at a time, then that would be keen too ...

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate