0

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

closed as primarily opinion-based by Tom V, Michael - sqlbot, Mat, jcolebrand Dec 25 '16 at 20:30

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    One thing that's missing in my opinion: Consider what's best integrated in your disaster recovery and backup strategy. If the needed features do not determine your database, your infrastructure should. – jmk Dec 26 '16 at 20:17
2

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.

  • @joanolo: Are you sure about sharding? What about foreign tables and inheritance in Postgres? That way you can easily (and transparently) shard tables across multiple servers – a_horse_with_no_name Dec 27 '16 at 9:24
  • @a_horse_with_no_name: sharding tends to be difficult in most databases (I think this is one of the reasons why MongoDB has a place on the market: using it, it's easier). I think the mySQL tools for sharding are easier to use that the PostgreSQL ones. This has improved a lot since FDW and pg_shard. Nevertheless, it's not something "at the core". – joanolo Dec 27 '16 at 19:28
1

Without knowing more detail about what your specific needs are it's difficult to give a complete answer.

From what you have described it sounds as though either PostgreSQL or MySQL would work for you and which one you choose is going to depend pretty heavily on what level of expertise your team has. If you've all been using mysql for years and know it's quirks, go that way. But, if you're starting from a blank slate postgresql does offer more sophisticated tooling and is a much more pleasant environment to work in for the expert.

Running either database on your own AWS instances is a perfectly valid choice.

You will want to take the following into consideration:

  • is your database going to have enough traffic to justify using an instance type with attached ssd's ?

  • if not and you have to run it on EBS volumes are you going to be able to provision enough IOPS so that you won't bottleneck on disk access ?

  • run your database in a VPC on two private subnets with replicas in different availability zones.

  • use an IAM instance role with access to your backup s3 bucket so that the database servers can back themselves up without needing to have access keys on their filesystem.

-2
  1. The volume of the data by itself is meaningless without the answers to these questions -
    • How much data are you going to process on a regular basis?
    • How many concurrent requests are you going to serve?

  1. From SQL perspective, It is PostgreSQL heaven Vs. MySQHell.

    If your queries differ from select * from t where x = ?, I would seriously consider if MySQL is capable of answering your needs.

    Here are some features that exists in PosgreSQL but not in MySQL which make a huge difference:

    • Analytic functions
    • Table functions
    • Regex functions (The real deal and not just RLIKE)
    • Arrays
    • Hash Join
    • Hash Group

Not the answer you're looking for? Browse other questions tagged or ask your own question.