# Streaming from Database

**URL:** <https://www.ruby-forum.com/t/streaming-from-database/96383>\
**Category:** Rails\
**Created:** [June 28, 2006, 3:41pm UTC](https://www.ruby-forum.com/t/streaming-from-database/96383 "2006-06-28T15:41:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jeff\_LaMarche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c5a1d2/32.png) [@Jeff\_LaMarche](https://www.ruby-forum.com/u/Jeff_LaMarche)\
**Post date:** [June 28, 2006, 3:41pm UTC](https://www.ruby-forum.com/t/streaming-from-database/96383/1 "2006-06-28T15:41:49Z")

</div>

I need to store rather large data in the database using blobs. I’ve been  
working with Rails and have designed a proof-of-concept setup that works  
pretty well, except there’s a considerable performance hit with very  
large files due to the fact that ActiveRecord loads the entire blob into  
memory before the data can get passed to send\_data.

I know at least one person is going to feel obliged to jump in here and  
tell me that I’m an idiot for trying to store large files in the  
database. Please don’t; there are valid reasons for doing this here,  
including:

1. It’s a hard requirement. An existing J2EE system is able to stream  
data from the database by only allocating a buffer; I’m trying to show  
that Rails can handle it as well
2. There will be multiple instances of the application running on  
multiple boxes all connecting to the same database, so the database is  
the only place we can store data that will be accessible to all  
instances
3. The database is a big honking box with a fiber connected SAN which  
will perform much better than than the single IDE drives in the  
application server boxes even if we could replicate the files to every  
instance

I know that doing this will probably involve some database specific  
code, and I don’t mind rolling up my sleeves and writing code, but I’m  
still relatively new to Rails, so if anyone could offer any advice or  
input about the best way to approach this or what objects and methods I  
need to override, I’d really appreciate it.

Thanks much,  
Jeff

BTW: My proof-of-concept system is PostgreSQL; the production system (if  
and when) will either be Oracle or PostgreSQL. Right now I’m just  
interested in PostgreSQL.

---

<div class="post-metadata">

**Author:** ![Jeff\_LaMarche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c5a1d2/32.png) [@Jeff\_LaMarche](https://www.ruby-forum.com/u/Jeff_LaMarche)\
**Post date:** [June 29, 2006, 2:46am UTC](https://www.ruby-forum.com/t/streaming-from-database/96383/2 "2006-06-29T02:46:12Z")

</div>

Okay, in case anyone is interested, I dove in and found a solution. I  
don’t claim it’s the best solution, but here is one way that you can  
bypass the memory overhead with large objects stored as bytea fields  
(blobs) in PostgreSQL. The solution code is database-specific, so all  
you MySQL folks won’t be able to use it, sorry.

Here was my original code to retrieve the object:

@thePic = Picture.find(params[:id])  
send\_data(@thePic.raw.data, :filename =\> @thePic.raw.filename, :type =\>  
@thePic.image.content\_type, :disposition =\> “attachment”)

This wasn’t useable for my situation for large files. When I had a large  
file (\>10 megs), this code would take 20 seconds or longer before I  
would begin to see the download in my dev environment (17" MacBook Pro).

With a little research and tweaking, I replaced it with this:

result = ActiveRecord::Base.connection.execute(“select a.content\_type,  
a.filename, a.data from picture\_data a, pictures b where b.raw\_image\_id  
= a.id and b.id = #{params[:id]}”);  
@response.headers[‘Pragma’] = ’ ’  
@response.headers[‘Cache-Control’] = ’ ’  
@response.headers[‘Content-type’] = result.getvalue(0,0)  
@response.headers[‘Content-Disposition’] = “attachment;  
filename=#{result.getvalue(0,1)}”  
@response.headers[‘Accept-Ranges’] = ‘bytes’  
@response.headers[‘Content-Transfer-Encoding’] = ‘binary’  
@response.headers[‘Content-Description’] = ‘File Transfer’  
render\_text result.getvalue(0,2)

It’s longer, ugly, and not as readable, but the end result? No  
noticeable delay before the download starts. There most likely are ways  
to improve upon this, but as it is, this gives me an order of magnitude  
performance boost and makes it possible for me to keep large files in  
the database 🙂

Comments, improvements, etc. welcome.

Jeff

---

<div class="post-metadata">

**Author:** ![Jeff\_LaMarche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c5a1d2/32.png) [@Jeff\_LaMarche](https://www.ruby-forum.com/u/Jeff_LaMarche)\
**Post date:** [June 29, 2006, 2:47am UTC](https://www.ruby-forum.com/t/streaming-from-database/96383/3 "2006-06-29T02:47:48Z")

</div>

Whoah… hold off on using that - it doesn’t work. :-/