A website can be static or dynamic. For any web application with a minimum of complexity a database management system (abbreviated as DBMS) is required.
It’s important to note that I will talk about public web applications such as e-commerce sites, social networks, classified ad sites, blogs, and so on (the kind of projects I have worked on, talking from my experience). I will not talk about intranets or extranets, which are closed environments that do not take into consideration the same speed and security concerns that a public site must manage.
In my opinion (and what I have seen so far) there are 3 database management systems that are commonly used for web applications.
The #1 in this top, the well-known and well-tested DBMS for the Internet: MySQL. An open-source DBMS for relational databases. It’s highly coupled with PHP, or at least all PHP project I have worked on used it. It’s the LAMP stack, which stands for Linux (as the operating system for the server), Apache (as the web server), MySQL (as the DBMS) and PHP (as the programming language). A very popular web stack. Probably the biggest project that uses it nowadays is WordPress.
It started as a fast solution, since it did not supported advanced (but useful) database functionalities such as foreign keys, check constraints and transactions. For that reason, for a new project, we analyzed the pros and cons of using it: what’s our main focus? data integrity or speed? Something that nowadays is absurd, because we have very advanced solutions that work insanely fast (in part because hardware has evolved and is reasonably cheap).
MySQL lets the user to choose from several data engines. There are 2 main options: MyISAM and InnoDB. The first one is faster due to the limitations I talked about in the previous paragraph, while InnoDB was the engine that provided the missing pieces and became more popular over the time until it was acquired by Oracle.
And that’s the problem with MySQL: it’s a product now owned by the biggest company in the subject which earns millions of dollars from corporates using their product. Yes, they want to be part of the non-corporate world controlling MySQL. For that reason many developers searched for alternatives, while the original MySQL creators forked the project to create MariaDB, a GPL project that keeps compatibility with MySQL.
Many developers switched to PostgreSQL, the most advanced open-source relational DBMS. It provides all the functionalities you may need from a database while being a product maintained by the community. It’s like the Debian of DBMS.
I would say it became very popular since 2005, the year Ruby on Rails launched its first version using this DBMS by default. Rails has been supporting some useful features from PostgreSQL, making the development easier. For instance, Rails 4 brought support for PostgreSQL’s arrays and hashes, out of the box. Why creating intermediate tables for this when we can use this feature right from our DBMS?
If you are used to a client to work with your DBMS directly, you may be disappointed. MySQL has lots of excellent clients that you will not find after switching to PostgreSQL. There are some small tools that provide very basic functionality, but definitely it’s best to get used to the command line.
This is probably the most popular choice (and open source) for a NoSQL DBMS. Instead of a relational design with tables, MongoDB stores data as collections of documents, being simpler in design to be easily scaled using clusters (in fact, this is automatic using auto-sharding).
This modern approach to store data, more natural for programmers, made many to switch to this NoSQL DBMS, but in many cases this was a mistake because they want to simply replace one system with another. MongoDB is not made to replace relational databases.
This top of commonly used database management systems does not intend to say that one is better than the other. Developers have personal preferences, tools work better with a specific one, problems can be solved easily choosing one over the others, and so on.
So as a conclusion, we must think that it’s important to analyze the problem and choose the right tool for the job. I could say that in the majority of cases, for common web applications with a classic relational data model in mind, a relational database will suffice. If you are using a LAMP stack, you should be using MySQL right away. If you work with Ruby on Rails, PostgreSQL is probably a more natural decision. For many modern needs, MongoDB is more appropriate. I encourage you to test them all, trying to use their unique features and read what they emphasize on their developer documentation sites. A good analysis will make your long-term development easier.