Best flash file system

A while ago I wanted to get most theoretical flash lifespan out of the following stack

app / sqlite / file system / linux / consumer flash

Background here, to summarize, writes smaller than erase page size put just as much strain on the flash as exactly one erase page. Typical erase page size for 1GB~4GB consumer flash is 64KB or 128KB.

Modern flashes implement some form of wear leveling, yet no consumer flash manufacturer specifies what algorithm they use or whether it works over entire flash or several logical blocks of flash. Some specs are available for industrial flashes, but those are pricey and, it seems, fall behind times, that is consumer flash cards are built on newer tech and ought to yield better results.

The app was modified to (read -- compare -- write if needed) in transaction, and to request possible changes in batches, thus removing unnecessary writes and combining multiple small changes into fewer larger writes.

SQLite is  a practical necessity, I want transaction safety as a guarantee of logical data consistency. SQLite has options though, these were tested. A 100 byte row takes 1KB in the database, the difference is comprised of indices, data structures and metadata.

Linux offers many file systems, some more developed than others, yet most were made for hard disk storage. As my type of flash is seen as a block block device to the system, I cannot use dedicated flash file systems anyway.

Result key:

• test1: update 1000 records 100 at a time
• test2: save 1000 records 1 at a time, delete 1000 records 1 at a time
• --
• total: total KB written
• bins: 128KB-size erase blocks touched
• worst: number of time most used erase sector overwritten
• bins >90% worst: number of erase blocks overwritten >90% of worst

Raw results:

• sqlite std options                against ext3 std options
  total     1288K, bins       15, worst       11, bins >90% worst 3 # whitelist
  total  106836K, bins     266, worst   2001, bins >90% worst 2 # offline
• sqlite page size 64K           against ext3 std options
  total     7740K, bins       26, worst       14, bins >90% worst 2
  total 616132K, bins      267, worst   2046, bins >90% worst 4
• sqlite journal_mode persist  against ext3 std options
  total      992K, bins       11, worst       21, bins >90% worst 1
  total  48016K, bins      118, worst   4004, bins >90% worst 1
• sqlite synchronous off         against ext3 std options    (unsafe)
  total      292K, bins       12, worst        7, bins >90% worst 1
  total    3748K, bins       25, worst       88, bins >90% worst 1
• sqlite write ahead logging    against ext3 std options
  total     7740K, bins       26, worst       11, bins >90% worst 4
  total 616100K, bins      267, worst   2046, bins >90% worst 4
• --
• sqlite std ext3 std (repeated for comparison)
  total     1288K, bins       15, worst       11, bins >90% worst 3 # whitelist
  total  106836K, bins     266, worst   2001, bins >90% worst 2 # offline
• sqlite std ext3 std noatime
  total     1312K, bins       16, worst       11, bins >90% worst 4
  total 106616K, bins      266, worst   2002, bins >90% worst 2
• --
• --
• sqlite best (std; persist)
  total     1288K, bins       15, worst       11, bins >90% worst 3 # std
  total  106836K, bins     266, worst   2001, bins >90% worst 2
  total      992K, bins       11, worst       21, bins >90% worst 1 # persist
  total  48016K, bins      118, worst   4004, bins >90% worst 1
• --
• sqlite std                         against ext4 std options
  total     1280K, bins       18, worst       10, bins >90% worst 2
  total 106768K, bins      296, worst   2000, bins >90% worst 1
• sqlite std,pers               against ext4 ^journal
  total      904K, bins       14, worst       13, bins >90% worst 1 # std
  total   42996K, bins      23, worst    2015, bins >90% worst 4
  total      820K, bins       11, worst       20, bins >90% worst 1 # persist
  total   34508K, bins        7, worst    4000, bins >90% worst 1
• sqlite std,pers,page    against ext4 stride 128K
  total     1284K, bins       19, worst       10, bins >90% worst 3 # std
  total 106852K, bins      266, worst   2000, bins >90% worst 1
  total    1012K, bins       12, worst       20, bins >90% worst 1 # persist
  total   58296K, bins      197, worst   4000, bins >90% worst 1
  total     7648K, bins       29, worst       11, bins >90% worst 2 # page size 64K
  total 600220K, bins      267, worst   2022, bins >90% worst 3
• --
• sqlite std                         against nilfs2
  total 319800K, bins    2500, worst       20, bins >90% worst 1
  total 255528K, bins    1998, worst       18, bins >90% worst 1 # nilfs2 ran out of disk space - it's gc bugs
• sqlite std, persist         against nilfs2 protection period 1 second, other tweaks
  total     2308K, bins       21, worst        3, bins >90% worst 1 #std
  total 319580K, bins    2498, worst      20, bins >90% worst 1
  total     2804K, bins       24, worst        3, bins >90% worst 1
  total 166032K, bins    1300, worst      10, bins >90% worst 1 # incomplete nilfs2 crashed
  total     2592K, bins       23, worst        3, bins >90% worst 9
  total 707968K, bins    5532, worst      27, bins >90% worst 1
• sqlite std                         against btrfs std
  # too raw - ran out of space copying in static image and code
• sqlite std                         against fuse-exfat
  total      116K, bins        9, worst        1, bins >90% worst 9
  # fuse evidently doesn't sync
  total        48K, bins        5, worst        1, bins >90% worst 5