Not signed in (Sign In)

Can Someone Help Me...: Sqlite3 in production

Bottom of Page

1 to 8 of 8

  1.  
    •  
      CommentAuthorprionux
    • CommentTimeFeb 2nd 2008
     permalink

    Hello everyone,

    I’m wrapping up development of a web app that allows people to aggregate shipping information from different carriers. During development I have been sqlite3 for my app’s database and I was wondering if anybody has had experience using sqlite in a production setting. I’m on a 256 slice and so I obviously would like to run as thin as possible. The sqlite website claims that the database is robust enough to handle queries for “low volume” websites. I quote:

    SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than a 100000 hits/day should work fine with SQLite. The 100000 hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

    My app is using the Django framework and the only other things that I am hosting on my slice are a few other low-volume static sites. I have considered using Postres but I think it my be a little overkill just starting out. Not interested in MySQL because of all the commotion that has been occurring on that front.

    So, what do you guys think?

    •  
      CommentAuthorsime
    • CommentTimeFeb 2nd 2008
     permalink

    Sure thing, go ahead!

    SQLite is seriously underestimated and everybody is assuming MySQL or PostgreSQL from the start—rarely anybody ever thinks or speaks about using SQLite in production. Personally, I always just used SQLite for all of my sites. Granted, they were not that high volume, but it doesn’t matter much—if you code responsibly, using caching, optimizing etc., there shouldn’t be a problem in most cases. And in some, SQLite even outperforms MySQL and other big boys. Source code is clean and extremely portable, it’s tiny, SQL standard is covered pretty good, it’s just a kick ass database that should be used far, far more often. Yeah, it is widely deployed, but much of that goes to the desktop (Mac OS X and its apps are using SQLite extensively), embedded devices, specialized use etc… I would love to see it deployed on the web more. There are also some cases where its use isn’t the most appropriate, database locking and that stuff…

    And don’t even get me started about no persistent server process, just a regular file, no overhead, simple use, be it with Python (built in), PHP (built in, PDO) etc…

    BTW, since you are using Django, switching databases (if there is a need to scale to PostgreSQL) is literally a snap, so what are you waiting for?

    •  
      CommentAuthormatt
    • CommentTimeFeb 3rd 2008
     permalink
    I'm a huge sqlite fan. That being said, our blog (running Typo) originally used sqlite and we ended up having some strange problems. The fix was to move to mysql. I don't remember what the problem was (locking related I thought), but it probably could have been fixed if we invested a little time.
  4.  permalink

    I LOVE sqlite, but I've been way too chicken to try it in production. One of these days, I'll have to do some benchmarking and try to figure out what exactly I can get away with, with mighty sqlite.

    • CommentAuthorfactor
    • CommentTimeFeb 3rd 2008
     permalink
    I am running the Django development version using Lighttpd + FastCGI + PostgreSQL on a 256slice running Debian Etch. Runs great. Just tune your allowed number of child processes using manage.py if you run into memory usage problems.
  6.  permalink
    We are also running it in production without any problems. Go for it.
    • CommentAuthorvoxxit
    • CommentTimeDec 4th 2008
     permalink
    I have another question in this regard: locking and Rails. With Rails 2.2, are there any other things people should consider before using SQLite3 in production?
    • CommentAuthorroshambo
    • CommentTimeDec 4th 2008 edited
     permalink
    The most important thing to consider when using SQLite is whether there is a chance of multiple concurrent database writes occurring in your application. Because SQLite databases are literally a single file, only one write operation (UPDATE or INSERT, for example) can occur at a time. When a write operation happens the entire database must literally be locked, written to, and then unlocked. While the database is usually only locked for a very brief moment, it's not uncommon for multiple concurrent writes to occur in many web applications.

    Think of a blog, for example. Because multiple users can comment on a single post, multiple concurrent writes aren't that far-fetched. If your blog does not have commenting capabilities, however, there's likely a small chance that two writes will occur at once (unless you have a fair number of authors).

    So, think about the possible use cases. If there is indeed a chance that two or more users will be writing to the database at a time then I'd avoid SQLite.

    For more on locking and concurrency see: http://www.sqlite.org/lockingv3.html

1 to 8 of 8

  1.