Choose a DBMS between MySQL, PostgreSQL, MariaDB and MSSQL?

Question

Database

Good afternoon! I am developing a SaaS application , and now I have a choice of a DBMS for storing basic data. I started development on MySQL, but now I'm not sure which one to choose. Moving to another DBMS at this stage will not be a problem for me (I use PDO). far from a clear understanding of what “high loads” are for a DBMS. It's just that according to my calculations, in about a year the database will be very weighty (see below).
The main choice is between MySQL, PostgreSQL, MariaDB. Also, it is possible, but not welcome, the option of Microsoft SQL Server on Windows Azure
The situation is as follows:

1. There are no complex queries to the database. Maximum JOIN of two tables
   
2. Most of the queries are read
   
3. There is one most important and "main" table (the structure of the table is below under the spoiler). The table will grow by about 10-30 thousand records per day. Writing data to this table is the most important thing!
   
4. Most of the read requests will be to the "master" table. This table will be used to search for any of the fields (in extremely rare cases, ~0.5% - for several at once). The search should be carried out quickly (despite point #3)
   
5. Indexes will most likely be added to the "main" table for each of the fields for two fields at once (ownerID and Field name, since ownerID will be specified in all queries). A quick search will be needed for any of the fields, but this is not such a priority task. (Or is it better to use Sphinx?)
   
6. The lion's share of requests (~ 80%) for reading to the "main" table are simple selects by indexes from and personalID with limit = 20. The remaining requests for any other fields by indexes (which are not yet) ownerID and Field name, also with limit = 20
   
7. Data changes in the records of the "main" table will occur extremely rarely. No records will be deleted from the table.
   
8. Support for transactions and foreign keys is optional
   
9. Requires the ability to replicate master-slave data
   
10. The possibility of sharding at the DBMS level is welcome
    
11. The reliability of the database is extremely important (i.e. such a crash as in MyISAM with manual recovery immediately disappears)
    
12. New fields can be added to the "main" table. Of course, this is an extremely rare occurrence and far from the most important requirement, but adding a new column to a table with a size of ten GB for MySQL is a very long process, and I really don’t want to move new fields to a separate table
    
13. All this at first will spin on such a dedicated server
    
14. Other tables will grow slowly, and access to them will be quite rare, I don't worry about them. Frequently updated data is spinning in redis
    

The structure of the "master" table

CREATE TABLE IF NOT EXISTS `clients` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `personalID` int(11) NOT NULL,
  `ownerID` int(11) NOT NULL,
  `fromID` int(11) NOT NULL DEFAULT '4',
  `fromDomain` varchar(255) NOT NULL,
  `datetime` datetime NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `paymentType` tinyint(4) NOT NULL DEFAULT '1',
  `wmSum` float NOT NULL DEFAULT '0',
  `wmCommission` float NOT NULL DEFAULT '20',
  `sysNumber` varchar(14) NOT NULL,
  `sysNumberLastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sysNumberStatus` varchar(250) NOT NULL,
  `timezone` float NOT NULL,
  `comment` varchar(500) NOT NULL,
  `countryID` int(11) NOT NULL,
  `postIndex` varchar(6) NOT NULL,
  `region` varchar(500) NOT NULL,
  `city` varchar(500) NOT NULL,
  `address` varchar(500) NOT NULL,
  `fio` varchar(500) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `price` float NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `label` varchar(30) NOT NULL,
  `tag` int(11) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `referer` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `from` (`ownerID`,`fromID`),
  KEY `paid` (`paid`),
  KEY `status` (`status`),
  KEY `label` (`label`),
  KEY `sysNumberLastUpdate` (`sysNumberLastUpdate`),
  KEY `personalID` (`ownerID`,`personalID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

PS Those wishing to send me to google please do not even answer. I was not able to find information on comparing the current versions of different DBMS, and studying the capabilities, pros and cons of PostgreSQL, Microsoft SQL Server and MariaDB for a person who has not worked with them is a very long task. Yes, and in MySQL I am far from an expert, and such a large project is new for me, and the capabilities of MySQL differ from version to version. The only thing I know for sure is that MyISAM type tables in MySQL will definitely not work for me.

Answer 1

It looks like you forgot to consider this option: Percona Server is an enhanced, drop-in MySQL replacement . For very fast query execution, you need to use a special NoSQL interface called HandlerSocket. Yes, and even multi-master replication is also there.
Somewhat confuses PDO and the desire to make a loaded site. I'm afraid that this layer will have to be abandoned immediately.

Answer 2

Sharding and replicating Postgre is easier and more reliable. But even with PDO, there are some differences in syntax and features between it and MySQL.
The main thing - do not plunge into MSSQL. You can work with it normally only inside the stack of MS-tools. It doesn't even support UTF-8. Well, MS puts a bolt on non-Windows drivers, they release them very rarely.

Answer 3

Choose what you know best!
If MySQL, then consider MariaDB or Percona. Yes, and you can move to them from MySQL at any time, as they are backwards compatible.
Judging by your data, the issue of load when taking into account straight arms will arise before you in two or three years. And premature optimization can ruin the project.
And try to avoid JOINs as much as possible. As a rule, most of the problems arise because of them.

Answer 4

There is about MySQL, MariaDB and PostgreSQL. I hope that at least partially answers your question eax.me/postgresql-vs-mysql/

Answer 5

Based on the requirement of reliability, I would choose PostgreSQL. All other operations are typical for a DBMS and, with the right settings, will not create a headache.

Answer 6

Why not look at MongoDB?
This is certainly not an ACID database, but horizontal scaling / sharding is much easier to do.

Answer 7

MongoDB