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:
- Write: Given strings
B- store both in db.
- Read: Given input
Bassociated 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:
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?