Database architecture for big data store? My team has developed service software with a PHP framework, so we are flexible to change database engine: MySQL, PostgreSQL etc. We have already more than 30 customers who wait to start using this service. My uncertainties are following:


Which DB engine is the best for use in such situations?
Is there also MySQL ok. or better to use PostgreSQL?
We use AWS so we plan to have own machine for DB hosting, is this the right decision?


More information:


Database will grow up to 10 GB in year.
Lots of selects but also inserts
Storing many texts and numbers. Files and images are stored on other storage.
Need 24/7 availability
The specified data is quite common on any LAPP or LAMP scenario. I think both databases would handle this kind of loads easily. If you really need high availability, and you are on AWS; I would use RDS, with either PostgreSQL or MySQL, depending on which database you feel more comfortable with. All the necessary steps to configure and maintain the databases are taken care of for you.

If you want to prepare for High Availability, the streaming replication options of both MySQL and PostgreSQL are good alternatives. The intermediate software, that should monitor the master and slave(s) databases and decide when the master is 'dead' and a slave needs to be promoted is the most difficult part (and many times, becomes your SPOF).

Every DB has its pros and its cons. I use mostly PostgreSQL, but also MySQL for some applications that require it. If I have to make a choice, 90% of the times I'll go for PostgreSQL. However, there is not a "good one" or a "bad one". There's normally one that fits best your needs. Sometimes your SQL needs are not that complicated, but you want to use a DB that is known by your team, and maybe your team knows better MySQL. Sharding is easier (better integrated) using MySQL, for instance.