Building a scalable time-series database on PostgreSQL

While I appreciate PostgreSQL every day, am I the only one who thinks this is a rather bad idea? The row based engine of PG is the antithesis of efficient storage and retrieval of timeseries of similar patterns, yielding almost no compression. Columnar storage should naturally be much better at that (BigQuery, Redshift, Citus), culminating in purpose built stores like Influx, Prometheus or KDB. Prometheus for example manages to compress an average 64-bit FP sample including metadata to just 1.3-3.3 bytes depending on engine. As most DB stuff is I/O bound, that usually translates into at least an order of magnitude faster lookups.

Congrats on the release!<p>I&#x27;m in the process of migrating from influxDB to a combination of Kafka and TimeScaleDB.<p>Has anyone beaten me to the lunch with testing if it works with [PipelineDB](<a href="https:&#x2F;&#x2F;www.pipelinedb.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.pipelinedb.com&#x2F;</a>)? I can see that being a powerful combination, perhaps also tied to their forthcoming [Stride](<a href="https:&#x2F;&#x2F;www.stride.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.stride.io&#x2F;</a>) analytics API.

Didn&#x27;t want to make the title too long, but I should note this is an open source database. Here if anyone has any questions.<p>Also, github if you just want to see the code: <a href="https:&#x2F;&#x2F;github.com&#x2F;timescale&#x2F;timescaledb&#x2F;" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;timescale&#x2F;timescaledb&#x2F;</a>

What do you plan on doing for benchmarking? I don&#x27;t expect you to get something like STAC done, but will you try to find general benchmarks that others use?<p>I&#x27;ve been working with TS databases for a long time now, and it never fails that every database vendor always has benchmarks showing they are the best (no put that more bluntly, when you come out with your own benchmark suite and you are the fastest&#x2F;smallest&#x2F;bestest I won&#x27;t be surprised or believe you).<p>I don&#x27;t expect you to be the fastest when having a row-oriented architecture, and it would be an unfair comparison against the non-free databases, but I would like realistic numbers.<p>Actually, if you came out 2nd to 3rd against competitors&#x27; workloads, I would be far more impresses.<p>(But really? A row-oriented TS database?)

At my last company we did tens of billions of sensor events per day into Cassandra and I thank god our engineering team was smart enough to spend manpower on product instead of writing yet another database

Wow, lots of very critical comments. I for one think this is a very good idea. I have a use case right now that pretty much fits the manifesto of timescale. Having the power of SQL is very atteactive. I&#x27;m looking to move my current setup to timescale. Will let you guys know how it goes.

from:<a href="http:&#x2F;&#x2F;www.timescale.com&#x2F;index.html" rel="nofollow">http:&#x2F;&#x2F;www.timescale.com&#x2F;index.html</a><p>Full SQL interface<p>Scale-out distributed architecture<p>Auto partitions across space and time<p>Single-node and cluster optimizations<p>Complex query predicates<p>Distributed aggregations<p>Native JOINs with relational data<p>Labels and metrics support<p>Geo-spatial query support<p>Fine-grained access control<p>Reliable (active replication, backups)<p>Automated data retention policies

If your main use case for timeseries is a graphite&#x2F;statsd replacement, another PostgesSQL-based project worth looking at is tgres.<p><a href="https:&#x2F;&#x2F;grisha.org&#x2F;blog&#x2F;2017&#x2F;03&#x2F;22&#x2F;tgres-0-dot-10-dot-0b-time-series-with-go-and-postgresql&#x2F;" rel="nofollow">https:&#x2F;&#x2F;grisha.org&#x2F;blog&#x2F;2017&#x2F;03&#x2F;22&#x2F;tgres-0-dot-10-dot-0b-tim...</a><p><a href="https:&#x2F;&#x2F;github.com&#x2F;tgres&#x2F;tgres" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;tgres&#x2F;tgres</a>

This looks nice. I&#x27;ve had to roll my own PostgreSQL schema and custom functions a few times for timeseries data and if this prevents the need for that I&#x27;m impressed.<p>The author mentioned write performance, but didn&#x27;t touch much on read performance. Are there any benchmarks you can share in that direction?<p>Also the author didn&#x27;t touch much on the space requirements or the node splitting expectations.<p>Those two areas would be useful to explain in greater detail.

Most distributed SQL databases fall into 2 uses: large data-warehouse and scalable primary data store.<p>For DW needs (including time series), MemSQL is still the best option for distributed SQL unless you absolutely need postgres features. Proprietary w&#x2F;mysql flavor but integrated columnstore + memory rowstore built for performance and fantastic ops management software makes it a great system. Also has interesting kafka and s3 ingest pipelines.<p>For an operational db, Citus is a great scale-out option for postgres. You can combine it with cstore to get columnar compression for DW use but at that point it&#x27;s better to just use a more focused product. There&#x27;s also CockroachDB which looks good (if it ever gets out of beta). I&#x27;d also recommend ScyllaDB + JanusGraph once both mature to get a scalable multi-master graph database if HA&#x2F;reliability&#x2F;replication are a big concern.<p>Timescale DB is interesting and it&#x27;s always nice to see more options, but right now there are better data warehouse options already that work just fine for time series + SQL. A built-in columnstore would go a long way to making it competitive though.

Whats the main difference compared to pg_partman? That provides parent and child tables, automated partitioning and the user only queries the parent tables.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;keithf4&#x2F;pg_partman" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;keithf4&#x2F;pg_partman</a>

Has anyone used gnocchi outside of OpenStack?<p>It can use postgresql as an index store.<p><a href="https:&#x2F;&#x2F;docs.openstack.org&#x2F;developer&#x2F;gnocchi&#x2F;install.html" rel="nofollow">https:&#x2F;&#x2F;docs.openstack.org&#x2F;developer&#x2F;gnocchi&#x2F;install.html</a>

Congrats on the launch!<p>I was investigating the same topic (PG based timeseries database) for a stock tick data project, would definitely give timescaledb a try.<p>Since financial data is mentioned in the blog, would be curious on how it performed &#x2F; scaled in practical.

So, having played&#x2F;worked with a few time-series databases, this just doesn&#x27;t seem to fit the picture we&#x27;re used to. For applications having to store a set of well-defined time-series in a more optimal way, it looks great. As a generic time-series database on the other hand, this sounds like a maintenance nightmare.<p>I quite like Postgresql (and deploy it all the time), and I&#x27;m no fan of nosql stuff, which just means you don&#x27;t have to properly analyze your database structure before-hand, but with time-series it&#x27;s different matter. The data you tend to send to generic time-series databases tends to be very unpredictable. I currently don&#x27;t care what data is sent to Prometheus or Influx. This includes, but is not limited to ZFS stats of our storage, system load, network traffic, VMWare, nginx&#x2F;haproxy, application usage and errors, ... I know that when I&#x27;ll need it, I&#x27;ll have it available and can try to correlate the data at any point in the future. In TimescaleDB it looks like I would have to pre-create tables fitting all those stats, which would make it an absolute pain in the ass.<p>As counter-intuitive as it sounds, to do it properly&#x2F;flexible&#x2F;usable in a real world, you should impose a fixed&#x2F;limited database structure. The tgres project mentioned elsewhere in this thread seems a lot more useful for &#x27;generic&#x27; cases. Maybe they can be combined in some way.<p>Also, some other important things in the time-series world are a REST api. Yes you might want advanced SQL to query data, but no you don&#x27;t want that to insert it. Pretty much any application can do http(s) stuff without any additional libraries (except when you&#x27;re talking about C&#x2F;C++ things). Postgres on the other hand? Oh no I need a JDBC driver for this Java app, I need an extra Python library for X, ... Not to mention you suddenly have to manage database pools in the applications and have something that is a potential bottleneck.<p>Then, no Grafana support is a no-go. It is the de-facto standard visualization tool these days.<p>What about retention policies, you don&#x27;t need ms-accurate data-points from 2 years ago, when talking about many data-points, you might want to aggregate this. Though since the data set in TimescaleDB will be limited due to specific data-sets, this could be less of a problem.

How does this compare to say, Aerospike, or Honeycomb.io?

Hi from Timescale, here are 6 insights we took away from this awesome discussion. Thank you!<p><a href="https:&#x2F;&#x2F;blog.timescale.com&#x2F;thank-you-hn-20-000-views-1000-stars-and-6-insights-from-the-timescaledb-launch-d7e9c5cdb9d0" rel="nofollow">https:&#x2F;&#x2F;blog.timescale.com&#x2F;thank-you-hn-20-000-views-1000-st...</a>

This looks promising! Somewhat unrelated question, but has anyone had success using AWS DynamoDB as a time-series database? I&#x27;m generally curious on the case for a &quot;managed&quot; TSDB versus setting up your own open source one.

Very cool. The partitioning reminds me of the way VoltDB does it. By splitting it across a set of keys it&#x27;s able to ensure that queries can be run concurrently on their own node.<p>Are you guys familiar with it and I am understanding this properly?

How does that differ from PostgreSQL&#x27;s stock table inheritance (partition data on arbitrary rules, while letting you efficiently query the parent table using constraints)? The TimescaleDB approach apparently looks very similar.

<i>Very</i> interesting.<p>Can you speak as to the stability of your system for production usage?

I couldn&#x27;t find the architectural diagram on how do you scale this across N nodes, can you elaborate on that? Is it similar to Postgres XL or Citus?

<i>A single-node version is currently available for download. A clustered version is in the works.</i><p>Adding clustering on sounds like the hardest part.

Are the hypertables effectively views with insert triggers on them?<p>Does Timescale have any baked in support bitemporal constructs?<p>Are temporal foreign keys supported?

Joins against time series data sound really nice.

How does this compare to citusdb?

would be interesting to get that integrated with Prometheus remote storage.

Congrats on the launch!

How many dimensions does each metric support?

Wow, cool!<p>How does this compare to Citus?

Loved this article, as a competing database company, they did a fantastic job relating to developers and being authentic! Great job, please keep this up, it will definitely make you a winner.<p>Key quote of interest: &quot;135K writes per second per node&quot; this is very good! Quite impressed.

I&#x27;m a novice regarding DBs. What&#x27;s the significance of this?

I really hate this stile of writing. Why does it have to sound like every other hipster it text?<p>- I&#x27;m not your padawan<p>- Postgresql is cool, why do you say it is boring?!<p>- yes awesome some PhD People are vouching with there titles to a peace of software i might wanna use in a production environment? Nope. Tx for the efford and i will watch it but no.<p>I do like that you do that work and tx for this but why not wrting a post which goes into details instead of this hipster pseudo tl;dr text?