PostgreSQL – Efficiently Storing Irregular or Repeating Intervals

database-designpostgresqltime

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing.

Right now I'm storing them in a postgres database, but I have a feeling it doesn't scale well.

The current layout is:

ID - MESSAGE - DATE - TIME

The DATE and TIME field holds the time and date for when the message should be send for processing. This doesn't scale well, as if a message needs to be send the first monday every month, it would take up 12x as much space.

Problem is that I can't seem to find another way to represent when a message should be send for processing? Ideally I'd love to be able to represent each and every date in a single row.

We were also disucssing using Redis, but quickly decided not too, as we would need the database for the webfrontend.

Anyone have any idea how to optimize the message storage? How to represent when a message should be send for processing?

I am also open for any other suggestions on how to tackle this.

Best Answer

Always look for standards that support your requirements. What is a standard that supports recurring calendar events? ICalendar RRULEs:

http://en.wikipedia.org/wiki/ICalendar

http://www.kanzaki.com/docs/ical/rrule.html

You can either store the rule as plain text and parse it as needed, or use a database schema for them:

SQL Schema: https://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard/1397019#1397019

Perl: http://search.cpan.org/~rfrankel/iCal-Parser-1.16/lib/iCal/Parser.pm

PostgreSQL-specific: http://svn.expressolivre.org/contrib/davical/dba/rrule_functions-8.1.sql
Performance-wise, you can use Materialized Views to calculate events, say one month in advance and one month prior (if required):

https://stackoverflow.com/questions/4239871/when-building-a-calendar-app-should-i-store-dates-or-recurrence-rules-in-my-dat/10151804#10151804

Additional information : http://www.vertabelo.com/blog/technical-articles/again-and-again-managing-recurring-events-in-a-data-model This article explains each and every aspect of repeated events. Please read it before coming to any conclusions.

Related Solutions

Mysql – Personal and Global messaging database design

You could combine recipient and message_status. That would halve the number of inserts.

Handling a chat room in a database

The following is kind of a shot in the dark in lack of clarification of the question, but as I had built it already earlier I'll post it in absense of any other answers.

How about implementing the message-deletion-per-recipient so that you only keep track of those messages that have been tagged as deleted-by-recipient? That would translate into getting rid of the recipient table as you had it and instead having a recipient table plus a cross-ref table for deleted messages:

recipient table

| recID              |
| recipientID --FK   |


deletedmessages table

| msgID --FK         |
| userID --FK   |

When fetching messages to be displayed to a given recipient, it would be enough to either join the two tables on msgID or to do something like

... WHERE NOT EXISTS (SELECT 1 FROM deletedmessages dm WHERE dm.recipientID = <current_user_ID> AND dm.msgID = m.msgID)

to filter out the deleted ones. Having deletedmessages indexed by both columns (probably mandatory to get the foreign keys references properly set up) would allow the DB to execute this very efficiently.

This way deletedmessages would only grow when a user actually deletes a message and not grow that much.

There's a SQLfiddle with the above solution.

Related Question