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

21–30 of 208 posts

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

#21

Post by akira2501 »

function_seven wrote: post #19
nimchimpsky wrote: post #15

Earlier quoted context omitted.

> have great difficulty retrieving Not that great, and the point is its comparing apples to oranges, pointless.

Writing your own methods to retrieve arbitrary chunks of data from a monolithic file would be a lot of work. Using map tiles as the example, how do you easily retrieve just the tiles in a specific region? Ok, how about all tiles that have the “hasLand” attribute set? Or the “containsCoastline” attribute? You’d end up rewriting your own version of a database.

> You’d end up rewriting your own version of a database.

This is the hell we just crawled out of in one of our codebases.. it's not so much the access for a single process, but when you want to share the data in the file, that's when it seriously starts to get messy and breaking through otherwise clean abstraction layers and/or forcing recompile on compatibility breaking changes.

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

#23

Post by paulddraper »

jstimpfle wrote: post #17
paulddraper wrote: post #13

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.

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

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

#24

Post by eridius »

jstimpfle wrote: post #22

This page does not live up to the standards of the other high quality technical articles on sqlite.org. I couldn't even find a reference to the file system used for comparison. But the whole setup is a farce anyway.

They tested it on 5 different OSes, which presumably included 3, probably 4 different filesystems (Windows, Mac, and Ubuntu would be using different ones, and I'm guessing the Ubuntu system has a different filesystem than Android too, though I don't actually know), so it doesn't really seem like it matters.

And why is the whole setup a farce anyway?

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

#25

Post by joesb »

jstimpfle wrote: post #20
joesb wrote: post #16

Earlier quoted context omitted.

So you never think that using database to store file will be slower than using file to store file? "Duh, I always knew that" is easy to say when you don't have to provide proof. Also, are you sure that CSV will be faster as well? Do you have a benchmark for it?

"Measure before optimizing" has limited application. There are many things that need not be measured. Appending to a file is definitely not slower than doing memory management inside the file to allocate a new chunk, seeking to that position and then writing out the chunk. Period. (And the serialization overhead is negligible compared to the disk I/O).

It's not only "writing" though. It's "writing" AND "reading".

Can you read the correct line in in CSV faster? Can you find the row with specific critiria like SQLite faster? Can you handle concurrency correctly like SQLite? Can you make sure your CSV is always in consistent state like SQLite?

The point of the article is that SQLite is still fast even with all the benefit of database.

The idea of SQLite is that you could default to using it, and move away when it hits its limit. Nobody should default to using CSV, you use CSV when you have to, despite all its limitation.

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

#26

Post by adventured »

akira2501 wrote: post #21
function_seven wrote: post #19

Earlier quoted context omitted.

Writing your own methods to retrieve arbitrary chunks of data from a monolithic file would be a lot of work. Using map tiles as the example, how do you easily retrieve just the tiles in a specific region? Ok, how about all tiles that have the “hasLand” attribute set? Or the “containsCoastline” attribute? You’d end up rewriting your own version of a database.

> You’d end up rewriting your own version of a database. This is the hell we just crawled out of in one of our codebases.. it's not so much the access for a single process, but when you want to share the data in the file, that's when it seriously starts to get messy and breaking through otherwise clean abstraction layers and/or forcing recompile on compatibility breaking changes.

If you don't mind answering, out of sheer curiosity how was the data stored in the individual file/s?

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

#27

Post by visarga »

tarasglek wrote: post #5

This is weird benchmarketing. They are comparing reading/writing 100,000 individual files vs writing 100,000 entries into a single file(sqlite database). For comparison one could concatenate the same data into a single big file even faster than into sqlite. They then do not offer logical analysis as to why things are faster. My understanding is that reads are probably faster due to operating system readahead being ab…

No, no, if it is faster than the filesystem it must mean that it has a faster storage medium. Maybe it is writing to memory, or the aether. /s Now, seriously, how can the foot be faster than the shoe?

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

#29

Post by elchief »

notacoward wrote: post #28
craigds wrote: 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

FYI, in sqlite is still on disk.

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

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

#30

Post by meritt »

joesb wrote: post #16
meritt wrote: post #11

If I asserted: "It's faster to put 10,000 rows of csv data in single file instead of 10,000 individual files" even the most junior programmer would likely say "Well, duh, it's 1 file instead of 10,000". Yet this benchmark is at the top of HN for some reason.

So you never think that using database to store file will be slower than using file to store file? "Duh, I always knew that" is easy to say when you don't have to provide proof. Also, are you sure that CSV will be faster as well? Do you have a benchmark for it?

I'm not attempting to compare the usefulness of CSV to a RDBMS. I'm simply pointing out it should be inherently obvious that writing to 1 file is faster than writing to N files, which is the only thing the benchmark is in question is concerned with as well.
Post reply on HN