Efficiently storing irregular/repeating intervals (think calendar/events)

Question

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.

Answer 1

1. 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

2. 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.

Answer 2

As Zoltan pointed out, unless you have MANY millions of rows, I don't see a scaling issue. There are also many libraries for scheduling things such as Quartz on Java for example. These will store the recurring schedule as a cron-like expression. Because your example above has a flaw, if the recurrence is every Monday, then it's 52 x number of years the service will go for.

So you can store a date, or a recurrence pattern.

Answer 3

Make a different table with the a part for te message id

table 1: id - message

table 2: id - date

See if you can somehow arrange the "table 2" by its date, and format the date as following:

min - hour - dom - mon - wom - dow (dom = day of month, mon = month, wom = week of month, dow = day of week)

You can use numbers as "12", or wildcards as "*", maybe ad a first as "<" or a last as ">".

Then you query the second table first, you may also use a 2nd and 3rd table where the 2nd talbe only have single use messages, and the 3rd table have repeating instructions. you can then also query the 3rd table to add messages to the 2nd table, but only fill up the second table up for a month or year.

This inspiration came from the way crontab schedules work

*     *     *   *    *        command to be executed
-     -     -   -    -
|     |     |   |    |
|     |     |   |    +----- day of week (0 - 6) (Sunday=0)
|     |     |   +------- month (1 - 12)
|     |     +--------- day of        month (1 - 31)
|     +----------- hour (0 - 23)
+------------- min (0 - 59) 

Answer 4

If I understand the question correctly, as a user I can set an "alarm" on a specific date and recurrence, and get reminded?

Generally speaking, until you are going to hit >millions, you won't have any scaling problems with a normal SQL database, just use the right indexes.

I'd suggest you optimize the schema a bit: instead of storing when you will need to send it next, just store when it was last sent and the replay interval - then you can easily compute when it should be sent again.

Answer 5

The first thing you should be doing is storing the message text out-of-line in a separate table.

while that will incur the cost of a join to fetch it, it'll be a trivial b-tree index lookup, and will be massively outweighed by the benefits of having narrower rows to scan in the table containing scheduled events.

I suspect that if you think of this like a "recurring alarm" or "recurring calendar event" problem you will find more writing about how to store and efficiently query such data. It's not simple, because index-friendly ways to store it tend to involve repeating rows, which makes editing and updating painful.

Answer 6

you can use partitioning of the table.

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.

When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.

Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. ALTER TABLE NO INHERIT and DROP TABLE are both far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

Seldom-used data can be migrated to cheaper and slower storage media.

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

Range Partitioning

The table is partitioned into "ranges" defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example one might partition by date ranges, or by ranges of identifiers for particular business objects.