Welcome to NBSoftSolutions, home of the software development company and writings of its main developer: Nick Babcock. If you would like to contact NBSoftSolutions, please see the Contact section of the about page.

Exploring TimescaleDB as a replacement for Graphite

At the monitoring docks, metrics prepare to board the packet ferry across the network sea to a data store

There’s a new time series database on the block, TimescaleDB, which is an extension to Postgres. I decided to test it to see how well it fits in my monitoring stack that culminates with visualizations in Grafana. Currently, I’m exclusively using Graphite as my time series database, and I’ve been curious how Timescale stacks up against it, as Influx and Prometheus failed to match.

Not to spoil the conclusion, but I found that while much touted plain ole SQL is Timescale’s greatest strength, SQL is also its greatest weakness, so Timescale definitely can have a spot in one’s stack, but it can’t wholesale replace graphite.

Mental Model

Acquainting tech support with Graphite’s architecture has been a major pain point because Graphite is colloquially used when referring to the individual components that make up a Graphite install: Carbon, a data ingestion engine that caches metrics before writing them to per-metric Whisper files, and graphite-web, which provides an rudimentary web interface and API to the data. Below is the architecture diagram for Graphite, lifted from their Github page. You will see that even in my brief description that I was skipping components, like an optional memcached layer:

Note: The diagram is incomplete with the new addition of a TagDB, which I’ll touch on later

When something goes wrong at a Graphite install it’s not intuitive what to check first:

  • Is it one of the thousands of whisper files? Check each file with python scripts. Merging renamed metrics and deleting old metrics can be tedious.
  • Is it carbon cache? If new metrics (ie: metric name) are being sent to carbon, by default, only 50 new ones are allowed per minute, so carefully crafted Grafana dashboards will look broken until enough time has passed, as new metrics will be dropped in the meantime. This and carbon-cache’s disk intensity (ssd’s are nigh a must) can be tuned, but it can be unintuitive what values are appropriate for the number of updates per second in this dance of disk vs ram utilization when compared to the postgres’s shared buffers.
  • Is it graphite web? Was installed correctly? Is it talking to the correct carbon-cache? Did a plugin go bottom up and require a restart of the service?

Tech support has better use of their time than to memorize the components and how to troubleshoot each one.

Compare this with TimescaleDB. It’s postgres. People know how to manage postgres or are able to tap into the vast amount of resources on the internet for administration help.

Data Data Data

Before painting a too poor of a picture of graphite, I find how it manages data levelheaded. While one may have thousands of whisper data files, they are all fixed size, so once allocated, one knows what disk requirements are necessary. This is possible due to one setting a max retention period (like one year), with older data automatically erased. In addition, one can set multiple retention period to downsample a single metric to save space. For instance, I use 10s:6h,1m:6d,10m:1800d, which reads “save data in 10 second intervals until 6 hours, then the data is bucketed into minute intervals by averaging (configurable) the (6) 10 second intervals that make up a minute, and after 6 days group minutely data into 10 minute buckets and keep those around for 5 years.” All told, for this retention policy, each whisper file consumes 3.1MB, which I find to be fairly incredible as there is over 250k 10 minute intervals in 5 years (one whisper data point is only 12 bytes).

My small home graphite install is 7GB with 2310 metrics representing 600 million data points over 5 years. To me, this is an incredible feat.

The biggest downside of carbon data retention is changing your mind (eg: one needs finer resolution) won’t change any metrics already created. One will need to whisper-resize appropriate files which can be easy to forget or mess up.

Back to doling out laurels. Inserting data into carbon could not be any easier, below we’re telling carbon that we see 10 unique clients at the current date:

echo "pihole.unique_clients 10 $(date +%s)" >> /dev/tcp/localhost/2003

Everyone that’s seen this groks it fairly quickly and can adopt it for their own scripts. There is no barrier to entry. And any prior entry for metric has its value overwritten, which make updates just as straightforward.

Contrast this with Timescale. Data is stored in SQL tables, which allows you to structure your data however you want, whether that be one table or many, wide or narrow, denormalized or normalized, indices galore and triggers aboard. Postgres – it’s flexible. But there are some caveats:

  • Deleting data from Timescale requires intervention, so one will need the cron or systemd script provided if they need to automate it. One may have limited disk space.
  • Timescale does not downsample data
  • Most Timescale installs will be much more bloated after 5 years unless one employs surrogate keys to reduce duplication from repeatedly storing “pihole.unique_clients” in a column, but this normalization has its own downsides (increased insert and query complexity).
  • Have to insert data via psql cli (or postgres library in an application) which will require authentication hoops to jump through.
  • Timescale will allow duplicates rows and create ambiguous situations (was the hard drive 40°C or 50°C?)

But each of these points can have a positive spin on them:

  • Business requirements will change so one can fine tune deleting data on a need to do basis or some other logic.
  • It may not be clear what aggregation is most appropriate when downsampling (graphite defaults to average), so by not downsampling, one keeps the raw data to determine what aggregation is most appropriate at time of query.
  • The beauty of SQL is that you can decide what’s right for you. Hopefully a denormalized form is appropriate, but anything is possible when JOINs are at your fingertips.
  • No unauthenticated access to the database. One should create service accounts with locked down access. Also with SQL inserts, one can use parameters to nullify injection, while graphite needs special characters removed in each metric name segment prior to transmission to prevent unpredictable errors during analysis.
  • If your business requirements disallow duplicates, you can dictate this through unique indices on necessary columns and upsert all your data (though at the cost of disk space for additional index and insert complexity as one needs to include the “ON CONFLICT DO” clause).

Setting Up

At home, I have the luxury of farming the onus of installation to Docker. Both Timescale and Graphite have official docker images. This simplifies getting started, but if we compare docker image sizes (Timescale: 15-30MB vs Graphite: 341MB), it helps shed light on how heavyweight each application is.

But let’s set aside docker for a moment and focus on installing in an enterprisey environment like enterprise linux.

Timescale is easy it’s about 2 lines of yum and 1 postgres configuration line change.

Graphite not so much:

  • Download python 2.7 source
  • Ensure all appropriate devel libraries are downloaded like openssl-devel
  • Compile and install this python 2.7 into a directory
  • Even if you’re not using the web ui you still need to install cairo-devel
  • Pip install all the requirements
  • Execute a django incantation (I think it sets up some kind of database)
  • Configure a reverse proxy like gunicorn or nginx (fastcgi)
  • Add init scripts / systemd.

It’s easy to slip up and omit a dependency, and you won’t know it by the opaque errors that are logged.

Concerning upgrades, for graphite, one will need to closely track the release notes to see what dependencies need to updating (like Twisted or Django), so things can get tricky, though with Docker, upgrades are seamless as the internal whisper format has never changed. Whereas for Timescaledb, one can use any standard postgres upgrade mechanism: pg_dump and restore, pg_upgrade, or logical replication.

On Tags

Within the last year, Graphite has gained support for tagging metrics. Tags allow one circumvent querying purely on the hierarchy of a metric name. Tags are a nice addition, as it’s now easier to cobble together disparate systems based on a tag. For instance you could have multiple projects that report on temperature, and now it doesn’t matter where in the hierarchy those temperatures are stored, one can select them with a simple graphite query:


I bring up tags for one good reason. Tags need a TagDB (a component missing from architecture diagram). The default TagDB is sqlite. However, according to an official Graphite maintainer, any serious interest in tags should use an external db like postgres, mysql, or redis. Now we have two systems: graphite-web + carbon + whisper + all those dependencies + postgres vs just postgres.

Using tags is entirely optional, but come at a cost of an additional component to manage. Not to mention, if one were to tack on another tag to a metric, it will create an entirely new metric and I haven’t figured how one migrates the old data. I just end up resigning data to rot. For Timescale, one relies on the standard ALTER TABLE syntax when adding / removing columns.


Graphite reigns supreme when it comes to querying. It’s whole purpose is to provide an HTTP API over time series data, so it comes packed with functions. I’ve been writing Graphite functions for probably over three years and I’m routinely surprised at how powerful they are (like two months ago when I finally used reduceSeries).

I wrote an application that will export hardware sensor data from my Windows machines called OhmGraphite. We’re going to use it to drive our example, as it’s the only metrics agent I know that has a nice integration with Timescale. Below is a graph we’ll create of the temperature for the hottest two hard drive on average:

Here is the Graphite query:

seriesByTag('sensor_type=Temperature', 'hardware_type=HDD')
  | highest(2, 'average')
  | aliasByTags('name')

It’s 3 concise and readable lines. I can easily switch from highest(2, 'average') to highest(5, 'stddev') to see the top 5 hard drives with the highest standard deviation. Extremely powerful, extremely simple.

The same can’t be said for Timescale:

-- Get the temperature data for each hard drive grouped into time buckets
WITH data AS (
        time_bucket ('$__interval', time) AS "btime",
        AVG(value) as avg_data
    FROM ohm_stats
    WHERE $__timeFilter(time)
        AND sensor_type = 'Temperature'
        AND hardware_type = 'HDD'
    GROUP BY btime, identifier

-- Compute the overall average temperature from the data buckets and
-- give that hard drive a rank
ranks AS (
        RANK() OVER(ORDER BY avg(avg_data) DESC) as rnk
    FROM data
    GROUP BY identifier

-- Since the hard drive may not have data for the entire interval,
-- we need to create a gap filling function
 period AS (
    SELECT time_bucket ('$__interval',  no_gaps) AS "btime"
    FROM generate_series($__timeFrom()::timestamptz, $__timeTo(), '$__interval') no_gaps

-- Then for each top hard drive, create a series with just the time
-- and the identifier (cross join) and then selectively grab the
-- data that's available (left join)
    ranks.identifier AS "metric"
    CROSS JOIN ranks
    LEFT JOIN data ON
        period.btime = data.btime
        AND data.identifier = ranks.identifier
WHERE ranks.rnk <= 2
ORDER BY period.btime

Wow. It took me an embarrassing amount of time to craft this query. We’re talking hours of tweaking. I’m cautiously optimistic that there are ways to improve this query because a 10-15x increase in a query statement length is not something I look forward to maintaining. Changing the aggregation from average to something like standard deviation, or returning the top 5 instead of 2 isn’t apparent. Grafana variables make it a little easier to inject these types of changes. Since I said “inject” in the context of SQL, I must now mention that postgres user grafana utilizes to interact with Timescale should be greatly restricted, else expect someone to delete all the data!

Aside: while I have not done a deep performance analysis, both queries are within spitting distance of each other in the time it takes to return results. I’m secretly hoping that if I am able to migrate entirely to Timescale that I’d have more predictable memory usage, and less resource usage in general, but I can’t even speculate on that at the moment.

All this SQL written does give a bit of flexibility. Postgres aggregate and window functions are at our fingertips. For instance, we can graph how the rank of the hottest hard drives change throughout time (eg: the 5th hottest hard drive on average may have intervals where it is the hottest or coldest), by simply tweaking the final SELECT statement from the previous section to rank each hard drive at each interval:

-- Then for each top hard drive, create a series with just the time
-- and the identifier (cross join) and then selectively grab the
-- data that's available (left join)
    period.btime AS "time",
    RANK() OVER (PARTITION BY period.btime ORDER BY data.avg_data DESC) brnk
    ranks.identifier AS "metric",
    CROSS JOIN ranks
    LEFT JOIN data ON
        period.btime = data.btime
        AND data.identifier = ranks.identifier
ORDER BY period.btime

This is where we start thinking about creating a function to extract all this functionality so we can stop repeating ourselves, but I’m going to stop on the querying part. If you’re interested in what’s next, here’s an example of a postgres function. I just wanted to convey that crafting queries in grafana is 10x easier with graphite than timescale. You’ll need to write your own scaffolding to make it easier with timescale. At least, the query language is only SQL, which more business analysts are privy to, unlike some other custom time series database languages!

Hopefully soon the Timescale team will add in much needed features like gap filling by previous value, as current SQL workarounds are obtuse. Ideally, I’d like to see a translation document of Graphite functions to postgres SQL, as Graphite is the query gold standard.

While I can’t laud Graphite enough in the context of Grafana, Timescale does have a trick up its sleeve. By virtue of being just Postgres, it integrates more readily into traditional BI programs like Tableau that can query the database directly. I was able to get graphite data into a state Tableau could query by using an intermediate SQL database (go figure) and writing an application that formats graphite csv output into appropriate sql statements. This method is not something I recommend.


  • I have tiny scripts (like Robust systemd Script to Store Pi-hole Metrics in Graphite that I’m unlikely to migrate off graphite, due to the script not being important and I’d need to format the queries as SQL statements (I’d need to figure out how I’d want to access psql: docker or install via package manager)).
  • Timescale is new enough not to have wide adoption amongst collection agents. I can’t migrate my post Monitoring Windows system metrics with Grafana to Timescale, as telegraf doesn’t support reporting to Timescale (maybe it is time I look past telegraf).
  • While collectd does support writing to postgres, reviewing the code – it doesn’t batch INSERT statements most likely leading to less than stellar performance. I may have to end up writing a collectd plugin as a proof of concept.
  • The only thing I have actually writing to Timescale is OhmGraphite
  • Thus getting high quality support among collection agents is Timescale’s first hurdle
  • The next step for Timescale is making queries more succinct and readable without forcing the user to create the scaffolding.
  • Graphite can’t compare in ease of setup, administration, security, and sheer man hours spent in creating postgres.
  • Graphite has greatly optimized ease of insertion and small disk footprint (though disk write footprint is bit of a different story)

So it’s bit of a wash. Depending on the situation, graphite or Timescale is appropriate. Not everything can insert into Timescale, but not everything can query graphite.

This article can be discussed on Hacker News too

Hashing strings bytewise in C#

This post has been adapted from the documentation I wrote for Farmhash.Sharp. I think there is misleading information out there when it comes to string hashing, so I wanted shed some light on how Farmhash.Sharp does it efficiently.


It is very easy to hash a string with Farmhash.Sharp with performance equivalent to hashing bytes directly

ulong hash64 = Farmhash.Hash64("Hello");
uint hash32 = Farmhash.Hash32("Hello");

At this point all the public APIs with strings have been covered, but there is some additional information that may help one understand the implementation and use cases.

Behind the Scenes

The Farmhash algorithm operates on a sequence of bytes. A string is a sequence of characters. These two seem fundamentally at odds. Googling “C# string to bytes” will yield the top Stackoverflow question and dot net perls as the second result. Both of these pages instruct one to decode the string according to a character encoding. This would be bad advice for Farmhash.Sharp for two reasons:

  • Would incur a performance penalty as an intermediate byte array would be allocated
  • Would complicate the API by forcing the user to pass in an Encoding class else risk increased hash collisions due to decoding issues.

To illustrate the hash collision:

var payload = "ø";

var data = Encoding.ASCII.GetBytes(payload);
// byte[1] { 63 }

// "?"

Thus if we ASCII decoded “ø”, we’d get the same hash as “?”. Collision via replacement characters would be a terrible property of any hash function.

Encodings are incredibly important, as the choice can affect the byte representation. The Hindi character for you (यू) decoded in UTF8, UTF16, and UTF32 will yield 3 different results

// byte[6] { 224, 164, 175, 224, 165, 130 }

// byte[4] { 47, 9, 66, 9 }

// byte[8] { 47, 9, 0, 0, 66, 9, 0, 0 }

All three byte arrays are valid, but are near useless as one can’t recover the original string without keeping track of the encoding. While recognizing the encoding of the data you are working with is always good idea, chaining users to an Encoding before they can use Farmhash.Sharp would be an ergonomic hurdle. The way I think of it, hashing a string should be akin to String::GetHashCode in simplicity.


We can avoid any performance penalties of converting a string to bytes without any downsides with the following approach:

public static unsafe ulong Hash64(string s)
    fixed (char* buffer = s)
        return Hash64((byte*)buffer, s.Length * sizeof(char));

Some may state that this will fail for characters that fall into the surrogate pair range. When it’s necessary to join two 16 bit characters to form single character, it’s called a surrogate pair. Surrogate pairs are necessary to encode less commonly used symbols, as there are more than 65536 (16 bits) symbols across all languages, emojis, etc. I’ll address the two failure points that one might think could occur: buffer overflow causing undefined behavior, and not consuming the entire byte buffer, resulting in an increase of hash collisions.

Let’s take the Mandarin compound “to shatter”: 𤭢

// 2

// char[2] { '\ud852', '\udf62' }

var data = Encoding.Unicode.GetBytes("𤭢")
// byte[4] { 82, 216, 98, 223 }

// "𤭢"

While “𤭢” may be a single symbol, it is composed of multiple characters, so our implementation earlier will handle it smoothly and accurately interpret the string as composed of four bytes. For a bit of context on why the string has a length of 2 instead of 1, Wikipedia has the explanation:

String implementations based on UTF-16 typically return lengths and allow indexing in terms of code units, not code points. Neither code points nor code units correspond to anything an end user might recognize as a “character”; the things users identify as characters may in general consist of a base code point and a sequence of combining characters (or be a sequence of code points of other kind, for example Hangul conjoining jamos) – Unicode refers to this as a grapheme cluster – and as such, applications dealing with Unicode strings, whatever the encoding, have to cope with the fact that they cannot arbitrarily split and combine strings.

Thus we can say that the grapheme cluster of “𤭢” is composed of two UTF-16 characters, or four bytes. For an extreme example, let’s take the family emoji: 👨‍👨‍👧‍👧, , a grapheme cluster that is 11 UTF-16 characters.

// 25

// 22

// 28

So again we see how one grapheme cluster is encoded differently depending encoding used. For proof that Farmhash.Sharp works over UTF-16 encoded strings, we show that directly hashing “𤭢” and the UTF-16 bytes of “𤭢” are the same:

var d = Encoding.Unicode.GetBytes("𤭢");
Farmhash.Hash64(d, d.Length).ToString("X2")
// 7D9D6CEA9FCF031D

// 7D9D6CEA9FCF031D


A single sentence can sum up the Farmhash.Sharp string implementation:

Farmhash.Sharp hashes strings in a zero allocation implementation by interpreting the UTF-16 characters as bytes.

Sounds too simplistic, right? Like we’re doing something wrong, as the internet is rife to suggest, but sometimes the best answer is the simplest one.

IPv6 only networking not viable in 2018

Get on board the IPv6 train

Quick rant about the roadblocks that IPv6 only networking has caused:

Gist of it:

  • launchpad.net is ipv4, so adding Ubuntu apps via apt-add-repository or by an apt list (that reference launchpad.net) will fail. This severely hinders acquiring software that isn’t distributed by Ubuntu.
  • keyserver.ubuntu.com is ipv4 so one can’t use the default method for trusting package keys (something that shouldn’t be taken lightly)
  • thus everything ubuntu documents about packaging software is not possible via ipv6
  • github.com is ipv4 so one needs to either employ a proxy, nat, or manual transfer files

It can be quite crippling to not have access to these resources.

The full story:

I was checking out Vultr for a VPS. They offer a $2.50 a month package – the catch is that it is IPv6 only (inbound and outbound traffic must use IPv6 addresses). I figured this was no big deal. IPv6 has been in draft standard since 1998, and while it only became an internet standard in 2017, people have had 20 years to prepare. It’s not like you even have to abandon IPv4, one can serve a site with IPv6 and IPv4 side by side.

I got a kick out of IPv6 Excuse Bingo, and figured I should give IPv6 only a shot.

I wanted to setup algo, a personal VPN, and since I was just playing around, I wanted to keep costs down (hence the $2.50 a month package).

Following algo’s Ubuntu 18.04 server guide, I hit a snap on the first line:

apt-add-repository ppa:ansible/ansible

The line would error with

Cannot add PPA: 'ppa:~ansible/ubuntu/ansible'.
ERROR: '~ansible' user or team does not exist.

Searching online for this error would reveal questions only related to setting up an apt proxy. This and many other rabbit holes kept me scratching my head. It turns out, this is the error message that is given to users by apt-add-repository when it can’t access either or (bug report). With IPv6 only networking, it is not possible without some intermediate translation to access those addresses.

I soldiered on, maybe I could circumvent this issue.

I go to manually add the ansible apt key

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7BB9C367

With the lovely error:

gpg: keyserver receive failed: No keyserver available

A bug: keyserver.ubuntu.com has no IPv6 connectivity. The recommended workaround is to switch out the host and execute:

apt-key adv --keyserver pool.sks-keyservers.net --recv-keys 7BB9C367

I’m not familiar with sks-keyservers.net and retrieving keys from a domain I’m not familiar with sets off major red flags. Still, I continue and get ansible installed.

Next issue:

git clone https://github.com/trailofbits/algo

Github doesn’t support IPv6, so I downloaded the repo on another box and transferred accordingly.

I started running algo and then I noticed that it executed the following lines to set up Wireguard (obvious in hindsight):

add-apt-repository ppa:wireguard/wireguard
apt-get update
apt-get install wireguard

These commands fail as add-apt-repository needs to contact an IPv4 address.

And it presented a high enough barrier that I quit (as add-apt-repository won’t work with IPv6). I realized that if I’m having this much trouble setting up the box, I’d have even more trouble when routing traffic through it as a VPN. There were some things I did try or could try to try and soften the problem:

  • Use NAT64, but that would require me to setup another box.
  • Use an apt proxy, but that would also require another box
  • Try appending sixxs.org to domain name for IPv4 exclusive addresses, but sixxs has been sunset.

The solution is simple: upgrade to IPv4. Yes it’ll cost more, but $5 vs $2.50 isn’t something that should cause major headaches.

I can only hope that IPv6 becomes more popular soon.