On 9 Mar 2011, at 13:05, Igor Tandetnik wrote: > liran ritkop <rejt...@bgu.ac.il> wrote: >> Ok, I'll do it that way, it's a smart idea. >> The question is, if i use files and not blob data, when i want to update the >> data (this time the file, and not the blob field) it can lead to some kind >> of fragmentation. > > You are using Flash storage - why do you care about fragmentation? There are > no disk head seeks to be concerned about. In fact, file systems designed for > Flash often intentionally introduce fragmentation, for wear leveling. > >> Does anyone know, if sqlite know how to deal with it, if i >> save it in a blob field? > > SQLite just uses the underlying file system operations. It has no control > over fragmentation. > >> and if i save it in a file, so does ext4 filesystem >> for example, deals with it better than sqlite? > > This question doesn't make much sense. SQLite database is a file. SQLite uses > the file system to read and write to that file. It's up to the file system > how this file is physically written to the disk.
There are a couple of things you could call 'fragmentation', one of which you can't control very easily (and also doesn't matter on flash storage), and one that you can control (and potentially should worry about). Fragmentation in the Windows defrag.exe sense is indeed irrelevant. However if you put stuff in a blob field it gets stored in the database file, which will 'fragment' your database by dispersing your other data across more of a byte range in that file. This will affect how your database interacts with the operating system's cache: most operating systems will prefetch/cache data fairly linearly on a per-file basis, so if there's a lot of large blobs you don't really care for in most queries dispersed through your database file you'll be squandering a lot of that benefit. The most significant downsides to storing the data which would have been in blobs outside your database is that you can end up with the database identifying something which no longer exists or with a file existing which has been logically deleted from the database; you will need to do extra work to deal with those issues. Another alternative which is qualitatively somewhere between the two discussed so far is to keep your blobs in a separate database attached to the first: this means that your main database file won't be 'fragmented' and provided you aren't using WAL mode and you are using transactions correctly you should never have missing or orphaned blobs. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
