131–140 of 183 posts
Wouldn't Aerospike be a cheaper, lower maintenance, and more robust solution to this problem?
This is basically them failing to do enough research into existing solutions that would work far better.
Earlier quoted context omitted.
Trying to avoid using foreign key constraints in a relational database is not "system design 101", its an instant fail. When I cast my eye over a table with foreign key constraints, I am 100% certain that every single row conforms to those constraints, and always will. By contrast, when the same table does not have constraints, but instead relies on some business logic layer to enforce them, then I have to consider w…
Sadly, you remind me the DBA-is-everything system design style. In modern system designs, a system need more than just a database system to store business states, and to encapsulate business logic into higher layer is not just have flexibility also have scalability. Take sometime to think about the following three scenarios: Scenario 1: what if a system need to migrate to different database system, then the whole bus…
> Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL.
If it takes you more than a few minutes to rewrite a foreign key constraint to work on a different target database then you're doing something wrong.
> Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works.
You're right, it doesn't - you can't use a foreign key constraint there. That doesn't mean you abandon them altogether, just use them where they work - in your database.
> Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue.
Then use a message queue. Still no reason to give up on the declarative integrity checking of FK constraints inside your database.
No idea what you;re talking about wrt unit tests - perhaps you're saying you want to allow bad data into your database so that you can run your tests? In which case here's a better idea - use FK constraints to never let it in in the first place, and don't bother testing, since the DB vendor already did it for you.
On the other hand, many NoSQL databases like MongoDB and RethinkDB have a query language which was designed to run on both single-machines and distributed infrastructure (in a homogeneous way); the same queries which work on a single machine will also work at scale on multiple machines - No need to rewrite your queries as your app grows.
You CAN scale with SQL but you have to know what queries to avoid (E.g. table joins, nested queries...) but with NoSQL, you don't have to avoid any queries; if it's in the Docs, it's safe to use.
Finally, a major difference between SQL vs NoSQL is the typed vs untyped structure. Most SQL databases were designed in a time when statically typed languages were mainstream; so it made sense for SQL databases to enforce static typing on their data.
On the other hand, NoSQL was designed in a time when dynamically typed languages where popular and gaining more popularity (E.g. Ruby, Python, JavaScript); when using these languages, having to add SQL-specific types to data feels like an unnecessary step. With NoSQL you can still enforce a schema in the application code but your schema logic doesn't have to abide by any type constraints from DB layer - Your schema is the ultimate authority on typing of your DB - If gives you the flexibility to be lazy with type-checking in the areas which are low-importance (where errors are tolerable) and strict where data type consistency is paramount.
Generally, NoSQL DBs impose constraints to query expressiveness in order to free you from architectural constraints. SQL DBs impose few constraints on query expressiveness but because of this, they add architectural constraints to your system.
So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…
This is a key/value store inside an RDBMS that just works, and it works great!
I converted a crappy sloppy super messy 1000+ column main table in a ~800GB database to use hstore, it was, in real world benchmarks, between 7x and 10,000x (yes, really, ten thousand times) faster.
The CEO of the company who had a technical say in everything, and was very proud of his schema "wasn't excited" and it never happened in any production instance.
I've left since then, and the company has made very little advancement, especially when it comes to their database.
Really, just use hstore. Try it out. The syntax is goofy, but... I mean, SQL itself is a little bit goofy, right?
Earlier quoted context omitted.
>> Locks limit access to the table, so on a high throughput use case it may limit our performance. > Then use a proper database that implements MVCC. InnoDB does implement MVCC. MVCC is not a silver bullet. >> Do not use transactions, which introduce locks. Instead, use applicative transactions. > Or just use a database that handle transactions more efficiently. Easy to say, hard to implement at this scale. If you do…
> But we are talking about performance... Having something in a single table that is denormalized is always going to be faster than having an elegant data model with "Everything In It's Right Place" Unless you specify the workload, that's anywhere between completely true and exactly incorrect. Do you have big values you're always interested in and a couple of tiny ids? That's probably going to be faster in one table.…
That said, use a system that's a good match for your data model(s)... if you're data can fit in a handful of collections, but may have varying object shapes/structure for semi-related data a document store may work better. Need massive scale, C* may be your best bet. There are use cases that are great fits for just about every database server that's been made. Some similar options may be a better fit, but it all depends.
Personally, I'm hoping to see in the box replication options for PostgreSQL in the next few releases, then it will probably be my go to option for most stuff. RethinkDB is pretty damned nice too, and you should be able to scale to many millions of users.
Once you hit certain scales, you usually have to implement a number of solutions... be they sharding, caching or queuing to deal with higher load. It depends on where your bottlenecks are.
100M? Of course you'd scale an RDBMS for that, especially if you want searchability and analytics. It's way easier than a Hadoop -> Elasticsearch pipeline (or pick your flavor). NoSQL databases are for BIG data. As in, billions of rows big.
Replicating the data to mongodb at the time, with a thin API for search and read queries, and omg, it was a staggering difference. Beyond just caching, all the search queries. Today, I'd be more inclined to use ElasticSearch (there was an issue with geo indexing at the time iirc)... just the same, it really depends on the shape of your data.
I feel that the storage shape should serve the needs of the application. SQL databases encourage normalization to such a degree, that it's costly to construct a semi-complex object in memory, especially when your queries will run across many tables for many thousands of users. Joins kill performance at scale... If you can get around that, you're often better off.
Duplicating data to a system that is a better fit for mostly-read/query scenarios is a good idea. There's nothing that says you can't have your data in two places, and it's easy enough to setup services that copy on update.
> When someone clicks a link to a Wix site... That server has to resolve the requested site from the site address by performing a key/value lookup URL to a site. So Wix uses MySQL to resolve site routes internally? Is this the best way to do it? Would it be possible to use internal domain names and rely on DNS to resolve everything?
So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…
>> `site_id` varchar(50) NOT NULL, > Why varchar(50)? UUIDs are 16-bytes. Why do you think it's a UUID? > The best way to store them would be the binary bytes (which is how postgres stores them). Is it actually better than a pair of BIGINTs?
Because of this:
>Also notice that we are not using serial keys; instead, we are using varchar(50), which stores client-generated GUID values