[samcheng]

Any support for "rolling" partitions? e.g. A partition for data updated less than a day ago, another for data from 2-7 days ago, etc.

I miss this from Oracle; it allows nice index optimizations as the query patterns are different for recent data vs. historical data.

I think it could be set up with a mess of triggers and a cron job... but it would be nice to have a canonical way to do this.

[jtc331]

The fundamental issue here is that you'd actually have to move the rows between relations given that Postgres maintains separate storage etc. for each. There's no good way to do that.

[willvarfar]

Living with the cron jobs for a big mysql db, and wishing the DB understood this seemingly common use-case :(

[Klathmon]

Honestly i wouldn't call it "common". It's useful, and if it existed I could see it changing how I design a database, but it's not something I can say i've ever thought about needing before.

But then again, maybe i'm the outlier here.

[willvarfar]

Its very common to partition by a function of a date, e.g. `PARTITION BY RANGE( DAY(event_timestamp) )` etc. The docs talk a lot about partitioning by dates http://dev.mysql.com/doc/refman/5.7/en/partitioning-range.ht... but, as said, you have to have a cron job to keep adding new partitions and archiving/dropping old partitions etc. Its a shame that couldn't be automated by the DB itself.

[lobster_johnson]

How does this work in Oracle? Seeing as the partitioning constraint would be time-dependent, wouldn't it need to re-evaluate it at regular intervals in order to shuffle data around? Is the feature explicitly time-oriented?

[mulmen]

I don't think oracle can do this exactly but the query planner does understand time based partitions so if you do something like:

   SELECT * FROM partitioned_table WHERE partition_date_key > SYSDATE - 1;

The query planner will only use the most recent partition. Combine this with Oracle's ability to merge partitions and you get "daily" partitions that become "weekly" partitions when the new week starts. Alternately you could wait a month and combine all the days of last month into a single partition and then even combine months into years.

The partition intervals are based on specific dates/times, not on the relative time from query execution.

Oracle also supports row movement which is the biggest missing feature here I believe.