Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite small blob storage: 35% Faster Than the Filesystem

sqlite.org

Post reply on HN
Print view View original

81–90 of 208 posts

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#81

Post by diroussel »

imron wrote: post #58
inetknght wrote: post #37

Earlier quoted context omitted.

> Writing your own methods to retrieve arbitrary chunks of data from a monolithic file would be a lot of work. Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database. (it really depends on what language and/or library(ies) you use and for what purpose)

> Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database. It's not interacting with a file, it's interacting with a file that contains other files of varying sizes that need to be accessed randomly with good performance.

If you only want to read then both zip and tar are good for this purpose. It's commonly used and known to work well. There are many formats that are really just a renamed zip file. Like jar, and epub. File entries don't have to be compressed so you can mmap.

If you want to do random writes then more consideration is needed.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#82

Post by kccqzy »

Myrth wrote: post #45
elchief wrote: post #29

Earlier quoted context omitted.

It doesn't have to be. There's an in-memory option

I wonder if ram fs would perform exact the same as sqlite in memory.

I would believe ramfs would be slower, as there will be additional system calls needed.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#83

Post by Quarrelsome »

wyc wrote: post #35

I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.

and what exactly do you think any database is at the end of the day? It's not magic, it all ends up on the disk as a file. I mean why is a page file called a FILE?

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#84

Post by Quarrelsome »

songzme wrote: post #56

During my first week at American Express I did not have the credentials to install any applications. My manager wanted me to build a prototype and I couldn't install a database. Firebase and other third party was impossible because I had to work on an internal server. I thought SQLite would save my day, but I remembered it being really hard to install and set up. So I had to write my own DB that I called 'stupid-db'.…

> I remembered it being really hard to install and set up.

eh? Sqlite was the quickest thing I ever setup in my life. Just add a .dll and use some form of adapter written in your language and off you go.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#85

Post by imron »

diroussel wrote: post #81
imron wrote: post #58

Earlier quoted context omitted.

> Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database. It's not interacting with a file, it's interacting with a file that contains other files of varying sizes that need to be accessed randomly with good performance.

If you only want to read then both zip and tar are good for this purpose. It's commonly used and known to work well. There are many formats that are really just a renamed zip file. Like jar, and epub. File entries don't have to be compressed so you can mmap. If you want to do random writes then more consideration is needed.

> If you only want to read then both zip and tar are good for this purpose

And programmatically accessing them is significantly more complicated than using SQLite which involves dropping a single header in your project and about 10 lines of code.

> If you want to do random writes then more consideration is needed.

Which you almost certainly what you want to do for the sorts of use cases where SQLite is also under consideration.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#86

Post by Const-me »

SQLite is good when you’re mostly reading.

For writing, the major drawback of SQLite is it doesn’t support concurrent writes.

All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write.

Even embedded chips are multicore these days…

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#88

Post by someonewhocar3s »

Const-me wrote: post #86

SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…

It does keep things /very/ simple.

Treat every core as a node, and enjoy scaling across devices?

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#89

Post by TekMol »

The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency.

For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the data and writes it via fopen(), fputs() and fclose().

This migh be archaic but it has been working fine for years and never gave me any problems.

Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#90

Post by mpweiher »

rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency.

Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite generally did very well except for that point.

Post reply on HN