Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a chat application (Yes, another one :P) and am really curious about the best way to store a chat's history(String username, String Message, Time, and possibly string channel depending on how I end up setting it up.

Since I'm using PostgreSQL anyway, I was tempted for going with it for chat history. However, I don't see a sane approach for using it to do this without generating a new row for every message, something obviously not very scale-able.

I know some MongoDB and was thinking that using a json array for each channel and storing the history in that would work well.

Is using two entirely databases in the same application a terrible idea?

Are there any other alternatives I should take a look at? (I'm open to pretty much anything)

Is there a popular PostgreSQL way of doing this? I took a look at a JSON columns, but it seemed fairly new and limited at the moment.

share|improve this question
2  
"However, I don't see a sane approach for using it to do this without generating a new row for every message, something obviously not very scale-able" . Why not? So long as you use a commit delay or async commit, and/or batch messages into transactions, you should be fine. –  Craig Ringer Mar 12 at 4:47
    
Adding to @CraigRinger's comment, I am wondering if constantly updating the MongoDB document for a given chat is faster than storing rows in PostgreSQL (or fast enough for being a viable solution). –  dezso Mar 12 at 8:18
    
I'll add a warning - having an unbounded array is a very bad design approach for MongoDB - it causes the document to constantly grow (has a limit of 16MB) and can cause problems. It would be a better idea to have documents per message and collections for each channel, but likely testing would be needed to see how well/badly it would work. Hard to speak to best practice with such a general description. –  Adam C Mar 12 at 18:40
    
Thanks for the responses. I was concerned that the number of rows per table would get out of hand, as it would be easy for even a lightly used chat service to generate thousands of messages daily. It seems like I would run into the same problems using mongodb as well though. –  joshua-anderson Mar 12 at 23:00
add comment

1 Answer 1

You can use hstore for use EAV. hstore is available from pgsql 8.4. If you have installed json you can then use function hstore_to_json(hstore)

select hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')

this will output:

{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.