Scaling to 100M: MySQL Is a Better NoSQL
So much to disagree with here ...<p>> Locks limit access to the table, so on a high throughput use case it may limit our performance.<p>Then use a proper database that implements MVCC.<p>> Do not use transactions, which introduce locks. Instead, use applicative transactions.<p>Or just use a database that handle transactions more efficiently.<p>> `site_id` varchar(50) NOT NULL,<p>Why varchar(50)? UUIDs are 16-bytes. The best way to store them would be the binary bytes (which is how postgres stores them). If it's hex without dashes, it'll be varchar(32). If it's hex with dashes, it'll be varchar(36). Why did they pick 50? Future growth? Smart keys? Schema designer doesn't know what a UUID actually is?<p>> Do not normalize.<p>Bullshit. Normalize as much as is practical and denormalize as necessary. It's much easier to denormalize and it greatly simplifies any transaction logic to deal with a normalized model.<p>> Fields only exist to be indexed. If a field is not needed for an index, store it in one blob/text field (such as JSON or XML).<p>This is terrible advice. Fields (in a table) exist to be read, filtered, and returned. If everything is in a BLOB then you have to deserialize that BLOB to do any of those. That doesn't mean you can't have JSON "meta" fields but if your entire schema id (id uuid, data json) you're probably doing it wrong. It's next to impossible to enforce proper data constraints and all your application logic becomes if/then/else/if/then/else... to deal with the N+1 possibilities of data. Oh and when you finally add a new one, you have to update the code in M+1 places.