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

41–50 of 208 posts

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

#41

Post by jstimpfle »

eridius wrote: post #24
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?

Of course it matters. Different file systems have different performance characteristics. Make sqlite into a file system, and I'm pretty sure you can't beat it by putting another sqlite on top.

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

#42

Post by dragonwriter »

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.

> However, for better or worse, it's a flat file.

No, it's not. It's a file, but it's not flat.

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

#43

Post by rpdillon »

meritt wrote: post #30
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?

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.

The overhead of the database could dominate the gains from only working with a single file. It seems that in some cases, that may not be true.

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

#44

Post by wyc »

dragonwriter wrote: post #42
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.

> However, for better or worse, it's a flat file. No, it's not. It's a file, but it's not flat.

You are technically correct. The best kind of correct!

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

#46

Post by function_seven »

inetknght wrote: post #37
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.

> 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)

As far as I know, querying a database has always been easier than querying a custom file format. It's the whole reason databases exist in the first place. And using one off the shelf is clearly easier than writing your own.

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

#47

Post by 0xbadcafebee »

wyc wrote: post #44
dragonwriter wrote: post #42

Earlier quoted context omitted.

> However, for better or worse, it's a flat file. No, it's not. It's a file, but it's not flat.

You are technically correct. The best kind of correct!

They were not technically correct, they were just correct. A flat file database is a file with no indexing structure, usually plaintext, usually one record per line. SQLite uses a binary format consisting of an index of pages and a rollback journal or log file. They are significantly different.

Also, yes, there are networked databases with sqlite database drivers.

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

#48

Post by eridius »

jstimpfle wrote: post #41
eridius wrote: post #24

Earlier quoted context omitted.

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?

Of course it matters. Different file systems have different performance characteristics. Make sqlite into a file system, and I'm pretty sure you can't beat it by putting another sqlite on top.

When the author has tested 3 or 4 different filesystems against SQLite (and at least 2 of them you can be pretty sure as to what they are simply based on the OS, with a good guess at the other 2 as well), and SQLite beats them all, then it really doesn't matter.

As for turning SQLite into a filesystem, that's not really going to work. SQL isn't designed to support things like cheap "file" appends or reading only portions of a value or seeking or anything like that, so you'd end up having to read the entire value for any read, and write a new copy of the entire value for any write, and your performance would be really really bad. So yeah, putting SQLite inside of SQLite isn't going to work, because SQLite isn't a filesystem and wasn't designed to behave like one. Not to mention this entire article is about small blob storage, and embedding a SQLite database inside of SQLite isn't a small blob.

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

#49

Post by jstimpfle »

paulddraper wrote: post #23
jstimpfle wrote: post #17

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?

You got me wrong. I explained why appending to a CSV is faster than inserting into a "real" database.

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

#50

Post by i_feel_great »

asmosoinio wrote: post #39
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

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

There is some mention of that here: https://www.sqlite.org/rtree.html
Post reply on HN