After careful consideration, and quite a bit of testing and experimentation, we’ve decided to go with a RDBMS, specifically PostgreSQL, as our central database server engine for our upcoming game title, Arcanoria Revelations. This blog post recaps some of the issues we considered in making this decision. We hope that our research will be of interest to other developers.
Choosing a Database Model: Relational vs. Document Stores
When selecting a database engine for your application, you’ll first need to consider what type of data modeling you’ll need. Two of the most popular choices in recent years are RDBMS (relational) and NoSQL (document stores).
RDBMSs (Relational DataBase Management Systems) are the traditional “standard” structured type of database. They use indexed tables to store data, and your application can “query” that data using a Structured Query Language (SQL). SQL queries are used to add, retrieve, or modify the data in those tables, as well as the tables and indices themselves. Examples of popular RDBMSs include PostgreSQL, MySQL, SQLite, Microsoft SQL Server (MS-SQL), Oracle, etc. For applications that have highly structured data, it’s usually best to choose a RDBMS.
If you prefer to work in a more ad-hoc way with your data, without so many pesky rules about data types or required values, you may want to consider a free-form, document-store, NoSQL solution, like MongoDB, LiteDB, or others. Here is some background information about Document Stores.
Making this choice means thinking about the kinds of data you need to manage. Is it easy to think of your data in tables or spreadsheet format, or is it less structured? Here are some insights on your choice: http://www.zdnet.com/article/rdbms-vs-nosql-how-do-you-pick/.
If you decide to use a NoSQL document store then the remainder of this blog post will not apply to you. However, if you’ve chosen to use a RDBMS to hold your data (as I have), then please read on!
Indie Game Developer’s Perspective
As an Indie game developer, I’m on a very low budget. I need to economize wherever I can, but at the same time, I also want to be able to find support when I need it. That means choosing tools that are popular and have large community adoption. This is one of the reasons why we’ve chosen Unity for our game development engine. Choosing the best RDBMS for our project is no different.
Given our low budget, we can pretty much rule out the more expensive options like Oracle and MS-SQL. SQLite offers free licensing, but it’s probably not your best choice for a multi-user application (see the SQLite section below for more details). That leaves us looking at the two most popular remaining choices: PostgreSQL and MySQL.
PostgreSQL vs. MySQL – Major Similarities and Differences:
Tools
Both PostgreSQL and MySQL have excellent management tools, including free and paid ones – https://en.wikipedia.org/wiki/Comparison_of_database_tools
Adoption
Both are used by many large companies and projects – https://db-engines.com/en/ranking
Licensing
- PostgreSQL has a liberal open-source, BSD/MIT type license – https://www.postgresql.org/about/licence/. It is free to use for any purpose, including commercial.
- MySQL is free for open source projects that conform to the GPL, but for closed-source projects, MySQL requires the purchase of a proprietary commercial license from Oracle that will cost thousands of dollars per year, even when you host it yourself. https://www.mysql.com/about/legal/licensing/oem/
Compliance to Established ANSI SQL Standards
- PostgreSQL is known for its high level of compliance to the standards
- MySQL has a more proprietary dialect, but still mostly compliant
- https://dba.stackexchange.com/questions/110562/which-dbms-are-more-standard-compliant
Common Perceptions
- PostgreSQL is often seen as a “more advanced” and “more reliable” RDBMS
- MySQL is often thought of as “faster”, particularly when using their InnoDB engine
- It’s likely that the above are nothing more than perceptions, mostly based on out of date information, and the two systems are technically comparable at this point. In recent versions, PostgreSQL has gotten faster, and MySQL has become more reliable. Which one is “better” is largely a matter of opinion and preference in most cases.
Why not use SQLite for my Main Application Database?
SQLite is a free, reliable, fast, server-less (file-based) RDBMS. This means that it’s run in-process by a single application, and not made to handle the many concurrent reads and writes of a typical multi-user, client-server application’s central database. SQLite may be a fantastic choice if you’re developing a single-player game. But we’re making a multi-user game.
That being said, SQLite is still an excellent solution for managing a multi-user application’s secondary data that can be stored locally, on the user’s device. This includes things like game content caches, user configuration/preferences, application temporary update/patch aggregation, and more. We’ll be using SQLite for those things!
Conclusions
PostgreSQL and MySQL are similar in many ways, however, MySQL remains more popular by far… about 3 or 4 times more popular. This could be for many reasons, but certainly one major factor is that MySQL is promoted by Oracle as a commercial product. That all being said, PostgreSQL is gaining ground lately in terms of adoption!
PostgreSQL, by comparison, has no significant marketing budget or sales team. It’s free! This one simple fact is enough for me to decide to use PostgreSQL for my indie game development projects, over MySQL.
Some outside references for reading:
- http://insights.dice.com/2015/12/03/choosing-postgresql-over-mysql-mariadb/
- https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
- https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL
- https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
So... what do you think?