3 ms·

Having used MySQL for every side-project and website I've ever worked on, last month I finally switched to Postgres. The main motivation was that MySQL doesn't

Having used MySQL for every side-project and website I've ever worked on, last month I finally switched to Postgres. The main motivation was that MySQL doesn't support a native UUID datatype (!!!) but, having switched, it seems that Postgres is much better thought-out and designed. For example, it supports a wider range of types and I find it less confusing to choose the right type compare to MySQL. It appears to support unicode better. Its system of privileges and roles seems more logical. Lots of small things like this add-up. I'd definitely recommend checking it out.

collyw 12y ago
For one problem I had recently, I found MySQL a better fit. It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are probably rarely used, but I wanted them for what I was trying to do (compare large combinations of DNA sequence). I tried implementing it in Python / Numpy and Java and PG but MySQL was still the fastest. (I am fairly sure if I spent more time, I could improve any of the solutions, but for a similar level of effort MySQL (plus Python) won.
- masklinn 12y ago
  Postgres does have a bit/varbit which may have been closer to your use case, though you're correct that there's no native bitcount (assuming by "bitcount" you mean "count the number of bits set to 1 in the field). Also even with CHECK constraints being available, the question of unsigned ints seems frequent enough that there's at least one extension implementing unsigned types: https://github.com/petere/pguint https://github.com/petere/pguint (no idea about the quality or production-readiness).
  - collyw 12y ago
    To be honest I could have probably used signed integers but conceptually representing DNA sequence (4 possibilities at each position) was far easier to think about that two's compliment. The idea was to make everything as small and lean as possible, to get the maximum amount into memory, and it seemed to work. Bitcount / popcount / hamming distance as a CPU operation will have saved a lot of CPU cycles over the PG method I tried. You sound like you know Postgres fairly well. How feasible would it be to write a CPU based popcount extension?
  - deleted 12y ago
    [deleted]

collyw 12y ago

For one problem I had recently, I found MySQL a better fit. It supports unsigned ints which Postgres doesn't and it has bitcount built in. I am aware these are probably rarely used, but I wanted them for what I was trying to do (compare large combinations of DNA sequence). I tried implementing it in Python / Numpy and Java and PG but MySQL was still the fastest. (I am fairly sure if I spent more time, I could improve any of the solutions, but for a similar level of effort MySQL (plus Python) won.

masklinn 12y ago
Postgres does have a bit/varbit which may have been closer to your use case, though you're correct that there's no native bitcount (assuming by "bitcount" you mean "count the number of bits set to 1 in the field). Also even with CHECK constraints being available, the question of unsigned ints seems frequent enough that there's at least one extension implementing unsigned types: https://github.com/petere/pguint https://github.com/petere/pguint (no idea about the quality or production-readiness).
- collyw 12y ago
  To be honest I could have probably used signed integers but conceptually representing DNA sequence (4 possibilities at each position) was far easier to think about that two's compliment. The idea was to make everything as small and lean as possible, to get the maximum amount into memory, and it seemed to work. Bitcount / popcount / hamming distance as a CPU operation will have saved a lot of CPU cycles over the PG method I tried. You sound like you know Postgres fairly well. How feasible would it be to write a CPU based popcount extension?
- deleted 12y ago
  [deleted]