NoSQL vs SQL during sharding when a single read operation only hits one shard

Question

I've a use case where I'm stuck between figuring out whether to use SQL or NoSQL db. The db has 2 fields - A (PK), B

A is 8 char-long, while B is 100-400 char long.

The operations are simply:

1. Write: Given strings A, B - store both in db.
2. Read: Given input A, return B associated with it.

The database is queried by multiple application server to reads and writes, implying parallel read/write transactions(operations).

Now, comes the scalability part:

SQLs:

Scaling writes - (Assuming partitioning is already done). Then enable sharding(shard based on some hash function) for SQL dbs. And as every write operation comes, write to the desired shard. This scales the writes.

Scaling reads - Enable replication(not sure if this is enable by default for SQL dbs). Assumption is that we are scaled for writes and reads. So, Sharding and replication are both enabled.

As, you can imply from above, the read operations are such that the db just needs to hit a single shard for reads, so basically no SQL-JOIN operation required and also no combining of results from multiple shards required in above case.

1. Will using SQL db perform better in above case than NoSQL if we are scaling for high number of reads and writes(#read is equivalent to the #writes )? I see that SQLs have to maintain ACID properties and maintaining strong-consistency slows down the writes, reads due to the reason that holding locks across shards is time-taking process but our case doesn't require cross-shard locks.

2. Does the above decision change when reads >>> writes OR writes >>> reads

4. If there's no difference between using SQL and NoSQL dbs then does it basically boils down to whether eventual consistency is permitted in which case NoSQL should be the better choice(I hope that's bcz writes will be much faster in NoSQL given that strong-consistency not a must which SQLs provide)?

A very basic question, given that the db grows to terabyte/petabyte limits, does it make sense to use SQL db with a single server instance which holds this much data in a single hardware with replication turned ON?

Answer 1

The reason to choose a NoSQL database system over a regular SQL database system has almost nothing to do with performance or scalability, and is mostly related to schema flexibility.

NoSQL is a good candidate for when your schema changes more frequently than you're able or willing to maintain, or when you're ingesting data from an external source that is liable to change without notice. That's mainly the use case for it. Though modern SQL databases can also handle this task pretty well now too, with their support of JSON.

For further in depth reading about the use case comparison of both, please see the following answers for other DBA.StackExchange questions (note I wrote some of them myself):

1. SQL or/with Nosql

2. How to tell if a project needs a NoSQL database solution?

3. What exactly is an unstructured data and why to use Non-relational DBMS for that?

If there's no difference between using SQL and NoSQL dbs then does it basically boils down to whether eventual consistency is permitted in which case NoSQL should be the better choice(I hope that's bcz writes will be much faster in NoSQL given that strong-consistency not a must which SQLs provide)?

Here's the deal, the time it takes to physically write data to disk is primarily bound by the underlying hardware. It doesn't matter if a NoSQL database is used, a SQL database, or raw files being written locally.

The benefit of eventual consistency from a NoSQL database is to minimizing locking bottlenecks when data is being written and read from concurrently. But this generally should be minimal or a non-issue with a well architected database, even for a SQL database. Furthermore, it can be almost completely alleviated in a SQL database with proper isolation level usage and other techniques such as data replication (akin to sharding).

A very basic question, given that the db grows to terabyte/petabyte limits, does it make sense to use SQL db with a single server instance which holds this much data in a single hardware with replication turned ON?

Sure, if your schema requirements fit the context of a SQL database, and you have no other specific reasons to use a NoSQL database. Certainly, performance should not be the main factor when considering such a decision.