InfluxDB compared to SQL databases

What’s in a database?

This page gives SQL users an overview of how InfluxDB is like an SQL database and how it’s not.It highlights some of the major distinctions between the two and provides a loose crosswalk between the different database terminologies and query languages.

In general…

InfluxDB is designed to work with time-series data.SQL databases can handle time-series but weren’t created strictly for that purpose.In short, InfluxDB is made to store a large volume of time-series data and perform real-time analysis on those data, quickly.

Timing is everything

In InfluxDB, a timestamp identifies a single point in any given data series.This is like an SQL database table where the primary key is pre-set by the system and is always time.

InfluxDB also recognizes that your schema preferences may change over time.In InfluxDB you don’t have to define schemas up front.Data points can have one of the fields on a measurement, all of the fields on a measurement, or any number in-between.You can add new fields to a measurement simply by writing a point for that new field.If you need an explanation of the terms measurements, tags, and fields check out the next section for an SQL database to InfluxDB terminology crosswalk.

Terminology

The table below is a (very) simple example of a table called foodships in an SQL databasewith the unindexed column #_foodships and the indexed columns park_id, planet, and time.

  1. +---------+---------+---------------------+--------------+
  2. | park_id | planet  | time                | #_foodships  |
  3. +---------+---------+---------------------+--------------+
  4. |       1 | Earth   | 1429185600000000000 |            0 |
  5. |       1 | Earth   | 1429185601000000000 |            3 |
  6. |       1 | Earth   | 1429185602000000000 |           15 |
  7. |       1 | Earth   | 1429185603000000000 |           15 |
  8. |       2 | Saturn  | 1429185600000000000 |            5 |
  9. |       2 | Saturn  | 1429185601000000000 |            9 |
  10. |       2 | Saturn  | 1429185602000000000 |           10 |
  11. |       2 | Saturn  | 1429185603000000000 |           14 |
  12. |       3 | Jupiter | 1429185600000000000 |           20 |
  13. |       3 | Jupiter | 1429185601000000000 |           21 |
  14. |       3 | Jupiter | 1429185602000000000 |           21 |
  15. |       3 | Jupiter | 1429185603000000000 |           20 |
  16. |       4 | Saturn  | 1429185600000000000 |            5 |
  17. |       4 | Saturn  | 1429185601000000000 |            5 |
  18. |       4 | Saturn  | 1429185602000000000 |            6 |
  19. |       4 | Saturn  | 1429185603000000000 |            5 |
  20. +---------+---------+---------------------+--------------+

Those same data look like this in InfluxDB:

  1. name: foodships
  2. tags: park_id=1, planet=Earth
  3. time                           #_foodships
  4. ----                           ------------
  5. 2015-04-16T12:00:00Z     0
  6. 2015-04-16T12:00:01Z     3
  7. 2015-04-16T12:00:02Z     15
  8. 2015-04-16T12:00:03Z     15
  10. name: foodships
  11. tags: park_id=2, planet=Saturn
  12. time                           #_foodships
  13. ----                           ------------
  14. 2015-04-16T12:00:00Z     5
  15. 2015-04-16T12:00:01Z     9
  16. 2015-04-16T12:00:02Z     10
  17. 2015-04-16T12:00:03Z     14
  19. name: foodships
  20. tags: park_id=3, planet=Jupiter
  21. time                           #_foodships
  22. ----                           ------------
  23. 2015-04-16T12:00:00Z     20
  24. 2015-04-16T12:00:01Z     21
  25. 2015-04-16T12:00:02Z     21
  26. 2015-04-16T12:00:03Z     20
  28. name: foodships
  29. tags: park_id=4, planet=Saturn
  30. time                           #_foodships
  31. ----                           ------------
  32. 2015-04-16T12:00:00Z     5
  33. 2015-04-16T12:00:01Z     5
  34. 2015-04-16T12:00:02Z     6
  35. 2015-04-16T12:00:03Z     5

Referencing the example above, in general:

  • An InfluxDB measurement (foodships) is similar to an SQL database table.
  • InfluxDB tags ( park_id and planet) are like indexed columns in an SQL database.
  • InfluxDB fields (#_foodships) are like unindexed columns in an SQL database.
  • InfluxDB points (for example, 2015-04-16T12:00:00Z 5) are similar to SQL rows.

Building on this comparison of database terminology,InfluxDB continuous queriesand retention policies aresimilar to stored procedures in an SQL database.They’re specified once and then performed regularly and automatically.

Of course, there are some major disparities between SQL databases and InfluxDB.SQL JOINs aren’t available for InfluxDB measurements; your schema design should reflect that difference.And, as we mentioned above, a measurement is like an SQL table where the primary index is always pre-set to time.InfluxDB timestamps must be in UNIX epoch (GMT) or formatted as a date-time string valid under RFC3339.

For more detailed descriptions of the InfluxDB terms mentioned in this section see our Glossary of Terms.

InfluxQL and SQL

InfluxQL is an SQL-like query language for interacting with InfluxDB.It has been lovingly crafted to feel familiar to those coming from otherSQL or SQL-like environments while also providing features specificto storing and analyzing time series data.

InfluxQL’s SELECT statement follows the form of an SQL SELECT statement:

  1. SELECT <stuff> FROM <measurement_name> WHERE <some_conditions>

where WHERE is optional.

To get the InfluxDB output in the section above, you’d enter:

  1. SELECT * FROM "foodships"

If you only wanted to see data for the planet Saturn, you’d enter:

  1. SELECT * FROM "foodships" WHERE "planet" = 'Saturn'

If you wanted to see data for the planet Saturn after 12:00:01 UTC on April 16, 2015, you’d enter:

  1. SELECT * FROM "foodships" WHERE "planet" = 'Saturn' AND time > '2015-04-16 12:00:01'

As shown in the example above, InfluxQL allows you to specify the time range of your query in the WHERE clause.You can use date-time strings wrapped in single quotes that have theformat YYYY-MM-DD HH:MM:SS.mmm(mmm is milliseconds and is optional, and you can also specify microseconds or nanoseconds).You can also use relative time with now() which refers to the server’s current timestamp:

  1. SELECT * FROM "foodships" WHERE time > now() - 1h

That query outputs the data in the foodships measure where the timestamp is newer than the server’s current time minus one hour.The options for specifying time durations with now() are:

LetterMeaning
nsnanoseconds
u or µmicroseconds
msmilliseconds
sseconds
mminutes
hhours
ddays
wweeks

InfluxQL also supports regular expressions, arithmetic in expressions, SHOW statements, and GROUP BY statements.See our data exploration page for an in-depth discussion of those topics.InfluxQL functions include COUNT, MIN, MAX, MEDIAN, DERIVATIVE and more.For a full list check out the functions page.

Now that you have the general idea, check out our Getting Started Guide.

A note on why InfluxDB isn’t CRUD

InfluxDB is a database that has been optimized for time series data.This data commonly comes from sources like distributed sensor groups, click data from large websites, or lists of financial transactions.

One thing this data has in common is that it is more useful in the aggregate.One reading saying that your computer’s CPU is at 12% utilization at 12:38:35 UTC on a Tuesday is hard to draw conclusions from.It becomes more useful when combined with the rest of the series and visualized.This is where trends over time begin to show, and actionable insight can be drawn from the data.In addition, time series data is generally written once and rarely updated.

The result is that InfluxDB is not a full CRUD database but more like a CR-ud, prioritizing the performance of creating and reading data over update and destroy, and preventing some update and destroy behaviors to make create and read more performant:

  • To update a point, insert one with the same measurement, tag set, and timestamp.
  • You can drop or delete a series, but not individual points based on field values. As a workaround, you can search for the field value, retrieve the time, then DELETE based on the time field.
  • You can’t update or rename tags yet - see GitHub issue #4157 for more information. To modify the tag of a series of points, find the points with the offending tag value, change the value to the desired one, write the points back, then drop the series with the old tag value.
  • You can’t delete tags by tag key (as opposed to value) - see GitHub issue #8604.