3 ms·

Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. InfluxDB is a good example. But y

Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. InfluxDB is a good example. But you're also making it sound like MySQL is easier to deal with than Postgres and I find that to be completely untrue. Postgres has far better documentation, sane defaults and only one engine whereas the mysql documentation is all over the place and "googlable information" conflicts depending on your engine or hell, even whether you're using MySQL or MariaDB. In fact if you don't want to have to care about administration, I'll go as far as recommending Amazon RDS Postgres.

siddboots 10y ago
I disagree about TS databases. It's only if you are dealing with huge amounts of monitoring data that you need a purpose specific store. The rest of the time, you might as well reap the benefits of the relational model.
- beagle3 10y ago
  When I was dealing with time series, the most common query was an 'as of' join. To the best of my knowledge, no common relational database supports this efficiently (which isn't surprising, as it is somewhat incompatible with the relational model in general)
  - siddboots 10y ago
    That's a good counterpoint. For most purposes a `last ... over partition by ...` query will do the job, but you are right that it will be not nearly as efficient as the native operation in a timeseries database. With that in mind, I don't come to a different conclusion though. Just about every database I've worked with has had some timeseries data in it, but separating data out into multiple systems/models is usually something I would avoid until there is no other option.
thom 10y ago
I think there are perhaps 'soft' and 'hard' requirements for time series data. I do a lot of analytics based on (mostly offline) data that is sequential, and I benefit greatly from clustered indexes and window functions for many ad-hoc queries. If you're talking inserting millions of readings per minute, then yes, more targeted solutions exist. I think moving to esoteric databases before exhausting what's possible with RDBMSs is generally suboptimal, but other people are smarter than me.
- stymaar 10y ago
  In my experience, the main benefit of influxdb are its built-in functions (percentiles, integration/derivation, etc.) but its scaling ability is over-emphazised : in my last company my coworkers have had issues exceeding 30k insertions per minutes, way less than «millions per minute».
  - profquail 10y ago
    How long ago were you at your last company? I wonder which version of InfluxDB your coworkers were using? There've been some significant performance improvements in v1.0.0 and beyond, compared to the earlier beta releases.
    - stymaar 10y ago
      It was in 2015 and early 2016, before version 1.0. You're right that's an important piece of context I should have added.
gaius 10y ago
Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. You're mixing your metaphors there. Vertica is a relational DB which deals with time series just fine...
ciconia 10y ago
> Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. InfluxDB is a good example. Better how? FWIW I'm currently running a 1TB time series DB on Postgres. It runs butter smooth, provides great performance (I'm using table inheritance), great querying tools, plus admin load is minimal.
manigandham 10y ago
Relational databases with columnstores (especially some of the distributed versions) are great at timeseries and often better than the actual TS databases by having full SQL query power and joins.

siddboots 10y ago

I disagree about TS databases. It's only if you are dealing with huge amounts of monitoring data that you need a purpose specific store. The rest of the time, you might as well reap the benefits of the relational model.

beagle3 10y ago
When I was dealing with time series, the most common query was an 'as of' join. To the best of my knowledge, no common relational database supports this efficiently (which isn't surprising, as it is somewhat incompatible with the relational model in general)
- siddboots 10y ago
  That's a good counterpoint. For most purposes a `last ... over partition by ...` query will do the job, but you are right that it will be not nearly as efficient as the native operation in a timeseries database. With that in mind, I don't come to a different conclusion though. Just about every database I've worked with has had some timeseries data in it, but separating data out into multiple systems/models is usually something I would avoid until there is no other option.

thom 10y ago

I think there are perhaps 'soft' and 'hard' requirements for time series data. I do a lot of analytics based on (mostly offline) data that is sequential, and I benefit greatly from clustered indexes and window functions for many ad-hoc queries. If you're talking inserting millions of readings per minute, then yes, more targeted solutions exist. I think moving to esoteric databases before exhausting what's possible with RDBMSs is generally suboptimal, but other people are smarter than me.

stymaar 10y ago
In my experience, the main benefit of influxdb are its built-in functions (percentiles, integration/derivation, etc.) but its scaling ability is over-emphazised : in my last company my coworkers have had issues exceeding 30k insertions per minutes, way less than «millions per minute».
- profquail 10y ago
  How long ago were you at your last company? I wonder which version of InfluxDB your coworkers were using? There've been some significant performance improvements in v1.0.0 and beyond, compared to the earlier beta releases.
  - stymaar 10y ago
    It was in 2015 and early 2016, before version 1.0. You're right that's an important piece of context I should have added.

gaius 10y ago

Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. You're mixing your metaphors there. Vertica is a relational DB which deals with time series just fine...

ciconia 10y ago

> Don't use a relational DB if you deal with time series data. There's time series databases which are far, far better at that. InfluxDB is a good example. Better how? FWIW I'm currently running a 1TB time series DB on Postgres. It runs butter smooth, provides great performance (I'm using table inheritance), great querying tools, plus admin load is minimal.

manigandham 10y ago

Relational databases with columnstores (especially some of the distributed versions) are great at timeseries and often better than the actual TS databases by having full SQL query power and joins.