Today’s guest author, Daniel Perano, is a Full-Stack Developer & Founder of MyWorld. He is an extraordinarily kind and insightful person, send him a note on twitter if you want to ask him a question directly. Also, if you’d like to guest author a blog about your project please reach out on our community slack channel.
* Editor’s note: this blog was originally published in 2020. We’re refreshing it today in light of recent conversations that have come up around the capabilties of Cassandra as they compare to CockroachDB.
MyWorld is a next-generation virtual world startup. Current social virtual worlds (like Second Life and OpenSimulator) are built on decades-old technology and are fundamentally limited in their design. MMOG (Massive Multiplayer Online Game) developers lack a common, extensible platform, meaning that multiple years of work and millions of dollars are required to build a custom engine for almost every MMOG - forcing indie studios and many other small developers out of the market entirely. At MyWorld, we’re building a brand new virtual world platform from the ground up, using modern tools and technologies to create a fast, scalable, and extensible platform to power the next generation of social virtual worlds and massive multi-player online games.
To accomplish this, we need a database that can:
In addition, we want a database that follows our own philosophy of making software that self-maintains as much as possible and requires minimal human interaction to operate. Lastly, we need any database we use to be open-source or open-core - this openness is central to MyWorld’s heritage and design philosophy, and since MyWorld will be open-sourced at some point after the alpha and beta releases we could not consider closed-source or vendor-locked databases.
Under the hood, our tech stack builds heavily on a combination of in-house code and some of the best open-source software in the industry. The database layer is based on a lightweight ORM pattern - the Postgres JDBC driver, a HikariCP Connection pool, and the jNimble ORM with a thin layer on top of it to map our domain objects to/from SQL (all queries are handwritten for design flexibility and performance - we don’t use the automatic mapping features of jNimble right now). At a higher level, our physics engine is Bullet and we use the jMonkeyEngine game engine both server side and client side. The world simulation is handled through an in-house entity system, and scripting is done in a powerful high-level in-house scripting language (object oriented with classes, first-class functions, dynamic typing, and polymorphism). We use the Jetty HTTP server for our embedded HTTP needs, and the client-side UI is done with JavaFX.
Prior to porting our database backend to use CockroachDB, we were using Cassandra. Initially, this went well, as our data model has always been designed to be as simple and straightforward as possible in terms of database representation. However, as we grew and discovered more and more query needs, Cassandra’s denormalization patterns and lack of flexible indexing began to be a real and very noticeable constraint, especially since its severely limited range queries meant that implementing our own higher-level indices on top of its out-of-the-box functionality would be a very difficult endeavor.
We held out on Cassandra for as long as we could - it’s a very fast and scalable database, and it’s been tried and proven at a scale unlike any other (Apple’s cluster surpassed 75k active nodes). True horizontal scalability and the ability to keep operating with nodes down is critical for us. However, the more we ran into Cassandra’s limitations on our data model, the more aware we became of Cassandra’s impact on our design:
Using Cassandra was unduly influencing the model, restricting our higher-level design choices, and forcing us to maintain certain areas of data consistency at the application level instead of in the database. Some design trade-offs always have to be made in a distributed environment, but Cassandra was influencing higher-level design choices in ways a database shouldn’t.
Cassandra is outstanding, but our needs were simply outgrowing what Cassandra was designed to handle. Additionally, while Cassandra’s “ccm” tool worked well for managing local-only database clusters in development, we had growing concerns about the level of operational overhead Cassandra might require in production.
Because of our investment in Cassandra and our concerns about performance trade-offs, we were slow to initially evaluate CockroachDB. Once we tried porting some of our database code and experimenting with the areas that had been difficult with Cassandra, however, the decision almost made itself. We could model our data whichever way suited each area best, and if we needed, say, a range query over several unrelated numeric columns (which is very difficult to do efficiently in Cassandra), we could always add an index and more or less forget about it.
Switching to SQL meant that we could model our data the way it ought to be modeled and rely on full multi-table transactions and SQL constraints to maintain data integrity. The migration from Cassandra to SQL was surprisingly painless - our data model was already in a strongly-typed tabular form, and since we had opted to write our queries as handwritten CQL (Cassandra Query Language, which is essentially a subset of SQL) instead of using an ORM, we only had to make minimal syntactic and type changes to existing queries in order to have a functional starting point running with CockroachDB.
Choosing SQL meant gaining flexible indices, integrity constraints, full-featured transactions, and the ability to efficiently model our data with far more flexibility than Cassandra allowed. Choosing CockroachDB in particular meant that we could have the power of SQL, make only minimal performance trade-offs (and more importantly, have the ability to make trade-offs in ways that Cassandra just couldn’t support), make effectively no compromises on the scale of traffic and data we could handle, and operate with little human intervention.
CockroachDB’s compatibility with Postgres was another big plus for us - that meant developing our code with tried-and-proven Postgres drivers & libraries, and in the worst case, the ability to switch to Postgres with little to no code modifications (albeit with a punishing hit to scalability) should something go catastrophically wrong with CockroachDB.
Last but not least, CockroachDB just works in development. When I first tried it out I tossed together a bare-bones 3 line shell script to start a 3 node cluster.
The initial installation and setup only took about 30 minutes, including fiddling with the clustering parameters and the built-in web dashboard. This shocked me - this just doesn’t happen with databases, especially in a clustered configuration.
You don’t download the software and then have a 3 node cluster up and running just like that. After I got an initial configuration that I liked, I’ve never had to spend any time fixing anything - it just doesn’t break. Cassandra’s “ccm” tool was also very effective, but every few months during a version upgrade (or just plain bad luck) something would go wrong and I would spend hours just getting the database cluster back up on my development machine. That hasn’t happened once with CockroachDB. CockroachDB’s trivial ability to adapt to both server and local development environments is a tremendous benefit for us.
We have not yet launched the MyWorld platform to the public. Right now, we’re running a lightweight 3 node cluster on a dev machine. We anticipate adoption around the globe, which means we’ll need to geo-distribute our clusters to keep latencies down for users and to comply with the GDPR and other data localization regulations worldwide. While we’re committed to gathering only as much personal user data as is critical for operation (such as email addresses for accounts and IP address logs for intrusion detection), the data we do collect may be subject to data locality laws - so we’ll likely need CockroachDB’s data locality capabilities to keep our users’ data in the countries where it belongs.
If you’re evaluating CockroachDB for your own product, I’d highly recommend just downloading it and running a few queries from the command line - you’ll quickly get a good feel for how it handles in development. [Editor’s note: CockroachDB Dedicated is another option for getting up and running quickly–it’s our fully managed and hosted database-as-a-service. Get started here.] If you have an existing codebase that you’re considering porting, take your time to go through the migration guides and the feature support page - I’ve found the documentation to be comprehensive and clear about what’s supported, what’s not, and any caveats you should be aware of.
*2022 Author Update
MyWorld is coming along well - slower at times than I’d like due to time constraints, but progressing well nonetheless. In the last few months we’ve rolled out a rebuilt asset/content delivery system that eliminates the potential for a security vulnerability that’s been present in Second Life for decades, we’ve released functional rigid body physics, we’re working on animation support, and the script language & engine have matured dramatically. There is plenty more to write about so I’ll have to block time for another blog soon. CockroachDB continues to be a rock-solid performer for us, and following a refactoring of our DB access code into the open-source Spruce database access library, I find myself enjoying DB interface code far more than I ever did before.
What’s the best way to do a database migration?
It’s a challenging question. No single approach is going to be the best …Read More