Welcome to WebmasterWorld Guest from 54.224.83.221

Forum Moderators: open

MySQL or MariaDB or PostgreSQL?

     
12:37 pm on Mar 1, 2018 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 22, 2004
posts: 140
votes: 0


I'm building up a new server to replace my current webserver. I run many sites, all php and all use SQL generously. I'm currently running MySQL. Wondering if anyone has any strong opinions (and accompanying arguments) one way or the other?

One downside to going with postgre would be that I have to update my php code, but that's not an issues since most of my code is so old, I'm going to have to upgrade it anyway since it uses mysql_ functions. And the sites that do use mysqli_ do so via my own little abstraction layer. So upgrading the PHP is a wash no matter what I do.
12:42 pm on Mar 1, 2018 (gmt 0)

Full Member

Top Contributors Of The Month

joined:Apr 20, 2017
posts:334
votes: 73


With PHP, I would stick with MySQL and MariaDB for ease of use.

And between MySQL and MariaDB, I would go with MariaDB which now has a more dynamic community than MySQL.
1:30 pm on Mar 1, 2018 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts:2773
votes: 112


Leaving aside PHP and any limitations its postgres functionality may have (because I do not use PHP so do not know about that), I prefer Postgres.

It has transactional DDL so schema changes can be transactional. Makes it a lot safer when applying changes (especially migrations rather than manual changes).

MySQL does things like silently ignoring things like a check clause: [dev.mysql.com...] and silently changin data: [cybertec-postgresql.com...]

Postgres seems to have better full text search, better handling of JSON data, better handling of geographical data multiple languages that can be integrated into it (e.g. run in things like triggers). Most of all it generally seems to be more focused on doing things correctly.
1:36 am on Mar 2, 2018 (gmt 0)

Senior Member from CA 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 25, 2003
posts:1143
votes: 284


It's been almost 20 years that I've been using PostgreSQL and it's been simply awesome for my various needs.

Most of my webdev friends use MySQL and are generally pleased. Since Oracle acquired it and the MariaDB fork began some of them have switched because they don't trust Oracle and/or the better rate of new features and security updates.

All three have good to great track records, all work well for most webdev needs. Therefore it comes down to feature requirements, personal experience and preference.

That said I recommend PostgreSQL :)
2:10 am on Mar 2, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Apr 1, 2016
posts:1980
votes: 505


I favor noSQL, MongoDB. It is schemaless, it stores everything as key:value pairs. It allows nesting of documents making it very flexible and fast and it is infinitely scalable. I use it to do some pretty awesome queries over largeish data-sets that I am pretty sure would be nearly impossible to reasonably implement using an SQL database.

I believe that PostGRE has some no-sql functionalities baked in, that is probably a great feature if you are porting data from an sql environment.

Note, that I do not use PHP. My server side coding is Python.

Python "dict" to MongoDB "bson/json" to client side JS in JSON, it works seamlessly.
11:16 am on Mar 2, 2018 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts:2773
votes: 112


MongoDB and the like lose all the guarantees a relational database gives you. It ends up leading to things like this:

[pcworld.com...]
[hackingdistributed.com...]
[cryto.net...]

It may or may not matter for a web app, and it certainly matters less than for a financial app. I find that other checks (e.g. enforcing the data types of columns, ensuring columns are not null, ensuring foreign keys exist) make bugs evident much earlier so you catch problems much earlier.

I use it to do some pretty awesome queries over largeish data-sets that I am pretty sure would be nearly impossible to reasonably implement using an SQL database.


I am sceptical. I have yet to find a query you cannot do with an appropriately designed SQL database. NoSQL has its place, but for particular purposes.
2:33 pm on Mar 2, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Apr 1, 2016
posts:1980
votes: 505


@graeme_p

Let start by saying that there is no one size fits all technology, and if you are running a bank MongoDB may or may not necessarily be the right solution. The first two links you share seem to blame a technology for a vulnerability, whereas the exploit was well known and should have been addressed by the app designers. This had more to do with bad app design then a specific technology. The choice of technology in that instance may well have been wrong but to blame the theft on MongoDB is a bit disingenuous.
MongoDB is rolling out new features in v4 to address the above issue: [mongodb.com...]

As for the third article, I assume that you must be running multiple instance of mySQL across a distributed cluster.

I am sceptical.

Skepticism is good, it keeps us on our toes.

I have yet to find a query you cannot do with an appropriately designed SQL database.

I didn't say it cannot be done. I said it cannot be reasonably done, that is without reasonable computing power, time and storage requirements. This is because of two requirements or constraints of SQL, data must be normalized, and tables cannot be nested. So as a result you end up with massive table structures and huge amount of tables all which are mostly sparse. Now if you want some specific item, you need to do multiple joins and merges just to get it, whereas in noSQL you access that same data with a simple query on a single document.
3:10 pm on Mar 2, 2018 (gmt 0)

Senior Member from CA 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 25, 2003
posts:1143
votes: 284


I've been intrigued by the number of webdevs such as NickMNS who use NoSQL/MongoDB. On first read not an obvious choice for site backend, however given that Postgres has adapted to handle NoSQL as well as SQL data and added enhanced JSONA/B storage the convergence of features is a strong illustration of their value for various requirements.

I remember all the Oracle, DB2, and MS Server types snickering at Postgres users back when so I'm not about to diss any software solution that someone finds appropriate/useful. It's a matter of requirements need met, capabilities in hand or quickly acquired, and comfort level.

And never forget that things change; maintained software tends to both get better and add features and user needs change over time as well. I've been moving from using PostgreSQL to Postgres-XL so while similar there is still a learning curve. Perhaps the best thing of webdev is that one is always learning, always challenged; headaches a feature not a bug!

Note: the more what I use is become 'standard' the older I feel.
@l008comm: best wishes whatever your choice.
11:41 am on Mar 3, 2018 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts:2773
votes: 112


Let start by saying that there is no one size fits all technology


Agreed.

The first two links you share seem to blame a technology for a vulnerability, whereas the exploit was well known and should have been addressed by the app designers.


A relational database makes it very clear whether you have covered it or not, because you do it in the schema (and sometimes settings if there are different isolation or concurrency levels available) and the DB handles the rest. Les development time and fewer bugs. Of course you can still wreck it with bad code, but its a lot less likely to happen.

MongoDB is rolling out new features in v4 to address the above issue


Promised new features may help, but at the cost of performance. I do not know whether what they promise is adequate to fix the issue as your links only states some form of transactions will be available - for this sort of application you need a high isolation level - generally called serializable.

As for the third article, I assume that you must be running multiple instance of mySQL across a distributed cluster.


I am genuinely puzzled by that comment. Why would it imply use of either mySQL or a distributed cluster, let alone both?

Now if you want some specific item, you need to do multiple joins and merges just to get it, whereas in noSQL you access that same data with a simple query on a single document.


At that point you are trading flexibility for performance. It works as long as your document structure (your implicit schema) matches your queries. In an extreme case (e.g. web page maps to data in one document) you may as well store you data in the file system.

IN most cases, given a good schema and using the tools available (indices, well designed queries, materialized views......) you can get good performance. If you still cannot, cache (and you might use a NoSQL database, usually a KV store, for the cache).

So as a result you end up with massive table structures and huge amount of tables all which are mostly sparse


Lots of tables true - but the structure is not so much more complex than that of a document DB as well defined. Lots of sparse tables is either an unusual case of a symptom of badly designed tables. It is also not always that much of a problem - Postgres requires one bit per row extra for nullable columns.