- Series: redb ecosystem
There's a table of GPS points. Vehicles push coordinates every few seconds, a day adds up to millions of rows, and the table is partitioned by time. Which means somebody has to create tomorrow's partition ahead of time and detach the ones older than ninety days. Otherwise, one fine night, an insert dies with no partition of relation "gps_points" found for row — at precisely the hour nobody is watching.
The problem is as old as the hills, and it's usually solved one of two ways: pg_cron inside the database, or an IHostedService with a PeriodicTimer in the app. The first has an observability problem — the job lives in the database, and your application logs know nothing about it. The second has the problem that a small private infrastructure grows around PeriodicTimer very quickly: retries, logging, "what if the previous run is still going", "what if there are three nodes".
I'll show a third way — a route. What follows is a walkthrough of the redb.Route.Quartz connector, calling a PostgreSQL function through the SQL connector, a splitter with error isolation, and an honest conversation about clustering, because that's where it actually gets interesting.
All the code in this post uses string URIs. redb.Route has fluent builders too, but a URI reads without knowing the API, and you can paste it into a config file.
redb ecosystem series. This is a continuation — newest posts first:
- The Amazon SQS + SNS connector in redb.Route, at-least-once and pub/sub via SNS→SQS. Still leaving MassTransit
- The RabbitMQ connector in redb.Route: the whole broker in one URI — RPC, competing consumers, dead-lettering
- redb 3.3.0: an enterprise .NET stack you actually own — typed store, a homegrown Apache Camel, and a runtime with a dashboard (all free)
- Two routes in an evening: from a debug worker to an enterprise runtime with redb.Route + Tsak
- Leaving MassTransit for a Camel state of mind: the Kafka connector, Scatter-Gather, and what really happens under a transaction
Full list on the profile. Sources: github.com/redbase-app/redb-route. About the database itself: redbase.app.
The SQL first
There's no magic at this level, so let's start from the most honest place — the function in the database. It takes a table name and a retention window, creates tomorrow's partition if it isn't there yet, and detaches everything past the window:
CREATE OR REPLACE FUNCTION maintain_partitions(tbl text, keep_days int)
RETURNS text AS $$
DECLARE
next_day date := (now() + interval '1 day')::date;
part_name text := format('%s_%s', tbl, to_char(next_day, 'YYYYMMDD'));
cutoff date := (now() - make_interval(days => keep_days))::date;
dropped int := 0;
old_part text;
BEGIN
IF to_regclass(part_name) IS NULL THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
part_name, tbl, next_day, next_day + 1);
END IF;
FOR old_part IN
SELECT c.relname
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
JOIN pg_class p ON p.oid = i.inhparent
WHERE p.relname = tbl
AND c.relname < format('%s_%s', tbl, to_char(cutoff, 'YYYYMMDD'))
LOOP
EXECUTE format('DROP TABLE %I', old_part);
dropped := dropped + 1;
END LOOP;
RETURN format('%s: +1 partition, -%s dropped', tbl, dropped);
END;
$$ LANGUAGE plpgsql;
The function returns a string so it shows up in the log. That "so it shows up in the log" is the single concession to convenience here; everything else is ordinary plpgsql you'd have written anyway.
The tick: cron://
The redb.Route.Quartz connector gives you two schemes. The first is cron:, a regular Quartz trigger with a cron expression:
cron://[group/]jobName?schedule=<cron-expression>&<options>
The second is qtimer:, a plain periodic trigger for when you don't need cron:
qtimer://[group/]jobName?period=5000&delay=1000&fixedRate=true
There is deliberately no quartz: scheme. That's not an oversight: scheduler setup — where jobs are stored, whether it's a cluster or a single node, what the thread pool looks like — is a property of the host, not of the route. Only the schedule belongs in a route URI. If a quartz: scheme existed, job store settings would immediately start growing into it, and the route would stop being portable.
The cron expression is the Quartz one, six fields, with seconds. Let's put partition maintenance at 02:30:
From("cron://maintenance/gps-partitions?schedule=0 30 2 * * ?")
.RouteId("gps-partitions")
The expression is validated when the endpoint is created, not when it first fires. A typo in the schedule is an ArgumentException at application startup, not silence until three in the morning.
Registering the component is one line at the module entry point:
context.AddComponent(new CronComponent());
Calling the function: sql:
The SQL connector has a single scheme, sql:, and the mode is picked with the mode parameter. To call a PostgreSQL function there's mode=Procedure with the asFunction=true flag — the connector then assembles SELECT maintain_partitions(@p1, @p2), executes it as a scalar, and puts the result in the message body:
sql:maintain_partitions
?mode=Procedure
&dataSource=#pg
&procedureName=maintain_partitions
&asFunction=true
&procedureParams=IN:tbl:String,IN:keep_days:Int32
¶m.keep_days=90
procedureParams declares the parameters in direction:name:type form, and the declaration order is the argument order in the call. There are three directions — IN, OUT, INOUT — and OUT values come back into the message headers under their own names after execution.
A parameter's value is resolved along a chain: first an explicit param.name from the URI, then a message header with the same name, then the body if it's a dictionary. Here keep_days is a constant right in the URI, and tbl will arrive from a header set by the splitter.
For simple cases there's a shorter path — mode=Execute (the default) with @name placeholders directly in the query text:
sql:SELECT maintain_partitions(@tbl, @keep_days)
?dataSource=#pg
&outputType=Scalar
¶m.tbl=${header.tbl}
¶m.keep_days=90
Note ${header.tbl} — that's an expression, resolved at runtime from the message header. Placeholders in SQL are @name only, the colon form isn't supported, and an unsubstituted parameter silently becomes NULL, so it pays not to mistype the names.
Both variants work. For the rest of the post I use mode=Procedure because it's the more illustrative one.
The splitter: three tables, and one failure doesn't take down the rest
There's rarely just one time-partitioned table. We have three: points, tracks, and events. The naive move would be a loop inside a processor, but then you're deciding by hand what happens when the second table fails — abort everything or carry on, and how do you find out afterwards what didn't run.
This is exactly the EIP Splitter pattern. A message carrying a list is split into one message per element, each goes down its own branch, and the branches can be processed in parallel:
From("cron://maintenance/gps-partitions?schedule=0 30 2 * * ?")
.RouteId("gps-partitions")
.Process(e => e.In.Body = new[] { "gps_points", "gps_tracks", "gps_events" })
.Split(Body())
.ParallelProcessing()
.MaxParallelism(2)
.SetHeader("tbl", Body())
.DoTry()
.To("sql:maintain_partitions"
+ "?mode=Procedure"
+ "&dataSource=#pg"
+ "&procedureName=maintain_partitions"
+ "&asFunction=true"
+ "&procedureParams=IN:tbl:String,IN:keep_days:Int32"
+ "¶m.keep_days=90")
.Log("[PART] ${body}")
.DoCatch<Exception>()
.Log("[PART] ${header.tbl}: ${exception.Message}", LogLevel.Error)
.End()
.EndSplit()
.Process(Summary);
Twelve lines, and they already contain everything you'd normally bolt on a week later. MaxParallelism(2) means two tables get serviced at once and the third waits for a free slot; DROP TABLE takes an ACCESS EXCLUSIVE lock, and there's no point burying the database under parallel locks. DoTry/DoCatch sit inside the split, so the exception is isolated to its own branch: a failing gps_tracks won't undo the gps_points that already succeeded, and won't stop gps_events. After EndSplit, control lands in Summary, where you can count how many branches made it and decide whether to page somebody.
This isn't a trick invented for the article. Exactly this shape runs in production — a job that syncs shipping points from SAP splits the list of points and processes three at a time, because one unreachable point shouldn't take down the whole tick:
From("timer://tsum-points?period=180000&delay=60000")
.RouteId("tsum-points-timer")
.ProcessWithRedb(PreloadContextAsync)
.Split(Body())
.ParallelProcessing()
.MaxParallelism(3)
.SetHeader("ShippingPoint", Body())
.DoTry()
.To(sqlTo)
.Process(DeserializeXml)
.ProcessWithRedb(ProcessPointsAsync)
.DoCatch<Exception>()
.Process(AddPointSyncError)
.Log(LogLevel.Error)
.Message("[TSUM-PT] SP=${header.ShippingPoint} failed, skipping: ${exception.Message}")
.EndLog()
.End()
.EndSplit()
.Process(BuildPointSyncSummary);
The splitter isn't the only EIP that docks onto a scheduler. A schedule pairs naturally with a Content-Based Router (one thing on weekdays, another on weekends), a Throttler (don't hammer an external API more than N times a second), an Aggregator (collect branch results into a single report), an Idempotent Consumer (more on that below) and a Dead Letter Channel. redb.Route implements nearly the whole Hohpe & Woolf catalogue — Splitter, Aggregator, Resequencer, Multicast, Recipient List, Dynamic Router, Wire Tap, Content Enricher, Claim Check, Saga, Scatter-Gather, Circuit Breaker, Load Balancer, Transactional Client and the rest. The scheduler here is just a source, not a separate world with its own rules.
What happens when the server was down at 02:30
This is where the reason you'd want Quartz rather than a PeriodicTimer starts.
The job didn't fire, because the node was down or a deploy ran long. What should happen when the scheduler comes back at 02:47? The answer depends on the job, and it isn't a philosophical question: for partition maintenance, a skip is a catastrophe — tomorrow's partition has to be created, at 02:47 or at 06:00. For a "send the morning report" job, running at noon is worse than not running at all.
Quartz calls this misfire, and the connector passes the policy straight through the URI:
cron://maintenance/gps-partitions?schedule=0 30 2 * * ?&misfireInstruction=CronFireOnceNow
CronFireOnceNow — catch up, run once, return to the schedule. CronDoNothing — skip it, wait for the next scheduled time. Simple triggers (qtimer:) have more policies — five of them — differing in what to do with the accumulated repeat count. But the choice always collapses to one question: does a missed run need catching up, or has it already gone stale?
The connector's default for qtimer: is picked by common sense: with fixedRate=true, catch up (you asked for a fixed rate); otherwise, reschedule from the next tick.
What happens when the previous run is still going
Partitions have piled up, DROP TABLE is waiting on a lock, the job is hanging. The next fire time arrives. Now what?
The standard Quartz answer is the [DisallowConcurrentExecution] attribute on the job class. The connector went a different way: concurrency is governed by the consumer's own semaphore, and if every thread is busy the fire is silently skipped:
// QuartzConsumerBase.cs
if (!await _semaphore.WaitAsync(0).ConfigureAwait(false))
return; // all threads busy, skip this fire
The semaphore size is set in the URI via threads (default 1). Why not the attribute: [DisallowConcurrentExecution] is either one run or no control at all, with nothing in between. A semaphore lets you say "up to three concurrent runs of this job" while staying friendly to a cluster, where the limit lives at the job store level rather than in a class attribute.
If you do want the Quartz semantics, there's a stateful=true flag that switches the job to a class carrying [DisallowConcurrentExecution] and [PersistJobDataAfterExecution].
One more detail about shutdown: when a route is stopped, the connector unschedules the trigger and waits for runs already in flight — up to thirty seconds. A job that happens to be dropping a partition right then won't be cut off halfway. And if a job does fire when its route is already gone (the module was unloaded, say), the job notices on startup that its consumer is dead and deletes itself from the scheduler, leaving no litter behind.
Three nodes
The most common question about cron in a distributed application: if there are three nodes, does the job fire three times?
It does — if every node keeps its own scheduler in memory. That's exactly how the connector's fallback works: no IScheduler found in the context, so it creates its own, RAM-backed, unique to that context. Fine for local development, not fine for three nodes.
The right answer is one scheduler per cluster — more precisely, one shared job store. Quartz does this out of the box via AdoJobStore, and the connector deliberately stays out of the way: it does not create its own scheduler if a ready one is already sitting in the route context. The host drops a clustered one in there, and the route needs no changes at all — the URI stays the same.
And here's where all the plumbing that usually gets hand-waved away comes into view. AdoJobStore is a set of QRTZ_* tables in your database. QRTZ_TRIGGERS holds the next fire time, QRTZ_FIRED_TRIGGERS holds who is executing what right now, QRTZ_LOCKS holds row-mutexes. The "only one node runs the job" mechanism isn't clever consensus — it's SELECT ... FOR UPDATE on a row in QRTZ_LOCKS: whoever takes the lock first takes the trigger. Each node checks in periodically in QRTZ_SCHEDULER_STATE, and if a node stops checking in, another one picks up its unfinished jobs — but only those marked recoverableJob=true. The flag is off by default: restarting a job when you don't know whether it's idempotent is a bad idea.
The table schema is created by the host at startup; the connection string and dialect come from the application's database configuration. So you don't write the DDL, but the tables are perfectly ordinary, they sit next to yours, they're visible in any client, and when something goes wrong you go in with a plain SELECT and see which trigger is stuck and on which node.
And since we brought up recoverableJob: recovery after a node dies means the job may run twice. For our partition maintenance function that's safe — it's written idempotently (IF to_regclass(...) IS NULL), and that's a requirement, not an accident. If the job weren't idempotent — say, it credited bonuses — you'd put an Idempotent Consumer in front of it, and redb.Route has one backed by SQL with a unique index. A unique index, not a "smart cache": in a cluster, that's the only thing that saves you from double execution.
What lands in the message
A scheduler is a source with no message body. The body is null, the pattern is InOnly, and everything about the firing lives in the properties:
| Property | What's in it |
|---|---|
CamelQuartzFireTime |
when the job actually fired |
CamelQuartzScheduledFireTime |
when it should have fired per the schedule |
CamelQuartzNextFireTime |
when it fires next |
CamelQuartzPreviousFireTime |
when it fired last |
CamelCronSchedule / CamelCronName / CamelCronGroup
|
the expression, name and group of the job |
The gap between FireTime and ScheduledFireTime is that misfire, in numbers. If they're seventeen minutes apart, the job was catching up.
The Camel-style names aren't nostalgia. redb.Route deliberately keeps Apache Camel's nomenclature wherever the semantics match, so someone arriving from Java integrations can read the headers without a dictionary.
Two jobs from production
So this doesn't read like a piece about a spherical cron in a vacuum — here are two routes that run every night in a transport management system.
Database backup at three:
From("cron://tsum-backup?schedule=0 0 3 * * ?")
.RouteId("tsum-backup-cron")
.ProcessWithRedb(RunBackupAsync);
Dead-route cleanup at four:
From("cron://tsum-cleanup?schedule=0 0 4 * * ?")
.RouteId("tsum-cleanup-cron")
.ProcessWithRedb(CleanupDeadRoutesAsync);
Nothing spectacular, and that's the point: the schedule is in the URI, the logic is in the processor, retries and logging come from the framework. Note that the schedule is hardcoded in the URI rather than pulled from config — that's allowed, and it's how things live for a while. When you need to change a schedule without a rebuild, you assemble the URI from config with ordinary concatenation, because it's just a string.
The full option list
So this doesn't turn into half a post of reference material — everything cron: understands:
schedule (required), timeZone (IANA name), threads, misfireInstruction, stateful, recoverableJob, durableJob, deleteJob, pauseJob, startAt, endAt, customCalendar, triggerStartDelay, prefixJobNameWithEndpointId.
qtimer: swaps schedule for period, delay, fixedRate, repeatCount; everything else is the same, minus the time zone (a simple trigger doesn't need one).
customCalendar is worth calling out: it's the Quartz exclusion calendar, registered in the context by name. That's how you get "except holidays" and "business days only" — things a cron expression cannot express at all.
Wrapping up
The scheduler in redb.Route is a message source, not a subsystem with its own rules. A job is a route, which means everything available to any route is available to it: the splitter, exception handling, transactions, retries, metrics. The trigger is described by a single URI string, and there's nothing about infrastructure in that string — only the schedule.
The infrastructure isn't hidden, either. The cluster runs on QRTZ_* tables and a row lock in the database, idempotency in a cluster comes from a unique index, and partition maintenance is an ordinary plpgsql function that you wrote and can read. The framework here spares you the glue code, not the understanding of what's happening in your database.
Code: github.com/redbase-app/redb-route · Docs: redbase.app
If this was useful — a ⭐ on GitHub helps others find it.
Top comments (0)