Live data from Hacker News

SQLite small blob storage: 35% Faster Than the Filesystem

sqlite.org

71–80 of 208 posts

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

#71

Earlier quoted context omitted.

Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.

Prompting the question: why isn't the whole disk just a SQLite database?

The parent comment is a bit misleading, or completely wrong. SQLite also has to allocate blocks in the database for anything you store. Some of the structures and techniques that SQLite uses for doing this are very similar to the way a filesystem does it.

Instead, think about it this way. With a filesystem, the database is managed by the kernel. Every time you want to read a file, you might do four system calls: open, fstat, read, close. Or you might do mmap instead of read, but you're still doing four context switches, at least in typical cases. Switching to the kernel and back has cost. Normally this cost is small, but if you make a lot of system calls you'll notice the costs piling up. The kernel also has to check permissions to make sure that you have permission to read the file.

With SQLite, the database is inside your application. When you read a row, there's a chance that the row is already in your application's memory. This means no context switches back and forth between application and kernel.

Additionally, when you read a row, the entire database page is read into memory, which includes other rows too. The kernel won't do anything like that with your application--it won't give you img1.png and img2.png if you just ask for img1.png. Maybe they'll both be in the kernel's page cache, but you still have to open and read the file.

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

#72
From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows."

I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and the application should go through some extra hoops to scan the content before saving it to blob, and this should be measured and part of the comparison.

Also, if the SQLite files are exempt from AV scan, then the level field should also exempt the uploaded files folder in test. I mean, knowing the dice are loaded and then claiming it as an advantage does not seem professional.

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

#73
post #61
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'.…

??? SQLite is a library, not a database server. There's no install step - it's a single .C file (and a header, if you're into that). Most of the major scripting languages have rock-solid bindings that don't require any additional system libraries or software installs. Python (in the stdlib!): https://docs.python.org/2/library/sqlite3.html Nodejs (prebuilt): https://www.npmjs.com/search?q=sqlite Ruby (needs libs, whic…

you are right. this was also many years ago so my memory might fail me. Perhaps my internal server didn't have access to npmjs.

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

#74

Earlier quoted context omitted.

Your intuition far surpasses mine. I for one am interested in the result and the reason.

Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.

This analysis is incorrect. SQLite must allocate database pages for rows just like the kernel must allocate filesystem blocks for files. Reading a row in SQLite means traversing a B-tree to find the row. Reading a file from the filesystem means traversing a B-tree to find the file metadata. Very similar. The kernel doesn't need to "seek" any more than SQLite does, the article says that all the hard drives are SSDs which don't have heads so they don't seek.

The major differences here are due to the different levels of isolation and authentication that the kernel and SQLite provide. Another difference is the fact that when you find a row in SQLite by primary key, you don't have to do a second lookup to get the data from the metadata. This is a good optimization to use when storing many small pieces of data, but a bad optimization for large pieces of data.

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

#75
Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file only once, and from then on only seek and read.

SQLite is a big code base, using a simple archive file format instead gives you most of the advantages, but without the bloat.

PS: This is mostly for read-only situations though. Using SQLite probably starts to make sense when the applications needs to write to and create new files in the archive.

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

#76

Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file…

But that would work only for static content, like the game assets. The discussion of BLOBs vs. filesystem comes up mostly in the context of content management and user/app uploaded content.

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

#77
post #76

Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file…

But that would work only for static content, like the game assets. The discussion of BLOBs vs. filesystem comes up mostly in the context of content management and user/app uploaded content.

Yes you are right. I wrote the 'PS' before I saw your comment. Apologies :)

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

#78
post #12

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles

Interesting and very relevant to the discussion! Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?

GeoPackage is a great geodata format on sqlite. Also check out spatialite.

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

#79
post #12

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles

Interesting and very relevant to the discussion! Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?

Try SpatialLite[0] if you need advanced geometry capabilities. Has R-Tree spatial indexes if you use SQLite 3.6+, and rudimentary MBR index for prior versions. Has rudimentary support for curves as well.

[0]: https://www.gaia-gis.it/fossil/libspatialite/index

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

#80
post #45
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.

The article speculates that the overhead of calling `fopen` for each file and subsequently reading it is more expensive than reading from an already opened database file.

But as for how expensive `fopen` is really depends many factors.

Post reply on HN