large crontab database design

[David Garamond]

I was thinking on how one would design an optimal (performance-wise)
database of large number of schedules with crontab-like semantic. There
will potentially be hundreds of thousands or even millions of "crontab"
entries, and there will be a query run once every minute to determine
which entries are to be run for that minute. The primary goal is, of
course, for the query to run as fast as possible.

My first stab is, straightforward enough, like below:

create table schedule (
id int not null primary key,
command text
);

create table schedule_detail(
schedule_id int not null references schedule(id),
minute smallint not null,
hour smallint not null,
day smallint not null,
month smallint not null,
year smallint not null,
dayofweek smallint not null
);

create index idx_schedule_detail_schedule_id on
schedule_detail(schedule_id);
create index idx_schedule_detail_minute on
schedule_detail(minute);
create index idx_schedule_detail_hour on
schedule_detail(hour);
create index idx_schedule_detail_day on
schedule_detail(day);
create index idx_schedule_detail_month on
schedule_detail(month);
create index idx_schedule_detail_year on
schedule_detail(year);
create index idx_schedule_detail_dayofweek on
schedule_detail(dayofweek);

A "multiple items" syntax like '1,2,3' in a crontab time field will be
presented with several records in the schedule_detail table. An "every
N" syntax like '*/5' will be represented by a negative number '-5' in
the database field.

For example, this crontab entry:

# every three hours except on weekends (sat/sun)
0 */3 * * 1,2,3,4,5 CHECK-THE-COPIER-MACHINE

will translate to these records:

insert into schedule values (1, 'CHECK-THE-COPIER-MACHINE');
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 1);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 2);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 3);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 4);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 5);

The query will then be:

select distinct schedule_id,command from schedule_detail
left join schedule on schedule_id=schedule.id
where
(year=extract(year from current_date) or
(year<0 and cast(extract(year from current_date) as smallint)
% year = 0)) and
(month=extract(month from current_date) or
(month<0 and cast(extract(month from current_date) as smallint)
% month = 0)) and
(day=extract(month from current_date) or
(day<0 and cast(extract(day from current_date) as smallint)
% day = 0)) and
(hour=extract(hour from current_time) or
(hour<0 and cast(extract(hour from current_time) as smallint)
% hour = 0)) and
(minute=extract(minute from current_time) or
(minute<0 and cast(extract(minute from current_time) as smallint)
% month = 0));

Several questions:

1. Any idea for a better design? The selectivity of each field is not so
great (only 60 different values for minute, 24 for day, etc).

2. What if we want to add support for things like "every 3 hours after
1:00AM" (1:00, 4:00, 7:00, and so on) or "beginning from 24 Jan 2004 and
every 12 days after that." We won't be able to use the negative number
and modulo trick, or even the crontab-like database fields for this.

3. I think I have a race condition in my query (because I call the
current_date & current_time function several times), but I don't know
how to fix this without resorting to procedure/function.

--
dave

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Csaba Nagy]

David,

I have another solution working (not using data base persistence, but
the idea is the same).
We have the next run time saved along with the original cron-tab string,
which is left as it is (not broken down to any details). The system
polls for the earliest "next runtime", and executes the associated taks
if the time is not in the future, otherwise does nothing.
Then find the next run time for the just executed task based on it's
time table, and update it in the schedule table.
Of course in a database version this would involve the locking of the
executed task's row, so no other machine/thread will execute it at the
same time (I assume it is about some high concurrency system). I'm not
sure what kind of race conditions can this cause...

Just my 2c,
Csaba.
On Tue, 2004-02-03 at 15:52, David Garamond wrote:

I was thinking on how one would design an optimal (performance-wise)
database of large number of schedules with crontab-like semantic. There
will potentially be hundreds of thousands or even millions of "crontab"
entries, and there will be a query run once every minute to determine
which entries are to be run for that minute. The primary goal is, of
course, for the query to run as fast as possible.

My first stab is, straightforward enough, like below:

create table schedule (
id int not null primary key,
command text
);

create table schedule_detail(
schedule_id int not null references schedule(id),
minute smallint not null,
hour smallint not null,
day smallint not null,
month smallint not null,
year smallint not null,
dayofweek smallint not null
);

create index idx_schedule_detail_schedule_id on
schedule_detail(schedule_id);
create index idx_schedule_detail_minute on
schedule_detail(minute);
create index idx_schedule_detail_hour on
schedule_detail(hour);
create index idx_schedule_detail_day on
schedule_detail(day);
create index idx_schedule_detail_month on
schedule_detail(month);
create index idx_schedule_detail_year on
schedule_detail(year);
create index idx_schedule_detail_dayofweek on
schedule_detail(dayofweek);

A "multiple items" syntax like '1,2,3' in a crontab time field will be
presented with several records in the schedule_detail table. An "every
N" syntax like '*/5' will be represented by a negative number '-5' in
the database field.

For example, this crontab entry:

# every three hours except on weekends (sat/sun)
0 */3 * * 1,2,3,4,5 CHECK-THE-COPIER-MACHINE

will translate to these records:

insert into schedule values (1, 'CHECK-THE-COPIER-MACHINE');
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 1);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 2);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 3);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 4);
insert into schedule_detail values (1, 0, -3, -1, -1, -1, 5);

The query will then be:

select distinct schedule_id,command from schedule_detail
left join schedule on schedule_id=schedule.id
where
(year=extract(year from current_date) or
(year<0 and cast(extract(year from current_date) as smallint)
% year = 0)) and
(month=extract(month from current_date) or
(month<0 and cast(extract(month from current_date) as smallint)
% month = 0)) and
(day=extract(month from current_date) or
(day<0 and cast(extract(day from current_date) as smallint)
% day = 0)) and
(hour=extract(hour from current_time) or
(hour<0 and cast(extract(hour from current_time) as smallint)
% hour = 0)) and
(minute=extract(minute from current_time) or
(minute<0 and cast(extract(minute from current_time) as smallint)
% month = 0));

Several questions:

1. Any idea for a better design? The selectivity of each field is not so
great (only 60 different values for minute, 24 for day, etc).

2. What if we want to add support for things like "every 3 hours after
1:00AM" (1:00, 4:00, 7:00, and so on) or "beginning from 24 Jan 2004 and
every 12 days after that." We won't be able to use the negative number
and modulo trick, or even the crontab-like database fields for this.

3. I think I have a race condition in my query (because I call the
current_date & current_time function several times), but I don't know
how to fix this without resorting to procedure/function.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[David Garamond]

Csaba,

Csaba Nagy wrote:

We have the next run time saved along with the original cron-tab string,
which is left as it is (not broken down to any details). The system
polls for the earliest "next runtime", and executes the associated taks
if the time is not in the future, otherwise does nothing.

A nice approach, thanks!

--
dave
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html