4 ms·

> Could you do it all in MySQL or Postgres? Possibly, but you'd have to write a lot of code to add the functionality many of these databases already provide. O

> Could you do it all in MySQL or Postgres? Possibly, but you'd have to write a lot of code to add the functionality many of these databases already provide. Or you can throw in something like [0], I guess. (This thing is still in my todo list though, so I can't tell anything beside the fact that this thing also exists.) [0] https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb

manigandham 9y ago
Any distributed relational database, especially with compressed columnstores, will be better than any existing timeseries specific database. Timescale, Citus, PipelineDB = postgres based but no columnstores. MemSQL, MariaDB, ClickHouse = with columnstores.
- saosebastiao 9y ago
  Time series databases have many many uses, not all of which require or even benefit from column stores. Some uses may even be hurt by column stores.
  - manigandham 9y ago
    Like what? All those use cases are easily covered and better done by scalable relational systems. Time series is just a narrow subset application.
    - saosebastiao 9y ago
      Time series is not even close to a narrow subset. There are tons of relevant use cases for time series databases that conflict with the cost/benefit positioning of column storage. What is someone to do if they need moving window queries (thereby requiring a ts db), but also have to deal with heavy mvcc transactions, lots of updates, weakly ordered inserts? These aren’t uncommon conditions to deal with, even in time series data. Examples: * Event sourcing collects events but also often has a notion of a “current” record, meaning inserting a new event requires updating a previous event to invalidate it. * Financial transactions may involve many append-only tables, but which are linked with data in heavily updated tables, often requiring transactional mvcc. * Logging events asynchronously or across several nodes often produces events that are emitted out of order which can sometimes span several time partitions, making insertion costly for column stores.
      - manigandham 9y ago
        None of that is difficult nor is it related to time-series data specifically, they are just modern data warehouse features. SQL already has window/analytical functions. Relational databases with columnstores also have their traditional rowstores which support all the OLTP features you need, along with easy joins across both table types. Many also now pair a rowstore or in-memory segment with each columnstore for background merges to handle rapid ingest and easy updates. If you want a polished system, use MemSQL or SQL Server Columnstore Indexes, or more manual work with clickhouse and others. We have 28 billion rows of classic "time series" monitoring data in memsql compressed to less than 50gb and complex aggregations return in milliseconds.
        Licenser 9y ago
        28 billion data points in 50 gigabytes are not impressive for time series use. That's nearly 2 bytes per data point, many time series databases achieve 1 or less byte per data point.
        manigandham 9y ago
        It's not a point, it's an entire row of data with dozens of columns and JSON. Even if the data took twice as much space, it's still worth it to have everything in a single data warehouse with easy joins and the full expressiveness of SQL.

manigandham 9y ago

Any distributed relational database, especially with compressed columnstores, will be better than any existing timeseries specific database. Timescale, Citus, PipelineDB = postgres based but no columnstores. MemSQL, MariaDB, ClickHouse = with columnstores.

saosebastiao 9y ago
Time series databases have many many uses, not all of which require or even benefit from column stores. Some uses may even be hurt by column stores.
- manigandham 9y ago
  Like what? All those use cases are easily covered and better done by scalable relational systems. Time series is just a narrow subset application.
  - saosebastiao 9y ago
    Time series is not even close to a narrow subset. There are tons of relevant use cases for time series databases that conflict with the cost/benefit positioning of column storage. What is someone to do if they need moving window queries (thereby requiring a ts db), but also have to deal with heavy mvcc transactions, lots of updates, weakly ordered inserts? These aren’t uncommon conditions to deal with, even in time series data. Examples: * Event sourcing collects events but also often has a notion of a “current” record, meaning inserting a new event requires updating a previous event to invalidate it. * Financial transactions may involve many append-only tables, but which are linked with data in heavily updated tables, often requiring transactional mvcc. * Logging events asynchronously or across several nodes often produces events that are emitted out of order which can sometimes span several time partitions, making insertion costly for column stores.
    - manigandham 9y ago
      None of that is difficult nor is it related to time-series data specifically, they are just modern data warehouse features. SQL already has window/analytical functions. Relational databases with columnstores also have their traditional rowstores which support all the OLTP features you need, along with easy joins across both table types. Many also now pair a rowstore or in-memory segment with each columnstore for background merges to handle rapid ingest and easy updates. If you want a polished system, use MemSQL or SQL Server Columnstore Indexes, or more manual work with clickhouse and others. We have 28 billion rows of classic "time series" monitoring data in memsql compressed to less than 50gb and complex aggregations return in milliseconds.
      - Licenser 9y ago
        28 billion data points in 50 gigabytes are not impressive for time series use. That's nearly 2 bytes per data point, many time series databases achieve 1 or less byte per data point.
        manigandham 9y ago
        It's not a point, it's an entire row of data with dozens of columns and JSON. Even if the data took twice as much space, it's still worth it to have everything in a single data warehouse with easy joins and the full expressiveness of SQL.