Re: Video storage in Postgres?

"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