On 16 Aug 2014, at 9:32pm, Baruch Burstein <bmburst...@gmail.com> wrote:
> On Fri, Aug 15, 2014 at 10:45 AM, Levente <leventel...@gmail.com> wrote: > >> I am thinking about putting an SQLite database on a flash drive (SD card). >> I would like to know what kind of file system is the optimal. I want to >> query a lot, and insert or update a few times per minute. > > Not directly related to your question, but I am curious: Since sqlite's > atomicity guarantee assumes that write to a disk sector are linear (e.g. > either beginning to end or end to beginning, but never the middle before > either end), Mmm ... no. SQLite's atomicity assumes in-order execution of writes. In other words that the changes to the storage device will be done in the order they are requested. Anything else breaks ACID. However, SQLite does a lot of things to try and avoid a corrupted database even if ACID is broken, in an effort to let you rescue the maximum amount of data even if something went wrong with hardware. Removing all the 'paranoid about hardware' and 'rescue' code from SQLite would make it significantly smaller. > I was wondering if this assumption was ever verified for flash > drives. I have no information to contrary, but while that assumption makes > a lot of sense for a spinning disk, I see no reason to assume this for > flash drives and other SSDs. I would think that they may parallelize writes > to different parts of the "sector" for efficiency. A good question. By which I mean it's one people should worry about, not one which we're not sure about. No. Physical writes to Flash drives are optimized for best use of hardware (minimum writes, promoting maximum lifetime for the device and fastest execution of programs). Worse still, there is no guarantee that any writes are done to the device at all until you have issued 'unmount' to it (shown to the user as 'Eject' or 'Make safe for removal' on popular popular operating systems). Until then, as far as you're concerned, all the changes may be made only to a cache held by the device driver. So if you pull your Flash drive without telling the OS to unmount it you can get all sorts of problems, including no files at all if they were created during that session. It's rare to see this actually happen (few systems create a cache that reflects the whole drive then never bother to flush it) but the specs of the hardware involved do allow it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
