Why does Firefox use Sqlite?

Apparently the latest version of Firefox has a problem with Linux: http://shaver.off.net/ explains the details, but the short of it is that Firefox 3 uses Sqlite for bookmarks and history, Sqlite in turn uses fsync() to ensure data integrity, and Linux fsync writes ALL disk buffers when fsync() is called and of course that can lead to long, long pauses where your computer is completely non-responsive. The issue will be fixed; again, that post has the details, but I have a more basic question:

Why on earth does Firefox need a database for bookmarks and history?

Am I nuts? Am I the only person who can't imagine why Joe Firefox User could possibly have enough bookmarks or browsing history that you need a database to manage the data?

Sheesh: I have a lot of bookmarks - too many, in fact and I need to clean them out. I have plenty of browsing history, too, but it's simply inconceivable to me that anyone would think they'd need anything but simple text files for this. I've written about this before at Text vs. Binary Data formats and I revisited that today with yet another test. This time I created a million line text file with random lines of 47 characters. That's 47 million bytes - I would thank that's more than enough to store my bookmarks and history. The code I used was just this:

#!/usr/bin/perl
$x=0;
while ($x < 1000000) {
 $y=rand(100000);
 printf "%0.6d Padding representing stored data %0.6d\n", $y,$x;
 $x++; 
}
 

I redirected that to a file and then searched it using the same horribly inefficient code I used the last time I griped about this foolishness:

#!/usr/bin/perl
$found=0;
$now=time();
$fnow=$now;
while (@ARGV) {
 $found=0;
 $x=shift @ARGV;
 open(O, "foop");
 while (<O>) {
   if (/^$x / ) {
     $found++;
   }
 }
$now=time();
$elapsed=$now - $fnow;
print "Found $found matches in $elapsed seconds \n"; 
}
 

Remember: that's a horrible search. We're not presorting, we haven't built any sort of auxiliary index to help us out, and we're using an interpreted language and not even optimizing that. Yet that silly code can find anything you want in that 47 million byte file in less than a second. We could cut that time by 30% just by changing "if (/^$x / ) { " to "if (/^$x /o ) { ", but my point here is not to write better Perl code - it's to show that flat text access is NOT slow on modern hardware. Heck, plain old "grep" can zip through that file like lightning:

$ time grep ^017581 foop
017581 Padding representing stored data 301404
017581 Padding representing stored data 346784
017581 Padding representing stored data 366993
017581 Padding representing stored data 402525
017581 Padding representing stored data 453018
017581 Padding representing stored data 510494
017581 Padding representing stored data 562309
017581 Padding representing stored data 705912
017581 Padding representing stored data 838930
017581 Padding representing stored data 888413

real	0m0.105s
user	0m0.047s
sys	0m0.036s
 

The Firefox developers seem rather adamant that they do need a database - in response to one user who questioned this as I do, they responded:

Browser history needs to be pretty fast, since it's consulted for link colouring and CSS rule values; we have to be very careful whenever we touch history because it can easily lead to performance regressions. You probably know that from your own work on browsers. The speed of even just URL autocomplete in FF2 (no multi-word, no adaptive, no title searches, no tags, no bookmark inclusion) was a common source of complaints for users with large histories, and we are very glad to be rid of it. I love that you think that bookmarks are a bigger performance issue than browsing history; just the smile I needed today!

OK, maybe I'm wrong. Maybe Joe Average Firefox User has millions of bookmarks and keeps his history for years on end. Maybe. That's hard for me to imagine, though.. I think this might be just yet another case of people not really thinking about the tools they need to use. By the way, I DETEST autocomplete..

Yet they insist:

Browser history, as used by Firefox 3, is exactly a large set of data that needs to be heavily indexed and queried in a number of different ways. Earlier designers weren't doing as much with history, and users didn't have as much of it; likewise with bookmarks.

Well, OK.. but then again, does history really NEED to be indexed six ways from Sunday? I don't need that, but if most users want it, and really do keep their history so long that Firefox needs a database.. well, there it is, right?

I'd rather use Google.

Click here to add your comments




Sat May 31 07:06:00 2008: Subject:   drag


I'd rather just use epiphany with webkit support. :)
I've always believed that web browsers should just be web browsers. If you want some advanced functionality then it should be done as a extension to your browser so that other people who don't want/need that feature don't have to deal with the decrease in performance and security that is a side effect of code bloat.





Sun Jul 6 11:59:19 2008: Subject: Full ack   anonymous


Congratulations for the harsh critique on this sqlite stuff in FF3. I just stumbled over it because I am keeping all bookmarks in a plain html file in a SVN repository (as well as the rest of the FF config) because I am using serval different machines and want the same config on all of them.

Binary formats for configuations are a misconcept -- it is as plain as that!

Michael,
who *never* used the FF history



Mon Oct 27 16:57:11 2008: Subject:   anonymous


I totally agree with your post. Moreover, there should be an option to use databases or text files. Additionally, bookmarks should be independent of other features such as history, etc. Many users like Firefox because they can be in control of basic features, files, etc; this does not follow the trend and the essential and basic features get hidden behind complex engines which are alien to the average user. The bridge between the browser and the user is getting destroyed as new versions come.

This reminds me of the registry appearance under NT. While it is justifiable for an OS, it is not for for a browser.

Marty



Tue Feb 17 22:08:14 2009: Subject: Agreed.   Lns
http://logicalnetworking.net

I can see both sides of the table, but it frustrates me that, if the FF devs went so far as to implement ANY sql backend for things like history/bookmarks/homepage/etc., that they didn't make it PORTABLE toward other SQL backends. Sure, sqlite is serverless, very fast, runs on cellphones, blah blah blah...but what about large network installations? I administrate a few LTSP networks, where all users live on a single server. I would *LOVE* to be able to globally manipulate users' bookmarks, homepages, etc. in my favorite SQL db editor. This is a huge benefit, but alas...sqlite is set to 'exclusive' mode in FF, thus can't be touched by anything else while it's in use. This makes it in no way useful for multi-user installations. In addition, I was at LEAST able to give people global bookmarks via a hack-ish script that copied bookmarks.html into users' ~/.mozilla profile dirs, but since sqlite took THAT over too, it became obsolete. The SQLITE decision hasn't done much but limit my own administration abilities in multi-user environments. It'd be nice to separate the SQL from SQLITE and allow choice for different setups.



Tue Feb 17 22:16:08 2009: Subject:   TonyLawrence


Ayup - it's always dumb to lock up data that could be useful elsewhere. I suppose users might protest that they don't WANT their bookmarks made available, but that's not a decision developers should make.





Sat Mar 7 14:51:02 2009: Subject: I strongly disagree   henri


The only argument I can understand is that old style bookmarks made it easy for an admin to push bookmarks onto his/her userbase (but I'm pretty sure there still is a way with sqlite.)
Myself, like many others, stopped using the bookmarks menus and use the awesome bar, stopped storing bookmarks into folders (or only do it in rare cases) and instead use tags.

My typical way of browsing would be to type a few keywords in the url bar and scan the results until the page I'm looking for appears (these results come from both the history and the tagged bookmarks). To work smoothly this has to be FAST. This I do maybe 100 times a day, and there is absolutely no way suitable speed could be obtained without a really efficient way to scan the bookmarks db. This could have been done while still retaining the bookmarks.html, but I just don't see the point in practice.

To Michael: If you *never* used your history, then I'm pretty sure you are not using your browser in the most efficient manner :) After a few days I just got rid of my bookmarks toolbar and forgot about it. Read about the awesome bar.




Sat Mar 7 16:09:02 2009: Subject:   TonyLawrence


"there is absolutely no way suitable speed could be obtained without a really efficient way to scan the bookmarks"

That's the point. A database is NOT an efficient way to scan small data sets.

---

Macworld Mac Security Superguide $9.99

---

Sun Apr 12 18:55:59 2009: Subject: Clarity and maintainability suffers....   StephenBungay


I ran full speed into this idiocy today and Firefox is NO LONGER my browser and I will NOT be recommending it in future.

Yes, one CAN use a database for bookmarks, the question is SHOULD it be done, and there better be a damn good reason to justify the need to replace a simple effective and eaisly maintainable system with a complex, effective, not eaisly maintainable system.

Never use a complex system where a simple system will do the job.




Sun Apr 12 21:02:51 2009: Subject:   TonyLawrence


The more often this stupid "awesome bar" tries sending me to places I don't want to go, the more I hate it.

If I start typing "bar", I want to go somewhere that BEGINS with those letters, not somewhere that has them somewhere in the middle or end. I *definitely* don't want a page that has "bar" in its title.

The "awesome bar" is the "I'm an idiot" bar.

I'm not ready to quit Firefox yet, but it definitely is ticking me off. If Safari or Chrome could restore my sessions, I might just leave..



Sat Apr 18 15:30:42 2009: Subject:   TonyLawrence


OK, I've had it with this stupid Awful Bar.

Disable it with about:config and set browser.urlbar.maxRichResults to 0

This morning I wanted to go to Macosxhints.com

Of course it immediately brings up the hundreds of other times I've been to internal pages there, when all I want is just the plain home page. So I keep typing but then I accidentally hit a bad letter: "macosxho" instead of "macosxhi" - and of course THAT sends the Stupid Bar off searching for something that has "macosxho" in it which locks me up for a few seconds until the idiot thing gives up and I can back space.

Disabled. Moron idea. "Nuff said.



Fri May 1 11:33:00 2009: Subject:   TonyLawrence


I found that a happier setting is to put browser.urlbar.maxRichResults at 6. That's been enough to find what I want easily without slowing down searching through thousands of matches.

But Firefox woes continue. I run no addons (even took out NoScript), no extra toolbars, yet it still freezes now and then. It doesn't freeze as often, and it's apparent that NoScript was the worst offender. But yesterday it froze and then would not start, not with "Previous session" or new. I threw away Preferences, no luck. I tried starting it with " -safe-mode" - still would not start.

I threw it in the trash, d/l'd a new copy wth Safari and it was fine.

Firefox on Mac definitely still has issues.



Sun Jun 14 08:59:17 2009: Subject:   dgutson


Firefox now competes with an ATM machine. It's not a banking system. It's just a BROWSER.

I'm seriously considering to develop a spin off without sqlite. There was a --no-places or similar argument for the ./configure, but it was removed.




Sat Jul 18 11:54:50 2009: Subject: Think SQLite as an extensible file format   ChristianSmith


SQLite is not designed to be a heavy database engine, it's more of an extensible file format implementation, that happens to have a SQL interface.

I only wish they'd move the cache, as well as browser history and bookmarks to the database file. That way, all but the current displayed pages can be kept in the database file, reducing the Firefox heap footprint massively (I assume firefox's footprint is so big because of caching.)

Having a SQL interface makes so many things so much easier. Want to search the history using some other variable. Just use a different WHERE clause. No manual coding of searches, sorts, indexing etc. Makes the code more robust, and SQLite keeps the data in the file consistent.



Thu Oct 1 04:16:57 2009: Subject:   anonymous


You guys are too trusting. Firefox stores your information in a database so its primary customers like Google and Amazon can access it. Like Google, FF keeps every bit of data about where you have been. Thats why it needs a a database. I know this because despite the fact that I have the privacy settings all checked to erase everything, Amazon accessed the sqlite files and knew who I was after I closed down FF and then opened it up again. No matter what your privacy settings are, FF keeps the your information in the sqlite files and no doubt the information there is a source of funding for Mozilla. My places.sqlite file is 330K despite the fact I have the "erase history" privacy box checked. Rather sneaky because you think you have erased all your browsing data but you haven't. But then, they are just Google's lackey boys, now aren't they?

---

Take Control of Screen Sharing in Leopard

Take Control of Permissions in Leopard

---

Thu Oct 1 11:06:30 2009: Subject:   TonyLawrence


You are misunderstanding completely.

Amazon knew who you were because of a cookie - and they only knew because you told them in the first place.





Wed Oct 7 23:41:32 2009: Subject: Amazon Cookie   anonymous


You missed the point entirely.

I had all the privacy settings on including "erase cookies." Additionally, when Amazon recognized me when it shouldn't have (because Firefox should have erased all the cookies), I checked. All the cookies were in fact erased from the normal cookie file we all know and love. There was a cookie alright, but it was buried in the sqlite file where most users would not know how to find it.

The point is that Firefox leads you to believe you are erasing cookies when in fact some secret cookies for special friends of Mozilla are buried where you can't see them or erase them (unless your one of the very few people that know where to find them)

Do you understand now?



Wed Oct 7 23:45:49 2009: Subject:   TonyLawrence


OK, that's different - and more disturbing.



Thu Oct 22 03:10:20 2009: Subject:   intuited


I'm no expert but I'm guessing from the complexity of the places.sqlite schema set:

 

$ sqlite3 places.sqlite .schema 

CREATE TABLE moz_anno_attributes (id INTEGER PRIMARY KEY,name VARCHAR(32) UNIQUE NOT NULL); 

CREATE TABLE moz_annos (id INTEGER PRIMARY KEY,place_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0); 

CREATE TABLE moz_bookmarks (id INTEGER PRIMARY KEY,type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER); 

CREATE TABLE moz_bookmarks_roots (root_name VARCHAR(16) UNIQUE, folder_id INTEGER); 

CREATE TABLE moz_favicons (id INTEGER PRIMARY KEY, url LONGVARCHAR UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG); 

CREATE TABLE moz_historyvisits (id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER); 

CREATE TABLE moz_inputhistory (place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input)); 

CREATE TABLE moz_items_annos (id INTEGER PRIMARY KEY,item_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0); 

CREATE TABLE moz_keywords (id INTEGER PRIMARY KEY AUTOINCREMENT, keyword TEXT UNIQUE); 

CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL); 

-- plus a roughly-equal number of lines creating indexes and triggers 

that it would have been significantly more complex, and probably noticeably more lag-inducing, to implement this using plain text files. Running a grep for a single RE on a plain text file is bound to be quick: they've been refining that process for a few decades now. Doing the equivalent of a DB join on multiple text files is likely to be a lot slower and use way more memory unless you've indexed the text files in some sort of "database".

I'm also guessing that one of the main reasons for doing this was to speed up development of what is by far the most popular cross-platform browser. It's no insignificant challenge to develop a browser capable of supporting modern features like, for example, "Internet Banking", that the majority of people using web browsers expect because these are things that modern web browsers do. Keeping stuff in a database not only speeds up complex queries but also provides lots of powerful and scaleable access strategies to busy developers. This means they get to spend less time doing infrastructure development, and more time adding features, fixing bugs and security holes (have you reported this amazon cookie issue to Mozilla?), and planning for the future.

As an added bonus, people comfortable writing SQL queries can use them to get useful data from a detailed body of information that includes every time they visited every URL they've ever visited with that browser profile, optionally adding the specification of bookmark keywords, time visited, total number of visits since your cat had kittens, etc., into the mix. To do that using text files would I think be pretty time consuming for everybody involved. With sqlite3 it works like this:

 

$ # make a copy so it won't be locked 

$ cp ~/.mozilla/firefox/pr0f113h45h.default/places.sqlite . 

 

$ function ffrecent { sqlite3 places.sqlite "SELECT DISTINCT places.url FROM moz_places AS places LEFT JOIN moz_historyvisits AS visits ON visits.place_id = places.id WHERE visits.visit_date > $(date +%s -d "$1")000000 ORDER BY visits.visit_date DESC"; } 

 

# get a count of unique url's that I've visited since the 10th 

$ time ffrecent 2009-10-10 | wc 

   1156    1156  122983 

 

real    0m0.306s 

user    0m0.248s 

sys     0m0.040s 

For those not so comfortable writing SQL but adept in grep wizardry or just patient enough to scan through a long text file, it's less easy to get data out. However I suspect that it's more likely that somebody will have written an extension that does what they want, because it's much easier, and pleasant, and more interesting, to do using the database model of history recording. For example there's an extension called I think xmarks that provides synchronization of bookmarks across different browser profiles, and may do what you(? somebody..) wanted in terms of providing a default set of them to all of a site's users.

As far as using standard industry tools goes, it's certainly possible to convert a sqlite3 dump to one that can be loaded into a different DB, though it might take a bit of googling and/or cash to get it working. There is a bit of an ecosystem of tools developed specifically for working with sqlite3 databases.



Thu Oct 22 04:30:21 2009: Subject:   Intuited


PS I'm not totally sure that that's _exactly_ what that SQL code does; but it does seem to be in compliance with the 80/20 rule.



Thu Oct 22 11:44:55 2009: Subject:   TonyLawrence


I think your examples are way beyond what a browser should be. If somebody wants crap like that, bolt whatever dumb code is needed on top of a simple, "do what needs to be done and only what needs to be done" browser.

Wasn't that the whole POINT of extensions?




Thu Oct 22 12:11:27 2009: Subject:   intuited


I see where you're coming from... that's why I use elinks for most of my web browsing. But I do find it useful, actually in particular for web development, to be able to type a word into the address bar and have it quickly give me popular results for that keyword. This is great for hopping around in Drupal without having to go through the Admin page every time.
Keeping things simple has its benefits, but when those added levels of intelligence are helpful to, or even required by, the common web surfer, it becomes a lot more complicated or inefficient to add them into each extension that wants to use them... you would either end up with a bunch of different extensions all implementing similar functionality in different ways, or some sort of extension dependency system. While this last would be Awesome, it would likely end up being a lot more complicated to implement than just building more stuff into the browser core, especially when in doing so you can take advantage of a standard database system that's readily available. It also helps avoid having the end result be a mess of hacks of hacks.
Presumably other people use this functionality too, or at least that's Mozilla's opinion on the matter. Personally even if I didn't use advanced history queries at all I'd still rather that it be in there, if only to prevent Firefox from being slowly obsoleted by more capable browsers, and not having a standard that you can get on whichever platform.

---

Take Control of Your iPhone Apps

---

Thu Oct 22 12:13:53 2009: Subject:   TonyLawrence


I think your usage of elinks proves my point :-)



Thu Oct 22 12:34:54 2009: Subject:   intuited


For that to be true... I would have to be everyone 8'>



Thu Nov 19 20:46:42 2009: Subject:   anonymous


Developers know better how to implement software. I can't understand why users keep arguing with developers about such inner workings.



Thu Nov 19 21:38:59 2009: Subject:   TonyLawrence


I'm not exactly a "user".

But even if I were, this is a bad case of feature bloat and an example of applying the wrong technology to a perceived need. The need doesn't exist for most users and the badly applied technology interferes with use.









Don't miss responses! Subscribe to Comments by RSS or by Email

Click here to add your comments

If you want a picture to show with your comment, go get a Gravatar

Jump to Comments

Many of the products and books I review are things I purchased for my own use. Some were given to me specifically for the purpose of reviewing them. I resell or can earn commissions from the sale of some of these items. Links within these pages may be affiliate links that pay me for referring you to them. That's mostly insignificant amounts of money; whenever it is not I have made my relationship plain. I also may own stock in companies mentioned here. If you have any question, please do feel free to contact me.

Specific links that take you to pages that allow you to purchase the item I reviewed are very likely to pay me a commission. Many of the books I review were given to me by the publishers specifically for the purpose of writing a review. These gifts and referral fees do not affect my opinions; I often give bad reviews anyway.

We use Google third-party advertising companies to serve ads when you visit our website. These companies may use information (not including your name, address, email address, or telephone number) about your visits to this and other websites in order to provide advertisements about goods and services of interest to you. If you would like more information about this practice and to know your choices about not having this information used by these companies, click here.