1

I am designing a content-based website capable of managing huge amounts of pages (mostly web pages; images, videos, etc would be stored on the file system). Which would be faster/more efficient/most stable:

A traditional database (eg. MySQL) with each web page stored as a row with indexes strategically located or

A key/value based NoSQL database (eg. MongoDB) where the key is like PAGE-ID_TITLE or PAGE-ID_BODY etc.

Either option could allow caching (either built-in or roll-your-own).

I do not want to directly compare MySQL and MongoDB but rather SQL vs key/value

Thanks

Montgomery
  • 11
  • 2
  • 1
    Hi there. Can you clarify huge amounts of data are you talking videos, terabytes? What kind of data do you intend processing? Is it structured or unstructured. In my experience stay away from big data unless you really are using unstructured big data. it can cause a headache where minor changes are concerned - need to delete down tables and reboot clusters. The traditional and more mature RDBMS is your old reliable. You can use both together as well. But you would be better to define the data you intend to use and then you will get a good answer. – Treasa Jan 02 '17 at 21:29

1 Answers1

2

This is a very open question, with lots of considerations to be made and lots of choices that are more "opinions" than "facts". So, I won't try to provide an answer, but to point out some of the big differences and things to conisder.

If your only need is to store key/value pairs (page_id, body), a key/value datastore is a good match for your need. You can use Riak, Oracle NoSQL and several others (you can take a look at the Wikipedia for a longer list). You get very fast access to your bodies (or titles); normally you also get some easy sharding and replication (i.e.: MongoDB), which allows for really good horizontal scalability if you ever need really very huge amounts of data.

What you don't get is an easy way to join data and query it. If one day you decide to have a list of (for instance) article authors, or article comments, or whatever, the key-value store might not be that comfortable any more.

As a rule of thumb, key-value stores or document stores require that you plan very well in advance how your data is going to be used, because the structure of your documents will have to reflect this usage. A relational database is based on structured tables where you store information which can be searched and joined easily.

The majority of contemporary SQL databases allow for some combinations that can be thought as "the best of both worlds", mixing SQL with JSON (or documents). For instance, you can check PostgreSQL hstore, which gives you a key-value store within a SQL database, or use JSON with MySQL, PostgreSQL, Oracle, SQL Server, and others.

You can check previous questions such as Use SQL or NoSQL?, or Should I use SQL or NoSQL for this specific design (surveys)?.

I'd also recommend SQL vs NoSQL: The Differences

joanolo
  • 13,397
  • 7
  • 36
  • 65