3 ms·

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

scrollaway 8y ago
The TimescaleDB plugin is extremely promising: https://www.timescale.com/ https://www.timescale.com/ Natively without a plugin, it's not possible though.
gaius 8y ago
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 https://news.ycombinator.com/item?id=16539317
StreamBright 8y ago
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?
- threeseed 8y ago
  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.
  - mslot 8y ago
    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.
    - threeseed 8y ago
      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.
      - xdanger 8y ago
        Actually you can use PostgreSQL in those with timescaledb plugin. ;)
manigandham 8y ago
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/ https://www.timescale.com/ Natively without a plugin, it's not possible though.

gaius 8y ago

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 https://news.ycombinator.com/item?id=16539317

StreamBright 8y ago

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?

threeseed 8y ago
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.
- mslot 8y ago
  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.
  - threeseed 8y ago
    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.
    - xdanger 8y ago
      Actually you can use PostgreSQL in those with timescaledb plugin. ;)

manigandham 8y ago

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.