investigate contents of a sqlite3 file I have created a new, clean firefox profile from scratch, and added couple of bookmarks. These, I have learned, are stored in places.sqlite file inside the profile directory. This file seems to be too big (1.2 MB) and I am wondering why:

$ ls -lh places.sqlite 
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite


When I open it with sqlite3 from the commandline, I see there are couple of tables:

$ sqlite3 places.sqlite 
SQLite version 3.7.13 2012-06-11 02:05:22
sqlite&gt; .tables
moz_anno_attributes  moz_favicons         moz_items_annos    
moz_annos            moz_historyvisits    moz_keywords       
moz_bookmarks        moz_hosts            moz_places         
moz_bookmarks_roots  moz_inputhistory   


but none of them contain much data (I have tried SELECT * FROM &lt;TABLE&gt; ; for all of them).

Further, if I dump the entire database into file dump.sqlite:

sqlite&gt; .output dump.sqlite
sqlite&gt; .dump


The file has only couple of KB:

$ ls -lh
total 1.2M
-rw------- 1 martin martin  39K Sep  8 19:01 dump.sqlite
-rw------- 1 martin martin 1.2M Sep  8 07:24 places.sqlite


I am aware of the vacuum command in sqlite3. I did that, but it has no effect. The database file is still same size.

How can I investigate why the database file is so big, and what does it contain ?
I had a file that was around 30 MB, and issued the vacuum full (not the normal VACUUM as it has the same effect that the Postgres behavior) for reducing its size. After the operation the file got sized 22 MB. I'm pasting the stats of each execution, which shows the differences in between (you will see more failures due to size for the VF).

sqlite&gt; vacuum;
Successful lookaside attempts:       3690
Lookaside failures due to size:      913
Lookaside failures due to OOM:       0

sqlite&gt; vacuum full;
Successful lookaside attempts:       6509
Lookaside failures due to size:      1621
Lookaside failures due to OOM:       0


The command .dbinfo will provide some information as the database page size and its count. Database pages are variable as specified in the file format doc page (this link contains all the internal structures). 

For investigating the contents you can use sqlite_stats1 (the link contains the specifications of this table) table, which will be created after the ANALYZE:

analyze;
select * from sqlite_stat1;