4 ms·

That's reminded me of one problem I had with it back in 2010 - it didn't have indexes on expressions[1]. We had a few hundred million rows with unindexed times

That's reminded me of one problem I had with it back in 2010 - it didn't have indexes on expressions[1]. We had a few hundred million rows with unindexed timestamps (I forget if they were MySQL-native or just ISO8601 strings) and the higher ups wanted a daily aggregate report of some stuff which was, obviously, slow to generate because of having to truncate all the timestamps to just YMD[2]. For Postgres, I would have just created an index on (the equivalent of) `truncate(YMD, timestamp)` (and I demonstrated this as a POC to the higher ups as a good reason to switch to Postgres. But alas, no traction.) [1] Seems to have arrived some time around 2013-14? [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived.

chrsig 4y ago
mysql has a datetime type that can be an index, and should be able use for a range scan. that is, with the appropriate data type, it sounds like you would've had a better time... > [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived. The real benefit of postgres*: offline transactional schema changes. mysql would've done a table copy in order to change it. if mysql supported offline schema changes, perhaps you'd have been able to change the data type, solving the root cause of the problem. * I haven't had the pleasure of using postgres in production, so I can't speak to how effective any given feature is -- only what the marketing appeal is.