User talk:Mak77

Places Database Splitting Brainstorming

• Ideas:
  • Split places.sqlite into history.sqlite and bookmarks.sqlite
  • Use a SYNC=OFF connection on history.sqlite
  • Use a SYNC=FULL connection on bookmarks.sqlite

• Needs:
  • Sqlite support for different sync on attached databases
  • History backup system

• Pros:
  • no fsyncs for history
  • faster history update/write queries
  • restore the old "delete history by deleting history file" behaviour
  • 2 smaller dbs instead of a huge one (easier/faster vacuuming)
  • possibility to regenerate table schema fixing bogus column name
  • possibility to prepare a better DB schema for future changes

• Cons:
  • history can be lost in case of an OS crash or power failure
  • migration will require time to move all data
  • in case of history lost, we lose icons, pageAnnos, frecencies, inputhistory
  • deleting a history item would not delete it from history backups

• Solutions:
  • backup history, hold backup copies of history.sqlite, or save a json backup containing the 100 (or more) more frecent pages
  • a progressbar on migration would probably make this less irritating
  • when doing the backup save all relevant data for the backed-up uris
  • not an easy one, unless the user understands we are restoring an old history

The basic idea is to change the PRIMARY KEY we are actually using in moz_places table, practically now we have TWO primary keys, the real one is "id", but also "url" is a UNIQUE column that could be used as a primary key. So changing the primary key to be "url" would allow us to easily split bookmarks related tables to a different database, replacing "place_id" columns with "url" columns and joining where our_table.url = moz_places.url. Ideally we could hold both keys, since adding an url table will increase the table size, we could continue joining on "place_id" inside history.sqlite, while using "url" inside bookmarks.sqlite.

Actually this change would also make bookmarks/keywords/tags checks against and url much faster since we would not need anymore to join with moz_places (url would be also inside moz_bookmarks)

The migration patch would allow us to regenerate all tables, fixing bogus column names and adding other columns we need from some time like last_visit and referer. Also regenerating dbs would make a VACUUM, that is surely a nice thing to do at this time.