Hacker News Reader

Comment by Avamander | Hacker News Reader

Could you please elaborate how to use Postgres as a time series database (like InfluxDB)?

There is absolutely nothing special about "time-series" to be an actual type of database. It's all hype.

Time-series data is data that has a time component which is usually the primary property to query by. Almost every database can handle this, like MongoDB/Redis for lightweight use, Cassandra for write-heavy/global replication, ElasticSearch for raw search-style quering, or an OLAP columnstore (Redshift, MSSQL, Snowflake, Clickhouse) for serious ingest and querying. Monitoring systems like Prometheus and Netdata even have time-series storage built-in because it just isn't that hard.

InfluxDB is only really useful in the context of the integrations that it provides with the common monitoring software stacks. You can easily just create a regular relational table with "time" as a column and get great performance with an index, and then use partitioning to break up the table to get great performance over lots of data.

pg_partman is an extension that makes partitioning automatic. Timescale is an extension that makes time-focused partitioning automatic. Citus is an extension that makes partitioning across multiple nodes automatic. Or use one of the distributed OLAP systems mentioned above.

The TimescaleDB plugin is extremely promising: https://www.timescale.com/

Natively without a plugin, it's not possible though.

Create a table and have a timestamp for your data? Not sure what you are asking.

On hand we have: 15+ years old mature product that is widely supported and it can be used as a time series database

Other hand: n+1 young database engine with all of the problems that any young data storage engine has (only exception is FoundationDB because those guys actually understood the challenges of writing a reliable data store)

The question is, what can InfluxDB offer over Postgres for storing time series data?

Well InfluxDB actually is a time series database. That's all it does.

The query language is optimised for time based queries. The storage is optimised for time series. It is part of the metrics/monitoring ecosystem so it has integrations with tools like Grafana.

PostgreSQL doesn't have any time series capabilities OOTB. It's just people modelling the concept in a relational fashion. No different to how Excel can be used as a time series database.

And seriously everyone should just get over FoundationDB. There are plenty of people who have invented new databases that got it right e.g. Cassandra, HBase, Redis etc.

PostgreSQL has excellent time series capabilities. It can load millions of rows per second, efficiently scan by time range, build rollup tables, has expressive SQL with excellent support for time (timezones, ranges, timestamps, intervals, conversion, etc.), it can combine multiple indexes to query large volumes of time series data quickly, it can do sampling, and it can expire data efficiently through partitioning. Ok, it's not entirely ootb. To make it work nicely you may need extensions like pg_partman to automate partitioning and Citus to scale out, but once you do you have a time series database that's faster and more powerful than anything else on the market.

Congratulations. You just described the capabilities of ANY SQL database.

But again there are capabilities that exist only in InfluxDB because all it does is time series data. It's not multi-purpose. Which again is why you see it all over the place in the metrics/monitoring ecosystem but you never see PostgreSQL, MySQL etc.

Actually you can use PostgreSQL in those with timescaledb plugin. ;)

Could you please elaborate how to use Postgres as a time series database (like InfluxDB)?

There's some good discussion of it here https://news.ycombinator.com/item?id=16539317

Reply on news.ycombinator.com