1

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.

Improve this question
4
  • 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 '14 at 4:47
  • 2
    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 '14 at 8:18
  • 1
    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 '14 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 '14 at 23:00
2

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"}
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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