Today, April 1st, we’re announcing Relational Flux (Reflux), our support of the Flux custom query language for time-series analysis.

[TLDR: April Fool's, SQL rules!]

Flux was initially introduced to create a language that was better for time-series than the widely-adopted SQL standard. Today we're picking up that ball and running with it. Because we're a team player. Or rather, a team of team players.

When we chose to base Reflux on Flux, we did so with the confidence that comes from choosing a language that is brand new. We did think Flux needed a few modifications, lest it start showing its age like SQL. To that end, we modified the Flux syntax slightly, which is best demonstrated with a few examples.

First, here’s how to compute a histogram in Flux and Reflux:

Flux:

from(bucket: "telegraf/autogen")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "mem" and
    r._field == "used_percent"
  )
  |> histogram(
    buckets: [10, 20, 30, 40, 50, 60, 70, 80, 90, 100]

Reflux:

SELECT histogram(mem_used_percent, 10, 100, 10)
FROM telegraf
WHERE time > now() - '1 hour';

As you can see, Reflux is pretty straightforward, and should be easy for the millions of developers who are already familiar with SQL.

In addition, with Reflux we were able to extend the query language to include functionality previously not supported by Flux. For example, joining time-series data with relational metadata at query time:

Flux:

(Not supported)

Reflux:

SELECT device_type, avg(temperature), avg(humidity)
	FROM sensor_data 
JOIN devices ON sensor_data.device_id = devices.id
WHERE time > now() - '1 hour'
GROUP BY device_type 
ORDER BY device_type LIMIT 10;

In some cases however, we struggled to capture the full expressiveness of Flux. Take for example this Flux query that performs math across measurements:

Flux:

// Memory used (in bytes)
memUsed = from(bucket: "telegraf/autogen")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "mem" and
    r._field == "used"
  )

// Total processes running
procTotal = from(bucket: "telegraf/autogen")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "processes" and
    r._field == "total"
    )

// Join memory used with total processes and calculate
// the average memory (in MB) used for running processes.
join(
    tables: {mem:memUsed, proc:procTotal},
    on: ["_time", "_stop", "_start", "host"]
  )
  |> map(fn: (r) => ({
    _time: r._time,
    _value: (r._value_mem / r._value_proc) / 1000000
  })
)

Much to our chagrin, our implementation of Reflux forces the user into this terse syntax:

Reflux:

SELECT time, (memUsed / procTotal / 1000000) as value
FROM measurements
WHERE time > now() - '1 hour';

We’re working on adding more required syntax for unfortunate situations such as this.

It wasn’t easy to get here

Building Flux compatibility via Reflux is the culmination of a lot of hard work and calcium carbonate. In fact, in the process of developing a new query language, we made a few mistakes along the way. The main one being deciding on the name, Reflux, before anything else. When we tried to work backwards, the first result fell a little short: it was a version of Flux where we ran everything twice (which is always more accurate than running it once):

from(db:"telegraf")
  |> range(start:-1h)
  |> again()
  |> filter(fn: (r) => r._measurement == "foo")
  |> again()
  |> exponentialMovingAverage(size:-10s)
  |> again!()   # the ! indicates to run it again with gusto

Early engineer feedback found that performance suffered by at least 2x. We couldn’t stomach that kind of downside, so we needed a different idea.

Luckily, we remembered our roots as a relational database and somebody mentioned portmanteaus (not a type of wine, as we first thought) and well, here we are. And it allowed us to also introduce support for Atomicity, Consistency, Isolation, and Durability, which was critically lacking in Flux, in a new feature called ACID Reflux. Surprisingly, this feature is also known to reduce heartburn and indigestion in engineers and operators alike.

But why (Re)Flux?

To SQL fans around the world, supporting Flux may seem like an odd choice. SQL is the fourth most popular language today, with over 14 million developers who know SQL. TimescaleDB itself is a time-series database that supports full SQL, so it wasn’t easy for us to support Flux. We had to learn Flux, retrain our teammates, and build new tooling that spoke Flux.

However, there are ways that Flux clearly measures up to SQL. A simple count of the letters in its name would show that (i.e., 4 > 3). But we wanted to make it more approachable for SQL users, which is why we went with our own variant, Reflux. We think it paid off: when our SQL-loving users used Reflux, they told us it gave them a burning feeling in their chest. We call that love. Also by adding an additional two letters to the name, we no longer have to live in a world where the best query language can be spelled with only a handful of letters.

Also, although SQL has a firm basis in an algebraic theory called (ordered) set theory and has been battle-tested for over 30 years, the wheel sometimes needs to be re-invented. Reflux has a firm basis in Flux, which looks like JavaScript and supports chained function calls like JQuery and D3.js. A web UI language paradigm is perfect for mission-critical data workloads: after all, Mongo uses JSON documents and it’s web scale.

Now some of you might be asking, isn’t this all just a ploy to make it harder for our users to migrate to other systems because, with our own query language, their queries will be significantly harder to translate? Of course we’re not trying to lock-in our users — we’re not Amazon — it’s all about reducing the time for users to have an awesomer experience.

Overall, we are thrilled to be able to share this news with you today. But if, for whatever reason, you’d prefer not to learn a new query language, not retrain your engineers, and not rebuild tooling...all just to access your data, then you may want to take a look at TimescaleDB, which also scales SQL for time-series analysis.

And then, please also check out our new InfluxDB to TimescaleDB migration tool, which we also just released today.