Efficiently storing irregular/repeating intervals (think calendar/events) 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.
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.