6 ms·

This is really about database semantics. If you think database semantics matter, you'll almost certainly prefer postgres. A lot of people simply don't care abo

This is really about database semantics. If you think database semantics matter, you'll almost certainly prefer postgres. A lot of people simply don't care about database semantics though. Possible reasons: * They are more interested in a particular application (e.g. wordpress), and use whatever default database it seems to prefer. * They are more interested in a particular framework/library (e.g. django), and use whatever database it seems to prefer. * They don't see any reason a database should have interesting semantics, because it's just a place to stash data until you need it again. * They really care about programming languages, but see SQL as a second-class citizen that looks archaic and is really just an obstacle to work around using cool language features. I believe database semantics do matter. It's hard to make a single argument that will convince everyone, but here are a couple things to consider: * SQL catches errors that may be impractical to catch even with haskell. For instance: you could have had a bug in the application three versions ago that lead to an inconsistent database state (e.g. a duplicated item ID), and you are just finding the bug now. SQL to the rescue: if the unique constraint exists, then you are fine, and the error would have been raised three versions ago before it ever got in the database. * Few languages have anything that comes close to ROLLBACK. Erlang kind of does, in the sense that it's a mostly-functional language and a process crash mostly resets you to a good state. But the guarantees of ROLLBACK are much stronger and clearer. I believe this is actually why erlang never really took off widely -- a PHP script hitting the database is a better version of the same concept: the PHP code can crash, and that will cause a lost connection and a ROLLBACK in the database, getting you back to a known-good state and the other requests just continue working (security and other PHP jokes aside). Rambling a bit, but I believe postgres really is the leader when it comes to database semantics, and I believe that is one of its most important contributions.

stephenr 9y ago
I get your point, but the two semantics you pointed out, work in MySQL/compatible engines too.
- jeffdavis 9y ago
  If you agree with my point then it's not hard to see how postgres is more friendly: You can write a function in python (or a number of other languages) and share code with your application, and use those functions almost anywhere in postgres (including unique indexes). That makes the unique indexes much more powerful, especially if you want to adapt to changes without rewriting all your data. Or, you can use range types and an exclusion constraint to form a non-overlapping constraint that prevents schedule conflicts: https://www.postgresql.org/docs/10/static/rangetypes.html https://www.postgresql.org/docs/10/static/rangetypes.html There are tons of examples and others list them all the time. The problem is that a lot of people read past them because they don't see why semantics matter.
  - stephenr 9y ago
    I didn't actually say I agree with you. I said I get your point. My point was that you admitted a single argument won't convince everyone that database semantics matter, and then went on to list two features that MySQL handles just fine, regardless of how well it handles the other things you care about. You may as well have said "a pet badger is better than a pet bulldog in so many ways, for example it will attack/chase home invaders". Edit: I have no actual idea if you can have a badger for a pet, or how effective they really are at home protection but I imagine they'd be badass at it.
    - jeffdavis 9y ago
      My reasoning is something like: "if these simpler database semantics matter, then maybe you should look at the more sophisticated semantics postgres has to offer". If you get my point but find flaws in my argument, well, I can live with that. Counterpoints welcome.
Aeolun 9y ago
I think the people you describe generally use Mongo. The people who just want a relational database that does what they need it to in 99% of the cases use MariaDB. The purists, or people that specifically need it for some specific feature use PostgreSQL.
- tensor 9y ago
  MySQL has been a toy compared to PostgreSQL for many years. This isn't remotely about being pure or niche, but about practical database concerns. Why have Oracle DB, MS SQL, and DB2 been profitable? It's because serious database features are genuinely important. MySQL doesn't cut it.
  - aljarry 9y ago
    Different use case. I've never seen people spawning wordpress-like stuff on Oracle. It's used for transaction-heavy applications, big data and so on. But web pages generate different load than what is "optimal" for the DBs you mentioned. MySQL on the other hand fits for them nicely.
defined 9y ago
> I believe this is actually why erlang never really took off widely -- a PHP script hitting the database is a better version of the same concept: the PHP code can crash, and that will cause a lost connection and a ROLLBACK in the database, getting you back to a known-good state and the other requests just continue working (security and other PHP jokes aside). Comparing Erlang to PHP + transactional DB is regrettable. Firstly, Erlang's "let it crash" philosophy, together with its sophisticated supervisor/worker framework, aims for resilience in the face of failure due to bugs or bad data. A PHP script hitting a database may be a better design for a simple web application, but it is in no way superior to the Erlang/OTP ecosystem for Erlang's primary use case: soft real-time, highly concurrent, resilient and robust systems. It cannot reasonably be compared to a scripting language + DB, nor does it make any sense to. Secondly, Erlang is also able to work with transactional databases, from its built-in Mnesia DBMS to Postgres and more. A more appropriate comparison would be between Erlang + DB vs PHP + DB, and then I think you will agree that Erlang would generally fare better than PHP in that case. WhatsApp was built almost entirely in Erlang + Mnesia. I honestly don't think they could have done it in PHP + Postgres. Finally, claiming that Erlang didn't take off because it lacked the transactional semantics of PHP + DB does not compute, because I never saw any claim that Erlang offers transactional DB semantics (unless it happens to be using a transactional DB).
- user5994461 9y ago
  >>> WhatsApp was built almost entirely in Erlang + Mnesia. I honestly don't think they could have done it in PHP + Postgres. They could have done it in Java, C# or Go. PHP/Python/Perl/Ruby are terrible for fast network applications.
  - defined 9y ago
    > They could have done it in Java, C# or Go. Maybe. But I think with much greater difficulty. None of Java, C#, or Go has the built-in distributed processing capability of Erlang. The last time I looked into it, WhatsApp's infrastructure consisted of 256 2-node clusters, each node of which was capable of supporting between one and two million concurrently open TCP sockets. The 2-node clusters were combined into (IIRC) 16 "islands", some of which were geographically remote, and each of which communicated with the other using a distribution protocol written by WhatsApp in Erlang.
iso-8859-1 9y ago
You can't compare SQL to Haskell like that. There are multiple b-tree implementations for Haskell that of course will not allow you to insert duplicate keys. There is even a real relational database system in Haskell that actually obeys the laws relational algebra, unlike SQL. https://github.com/agentm/project-m36/blob/master/docs/on_null.markdown https://github.com/agentm/project-m36/blob/master/docs/on_nu...
- jeffdavis 9y ago
  If you think haskell would make a good database language, you're probably right. It's a lazy, statically-typed, type-inferred, declarative language with tightly-controlled side effects -- just like SQL. But in its current form, Haskell is just not a DBMS. So, it must rely on a DBMS like postgres or mysql, and the semantics of the DBMS matter very much. And yes, I can compare SQL to Haskell, and I did a while back: http://thoughts.davisjeff.com/2011/09/25/sql-the-successful-cousin-of-haskell/ http://thoughts.davisjeff.com/2011/09/25/sql-the-successful-...
  - marcosdumay 9y ago
    An "algebraic data store" would be a very interesting piece of software. Bonus points if it's lazy and if you can store functions there too. Haskell creates some problems for joining different transactions into the same set of guarantees (like SQL does in optimist data sharing). But I'm sure there's some extension somewhere that makes the perfect data type for that possible - I just don't know about it.
    - iso-8859-1 9y ago
      Project M36 allows for storage of functions: https://github.com/agentm/project-m36/blob/master/docs/atomfunctions.markdown https://github.com/agentm/project-m36/blob/master/docs/atomf...

stephenr 9y ago

I get your point, but the two semantics you pointed out, work in MySQL/compatible engines too.

jeffdavis 9y ago
If you agree with my point then it's not hard to see how postgres is more friendly: You can write a function in python (or a number of other languages) and share code with your application, and use those functions almost anywhere in postgres (including unique indexes). That makes the unique indexes much more powerful, especially if you want to adapt to changes without rewriting all your data. Or, you can use range types and an exclusion constraint to form a non-overlapping constraint that prevents schedule conflicts: https://www.postgresql.org/docs/10/static/rangetypes.html https://www.postgresql.org/docs/10/static/rangetypes.html There are tons of examples and others list them all the time. The problem is that a lot of people read past them because they don't see why semantics matter.
- stephenr 9y ago
  I didn't actually say I agree with you. I said I get your point. My point was that you admitted a single argument won't convince everyone that database semantics matter, and then went on to list two features that MySQL handles just fine, regardless of how well it handles the other things you care about. You may as well have said "a pet badger is better than a pet bulldog in so many ways, for example it will attack/chase home invaders". Edit: I have no actual idea if you can have a badger for a pet, or how effective they really are at home protection but I imagine they'd be badass at it.
  - jeffdavis 9y ago
    My reasoning is something like: "if these simpler database semantics matter, then maybe you should look at the more sophisticated semantics postgres has to offer". If you get my point but find flaws in my argument, well, I can live with that. Counterpoints welcome.

Aeolun 9y ago

I think the people you describe generally use Mongo. The people who just want a relational database that does what they need it to in 99% of the cases use MariaDB. The purists, or people that specifically need it for some specific feature use PostgreSQL.

tensor 9y ago
MySQL has been a toy compared to PostgreSQL for many years. This isn't remotely about being pure or niche, but about practical database concerns. Why have Oracle DB, MS SQL, and DB2 been profitable? It's because serious database features are genuinely important. MySQL doesn't cut it.
- aljarry 9y ago
  Different use case. I've never seen people spawning wordpress-like stuff on Oracle. It's used for transaction-heavy applications, big data and so on. But web pages generate different load than what is "optimal" for the DBs you mentioned. MySQL on the other hand fits for them nicely.

defined 9y ago

> I believe this is actually why erlang never really took off widely -- a PHP script hitting the database is a better version of the same concept: the PHP code can crash, and that will cause a lost connection and a ROLLBACK in the database, getting you back to a known-good state and the other requests just continue working (security and other PHP jokes aside). Comparing Erlang to PHP + transactional DB is regrettable. Firstly, Erlang's "let it crash" philosophy, together with its sophisticated supervisor/worker framework, aims for resilience in the face of failure due to bugs or bad data. A PHP script hitting a database may be a better design for a simple web application, but it is in no way superior to the Erlang/OTP ecosystem for Erlang's primary use case: soft real-time, highly concurrent, resilient and robust systems. It cannot reasonably be compared to a scripting language + DB, nor does it make any sense to. Secondly, Erlang is also able to work with transactional databases, from its built-in Mnesia DBMS to Postgres and more. A more appropriate comparison would be between Erlang + DB vs PHP + DB, and then I think you will agree that Erlang would generally fare better than PHP in that case. WhatsApp was built almost entirely in Erlang + Mnesia. I honestly don't think they could have done it in PHP + Postgres. Finally, claiming that Erlang didn't take off because it lacked the transactional semantics of PHP + DB does not compute, because I never saw any claim that Erlang offers transactional DB semantics (unless it happens to be using a transactional DB).

user5994461 9y ago
>>> WhatsApp was built almost entirely in Erlang + Mnesia. I honestly don't think they could have done it in PHP + Postgres. They could have done it in Java, C# or Go. PHP/Python/Perl/Ruby are terrible for fast network applications.
- defined 9y ago
  > They could have done it in Java, C# or Go. Maybe. But I think with much greater difficulty. None of Java, C#, or Go has the built-in distributed processing capability of Erlang. The last time I looked into it, WhatsApp's infrastructure consisted of 256 2-node clusters, each node of which was capable of supporting between one and two million concurrently open TCP sockets. The 2-node clusters were combined into (IIRC) 16 "islands", some of which were geographically remote, and each of which communicated with the other using a distribution protocol written by WhatsApp in Erlang.

iso-8859-1 9y ago

You can't compare SQL to Haskell like that. There are multiple b-tree implementations for Haskell that of course will not allow you to insert duplicate keys. There is even a real relational database system in Haskell that actually obeys the laws relational algebra, unlike SQL. https://github.com/agentm/project-m36/blob/master/docs/on_null.markdown https://github.com/agentm/project-m36/blob/master/docs/on_nu...

jeffdavis 9y ago
If you think haskell would make a good database language, you're probably right. It's a lazy, statically-typed, type-inferred, declarative language with tightly-controlled side effects -- just like SQL. But in its current form, Haskell is just not a DBMS. So, it must rely on a DBMS like postgres or mysql, and the semantics of the DBMS matter very much. And yes, I can compare SQL to Haskell, and I did a while back: http://thoughts.davisjeff.com/2011/09/25/sql-the-successful-cousin-of-haskell/ http://thoughts.davisjeff.com/2011/09/25/sql-the-successful-...
- marcosdumay 9y ago
  An "algebraic data store" would be a very interesting piece of software. Bonus points if it's lazy and if you can store functions there too. Haskell creates some problems for joining different transactions into the same set of guarantees (like SQL does in optimist data sharing). But I'm sure there's some extension somewhere that makes the perfect data type for that possible - I just don't know about it.
  - iso-8859-1 9y ago
    Project M36 allows for storage of functions: https://github.com/agentm/project-m36/blob/master/docs/atomfunctions.markdown https://github.com/agentm/project-m36/blob/master/docs/atomf...