<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Mike Freedman on Medium]]></title>
        <description><![CDATA[Stories by Mike Freedman on Medium]]></description>
        <link>https://medium.com/@mike.freedman?source=rss-c5b841bfdada------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*W1enn37RaKJyl6h_Xee_MA.jpeg</url>
            <title>Stories by Mike Freedman on Medium</title>
            <link>https://medium.com/@mike.freedman?source=rss-c5b841bfdada------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 17:23:02 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mike.freedman/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[TimescaleDB 1.0 is Production Ready]]></title>
            <link>https://medium.com/timescale/1-0-enterprise-production-ready-time-series-database-open-source-d32395a10cbf?source=rss-c5b841bfdada------2</link>
            <guid isPermaLink="false">https://medium.com/p/d32395a10cbf</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mike Freedman]]></dc:creator>
            <pubDate>Tue, 30 Oct 2018 18:29:25 GMT</pubDate>
            <atom:updated>2018-10-31T02:01:54.062Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Since announcing our first release candidate in September, Timescale’s engineering team has merged over 50 PRs to harden the database, improving stability and ease-of-use.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_AQpOkN-uIBqGBaFkGH8Lg.jpeg" /></figure><p>Today, we are thrilled to announce that <a href="https://github.com/timescale/timescaledb/releases/tag/1.0.0">TimescaleDB 1.0</a> is now production ready.</p><p><a href="https://blog.timescale.com/announcing-1-0-enterprise-ready-time-series-database-sql-open-source-400b4342dfa0">First introduced several weeks ago</a> in an initial release candidate, <a href="http://bit.ly/2zan0E9">TimescaleDB</a> 1.0 is the result of more than two years of dedicated effort. Our engineering team, which started with a core of my former PhD students and postdocs out of Princeton University, now encompasses a team of highly experienced engineers across the U.S. and Europe.</p><p>While TimescaleDB itself has been production ready for several months now, with its official 1.0 release TimescaleDB is now the first enterprise-ready time-series database to support full SQL and scale. This release builds upon the open source momentum that we have seen so far, with over 1M downloads and production deployments at Comcast, Cray, Cree (and more).</p><p><a href="https://blog.timescale.com/announcing-1-0-enterprise-ready-time-series-database-sql-open-source-400b4342dfa0">Announcing TimescaleDB 1.0: First enterprise-ready time-series database to support full SQL &amp; scale</a></p><h4>Thanking our team and our contributors</h4><p>To get to 1.0, our engineering team has worked diligently over the past several weeks to merge over 50 PRs to harden the database, improving stability and ease-of-use.</p><p>Our journey to TimescaleDB 1.0 also would not have been possible without a long list of supporters, who have contributed both code, reports, and feedback. These include GitHub users: <em>@did-g, @kev009, @mx781, @HeikoOnnebrink, @znbang, @timolson, @alanhamlett, @oldgreen, @jesperpedersen, @skebanga, @p-alik, @mx781, @EvanCarroll, @Ngalstyan4 @hjsuh18, @fvannee, @fmacelw, @soccerdroid, @mfuterko, @linba708, @phlsmk, @virakothari, @mmouterde, @mfuterko, @gumshoes, @manigandham, @wallies, @cjrh, @thaxy, @bertmelis, @The-Alchemist, @chaintng, @jgrandstrom, @saosebastiao, @jbylund, @Anthares, @oldgreen, @maksm90, @Rashid987, @haohallo, @LonghronShen, @devereaux, @carlospeon, @simPod, @ryan-shaw, @camperterson, @jwdeitch, @vjpr, @sztanpet, @shaneodonnell, @jgraichen, @nathansgreen, @jonmd, @jwdeitch, @ziXet, @djk447, @qlandman, @min-mwei, @raycheung, @meotimdihia, @noyez, @andrew-blake, and @yadid.</em></p><h4>Installing TimescaleDB 1.0</h4><p>If you’re ready to get started, you can download and install TimescaleDB here (<a href="http://bit.ly/2ESEDP3">installation instructions</a>). Once you’re ready for production and are looking for deployment assistance and production-level SLAs, we also offer <a href="http://bit.ly/2EPrpCl">enterprise support</a>.</p><p>If you ever have any questions, please don’t hesitate to send us an email or join our 1500+ member-strong <a href="http://slack.timescale.com/">Slack community</a>.</p><p>Also, our engineering team is rapidly growing, both in our NYC and Stockholm offices and with other remote engineers spread across the US and Europe. If these are the types of technical challenges that excite you, then <a href="http://bit.ly/2Q5YY4f">we’d love to hear from you</a>.</p><h3>What’s new in 1.0 since RC-1</h3><h4>Broad usability improvements</h4><p><strong>Time-series manipulation: </strong>Our <a href="https://github.com/timescale/timescaledb/releases/tag/1.0.0">1.0 release</a> has a number of usability improvements to time-series manipulation functions. These include an explicit arbitrary datetime with which to start time bucketing for common aggregation functions (e.g., September 1), as well as aligning weeks starting from Monday to respect the ISO standard.</p><p><strong>Elastically scaling hypertables across multiple disks: </strong>Additionally, we’ve added cleaner management of multiple tablespaces to allow hypertables to elastically grow across many disks. And we’ve made better information available about the state of hypertables, their dimensions, and their chunks when creating and managing data.</p><p><strong>Better Windows, FreeBSD, NetBSD support: </strong>When it comes to usability, it’s important to have robust cross-operating system availability. This release added improvements for supporting Windows, FreeBSD, and NetBSD.</p><h4>The foundation for background job automation and scheduling</h4><p>One of the biggest features recently shipped, and hardened through our 1.0 release process, is the foundation for a database scheduling framework that manages background jobs.</p><p>Building and shipping this scheduling framework took several months of dedicated effort. One of the main challenges was that PostgreSQL, which underlies TimescaleDB, is architected to support multiple logical databases running within a single instance of PostgreSQL (with each database able to have its own installed extension). Because TimescaleDB is implemented as an extension (and thus, a single PostgreSQL instance can have multiple, different versions of TimescaleDB running), we had to ensure that this scheduling framework and job launcher would operate correctly even in this multi-versioned environment.</p><p>Upcoming releases of TimescaleDB will take advantage of this foundation with more automation around key ease-of-use capabilities like automatic data aggregation, retention, and archiving. They will also include automated data management techniques for improving query performance, such as non-blocking reclustering and reindexing of older data.</p><h4>Increased database hardening and testing</h4><p>Robustness and resilience in an enterprise-ready database is the top priority. This has always been an area where TimescaleDB has outshined other time-series databases, especially ones that were built from scratch, and is something that we continually focus on. This latest update more elegantly handles edge cases related to schema and tablespace modifications, provides cleaner permissions for backup/recovery in templated databases, and includes additional test coverage.</p><h4>Building the open-source monitoring stack: Native support for Grafana and Prometheus</h4><p>While not strictly part of this release, in parallel to making 1.0 production-ready we shipped a native query editor and support for TimescaleDB within <a href="https://grafana.com/">Grafana</a>. We also added capabilities for deploying TimescaleDB as the long-term store behind a <a href="https://prometheus.io/">Prometheus</a> HA deployment. These improvements expand our ecosystem of compatible tools, which thanks to PostgreSQL is the largest of any time-series database. This ecosystem includes tools such as Tableau, Kafka, Apache Spark, Zabbix, PostGIS, PowerBI, ORMs, and many more.</p><p><strong>Native Grafana visual query editor for TimescaleDB and PostgreSQL: </strong>Starting with Grafana v5.3, there is a new visual query editor for the <a href="http://docs.grafana.org/features/datasources/postgres/">PostgreSQL/TimescaleDB datasource</a>, so that users don’t <em>have</em> to write SQL to access their data. Both the query editor and the underlying PostgreSQL datasource are contributions made by TimescaleDB engineers to the open-source Grafana community.</p><p>More on how to use Grafana + TimescaleDB for time-series exploration on Grafana’s blog:</p><p><a href="https://grafana.com/blog/2018/10/15/make-time-series-exploration-easier-with-the-postgresql/timescaledb-query-editor/">Make time-series exploration easier with the PostgreSQL/TimescaleDB query editor</a></p><p><strong>New capabilities for deploying TimescaleDB with Prometheus High-Availability (HA): </strong>A few months ago, we wrote <a href="https://docs.timescale.com/v0.10/tutorials/prometheus-adapter?utm_source=timescale-blog&amp;utm_medium=blog&amp;utm_campaign=prometheus-post&amp;utm_content=link3#how-to">a detailed tutorial on how to use TimescaleDB behind Prometheus</a> for: scalable, durable long-term data storage; joining DevOps data with business data; query power and flexibility with SQL; operational ease including the ability to update and/or delete records; and better operational insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PG08HMzKY_awupvPeWK6ew.jpeg" /></figure><p>More recently, we released new capabilities to help with managing a high-availability Prometheus deployment. One of the main challenges in these deployments is synchronizing data, and in particular avoiding duplicated metrics, when there is a failover. To address this challenge, we introduced leader election mechanism, using some of the concurrency control features native to PostgreSQL. If you are using Prometheus in an HA setting, I would encourage you to take a look at our tutorial and the underlying <a href="https://github.com/timescale/prometheus-postgresql-adapter/releases/tag/0.3">prometheus-postgresql-adapter</a>.</p><p><a href="https://blog.timescale.com/prometheus-ha-postgresql-8de68d19b6f5">How to manage Prometheus high-availability with PostgreSQL + TimescaleDB</a></p><h3>Only the beginning…</h3><p>But this 1.0 release is only the beginning for Timescale, and we continue to bring easier, more powerful time-series analytics and insights to users.</p><p>So look forward to more announcements over the coming year. In the meantime, you can get started by <a href="http://bit.ly/2EO6akx">downloading and installing</a> TimescaleDB 1.0, inquiring about our <a href="http://bit.ly/2Rpy47X">enterprise-grade support and assistance</a>, or joining our 1500+ member-strong <a href="http://slack.timescale.com/">Slack community</a>. And if you are interested in helping us build the next great open-source company, <a href="http://bit.ly/2CP1JmT">come talk with us</a>.</p><p><em>Like this post? Please recommend and/or share. Want to stay updated with all things </em><a href="http://bit.ly/2OZVGTS"><em>Timescale</em></a><em>? Sign up for our mailing list below.</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2F3b1642%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2F1.0-final%2F&amp;image=https%3A%2F%2Fucarecdn.com%2Fb5cf98d5-3ed4-4247-902b-e731227dc98a%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/bed4d20f587c42a858ddf18f83f44af1/href">https://medium.com/media/bed4d20f587c42a858ddf18f83f44af1/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d32395a10cbf" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/1-0-enterprise-production-ready-time-series-database-open-source-d32395a10cbf">TimescaleDB 1.0 is Production Ready</a> was originally published in <a href="https://medium.com/timescale">Timescale</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[TimescaleDB vs. InfluxDB: purpose built differently for time-series data]]></title>
            <link>https://medium.com/timescale/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877?source=rss-c5b841bfdada------2</link>
            <guid isPermaLink="false">https://medium.com/p/36489299877</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Mike Freedman]]></dc:creator>
            <pubDate>Tue, 14 Aug 2018 20:47:25 GMT</pubDate>
            <atom:updated>2018-08-30T16:10:16.115Z</atom:updated>
            <content:encoded><![CDATA[<p><em>An in-depth look into how two leading time-series databases stack up in terms of data model, query language, reliability, performance, ecosystem, operational management, and company/community support</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v3mElefr3seH6VQUcq2dRQ.jpeg" /></figure><p><a href="https://blog.timescale.com/what-the-heck-is-time-series-data-and-why-do-i-need-a-time-series-database-dcf3b1b18563">Time-series data</a> is emerging in more and more applications, including IoT, DevOps, Finance, Retail, Logistics, Oil and Gas, Manufacturing, Automotive, Aerospace, SaaS, even machine learning and AI. Most recently, the focus of time-series databases has been narrowly on metrics and monitoring; today it’s become clear that software developers really need a true time-series database designed for a variety of operational workloads.</p><p>If you are investing in a time-series database, that likely means you already have a meaningful amount of time-series data piling up quickly and need a place to store and analyze it. You may also recognize that the survival of your business will depend on the database you choose.</p><h3>How to choose a time-series database</h3><p>There are several factors to consider when evaluating a time-series database for your workload:</p><ol><li><strong>Data model</strong></li><li><strong>Query language</strong></li><li><strong>Reliability</strong></li><li><strong>Performance</strong></li><li><strong>Ecosystem</strong></li><li><strong>Operational management</strong></li><li><strong>Company and community support</strong></li></ol><p>In this article, we compare two leading time-series databases, <a href="http://www.timescale.com/?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=influx-benchmark-post&amp;utm_content=firstlink">TimescaleDB</a> and <a href="https://www.influxdata.com/">InfluxDB</a>, to help software developers choose the time-series database best suited for their needs.</p><p>Typically database comparisons focus on performance benchmarks. Yet performance is just a part of the overall picture; it doesn’t matter how good benchmarks are if a database can’t be used in production because it has an incompatible data model or query language, or if it lacks reliability. With that in mind, we begin by comparing TimescaleDB and InfluxDB across three qualitative dimensions, <em>data model, query language, and reliability,</em> before diving deeper with <em>performance benchmarks</em>. We then round out with a comparison across <em>database ecosystem, operational management, and company/community support</em>.</p><p>Yes, we are the developers of TimescaleDB, so you might quickly disregard our comparison as biased. But if you let the analysis speak for itself, you’ll find that it tries to stay objective — indeed, we report several scenarios below in which InfluxDB outperforms TimescaleDB.</p><p>Also, this comparison isn’t a purely theoretical activity for us. Our company began as an IoT platform, where we first used InfluxDB to store our sensor data. However, owing to most of the differences listed below, we found InfluxDB unsatisfactory. So we built TimescaleDB as the first time-series database that satisfied our needs, and then discovered others who needed it as well, which is when we decided to open source the database. Today, less than 1.5 years later, TimescaleDB has already been downloaded hundreds of thousands of times and is deployed in production all around the world. (<a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">More on the genesis of TimescaleDB.</a>)</p><p>In the end, our goal is help you decide which is the best time-series database for your needs.</p><h3>What about “scalability”?</h3><p><em>If you look closely at the list above, you’ll see that “scalability” or even “clustering” is missing. What we’ve found is that when developers ask for either of these requirements, what they are really asking is some combination of a) performance metrics b) high availability and/or c) storage capacity. We find it more helpful to speak about these three topics independently, instead of lumping them into a catch-all term, which is what we’ve done below.</em></p><h3>Data model</h3><p>Databases are opinionated. The way a database chooses to model and store your data will determine what you can do with it.</p><p>When it comes to data models, TimescaleDB and InfluxDB have two very different opinions: TimescaleDB is a relational database, while InfluxDB is more of a custom, NoSQL, non-relational database. What this means is that TimescaleDB relies on the <em>relational data model</em>, commonly found in PostgreSQL, MySQL, SQL Server, Oracle, etc. On the other hand, InfluxDB has developed its own custom data model, which, for the purpose of this comparison, we’ll call the <em>tagset data model</em>.</p><h4>Relational data model</h4><p>The <a href="https://en.wikipedia.org/wiki/Relational_model"><em>relational data model</em></a> has been in use for several decades now. With the <a href="https://docs.timescale.com/v0.11/introduction/data-model?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=influx-benchmark-post&amp;utm_content=firstdocslink">relational model in TimescaleDB</a>, each time-series measurement is recorded in its own row, with a time field followed by any number of other fields, which can be floats, ints, strings, booleans, arrays, JSON blobs, <a href="http://postgis.net/">geospatial dimensions</a>, date/time/timestamps, currencies, binary data, or even <a href="https://www.postgresql.org/docs/current/static/datatype.html">more complex data types</a>. One can create indexes on any one field (standard indexes) or multiple fields (composite indexes), or on expressions like functions, or even limit an index to a subset of rows (partial index). Any of these fields can be used as a <em>foreign key</em> to secondary tables, which can then store additional metadata.</p><p>An example is below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OUL0wYG8m5se1dEaseMR5w.jpeg" /></figure><p>The advantage of this approach is that it is quite flexible. One can choose to have:</p><ul><li>A narrow or wide table, depending on how much data and metadata to record per reading</li><li>Many indexes to speed up queries or few indexes to reduce disk usage</li><li>Denormalized metadata within the measurement row, or normalized metadata that lives in a separate table, either of which can be updated at any time (although it is easier to update in the latter case)</li><li>A rigid schema that validates input types or a schemaless JSON blob to increase iteration speed</li><li>Check constraints that validate inputs, for example checking for uniqueness or non-null values</li></ul><p>The disadvantage of this approach is that to get started, one needs to generally choose a schema, and explicitly decide whether or not have indexes.</p><p><em>Note: In the past several years it’s been popular to criticize the relational model by claiming that it is not scalable. However, </em><a href="https://blog.timescale.com/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c"><em>as we have already shown</em></a><em>, this is simply not true: relational databases can indeed scale very well for time-series data.</em></p><h4>Tagset data model</h4><p>With the InfluxDB <a href="https://docs.influxdata.com/influxdb/v1.6/concepts/schema_and_data_layout/"><em>tagset data model</em></a>, each measurement has a timestamp, and an associated set of tags (tagset) and set of fields (fieldset). The fieldset represents the actual measurement reading values, while the tagset represents the metadata to describe the measurements. Field data types are limited to floats, ints, strings, and booleans, and cannot be changed without rewriting the data. Tagset values are indexed while fieldset values are not. Also, tagset values are always represented as strings, and cannot be updated.</p><p>An example is below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p4RuHLRIPs5n1NP2ExQTdQ.jpeg" /></figure><p>The advantage of this approach is that if one’s data naturally fits the tagset model, then it is quite easy to get started, as one doesn’t have to worry about creating schemas or indexes. Conversely, the disadvantage of this model is that it is quite rigid and limited, with no ability to create additional indexes, indexes on continuous fields (e.g., numerics), update metadata after the fact, enforce data validation, etc. In particular, even though this model may feel “schemaless”, there is actually an underlying schema that is auto-created from the input data, which may differ from the desired schema.</p><h4>Data model summary</h4><p>If your data fits perfectly within the tagset data model, and you don’t expect that to change in the future, then you should consider using InfluxDB as this model is easier to get started with. However, the relational model is more versatile and offers more functionality, flexibility, and control. This is especially important as your application evolves. And when planning your system you should consider both its current and future needs.</p><h3>Query language</h3><p>Generally in the world of database query languages, there have been two extremes: full SQL support on one end, and completely custom languages (sometimes known as “NoSQL”) on the other.</p><figure><a href="https://blog.timescale.com/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a"><img alt="" src="https://cdn-images-1.medium.com/max/800/0*EVwGCotBA8Ndj8DL" /></a><figcaption>For more, please read our recently published detailed comparison on <a href="https://blog.timescale.com/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a">SQL vs. Flux</a>.</figcaption></figure><p>From the beginning, TimescaleDB has firmly existed at the SQL end of the spectrum, fully embracing the language from day 1, and later further extending it to simplify time-series analysis. This has enabled TimescaleDB to have a minimal learning curve for new users, and allowed it to inherit the entire SQL ecosystem of 3rd party tools, connectors, and visualization options, which is larger than that of any other time-series database.</p><p>In contrast, InfluxDB began with a “SQL-like” query language (called <a href="https://docs.influxdata.com/influxdb/v1.6/query_language/spec/">InfluxQL</a>), placing it in the middle of the spectrum, and has recently made a marked move towards the “custom” end with its new <a href="https://github.com/influxdata/platform/blob/master/query/docs/SPEC.md">Flux</a> query language. This has allowed InfluxDB to create a new query language that its creators would argue overcomes some SQL shortcomings that they had experienced. (Read the <a href="https://www.influxdata.com/blog/why-were-building-flux-a-new-data-scripting-and-query-language/">Flux announcement</a>, the <a href="https://news.ycombinator.com/item?id=17567554">Hacker News reaction</a>, and our comparison of <a href="https://blog.timescale.com/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a">SQL vs. Flux</a>.)</p><p>At a high-level, here’s how the two language syntaxes compare, using the computation of an <em>exponential moving average</em> as an example:</p><p><strong>TimescaleDB (SQL)</strong></p><pre>SELECT time,<br>exponential_moving_average(value, 0.5) OVER (ORDER BY time)<br>FROM metrics<br>WHERE measurement = &#39;foo&#39; and time &gt; now() - &#39;1 hour&#39;;</pre><p><strong>InfluxDB (Flux)</strong></p><pre>from(db:&quot;metrics&quot;)<br>|&gt; range(start:-1h)<br>|&gt; filter(fn: (r) =&gt; r._measurement == &quot;foo&quot;)<br>|&gt; exponentialMovingAverage(size:-10s)</pre><p><em>For more, please read our recently published detailed comparison on </em><a href="https://blog.timescale.com/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a"><em>SQL vs. Flux</em></a><em>.</em></p><p>To summarize: For most use cases, we believe that SQL is the right query language for a time-series database.</p><p>While Flux may make some tasks easier, there are significant trade-offs to adopting a custom query language like it. The fact is that new query languages introduce significant overhead and reduce readability. They force a greater learning curve onto new users and possess a scarcity of compatible tools.</p><p>And they may not even be a viable option: rebuilding a system and re-educating a company to write and read a new query language is often not practically possible. Particularly if the company already is using SQL-compatible tools on top of the database, e.g., Tableau for visualization.</p><p>This is also why <a href="https://blog.timescale.com/why-sql-beating-nosql-what-this-means-for-future-of-data-time-series-database-348b777b847a">SQL is making a comeback</a> as the query language of choice for data infrastructure in general.</p><h3>Reliability</h3><p>Another cardinal rule for a database: it cannot lose or corrupt your data. This is a dimension where there is a stark difference in the approaches TimescaleDB and InfluxDB have taken, which has implications for reliability.</p><p>At its start, InfluxDB sought to completely <a href="https://blog.gopheracademy.com/birthday-bash-2014/why-influxdb-uses-go/">write an entire database in Go</a>. In fact, it doubled down on this decision with its 0.9 release, which again <a href="https://www.influxdata.com/blog/new-storage-engine-time-structured-merge-tree/">completely rewrote the backend storage engine</a> (the earlier versions of Influx were going in the direction of a pluggable backend with LevelDB, RocksDB, or others). There are indeed benefits from this approach, e.g., you can build domain-specific compression algorithms that are better suited for a particular use case, as InfluxDB has done with its use of Facebook’s Gorilla encoding.</p><p>Yet these design decisions have significant implications that affect reliability. First, InfluxDB has to implement the full suite of fault-tolerance mechanisms, including replication, high availability, and backup/restore. Second, InfluxDB is responsible for its on-disk reliability, e.g., to make sure all its data structures are both durable and resist data corruption across failures (and even failures during the recovery of failures).</p><p>Due to its architectural decisions, on the other hand, TimescaleDB instead relies on the 25+ years of hard, careful engineering work that the entire PostgreSQL community has done to build a rock-solid database that can support truly mission-critical applications.</p><p>In fact, this was at the core of my co-founder’s launch post about TimescaleDB: <a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">When Boring is Awesome</a>. Stateless microservices may crash and reboot, or trivially scale up and down. In fact, this is the entire “<a href="https://en.wikipedia.org/wiki/Recovery-oriented_computing">recovery-oriented computing</a>” philosophy, as well as the thinking behind the new “serverless” design pattern. But your database needs to actually persist data, and should not wake you up at 3am because it’s in some broken state.</p><p><a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">When Boring is Awesome: Building a scalable time-series database on PostgreSQL</a></p><p><strong>So let us return to these two aspects of reliability.</strong></p><p>First, programs can crash, servers can encounter hardware or power failures, disks can fail or experience corruption. You can mitigate but not eliminate this risk, e.g., robust software engineering practices, uninterrupted power supplies, disk RAID; it’s a fact of life for systems. In response, databases have been built with an array of mechanisms to further reduce such risk, including streaming replication to replicas, full-snapshot backup and recovery, streaming backups, robust data export tools, etc.</p><p>Given TimescaleDB’s design, it’s able to leverage the full complement of tools that the Postgres ecosystem offers and has rigorously tested, and all of these are available in open-source: <a href="https://blog.timescale.com/scalable-postgresql-high-availability-read-scalability-streaming-replication-fb95023e2af">streaming replication</a> for high availability and read-only replicas, <a href="https://www.postgresql.org/docs/current/static/app-pgdump.html">pg_dump and pg_recovery</a> for full database snapshots, <a href="https://www.postgresql.org/docs/current/static/app-pgbasebackup.html">pg_basebackup</a> and log shipping / streaming for incremental backups and arbitrary point-in-time recovery, <a href="https://github.com/wal-e/wal-e">WAL-E</a> for continuous archiving to cloud storage, and robust COPY FROM and COPY TO tools for quickly importing/exporting data with a variety of formats.</p><p>InfluxDB, on the hand, has had to build all these tools from scratch. In fact, it doesn’t offer many of these capabilities even today. It initially offered replication and high availability in its open source, but subsequently pulled this capability out of open source and into its enterprise product. Its backup tools have the ability to perform a full snapshot and recover to this point-in-time, and only recently added some support for a manual form of incremental backups. (That said, its approach of performing incremental backups based on database time ranges seems quite risky from a correctness perspectness, given that timestamped data may arrive out-of-order, and thus the incremental backups -since some time period would not reflect this late data.) And its ability to easily and safely export large volumes of data is also quite limited. We’ve heard from many users (including Timescale engineers in their past careers) that had to write custom scripts to safely export data; asking for more than a few 10,000s of datapoints would cause the database to out-of-memory error and crash.</p><p>Second, databases need to provide strong on-disk reliability and durability, so that once a database has committed to storing a write, it is safely persisted to disk. In fact, for very large data volumes, the same argument even applies to indexing structures, which could otherwise take hours or days to recover; there’s good reason that file systems have moved from painful fsck recovery to journaling mechanisms.</p><p>In <a href="http://www.timescale.com/?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=influx-benchmark-post&amp;utm_content=middlelink">TimescaleDB</a>, we made the conscious decision not to change the lowest levels of PostgreSQL storage, nor interfere with the proper function of its write-ahead log. (The WAL ensures that as soon a write is accepted, it gets written to an on-disk log to ensure safety and durability, even before the data is written to its final location and all its indexes are safely updated.) These data structures are critical for ensuring consistency and atomicity; they prevent data from becoming lost or corrupted, and ensure safe recovery. This is something the database community (and PostgreSQL) has worked hard on: what happens if your database crashes (and will subsequently try to recover) while it’s already in the middle of recovering from another crash.</p><p>InfluxDB had to design and implement all this functionality itself from scratch. <a href="https://dl.acm.org/citation.cfm?id=128770">This is a notoriously hard problem in databases</a> that typically takes many years or even decades to get correct. Some metrics stores might be okay with occasionally losing data; we see TimescaleDB being used in settings where this is not acceptable. In fact, across all our users and deployments, we’ve had only one report of data being corrupted, which on investigation turned out to be the fault of the commercial SAN the user was employing, not TimescaleDB (and their recovery from backup was successful). InfluxDB forums, on the other hand, are rife with such complaints: “<a href="https://github.com/influxdata/influxdb/issues/1315">DB lost after restart</a>”, “<a href="https://github.com/influxdata/influxdb/issues/7330">data loss during high ingest rate</a>”, “<a href="https://community.influxdata.com/t/data-lost-from-influxdb-databases/1287">data lost from InfluxDB databases</a>”, “<a href="https://github.com/influxdata/influxdb/issues/9949">unresponsive due to corruption after disk disaster</a>”, “<a href="https://github.com/influxdata/influxdb/issues/9796">data messed up after restoring multiple databases</a>”, and so on.</p><p>These challenges and problems are not unique to InfluxDB, and every developer of a reliable, stateful service must grapple with them. Every database goes through a period when it sometimes loses data because its really, really hard to get <em>all</em> the corner cases right. And eventually, all those corner cases come to haunt some operator. But PostgreSQL went through this period in the 1990s, while InfluxDB still needs to figure these things out.</p><p>These architectural decisions have thus allowed TimescaleDB to provide a level of reliability far beyond its years, as it stands on the proverbial “shoulders of giants”. Indeed, just one month after we first released TimescaleDB in April 2017, it was deployed at the operator-facing dashboards in 47 power plants across Europe and Latin America. And so while InfluxDB (2013) was released several years <em>before</em> TimescaleDB (2017), we believe it still has many years of dedicated engineering effort just to catch up, specifically because it was built from scratch.</p><h3>Performance</h3><p>Now, let’s get into some hard numbers with a quantitative comparison of the two databases across a variety of insert and read workloads.</p><p><em>Note: We recently released all the code and data used for the below benchmarks as part of the open-source Time Series Benchmark Suite (TSBS) (</em><a href="https://github.com/timescale/tsbs"><em>Github</em></a><em>, </em><a href="https://blog.timescale.com/time-series-database-benchmarks-timescaledb-influxdb-cassandra-mongodb-bc702b72927e"><em>announcement</em></a><em>).</em></p><p><a href="https://blog.timescale.com/time-series-database-benchmarks-timescaledb-influxdb-cassandra-mongodb-bc702b72927e">You are what you benchmark: Introducing the Time Series Benchmark Suite (TSBS)</a></p><p>We used the following setup for each database:</p><ul><li>TimescaleDB <a href="https://github.com/timescale/timescaledb/releases/tag/0.10.1">version 0.10.1</a>, InfluxDB <a href="https://github.com/influxdata/influxdb/releases/tag/v1.5.2">version 1.5.2</a></li><li>1 remote client machine, 1 database server, both in the same cloud datacenter</li><li>Azure instance: Standard DS4 v2 (8 vCPU, 28 GB memory)</li><li>4 1-TB disks in a raid0 configuration (EXT4 filesystem)</li><li>Both databases were given all available memory</li><li>Dataset: 100–4,000 simulated devices generated 1–10 CPU metrics every 10 seconds for 3 full days (~100M reading intervals, ~1B metrics)</li><li>10K batch size was used for both on inserts</li><li>For TimescaleDB, we set the chunk size to 12 hours, resulting in 6 total chunks (<a href="http://docs.timescale.com/v0.9/using-timescaledb/hypertables?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=influx-benchmark-post&amp;utm_content=seconddocslink#best-practices">more here</a>)</li><li>For InfluxDB, we enabled the <a href="https://docs.influxdata.com/influxdb/v1.6/concepts/tsi-details/">TSI (time series index)</a></li></ul><h4>Insert performance</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HrPeP9hPtlzTyfk5t8Cq_Q.jpeg" /></figure><p>On inserts, the results are fairly clear: For workloads with extremely low cardinality, InfluxDB outperforms Timescale by over 2x. However, as cardinality moderately increases, InfluxDB performance drops dramatically due to its reliance on time-structured merge trees (which, similar to the log-structured merge trees it is modeled after, suffers with higher-cardinality datasets). This of course should be no surprise, as high cardinality is a well known Achilles heel for InfluxDB (source: <a href="https://github.com/influxdata/influxdb/search?q=%22high+cardinality%22&amp;type=Issues">Github</a>, <a href="https://community.influxdata.com/search?q=cardinality%20order%3Alatest">Forums</a>). In comparison, TimescaleDB only sees a moderate drop off as cardinality increases, and very quickly surpasses InfluxDB in terms of insert performance.</p><p>That said, it is worth doing an honest analysis of your insert needs. If your insert performance is far below these benchmarks (e.g., if it is 2,000 rows / second), then insert performance will not be your bottleneck, and this comparison becomes moot.</p><p><em>Note: These metrics are measured in terms of rows per second (in the case of InfluxDB, defined as a collection of metrics recorded at the same time). If you are collecting multiple metrics per row, then the total number of metrics per second can be much higher. For example, in our [4,000 devices x 10 metrics] test, you would multiply [rows per second] by [10], resulting in 1.44M metrics/sec for TimescaleDB and 0.56M metrics/sec for InfluxDB.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*S0TtX1fqqq25D8SA" /></figure><p><strong>Insert performance summary</strong></p><ul><li>On inserts, for workloads with very low cardinality (e.g., 100 devices sending 1 metric), InfluxDB outperforms TimescaleDB.</li><li>As cardinality increases, InfluxDB insert performance drops off faster than that with TimescaleDB.</li><li>For workloads with moderate to high cardinality (e.g., 100 devices sending 10 metrics), TimescaleDB outperforms InfluxDB.</li><li>Be aware of your insert needs; these limits may not be your bottleneck.</li></ul><h4>Read latency</h4><p>On read (i.e., query) latency, the results are more complex. This is because, unlike inserts which primarily vary on cardinality size (and perhaps also batch size), the universe of possible queries is essentially infinite, especially with a language as powerful as SQL. With that in mind, we’ve found that the best way to benchmark read latency is to do it with the actual queries you plan to execute.</p><p>That said, internally we use a broad set of queries to mimic the most common query patterns. The results are below, using the same workloads we used for inserts. Latencies in this chart are all shown as milliseconds, with an additional column showing the relative performance of TimescaleDB compared to InfluxDB (highlighted in orange when TimescaleDB is faster, in blue when InfluxDB is faster).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8lkqIeymY2ft_3BXx9pnSQ.png" /></figure><p><strong>SIMPLE ROLLUPS<br></strong>For simple rollups (i.e., groupbys) aggregating metrics by time: When aggregating one metric across one host for 1 or 12 hours, or multiple metrics across one or multiple hosts (either for 1 hour or 12 hours), TimescaleDB generally outperforms InfluxDB at low-to-medium cardinality, but at high-cardinality this reverses. The one exception is when aggregating multiple metrics across one host for one hour, where TimescaleDB outperforms InfluxDB regardless of cardinality. When aggregating one metric across several hosts, InfluxDB outperforms TimescaleDB, although the delta decreases as cardinality increases.</p><p><strong>DOUBLE ROLLUPS<br></strong>For double rollups aggregating metrics by time and another dimension (e.g., GROUPBY time, deviceId): When aggregating one metric, InfluxDB outperforms TimescaleDB. But when aggregating multiple metrics, TimescaleDB outperforms InfluxDB.</p><p><strong>THRESHOLDS<br></strong>When selecting rows based on a threshold, TimescaleDB outperforms InfluxDB, except in the one case of computing threshold on one device with a high cardinality dataset.</p><p><strong>COMPLEX QUERIES<br></strong>For complex queries that go beyond rollups or thresholds, there really is no comparison: TimescaleDB <em>vastly</em> outperforms InfluxDB here (in some cases over thousands of times faster). The <em>absolute</em> difference in performance here is actually quite stark: While InfluxDB might be faster by a few milliseconds or tens of milliseconds for some of the single-metric rollups, this difference is mostly indistinguishable to human-facing applications.</p><blockquote>For complex queries that go beyond rollups or thresholds, there really is no comparison: TimescaleDB <em>vastly</em> outperforms InfluxDB here (in some cases over thousands of times faster).</blockquote><p>Yet for these more complex queries, TimescaleDB provides real-time responses (e.g., 10–100s of milliseconds), while InfluxDB sees significant human-observable delays (tens of seconds). It is also worth noting that were several other complex queries that we couldn’t test because of lack of support from InfluxDB: e.g., joins, window functions, geospatial queries, etc.</p><p><strong>Read latency performance summary</strong></p><ul><li>For simple queries, the results vary quite a bit: there are some where one database is clearly better than the other, while others depend on the cardinality of your dataset. The difference here is often in the range of single-digit to double-digit milliseconds.</li><li>For complex queries, TimescaleDB vastly outperforms InfluxDB, and supports a broader range of query types. The difference here is often in the range of seconds to tens of seconds.</li><li>With that in mind, the best way to properly test is to benchmark using the queries you plan to execute.</li></ul><h4>Stability issues during benchmarking</h4><p>It is worth noting that we had several operational issues benchmarking InfluxDB as our datasets grew, even with TSI enabled. In particular, as we experimented with higher cardinality data sets (100K+ tags), we ran into trouble with both inserts and queries on InfluxDB (but not TimescaleDB).</p><p>While we were able to insert batches of 10K into InfluxDB at lower cardinalities, once we got·to 1M cardinality we would experience timeouts and errors with batch sizes that large. We had to cut our batches down to 1–5K and use client side code to deal with the backpressure incurred at higher cardinalities. We had to force our client code to sleep for up to 20 seconds after requests received errors writing the batches. With TimescaleDB, we were able to write large batches size at higher cardinality without issue.</p><p>Starting at 100K cardinality, we also experienced problems with some of our read queries on InfluxDB. Our InfluxDB HTTP connection would error out with a cryptic ‘End of File’ message. When we investigated the InfluxDB server we found out that InfluxDB had consumed all available memory to run the query and subsequently crashed with an Out of Memory error. Since PostgreSQL helpfully allows us to limit system memory usage with settings like shared_buffers and work_mem, this generally was not an issue for TimescaleDB even at higher cardinalities.</p><p><strong>Stability issues summary</strong></p><ul><li>Even with TSI, InfluxDB has stability and performance issues at high (100K+) cardinalities.</li></ul><h3>Ecosystem</h3><p>The database can only do so much, which is when one typically turns to the broader 3rd party ecosystem for additional capabilities. This is when the size and scope of the ecosystem make a large difference.</p><p>TimescaleDB’s approach of embracing SQL pays large dividends, as it allows TimescaleDB to speak with any tool that speaks SQL. In contrast, the non-SQL strategy chosen by InfluxDB isolates the database, and limits how InfluxDB can be used by its developers.</p><p>Having a broad ecosystem makes deployment easier. For example, if one is already using Tableau to visualize data, or Apache Spark for data processing, TimescaleDB can plug right into the existing infrastructure due to its compatible connectors.</p><p>Here is a non-exhaustive list of 1st party (e.g., the components of the InfluxData TICK stack) and 3rd party tools that connect with either database, to show the relative difference in the two database ecosystems.</p><p>For the open-source projects below, to reflect the popularity of the projects, we included the number of GitHub stars they had as of publication in parentheses, e.g., Apache Kafka (9k+). For many of the unofficial projects for InfluxDB, for example, the unofficial supporting project was often very early (very few stars) or inactive (no updates in months or years).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1kjbPDo5G6vrDT_vrqnBPw.jpeg" /><figcaption><a href="https://docs.google.com/spreadsheets/d/1v4rLgph1LemuJBfh4rZxYTuektWvVF7XQphHi87oSxA/edit#gid=0">View full spreadsheet for project links.</a></figcaption></figure><h3>Operational Management</h3><p>Even if a database satisfies all the above needs, it still needs to work, and someone needs to operate it.</p><p>Based on our experience, operational management requirements typically boil down to these categories: high availability, resource consumption (memory, disk, cpu), general tooling.</p><h4>High availability</h4><p>No matter how reliable the database, at some point your node will go down: hardware errors, disk errors, or some other unrecoverable issue. At that point, you will want to ensure you have a standby available for failover with no loss of data.</p><p>TimescaleDB supports high availability via PostgreSQL streaming replication (as explained in <a href="https://blog.timescale.com/scalable-postgresql-high-availability-read-scalability-streaming-replication-fb95023e2af">this tutorial</a>). At one point, open source InfluxDB offered high availability via <a href="https://github.com/influxdata/influxdb-relay">InfluxDB-relay</a>, but it appears to be a dormant project (last update November 2016). Today InfluxDB HA is only offered by their <a href="https://docs.influxdata.com/influxdb/v1.6/high_availability/">enterprise version</a>.</p><h4>Resource consumption</h4><p><strong><em>Memory usage</em><br></strong>For memory utilization, cardinality again plays a large role. Below are some graphs using the same workloads we used earlier for measuring insert performance.</p><p>At low cardinality (100 devices sending one metric), InfluxDB requires less memory than TimescaleDB:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0wMKeS4ZbbCSLK-F" /></figure><p><em>Note: Both databases are inserting the same volume of data but take different amounts of time, which is why both line plots above and below don’t end at the same time.</em></p><p>However, as cardinality increases (100,000 devices sending 10 metrics), InfluxDB memory consumption far outpaces that of TimescaleDB (and with more volatility):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yJdf1kScaMEmrlUQ" /></figure><p>In particular, as far as we could tell, there was no way to limit total memory consumed by the InfluxDB TSI. So at higher cardinalities, InfluxDB would run out of memory on inserts, which would lead to the database crashing and restarting.</p><p><strong><em>Disk usage</em><br></strong>InfluxDB, like most databases that use a column-oriented approach, offers significantly better on-disk compression than PostgreSQL and TimescaleDB.</p><p>With the dataset used for the performance benchmarks, here’s how the two databases fared at the varying cardinalities:</p><ul><li><strong>100 devices x 1 metric x 30 days</strong>: InfluxDB (12MB) vs. TimescaleDB (700MB) = 59x</li><li><strong>100 devices x 10 metrics x 30 days</strong>: InfluxDB (113MB) vs. TimescaleDB (1400MB) = 12x</li><li><strong>4,000 devices x 10 metrics x 3 days</strong>: InfluxDB (769MB) vs. TimescaleDB (5900MB) = 8x</li></ul><p><em>Note: Disk size benchmarks were run using ZFS. Numbers do not include WAL size, as that is configurable by the user.</em></p><p>If minimizing disk storage is a primary requirement for your workload, then this is a big difference, and you may want to consider InfluxDB.</p><p>However, as we saw earlier, depending on your workload InfluxDB may also require much more memory. Given that memory is typically 100x-1000x more expensive than disk, trading off high disk usage for lower memory may be worthwhile for certain workloads.</p><p>TimescaleDB also allows one to elastically scale the number of disks associated with a hypertable without any data migration, which is another option to offset the higher disk consumption, particularly in SAN or cloud contexts. Users have scaled a single TimescaleDB node to 10s of TB using this method.</p><p>The other cost of InfluxDB’s better on-disk compression is that it required the developers to rewrite the <a href="https://www.influxdata.com/blog/new-storage-engine-time-structured-merge-tree/">backend storage engine</a> from scratch, which raises reliability challenges.</p><p><strong><em>CPU usage</em><br></strong>According to an <a href="https://blog.dnsfilter.com/3-billion-time-series-data-points-dnsfilter-replaced-influxdb-with-timescaledb-d9f827702f8b">external comparison</a> by <a href="https://www.dnsfilter.com/">DNSFilter</a>, using TimescaleDB resulted in 10x better resource utilization (even with 30% higher requests) when compared to InfluxDB:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*G2yRdB68jgcKXs9b" /><figcaption>Source: <a href="https://blog.dnsfilter.com/3-billion-time-series-data-points-dnsfilter-replaced-influxdb-with-timescaledb-d9f827702f8b">DNSFilter Comparison (March 2018)</a></figcaption></figure><h4>General tooling</h4><p>When operating TimescaleDB, one inherits all of the battle-tested tools that exist in the PostgreSQL ecosystem: <a href="https://www.postgresql.org/docs/9.3/static/app-pgdump.html">pg_dump</a> and <a href="https://www.postgresql.org/docs/9.2/static/app-pgrestore.html">pg_restore</a> for backup/restore, HA/failover tools like <a href="https://github.com/zalando/patroni">Patroni</a>, load balancing tools for clustering reads like <a href="http://www.pgpool.net/mediawiki/index.php/Main_Page">Pgpool</a>, etc. Since TimescaleDB looks and feels like PostgreSQL, there are minimal operational learning curves. TimescaleDB “just works”, as one would expect from PostgreSQL.</p><p>For operating InfluxDB, one is limited to the <a href="https://docs.influxdata.com/influxdb/v1.6/administration/">tools that the Influx team has built</a>: backup, restore, internal monitoring, etc.</p><h3>Company and Community Support</h3><p>Finally, when investing in an open source technology primarily developed by a company, you are implicitly also investing in that company’s ability to serve you.</p><p>With that in mind, let’s note the differences between Timescale and InfluxData, the companies behind TimescaleDB and InfluxDB, when it comes to company size and maturity, starting with funding.</p><p>In January of this year, Timescale announced it <a href="https://blog.timescale.com/raised-16m-from-benchmark-nea-to-understand-machines-time-series-data-iot-9eff8213c15c">raised $16M</a> (combined of Series A and Seed financing). Meanwhile, this past February, InfluxData announced it <a href="https://www.influxdata.com/blog/influxdata-raises-35m-to-accelerate-growth-and-meet-growing-global-demand-for-time-series-database/">closed a $35M </a>Series C round of financing, increasing their total amount raised to $59.9M.</p><p>These levels of fundraising directly correlate with each organization’s respective history. TimescaleDB was <a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">officially launched</a> on April 4, 2017 (a year and four months from time of publishing this post). InfluxDB was <a href="https://techcrunch.com/2013/03/18/errplane-performance-monitoring-and-alert-service-for-web-apps/">initially released back in September 2013</a> (around five years from this publishing date).</p><p>These varying fundraising amounts and histories also correspond to a large difference in the two companies’ technical and product approaches.</p><p>InfluxData needed to raise a large amount of money and build a large team to develop all of the internals required to build a production-ready database. In contrast, because TimescaleDB is developed on top of PostgreSQL, its engineering team needed to spend less effort creating basic database building blocks. Instead, Timescale has been able to focus on more advanced features directly related to time-series workloads, as well as user support, despite having the smaller engineering team.</p><p>This difference can also be seen in how much less time it took TimescaleDB to reach production-level maturity than InfluxDB (and perhaps even greater reliability by some metrics).</p><p>Additionally, sometimes database support comes not from the company, but from the community. InfluxData is building their community from scratch, while Timescale is able to inherit and build on PostgreSQL’s community.</p><p>And the PostgreSQL community is quite large. One can see its size by comparing the 77x difference in StackOverflow articles for “<a href="https://stackoverflow.com/questions/tagged/postgresql">PostgreSQL</a>” (88,245 as of this publication) versus those for “<a href="https://stackoverflow.com/questions/tagged/influxdb">InfluxDB</a>” (1,141). Because TimescaleDB operates just like PostgreSQL, many of these PostgreSQL questions are relevant to TimescaleDB. So if you are new to TimescaleDB (or PostgreSQL), there are many resources available to help get you started. Alternatively, if you are already a PostgreSQL expert, you will already know how to use TimescaleDB.</p><p>At the end of the day, both Timescale and InfluxData are stable companies dedicated to their users.</p><h3>Summary</h3><p>One of the worst mistakes a business can make is investing in a technology that will limit it in the future, let alone be the wrong fit today. That’s why we encourage you to take a step back and analyze your stack before you find your database infrastructure crumbling to the ground.</p><p>In this post, we performed a detailed comparison of TimescaleDB and InfluxDB. We don’t claim to be InfluxDB experts, so we’re open to any suggestions on how to improve this comparison. In general, we aim to be as transparent as possible about our data models, methodologies, and analysis, and we welcome feedback. We also encourage readers to raise any concerns about the information we’ve presented in order to help us with benchmarking in the future.</p><p>We recognize that <a href="http://www.timescale.com/?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=influx-benchmark-post&amp;utm_content=lastlink">TimescaleDB</a> isn’t the only time-series solution and there are situations where it might not be the best answer. And we strive to be upfront in admitting where an alternate solution may be preferable. But we’re always interested in holistically evaluating our solution against that of others, and we’ll continue to share our insights with the greater community.</p><p><em>Like this post? Please recommend and/or share.</em></p><p><em>Want to learn more? Join our </em><a href="http://slack.timescale.com/"><em>Slack community</em></a><em>, follow us </em><a href="https://blog.timescale.com/"><em>here</em></a><em> on Medium, check out our </em><a href="https://github.com/timescale/timescaledb"><em>GitHub</em></a><em>, and sign up for the community mailing below.</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2F7880b7%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2F7880b7%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/b3821216f4c2bddf1c42fbe2de144085/href">https://medium.com/media/b3821216f4c2bddf1c42fbe2de144085/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=36489299877" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877">TimescaleDB vs. InfluxDB: purpose built differently for time-series data</a> was originally published in <a href="https://medium.com/timescale">Timescale</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Time-series data: Why (and how) to use a relational database instead of NoSQL]]></title>
            <link>https://medium.com/timescale/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c?source=rss-c5b841bfdada------2</link>
            <guid isPermaLink="false">https://medium.com/p/d0cd6975e87c</guid>
            <category><![CDATA[timeseries]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[engineering]]></category>
            <dc:creator><![CDATA[Mike Freedman]]></dc:creator>
            <pubDate>Thu, 20 Apr 2017 19:04:11 GMT</pubDate>
            <atom:updated>2018-08-30T16:58:49.145Z</atom:updated>
            <content:encoded><![CDATA[<p>These days, time-series data applications (e.g., data center / server / microservice / container monitoring, sensor / IoT analytics, financial data analysis, etc.) are proliferating.</p><p>As a result, time-series databases are in fashion (<a href="https://misfra.me/2016/04/09/tsdb-list/">here are 33 of them</a>). Most of these renounce the trappings of a traditional relational database and adopt what is generally known as a NoSQL model. Usage patterns are similar: <a href="https://www.percona.com/blog/2017/02/10/percona-blog-poll-database-engine-using-store-time-series-data/">a recent survey</a> showed that developers preferred NoSQL to relational databases for time-series data by over 2:1.</p><figure><a href="https://www.percona.com/blog/2017/02/10/percona-blog-poll-database-engine-using-store-time-series-data/"><img alt="" src="https://cdn-images-1.medium.com/max/570/0*2EgHC6qFtl9E6_Om." /></a><figcaption><strong>Relational databases include:</strong> MySQL, MariaDB Server, PostgreSQL. <strong>NoSQL databases include:</strong> Elastic, InfluxDB, MongoDB, Cassandra, Couchbase, Graphite, Prometheus, ClickHouse, OpenTSDB, DalmatinerDB, KairosDB, RiakTS. <strong>Source:</strong> <a href="https://www.percona.com/blog/2017/02/10/percona-blog-poll-database-engine-using-store-time-series-data/">https://www.percona.com/blog/2017/02/10/percona-blog-poll-database-engine-using-store-time-series-data/</a></figcaption></figure><p>Typically, the reason for adopting NoSQL time-series databases comes down to scale. While relational databases have many useful features that most NoSQL databases do not (robust secondary index support; complex predicates; a rich query language; JOINs, etc), they are difficult to scale.</p><p>And because time-series data piles up very quickly, many developers believe relational databases are ill-suited for it.</p><p>We take a different, somewhat heretical stance: relational databases can be quite powerful for time-series data. One just needs to solve the scaling problem. That is what we do in <a href="https://github.com/timescale/timescaledb">TimescaleDB</a>.</p><p>When we <a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">announced TimescaleDB two weeks ago</a>, we received a lot of <a href="https://blog.timescale.com/thank-you-hn-20-000-views-1000-stars-and-6-insights-from-the-timescaledb-launch-d7e9c5cdb9d0">positive feedback</a> from the community. But we also heard from skeptics, who found it hard to believe that one should (or could) build a scalable time-series database on a relational database (in our case, PostgreSQL).</p><p>There are two separate ways to think about scaling: <strong>scaling up</strong> so that a single machine can store more data, and <strong>scaling out</strong> so that data can be stored across multiple machines.</p><p>Why are both important? The most common approach to scaling out across a cluster of <em>N</em> servers is to partition, or shard, a dataset into <em>N</em> partitions. If each server is limited in its throughput or performance (i.e., unable to scale up), then the overall cluster throughput is greatly reduced.</p><p>This post discusses <strong>scaling up</strong>. (A <strong>scaling-out</strong> post will be published on a later date.)</p><p>In particular, this post explains:</p><ul><li>Why relational databases do not normally scale up well</li><li>How LSM trees (typically used in NoSQL databases) do not adequately solve the needs of many time-series applications</li><li>How time-series data is unique, how one can leverage those differences to overcome the scaling problem, and some performance results</li></ul><p>Our motivations are twofold: <strong>for anyone facing similar problems</strong>, to share what we’ve learned; and <strong>for those considering using TimescaleDB for time-series data</strong> (including the skeptics!), to explain some of our design decisions.</p><h3>Why databases do not normally scale up well: Swapping in/out of memory is expensive</h3><p>A common problem with scaling database performance on a single machine is the significant cost/performance trade-off between memory and disk. While memory is faster than disk, it is much more expensive: about 20x costlier than solid-state storage like Flash, 100x more expensive than hard drives. Eventually, our entire dataset will not fit in memory, which is why we’ll need to write our data and indexes to disk.</p><p>This is an old, common problem for relational databases. Under most relational databases, a table is stored as a collection of fixed-size pages of data (e.g., 8KB pages in PostgreSQL), on top of which the system builds data structures (such as <a href="https://en.wikipedia.org/wiki/B-tree">B-trees</a>) to index the data. With an index, a query can quickly find a row with a specified ID (e.g., bank account number) without scanning the entire table or “walking” the table in some sorted order.</p><p>Now, if the working set of data and indexes is small, we can keep it in memory.</p><p>But if the data is sufficiently large that we can’t fit all (similarly fixed-size) pages of our B-tree in memory, then updating a random part of the tree can involve significant disk I/O as we read pages from disk into memory, modify in memory, and then write back out to disk (when evicted to make room for other B-tree pages). And a relational database like PostgreSQL keeps a B-tree (or other data structure) for <em>each</em> table index, in order for values in that index to be found efficiently. So, the problem compounds as you index more columns.</p><p>In fact, because the database only accesses the disk in page-sized boundaries, even seemingly small updates can cause these swaps to occur: To change one cell, the database may need to swap out an existing 8KB page and write it back to disk, then read in the new page before modifying it.</p><p>But why not use smaller- or variable-sized pages? There are two good reasons: minimizing disk fragmentation, and (in case of a spinning hard disk) minimizing the overhead of the “seek time” (usually 5–10ms) required in physically moving the disk head to a new location.</p><p>What about solid-state drives (SSDs)? While solutions like NAND Flash drives eliminate any physical “seek” time, they can only be read from or written to at the page-level granularity (today, typically 8KB). So, even to update a single byte, the SSD firmware needs to read an 8KB page from disk to its buffer cache, modify the page, then write the updated 8KB page back to a new disk block.</p><p>The cost of swapping in and out of memory can be seen in this performance graph from PostgreSQL, where insert throughput plunges with table size and increases in variance (depending on whether requests hit in memory or require (potentially multiple) fetches from disk).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*G2Mxb8_9EHHv8OLU." /></figure><p><em>Insert throughput as a function of table size for PostgreSQL 9.6.2, running with 10 workers on a Azure standard DS4 v2 (8 core) machine with SSD-based (premium LRS) storage. Clients insert individual rows into the database (each of which has 12 columns: a timestamp, an indexed randomly-chosen primary id, and 10 additional numerical metrics). The PostgreSQL rate starts over 15K inserts/second, but then begins to drop significantly after 50M rows and begins to experience very high variance (including periods of only 100s of inserts/sec).</em></p><h3>Enter NoSQL databases with Log-Structured Merge Trees (and new problems)</h3><p>About a decade ago, we started seeing a number of “NoSQL” storage systems address this problem via <a href="http://www.cs.umb.edu/~poneil/lsmtree.pdf">Log-structured merge (LSM) trees</a>, which reduce the cost of making small writes by only performing larger append-only writes to disk.</p><p>Rather than performing “in-place” writes (where a small change to an existing page requires reading/writing that entire page from/to disk), LSM trees queue up several new updates (including deletes!) into pages and write them as a single batch to disk. In particular, all writes in an LSM tree are performed to a sorted table maintained <em>in memory</em>, which is then flushed to disk as an immutable batch when of sufficient size (as a “sorted string table”, or SSTable). This reduces the cost of making small writes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/631/0*DU6_cnlS65OkX_2C." /><figcaption><strong>In an LSM tree, all updates are first written a sorted table in memory, and then flushed to disk as an immutable batch, stored as an SSTable, which is often indexed in memory.<br></strong>(Source: <a href="https://www.igvita.com/2012/02/06/sstable-and-log-structured-storage-leveldb/">https://www.igvita.com/2012/02/06/sstable-and-log-structured-storage-leveldb/</a>)</figcaption></figure><p>This architecture — which has been adopted by many “NoSQL” databases like LevelDB, Google BigTable, Cassandra, MongoDB (WiredTiger), and InfluxDB — may seem great at first. Yet it introduces other tradeoffs: higher memory requirements and poor secondary index support.</p><p><strong>Higher-memory requirements:</strong> Unlike in a B-tree, in an LSM tree there is no single ordering: no global index to give us a sorted order over all keys. Consequently, looking up a value for a key gets more complex: first, check the memory table for the latest version of the key; otherwise, look to (potentially many) on-disk tables to find the latest value associated with that key. To avoid excessive disk I/O (and if the values themselves are large, such as the webpage content stored in Google’s BigTable), indexes for all SSTables may be kept entirely in memory, which in turn increases memory requirements.</p><p><strong>Poor secondary index support:</strong> Given that they lack any global sorted order, LSM trees do not naturally support secondary indexes. Various systems have added some additional support, such as by duplicating the data in a different order. Or, they emulate support for richer predicates by building their primary key as the concatenation of multiple values. Yet this approach comes with the cost of requiring a larger scan among these keys at query time, thus supporting only items with a limited cardinality (e.g., discrete values, not numeric ones).</p><p>There is a better approach to this problem. Let’s start by better understanding time-series data.</p><p><a href="https://blog.dnsfilter.com/3-billion-time-series-data-points-dnsfilter-replaced-influxdb-with-timescaledb-d9f827702f8b">Towards 3B time-series data points per day: Why DNSFilter replaced InfluxDB with TimescaleDB</a></p><h3>Time-series data is different</h3><p>Let’s take a step back, and look at the original problem that relational databases were designed to solve. Starting from <a href="https://en.wikipedia.org/wiki/IBM_System_R">IBM’s seminal System R</a> in the mid-1970s, relational databases were employed for what became known as online transaction processing (OLTP).</p><p>Under OLTP, operations are often transactional updates to various rows in a database. For example, think of a bank transfer: a user debits money from one account and credits another. This corresponds to updates to two rows (or even just two cells) of a database table. Because bank transfers can occur between any two accounts, the two rows that are modified are somewhat randomly distributed over the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-uSRCxxqBqEAOVr_." /><figcaption><strong>Time-series data arises from many different settings: industrial machines; transportation and logistics; DevOps, datacenter, and server monitoring;, and financial applications.</strong></figcaption></figure><p>Now let’s consider a few examples of time-series workloads:</p><ul><li><strong>DevOps/server/container monitoring.</strong> The system typically collects metrics about different servers or containers: CPU usage, free/used memory, network tx/rx, disk IOPS, etc. Each set of metrics is associated with a timestamp, unique server name/ID, and a set of tags that describe an attribute of what is being collected.</li><li><strong>IoT sensor data.</strong> Each IoT device may report multiple sensor readings for each time period. As an example, for environmental and air quality monitoring this could include: temperature, humidity, barometric pressure, sound levels, measurements of nitrogen dioxide, carbon monoxide, particulate matter, etc. Each set of readings is associated with a timestamp and unique device ID, and may contain other metadata.</li><li><strong>Financial data. </strong>Financial tick data may include streams with a timestamp, the name of the security, and its current price and/or price change. Another type of financial data is payment transactions, which would include a unique account ID, timestamp, transaction amount, as well as any other metadata. (Note that this data is different than the OLTP example above: here we are recording every transaction, while the OLTP system was just reflecting the current state of the system.)</li><li><strong>Fleet/asset management.</strong> Data may include a vehicle/asset ID, timestamp, GPS coordinates at that timestamp, and any metadata.</li></ul><p>In all of these examples, the datasets are a stream of measurements that involve inserting “new data” into the database, typically to the latest time interval. While it’s possible for data to arrive much later than when it was generated/timestamped, either due to network/system delays or because of corrections to update existing data, this is typically the exception, not the norm.</p><p>In other words, these two workloads have very different characteristics:</p><h4><strong>OLTP Writes</strong></h4><ul><li>Primarily UPDATES</li><li>Randomly distributed (over the set of primary keys)</li><li>Often transactions across multiple primary keys</li></ul><h4><strong>Time-series Writes</strong></h4><ul><li>Primarily INSERTs</li><li>Primarily to a recent time interval</li><li>Primarily associated with both a timestamp and a separate primary key (e.g., server ID, device ID, security/account ID, vehicle/asset ID, etc.)</li></ul><p>Why does this matter? As we will see, one can take advantage of these characteristics to solve the scaling-up problem on a relational database.</p><h3>A new way: Adaptive time/space chunking</h3><p>When previous approaches tried to avoid small writes to disk, they were trying to address the broader OLTP problem of UPDATEs to random locations. But as we just established, time-series workloads are different: writes are primarily INSERTS (not UPDATES), to a recent time interval (not a random location). In other words, time-series workloads are <em>append only.</em></p><p>This is interesting: it means that, if data is sorted by time, we would always be writing towards the “end” of our dataset. Organizing data by time would also allow us to keep the actual working set of database pages rather small, and maintain them in memory. And reads, which we have spent less time discussing, could also benefit: if many read queries are to recent intervals (e.g., for real-time dashboarding), then this data would be already cached in memory.</p><p>At first glance, it may seem like indexing on time would give us efficient writes and reads for free. But once we want any other indexes (e.g., another primary key like server/device ID, or any secondary indexes), then this naive approach would revert us back to making random inserts into our B-tree for that index.</p><p>There is another way, which we call, “adaptive time/space chunking”. This is what we use in TimescaleDB.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dxNauajLW6KITjr8." /><figcaption><strong>TimescaleDB stores each chunk in an internal database table, so indexes only grow with the size of each chunk, not the entire hypertable. As inserts are largely to the more recent interval, that one remains in memory, avoiding expensive swaps to disk.</strong></figcaption></figure><p>Instead of just indexing by time, TimescaleDB builds distinct <em>tables </em>by splitting data according to two dimensions: the time interval <em>and</em> a primary key (e.g., server/device/asset ID). We refer to these as <em>chunks</em> to differentiate them from <em>partitions</em>, which are typically defined by splitting the primary key space. Because each of these chunks are stored as a database table itself, and the query planner is aware of the chunk’s ranges (in time and keyspace), the query planner can immediately tell to which chunk(s) an operation’s data belongs. (This applies both for inserting rows, as well as for pruning the set of chunks that need to be touched when executing queries.)</p><p>The key benefit of this approach is that now all of our indexes are built only across these much smaller chunks (tables), rather than a single table representing the entire dataset. So if we size these chunks properly, we <em>can </em>fit the latest tables (and their B-trees) completely in memory, and avoid this swap-to-disk problem, while maintaining support for multiple indexes.</p><h3>Approaches to implementing chunking</h3><p>The two intuitive approaches to design this time/space chunking each have significant limitations:</p><h4><strong><em>Approach #1: Fixed-duration intervals</em></strong></h4><p>Under this approach, all chunks can have fixed, identical time intervals, e.g., 1 day. This works well if the volume of data collected per interval does not change. However, as services become popular, their infrastructure correspondingly expands, leading to more servers and more monitoring data. Similarly, successful IoT products will deploy ever more numbers of devices. And once we start writing too much data to each chunk, we’re regularly swapping to disk (and will find ourselves back at square one). On the flip side, choosing too-small intervals to start with leads to other performance downsides, e.g., having to touch many tables at query time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*zZymZ1IFG9mbmdxJdmF2PA.png" /><figcaption><strong><em>Each chunk has a fixed duration in time. Yet if the data volume per time increases, then eventually chunk size becomes too large to fit in memory.</em></strong></figcaption></figure><h4><strong><em>Approach #2: Fixed-sized chunks</em></strong></h4><p>With this approach, all chunks have fixed target sizes, e.g., 1GB. A chunk is written to until it reaches its maximum size, at which point it becomes “closed” and its time interval constraints become fixed. Later data falling within the chunk’s “closed” interval will still be written to the chunk, however, in order to preserve the correctness of the chunk’s time constraints.</p><p>A key challenge is that the time interval of the chunk depends on the order of data. Consider if data (even a single datapoint) arrives “early” by hours or even days, potentially due to a non-synchronized clock, or because of varying delays in systems with intermittent connectivity. This early datapoint will stretch out the time interval of the “open” chunk, while subsequent on-time data can drive the chunk over its target size. The insert logic for this approach is also more complex and expensive, driving down throughput for large batch writes (such as large COPY operations), as the database needs to make sure it inserts data in temporal order to determine when a new chunk should be created (even in the <em>middle</em> of an operation). Other problems exist for fixed- or max-size chunks as well, including time intervals that may not align well with data retention policies (“delete data after 30 days”).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/706/1*8OfTBL-tpSCvEtQlBYzkhw.png" /><figcaption><strong><em>Each chunk’s time interval is fixed only once its maximum size has been reached. Yet if data arrives early, this creates a large interval for the chunk, and the chunk eventually becomes too large to fit in memory.</em></strong></figcaption></figure><p>TimescaleDB takes a third approach that couples the strengths of both approaches.</p><h4><strong><em>Approach #3: Adaptive intervals (our current design)</em></strong></h4><p>Chunks are created with a fixed interval, but the interval adapts from chunk-to-chunk based on changes in data volumes in order to hit maximum target sizes.</p><p>By avoiding open-ended intervals, this approach ensures that data arriving early doesn’t create too-long time intervals that will subsequently lead to over-large chunks. Further, like static intervals, it more naturally supports retention policies specified on time, e.g., “delete data after 30 days”. Given TimescaleDB’s time-based chunking, such policies are implemented by simply dropping chunks (tables) in the database. This means that individual <em>files </em>in the underlying file system can simply be deleted, rather than needing to delete individual <em>rows,</em> which requires erasing/invalidating portions of the underlying file. Such an approach therefore avoids fragmentation in the underlying database files, which in turn avoids the need for <a href="https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html">vacuuming</a>. And this vacuuming can be prohibitively expensive in very large tables.</p><p>Still, this approach ensures that chunks are sized appropriately so that the latest ones can be maintained in memory, even as data volumes may change.</p><p>Partitioning by primary key then takes each time interval and further splits it into a number of smaller chunks, which all share the same time interval but are disjoint in terms of their primary keyspace. This enables better parallelization both on servers with multiple disks — for both inserts and queries — — as well as multiple servers. More on these issues in a later post.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*EZGWtjLpM_-gktBnzLRw4g.png" /><figcaption><strong>If the data volume per time increases, then chunk interval decreases to maintain right-sized chunks.<br>If data arrives early, then data is stored into a “future” chunk to maintain right-sized chunks.</strong></figcaption></figure><h3>Result: 15x improvement in insert rate</h3><p>Keeping chunks at the right size is how we achieve our INSERT results that surpass vanilla PostgreSQL, that Ajay already showed in his <a href="https://blog.timescale.com/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2">earlier post</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4rqKQvY9PpVkFpzq." /><figcaption><strong>Insert throughput of TimescaleDB vs. PostgreSQL, using the same workload as described earlier. Unlike vanilla PostgreSQL, TimescaleDB maintains a constant insert rate (of about 14.4K inserts/second, or 144K metrics/second, with very low variance), independent of dataset size.</strong></figcaption></figure><p>This consistent insert throughput also persists when writing large batches of rows in single operations to TimescaleDB (instead of row-by-row). Such batched inserts are common practice for databases employed in more high-scale production environments, e.g., when ingesting data from a distributed queue like Kafka. <strong>In such scenarios, a single Timescale server can ingest 130K rows (or 1.3M metrics) per second, approximately 15x that of vanilla PostgreSQL once the table has reached a couple 100M rows.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JXwRxrXy_iCE5rkv." /><figcaption><strong>Insert throughput of TimescaleDB vs. PostgreSQL when performing INSERTs of 10,000-row batches.</strong></figcaption></figure><h3>Summary</h3><p>A relational database can be quite powerful for time-series data. Yet, the costs of swapping in/out of memory significantly impacts their performance. But NoSQL approaches that implement Log Structured Merge Trees have only shifted the problem, introducing higher memory requirements and poor secondary index support.</p><p>By recognizing that time-series data is different, we are able to organize data in a new way: adaptive time/space chunking. This minimizes swapping to disk by keeping the working data set small enough to fit inside memory, while allowing us to maintain robust primary and secondary index support (and the full feature set of PostgreSQL). And as a result, we are able to <strong>scale up</strong> PostgreSQL significantly, resulting in a 15x improvement on insert rates.</p><p>But what about performance comparisons to NoSQL databases? That post is coming soon.</p><p>In the meantime, you can download the latest version of TimescaleDB, released under the permissive Apache 2 license, on <a href="https://github.com/timescale/timescaledb">GitHub</a>.</p><p><em>Like this post? Interested in learning more?</em></p><p><em>Follow us </em><a href="https://blog.timescale.com/"><em>here</em></a><em> on Medium, check out our </em><a href="https://github.com/timescale/timescaledb"><strong><em>GitHub</em></strong></a><em>, join our </em><a href="http://slack.timescale.com/"><strong><em>Slack communit</em></strong><em>y</em></a><em>, and sign up for the community mailing list below. We’re also </em><a href="https://www.timescale.com/careers"><em>hiring</em></a><em>!</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fc9b4ae%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Fc9b4ae%2F&amp;image=https%3A%2F%2Fucarecdn.com%2F8ef44c8d-91be-4dc5-b7ef-b14e3061ee8a%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/6388f775e233880849ddc48df80ffccc/href">https://medium.com/media/6388f775e233880849ddc48df80ffccc/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d0cd6975e87c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c">Time-series data: Why (and how) to use a relational database instead of NoSQL</a> was originally published in <a href="https://medium.com/timescale">Timescale</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>