I am trying to follow tuts on learning Apollo Client, GraphQL, etc, however, it seems that MySQL is used everywhere, but almost never in a professional setting? Why is this and what are the benefits of MySQL as opposed to PostgreSQL, etc?
The relational database is a very effective industry standard data organization and storage technology. There is a rigor to designing relational databases, and SQL has proven to be an excellent way of accessing that data. It’s also not at all true that MySQL isn’t used professionally. Most people know it was used by Facebook.
Here is a list of other companies using it:
- Airbnb.
- Uber.
- Netflix.
- Dropbox.
- Pinterest.
- Amazon.
- Twitter.
- Slack
MySQL became popular largely because it had a FOSS license, was written to run on Linux ,and was simple to install with a relatively small footprint, while still providing most of the things you want in a relational database engine you will code against using embedded SQL statements. Installing MySQL came in a tarball or a couple of packages, and would be up and running in minutes if you knew your way around a linux system.
This was at a time when the commercial market leaders (Oracle, Sybase & Informix) were expensive to license, and required expensive mini computer hardware to run.
None of the commercial leaders even had a Linux port, and they were not highly enthused about linux, as they could command big dollars to license it on HP, SGI or Sun hardware.
If you wanted to run on Intel/AMD based PC hardware, your only option was to use the Microsoft licensed Sybase version they ported and branded as Microsoft SQL Server product which was built for Windows NT Server. And that product still came with a stiff license fee structure.
MySQL adoption was driven by DBA’s and developers who were usually already familiar with the commercial options running under unix, and learned to understand and live with the tradeoffs. This lead to promotion of the product which helped it build its reputation as the defacto linux relational database.
The footprint of a basic MySQL or MariaDB instance is still quite small, although we now live in a different time, where virtualization allows us to run clusters of virtual machines or containers on a PC, and everyone has a Linux port, but that was not imaginable in the early days of the internet and the explosion of the WWW.
I believe the other reason MySQL took off in comparison to the older PostgreSQL, is that Postgres started out as a DARPA project, and evolved in Academia as a persistence technology that was not originally intended to be a relational database management system but more of a storage engine you would build into a system.
Around the time that the first version of MySQL was released, Postgres (as it was originally called) got a SQL interpreter added and that release was named Postgres95. As early as 1992 Postgres had been commercialized by a company called Illustra, so the perception was that Postgres was a storage engine and not a SQL system, which was already well on its way to commercialization.
So from the point of view of the open source crowd, you had a new Relational database management system (MySQL) with a FOSS license, vs a government funded persistence engine.
Another thing that is interesting about the MySQL architecture is that allowed for different storage engines, and this re-imagined what a relational database could be, and caught the imagination of developers who had interesting ideas for different persistence solutions.
There are many use cases that exist in regards to relational data, and they are not all the same. What has been happening over the years is that some use cases require different architectures or extensions. MySQL seemed to many, uniquely suited to the task of extension, since you could tackle many issues by writing a storage engine that was designed to suit a particular use case and people would be able to start making use of that engine in their existing MySQL infrastructure.
The obvious analogy everyone likes to bring up is whether MySQL vs PostgreSQL is similar to Betamax vs VHS, but it’s a good deal more complicated than that. There has and probably always will be a desire for people to be able to get a monolithic server up and running quickly, and at this juncture MySQL has been used and is now understood well by so many people, that any open source application (think wordpress or forums, or crm software) that requires MySQL to work with, is going to have the ability to support MySQL at install time, or they risk minimizing their market share.
I could probably write a lot more about the topic, but this is already pretty verbose.
PostgreSQL has become popular with many developers as it has evolved into a system that is very close to Oracle in terms of the SQL syntax it supports and the type of database objects. For example, if you want a sequential number generator capability for the primary key to a table, Oracle has sequence objects which you use to generate numbers when you are inserting new rows.
With MySQL you define a column as being AUTO_INCREMENT and it generates id’s for you rather than having to reference an independent object. Thus the SQL syntax is going to be different for each database.
Oracle was famous for the performance and value of its stored procedure and trigger feature. The default sproc language in Oracle is PL/SQL. PostgreSQL’s sproc language pl/pgSQL is so close to pl/sql that you can move over an oracle sproc with no changes required in most cases. People looking to port an Oracle application to Linux, especially one that depends on a large codebase of sproc packages and table triggers can do so with PostgreSQL.
MySQL did not even have sprocs or triggers until October of 2005.
There are really far too many differences to talk about in detail, complicated by the forks of MySQL and the different storage engines which essentially turn MySQL into a different product.
For example, for years the default MySQL engine was MyISAM. If you created a table without specifying something different you got an engine that doesn’t have transactions, referential integrity constraints, or row level locking (it has table locking).
Most people who use MySQL in production use it with the InnoDB engine, which was developed by a Finnish developer independently of the MySQL project.
I Hope this helps you get a bit of an answer to your question. Currently speaking, you can use MySQL or PostgreSQL for just about any relational database integrated project you can think up, and either one will be able to do the job well and (given some thinking in advance) scale to a level that will support a lot of businesses.
1 Like