3 ms·

If you are just looking for something to point an ORM at, you are probably fine with MySQL, you may see performance gains, and administration is probably a litt

by thom 10y ago

If you are just looking for something to point an ORM at, you are probably fine with MySQL, you may see performance gains, and administration is probably a little more straightforward (and certainly more googlable). If you spend actual time in the database using SQL, Postgres offers vastly more functionality, e.g. window functions, broader and more mature GIS support, more data modelling options. Postgres also certainly _can_ outperform MySQL in a variety of situations - it supports different indexing options (i.e. last I checked MySQL didn't have partial indexes), and is moving to more parallelism in its query plans. MySQL's partitioning support is a little more natural than Postgres's but that's changing in 10.0. So, I would say, if you just want a back end for an app and have never _really_ cared about your RDMBS, use MySQL or whatever you're most familiar with. If you have complex query requirements, time series data, analytics workloads etc, try Postgres.

scrollaway 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. 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.

by thom 10y ago

scrollaway 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. 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.