On Jun 27, 2011, at 12:36 PM, Christopher Browne wrote: > I wrote something on this on pgsql-general about 5 years ago that > still seems pretty relevant. > > http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php
iwantsandy.com (now defunct) originally had a solution like this. However it supported a slew of recurrences: * hours * 2xday * days * weeks * months * quarters * years * decades We had materializations of all of these going out 5 years or so. It took up an incredible amount of database space and was really slow. I replaced it with a variation on the code described in this blog post: http://www.justatheory.com/computers/databases/postgresql/recurring_events.html The database was a fraction of the original size and, because views were usually limited to a month at most, the number of rows generated for a query to show recurring events was quite limited (no one had an hourly reminder that when for more than a couple of days). Queries were a lot faster, too. So I think the materialization of dates can work in certain limited cases such as your "vacations 2005" example, and will be easier to use thanks to JOINs, I found that it performed poorly and was unnecessarily resource-intensive for our usage. And I suspect the same would be try for anyone building a calendar app with more than one simple kind of limited recurrence. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers