Comparing Relational Database Management Systems

One of our most basic, yet powerful tools as a developer is the ability to create, parse, and manipulate databases. Without building these databases, our data would not persist, and we would only be able to access that data during that single instance of runtime in our app. Without even thinking critically, a web developer would immediately see why this would be an issue: our users expect their experience from their last visit to carry over to their next. Having an understanding of that made me start to wonder about the tools that we use to do it, and the advantages and disadvantages of the three I’ve used so far: MySQL, SQLite3, and Postgresql. Below, I’m mostly going to highlight certain attributes you’d be looking for, which RDBMS you’d probably use, and why you wouldn’t use the others.

Memory performance

Although not really an issue when developing locally, a developer must take into account that they have very little control over the actual hardware that their app is being run on. To alleviate that issue, you could look at the RDBMS you’re using and see how much memory processes are using. If you’re really concerned you can sacrifice a lot of functionality for memory performance by using SQLite3, but you’d be limited to one process at a time, as well as by the datatypes you can process. MySQL would be a good middle-ground, as it accepts a large amount of datatypes, and Postgres’ 10MB per process is very memory intensive

Data Storage

The more we use the internet, the more data we create. The more data we create, the more data we store. SQLite3 allows for 140TB of data storage, but only technically, as volumes larger than 1TB are required to be remote. Its a very basic RDBMS mainly intended for you to work with volumes on disk. MySQL and Postgres are both scalable in different manners, but Postgres ends up having an advantage because of something I’ll highlight next.

Concurrency of Operations

As stated before in the paragraph about memory performance, SQLite3 has *NO* concurrent processes. This means everything is executed one after another. MySQL can be used for concurrent Read operations, but starts break down when users are constantly Read-Writing to its database. Here we would use Postgres, mainly because it allows us to run concurrent processes smoothly (at the expense of memory, of course). MySQL would be a better idea for something less interactive, and SQLite3 is better suited for applications you’ll only be using on your local machine.

Use Cases

I’ve tried to think of a few scenarios and which RDBMS I’d end up using in each, so here we go! SQLite3 I would likely use for something that would have very little relational data, where I know I’d be the only user, and I am mostly committing to the data I’m inputting. MySQL I would use more for an app that would display objects to a user, with that user being able to click around and browse, but not edit or add any data. Postgresql would be better suited for an app that relies mostly on user inputted data to work, and where users can edit each other’s data. Personally, I’m still wrapping my head around the features of Postgres after moving on from MySQL, but the added interactivity and ease of use (minus having to manually ‘create’ a db upon spinning up a new app) have kind of sold me so far.