Home | About | Contact

Pick A Database Wisely

---

This week I took on a new client that had begun development of their own Django website, but ran out of internal resources and needed to offload some of the development. Their current site is very static, and has little to no content management behind it, so developing something with Django is a big step up.

One of the first things I noticed was that their current achitecture had SQLite running the backend instead of a more stable database, such as MySQL. It wasn't a production server but it was a live webserver that they intended to make live once the creation of the website was complete.

Using SQLite should be saved for those instance when you don't have a lot of traffic. For instance, development on a personal laptop is a nice use of SQLite. SQLite3 comes with Python 2.5 and above, it's also much lighter than MySQL. Notice I didn't say it's better, it's just lighter.

When I first started developing things in Django for customers, I tried to use SQLite. I figured, hey it's a production quality right? Well, the answer to this is both yes and no. Here's what happened to me. The website I created was basically flat content, meaning it was created posted and read, very little to no interaction at all was done with the content. No voting, commenting, ranking, anything social for that matter. However, there was one little piece that was interactive and that was counting the number of times a user viewed a detail page for a particular asset. What I was doing on the database side was increment the number of times an object was viewed to get a realistic and real time account of what was the most popular item on the website.

This worked great when traffic was slow, but when a video was produced and employees all wanted to see it, the number of site visitors would rise dramatically. Thus the application would need to increment the number by one. So far not a really bad way of doing things, however with SQLite, any database transaction requires the DB file to be locked. So as you can imagine there was a lot of instances where the DB file would lock while traffic was high, which gave the appearance that the site wasn't working properly. However the site was working, it was just to slow because the whole DB needed to be locked when a view was happening.

I did eventually end up moving that client to MySQL and the problem has gone away. Just like this client, I suggested we move away from SQLite and move towards something like MySQL or PostgreSQL. Making these decisions in the beginning can be a life saver, or at the very least a time saver. Using SQLite in development is a fast and resource effective way of getting something going. In production however you must remember that you'll have more people looking at your work and you'll want to make sure everything is running smoothly. This is why in production I think that running something beefy will be more to your liking.

Current Projects

In Production