3 ms·

Maybe it was not only for scalability, but also for reliability or embracing the Do the Right Thing thing? From my experience with PostgreSQL and MySQL, it is

by gbog 13y ago

Maybe it was not only for scalability, but also for reliability or embracing the Do the Right Thing thing? From my experience with PostgreSQL and MySQL, it is a bit like the difference between python and php, one is a great work of craftmanship that is reliable, predictible, coherent, enjoyable to work with and minimise the wtf/mn rate (which is the best quality measure in software), while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping.

strlen 13y ago
Those are perfectly fine reasons, but the stated reason had been scalability.
- brennen 13y ago
  See my reply to rpedela elsewhere in this thread.
csmuk 13y ago
Exactly this. To put it bluntly after managing both for years, Postgres doesn't scare the shit out of me like MySQL does. I've had quite a few moments with MySQL doing stupid things that aren't intuitive or right, particularly in the backup/restore space. For example backup one schema and restore onto later versions doesn't always work. Postgres has the same feel as *BSD i.e. deterministic. It is well documented and does exactly what the manual says and is devoid of surprises. It feels engineered and I can provide reliably engineered solutions because of this. Basically I can sleep at night. I really give less of a crap about scalability. I'd throw a bigger box at the problem or use heavy caching. I've built much larger ecommerce solutions with orders of magnitude more hits/orders than sparkfun on much smaller kit
- Negitivefrags 13y ago
  I have to say, my experience with Postgres is the opposite. Sometimes the query planner will suddenly decide to pick a bad execution plan. I have been woken up in the night multiple times and found that the problem is that Postgres has arbitrarily decided to stop using an index and changed to do a full table scan and the database has ground to a halt under the load. Doing an ANALYZE sometimes fixes it. Increasing the statistics target for the table in addition sometimes fixes it. One time we had to add an extra column to an index to make it decide to use it even though it shouldn't have needed it (and didn't a few hours before!). The developers seem dead set against allowing you to override the query planner to add determinism. I'm not saying MySQL is any better, as I have not used that in production before.
  - csmuk 13y ago
    I genuinely haven't had this problem and we have a fairly large instance. I'll keep my eyes peeled though. Thanks for the heads up.
  - lucian1900 13y ago
    MySQL is much, much worse. It barely even has a query planner in the first place and it will often ignore perfectly good indexes. Query planner changing its mind can be a problem for most DBs, Postgres is not special in this regard.
    - dspillett 13y ago
      My experience with mySQL (admittedly none of that experience is recent so things may have changed a lot since) suggested that it didn't have a planner at all and it just attacked things in the order of your FROM/JOIN clauses using indexes where obvious from the WHERE clauses and linking ON definitions: it wouldn't for instance see circumstances where it could hit the second table of a join first and so need to consider for less of the first larger table for instance, so reordering inner joins in a query could make a significant difference. I use MSSQL a lot in my day job and sometimes the query planner there surprises us by being fairly bright about this and other optimisations. Of course it sometimes gets things very wrong and you have to use index hints to push it in the right direction, but generally speaking if your queries are complex enough to hit that problem you might need to consider refactoring your design for other reasons too.
    - vidarh 13y ago
      But the difference with MySQL is that you can easily forcibly make MySQL use the indexes you want it to, as long as it is possible. With Postgres, while it usually seems to do a better job, when it refuses to use your indexes it can be a massive pain to coax it into doing what you want it to, even when you know it's making a bad choice. Postgres has a number of issues like that, where ideological purity has a negative impact on usability. It still mainly use Postgres these days, but I regularly swear at it over things I know would be far simple than MySQL for those reasons (replication used to be a huge one, though that is now finally getting there, 8 years after I set up my first "large" replicated MySQL setup)
jafaku 13y ago
> while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping. You mean hacks like these? http://docs.python.org/2/library/abc.html http://docs.python.org/2/library/abc.html I agree.
Frencil 13y ago
This is perhaps one of the more succinct ways of summarizing our core reasons for the switch. There's a lot more to it than that, of course, but in a nutshell we saw the upcoming scaling of our data footprint and complexity and wanted to use (what we evaluated to be) the better tool for the job.

by gbog 13y ago

strlen 13y ago

Those are perfectly fine reasons, but the stated reason had been scalability.

brennen 13y ago
See my reply to rpedela elsewhere in this thread.

csmuk 13y ago

Exactly this. To put it bluntly after managing both for years, Postgres doesn't scare the shit out of me like MySQL does. I've had quite a few moments with MySQL doing stupid things that aren't intuitive or right, particularly in the backup/restore space. For example backup one schema and restore onto later versions doesn't always work. Postgres has the same feel as *BSD i.e. deterministic. It is well documented and does exactly what the manual says and is devoid of surprises. It feels engineered and I can provide reliably engineered solutions because of this. Basically I can sleep at night. I really give less of a crap about scalability. I'd throw a bigger box at the problem or use heavy caching. I've built much larger ecommerce solutions with orders of magnitude more hits/orders than sparkfun on much smaller kit

Negitivefrags 13y ago
I have to say, my experience with Postgres is the opposite. Sometimes the query planner will suddenly decide to pick a bad execution plan. I have been woken up in the night multiple times and found that the problem is that Postgres has arbitrarily decided to stop using an index and changed to do a full table scan and the database has ground to a halt under the load. Doing an ANALYZE sometimes fixes it. Increasing the statistics target for the table in addition sometimes fixes it. One time we had to add an extra column to an index to make it decide to use it even though it shouldn't have needed it (and didn't a few hours before!). The developers seem dead set against allowing you to override the query planner to add determinism. I'm not saying MySQL is any better, as I have not used that in production before.
- csmuk 13y ago
  I genuinely haven't had this problem and we have a fairly large instance. I'll keep my eyes peeled though. Thanks for the heads up.
- lucian1900 13y ago
  MySQL is much, much worse. It barely even has a query planner in the first place and it will often ignore perfectly good indexes. Query planner changing its mind can be a problem for most DBs, Postgres is not special in this regard.
  - dspillett 13y ago
    My experience with mySQL (admittedly none of that experience is recent so things may have changed a lot since) suggested that it didn't have a planner at all and it just attacked things in the order of your FROM/JOIN clauses using indexes where obvious from the WHERE clauses and linking ON definitions: it wouldn't for instance see circumstances where it could hit the second table of a join first and so need to consider for less of the first larger table for instance, so reordering inner joins in a query could make a significant difference. I use MSSQL a lot in my day job and sometimes the query planner there surprises us by being fairly bright about this and other optimisations. Of course it sometimes gets things very wrong and you have to use index hints to push it in the right direction, but generally speaking if your queries are complex enough to hit that problem you might need to consider refactoring your design for other reasons too.
  - vidarh 13y ago
    But the difference with MySQL is that you can easily forcibly make MySQL use the indexes you want it to, as long as it is possible. With Postgres, while it usually seems to do a better job, when it refuses to use your indexes it can be a massive pain to coax it into doing what you want it to, even when you know it's making a bad choice. Postgres has a number of issues like that, where ideological purity has a negative impact on usability. It still mainly use Postgres these days, but I regularly swear at it over things I know would be far simple than MySQL for those reasons (replication used to be a huge one, though that is now finally getting there, 8 years after I set up my first "large" replicated MySQL setup)

jafaku 13y ago

> while the other is a bunch of hacks knit together to make it work asap and its wtf/mn is skyscraping. You mean hacks like these? http://docs.python.org/2/library/abc.html http://docs.python.org/2/library/abc.html I agree.

Frencil 13y ago

This is perhaps one of the more succinct ways of summarizing our core reasons for the switch. There's a lot more to it than that, of course, but in a nutshell we saw the upcoming scaling of our data footprint and complexity and wanted to use (what we evaluated to be) the better tool for the job.