[meritt]

> EXAMPLE: I was once handed a MySQL database of IoT signals where timestamps were in seconds since the epoch and asked to report on this data without changing the database

I'm not following why you moved the data into postgres other than to say you did? Are you suggesting that because you were restricted from making schema changes to the MySQL instance that that's a reason why postgres is superior?

[cosmie]

The reason for the migration was to be able to leverage the Functional/Expression Index[1] capability of Postgres. Any type of aggregated time series reporting queries were likely painful from a performance perspective, since an index on the epoch timestamp would have limited usefulness from an optimization standpoint.

So instead, he leveraged the expression index functionality of Postgres to pre-materialize an index against the converted timestamp. He didn't touch the table structure itself so it's transparent, but gets the performance benefits of that index already existing.

MySQL doesn't support function based indexes directly, although you can achieve a similar result in newer versions of MySQL with an intermediate step. You can create a Generated Column[2] first, and then build an index against that. If you specify it as a virtual generated column, then it's essentially the same as the above process where the column isn't physically stored, but you can index it. That said, asah may still not have been able to do that if the version of MySQL was too old or even that level of schema change was not allowed.

[1] https://www.postgresql.org/docs/current/static/indexes-expre...

[2] https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...

[fermuch]

I read it as he had no way to alter mysql, and he needed to work with time functions, which is tricky without a real timestamp instead of an integer, and is impossible without correct indexes.

To me it seems like, in that case, he chose postgres since he could sync the data and use a custom schema over it, with proper types and indexes.