What is the best platform for a large-scale database

Question

Between Mysql and PostgreSQL, which is a set for a very large amount of data ... for example, millions of records ... I think I should use PostgreSQL ... any suggestions?

Answer 1

I think it depends a lot on what you mean by "better." You should probably identify your needs before choosing one or the other.

Faster? More reliable? Allows replication? Is it possible to perform more complex queries? Is your application black-out, in which case you probably need a database that can be grouped and administered more easily, or you need all in one massive set of related tables, in which case you probably need good support for many cores and great memory. Do you have complex authentication or is it a simple "single-user" web application? Is the bulk of the data binary or is it prime numbers and strings? How will you make backups?

MySQL and PostgreSQL seem to be very capable databases, and both have been used successfully on a large scale, so I would advise you to first determine the specific needs of your application.

My penchant was for PostgreSQL, but mainly because I had some disasters when MySQL lost data a few years ago and I did not come to trust it again. PostgreSQL was very enjoyable in terms of the ability to make backups easy.

Answer 2

I used both in similar situations, and the size of the database does not seem to affect their scaling in different ways. PostgreSQL is much more complete and reliable and will be much better at supporting complex queries and optimizing them, while MySQL can shine in terms of search speed for extremely simple queries; but these aspects are independent of the font size issue.

Answer 3

Postgres has a rich set of capabilities and the best optimizer; its ability to make hash joins often makes it much faster than MySQL for joins. According to rumors, MySQL is faster for simple table scanning. The storage mechanism that you use is also of great importance.

At some point, scaling becomes a choice between two options: scaling by purchasing more equipment or scaling by introducing new machines (which you can trick into data, use replicas as subordinates, or try installing a master master - both Posgres and MySQL have solutions various levels of quality for this kind of thing).

Several million rows of table data currently correspond to standard server memory; if that’s all you do, you don’t need to worry about it - just optimize whatever database you are most comfortable with to ensure that the correct indexes are created, everything is cached (and something like memchached is used where necessary), etc.

People note that Facebook uses MySQL; it's true. Due to the fact that they actually use hundreds (thousands ??) of mysql databases, they are all responsible for their own small cross-section of the data. If you think you can upload facebook to an instance of MySQL (or postgres or oracle) ... well, they will probably be happy to hear from you; -).

Once you get into a terabyte, everything becomes complicated. There are specialized solutions such as Vertica, Greenplum, Aster Data. There are various "nosql" data storages such as Cassandra, Voldemort and HBase. But I doubt that you need to go to such an extreme. Just buy a little more RAM.

Answer 4

Well, ultimately it depends on what you like best. According to MySQL, there is no theoretical database size limit ... it depends on the capabilities of the hardware supporting it. With the number of lines using InnoDB, the theoretical limit is 256 terabytes. The reason I always give up on the theoretical is that there is probably a very small chance that you can index 256 terabytes of data, so this is what they approximate may be the limit. If you hit this maximum, you will have big problems. The current MySQL users in production that I can think of are YouTube and Facebook. These are probably the two largest ... and it seems that they are doing well.

But again, as I said above. This is what you like best.